Tools and Utilities

Well I started work in my new version of vAudit, making more functionality and most important support for View 5. In case you do not know what vAudit is, it is mainly a tool to understand who and when is using your View environment. This can help you see how well the adoption of your VDI systems is going, when not to plan maintenance, etc.

In the old version of vAudit I used WMI to query the event logs of the brokers to see who was logging in and out. Since View 4 the products comes with the option to store all events in an events database (Microsoft SQL or Oracle). So for this new version this is what I want to use. Unfortunately VMware’s View engineers are not easily storing the event data (and this is an understatement!). So it took me a while to even get some basic information out of the system. As my vAudit is not ready for release yet, I thought I would at least share some SQL statements with you, in case you want to start cracking

Checking daily max concurrent users.
Well this is the easiest (and the only easy thing) to do. As the broker service daily writes an event 5 minutes to midnight in the events database on that days max concurrent user. You can easily get this using a query like this: (MS SQL)

select Count, Time from(select top 30 dbo.view_event_data_historical.IntValue as 'Count', dbo.view_event_historical.Time as 'Time' from dbo.view_event_historical,dbo.view_event_data_historical where dbo.view_event_historical.EventID = dbo.view_event_data_historical.EventID and  dbo.view_event_data_historical.Name = 'UserCount' and dbo.view_event_historical.EventType='BROKER_DAILY_MAX_USERS' order by dbo.view_event_historical.Time DESC) A Order by Time

This might seem like an extensive way of doing this, but it will allow you to control the last so many days to query. Just change the ‘top 30′ to any amount of days.

With this data you can chart a nice daily max concurrent user overview. See my screenshot of the new vAudit release. (I did alter the data in the database to make it look like I have a lot of sessions).

NOTE: also on the name of the tables. When you install view, it asks for a table prefix. In my case I supplied “view” as prefix. So make sure to check your prefix and modify the table names based on that.

Session overview

If you want to make more sense of the sessions used by your users, it becomes more of a challenge. Each time a users logins to the broker an events is written. The same for when they session is broken/loggedout. In the event table these events are not easily connected so you would have to query for each the event_data table as well to get the broker session ID and match them all up. The super irritating thing is that the time stamp is NOT in the event_data table but just in the events table. This would else have made it into a very easy query. So after a lot of googling, cursing and wishing I wan an SQL expert, here is what I came up with.

drop table #sessions
drop table #logoffs
drop table #users

create table #sessions (SessionID varChar(32), StartSession datetime, EventID int)

insert into #sessions (SessionID, StartSession, EventID) select view_event_data_historical.StrValue, view_event_historical.Time, view_event_data_historical.EventID  from view_event_historical, view_event_data_historical  where view_event_historical.EventID = view_event_data_historical.EventID and view_event_historical.EventType = 'BROKER_USERLOGGEDIN' and view_event_data_historical.Name = 'BrokerSessionId'

create table #users (EventID int, username VarChar(512))

insert into #users (EventID, username) select view_event_data_historical.EventID, view_event_data_historical.StrValue from view_event_data_historical, view_event_historical  where view_event_historical.EventID = view_event_data_historical.EventID and view_event_historical.EventType = 'BROKER_USERLOGGEDIN' and view_event_data_historical.Name = 'UserDisplayName'

create table #logoffs (SessionID varChar(32), EventID int, EndSession datetime)

insert into #logoffs (SessionID, EventID, EndSession)select view_event_data_historical.StrValue, view_event_historical.EventID, view_event_historical.Time from view_event_historical, view_event_data_historical  where view_event_historical.EventID = view_event_data_historical.EventID and view_event_historical.EventType = 'BROKER_USERLOGGEDOUT' and view_event_data_historical.Name = 'BrokerSessionId'  

 select #sessions.SessionID, #sessions.StartSession, #logoffs.EndSession, #users.username from #logoffs, #users, #sessions where #logoffs.SessionID = #sessions.SessionID and #users.EventID = #sessions.EventID

The query uses some temporary tables to help match the session ID’s, get the Start time from one event, the end time of an other event and get the displayname of the user. See the picture of my result.

Next step would be to use this data to make daily charts on when most uses are logged in and out, users stats like average session time, etc. So more to come

Just to extra clarify what this query does, it check sessions to the Broker! not to the actual VMs. You can do this as well, but even more complicated (as those do not have unique IDs like BrokerSessionID). The the list shows people logging into the broker, but you will NOT know if they started one, two or more virtual machine sessions.

If you have any SQL work for View you want to share, please do! If you can optimize my lame-ass sql queries, please do

I will hopefully soon be posting a beta of the next vAudit that does all this and translates it into nice charts.