The first part is to extract the session data. In View's database is a table called "View_Events", which logs all sorts of things about the environment (read KB article here). For my interests, I noticed it logged and event when a user got connected, and when they disconnected. I didn't care about when they were logged in, because most of us use the lab for short sharp demos, and tend to leave our desktops logged in all the time, and then quickly jump in to do a demonstration, and then jump off again. It was only the ACTIVE session count and length of stay that I wanted to know. In particular:
- How frequently were people connecting to the lab, and
- How long were people in session for (quick demo, longer demo, or working on something bigger like an all-day marketing event)
USE View_Events
SELECT LoginEvents.UserDisplayName AS Username, LoginEvents.EventID AS LoginEventID, LoginEvents.Time AS TimeIn, LogoutEvents.EventID AS LogoutEventID, LogoutEvents.Time AS TimeOut, LoginData.StrValue AS SessionId, LogoutEvents.Time - LoginEvents.Time AS SessionTime
FROM VE_user_events_hist AS LoginEvents INNER JOIN VE_event_data_historical AS LoginData ON LoginEvents.EventID = LoginData.EventID INNER JOIN VE_event_data_historical AS LogoutData ON LoginData.StrValue = LogoutData.StrValue INNER JOIN VE_user_events_hist AS LogoutEvents ON LogoutData.EventID = LogoutEvents.EventID
WHERE (LoginEvents.Module = N'Broker') AND (LoginEvents.EventType = N'BROKER_USERLOGGEDIN') AND (LoginData.Name = N'BrokerSessionId') AND (LogoutData.Name = N'BrokerSessionId') AND (LogoutEvents.Module = N'Broker') AND (LogoutEvents.Module = N'Broker') AND (LogoutEvents.EventType = N'BROKER_USERLOGGEDOUT')
ORDER BY LoginEvents.Time DESC
If you are good at reading SQL, you might notice that the last column selected, which I call "SessionTime". This is actually a SQL calculation of logout time minus login time. This tells me how long the person was connected for.
The results look a bit like the below, when extracted as raw CSV.
MELB\nwheat,28366,2013-11-20 21:47:39.513,28370,2013-11-20 22:07:45.483,e2fc4503_1633_4634_8e3b_bdd8dc098438,1900-01-01 00:20:05.970
Putting it through the Excel wringer, I turned it into something a LOT more palatable, as below. I also add some further calculated fields, which helps me turn it into a pretty PivotChart. The bolded fields are the ones I used for my report.
- Username
- I performed a find and replace to remove the unneeded 'DOMAIN\' part.
- LoginEvent
- Completely ignored field, but is the ID for the BROKER_USERLOGGEDIN event.
- LoginTime
- Event timestamp, ignored hereafter.
- LogoutEvent
- Completely ignored field, but is the ID for the BROKER_USERLOGGEDOUT event.
- LogoutTime
- Event timestamp, ignored hereafter.
- SessionID
- This magic field is present for both the Login and Logout event and connects the login/logout events together so I can calculate the session duration!
- SessionTime
- This is the field calculated in the SQL query. Unfortunately, it comes through as a timestamp, which Excel displays as "one day plus the calculated time", so I convert it below.
- SessionLength
- Added in Excel to remove the additional "day" in the timestamp above.
- Formula is {"=[@SessionTime]-1"}
- Short
- Added in Excel, to be "1" if the SessionLength is less than 30 minutes (1 day / 48)
- Formula is {"=IF(([@SessionLength]<(1/48)),1,0)"}
- Medium
- Added in Excel, to be "1" if the SessionLength is more than 30 minutes but less than 90 minutes.
- Formula is {"=IF((AND([@SessionLength]>=(1/48),[@SessionLength]<(1/16))),1,0)"}
- Long
- Added in Excel, to be "1" if the SessionLength is more than 90 minutes.
- Formula is {"=IF(([@SessionLength]>=(1/16)),1,0)"}
- Month
- Added in Excel, month extracted for my PivotChart later.
- Formula is {"=(MONTH([@LoginTime]))"}
- Year
- Added in Excel, month extracted for my PivotChart later.
- Formula is {"=(YEAR([@LoginTime]))"}
Phew! That was a bunch of playing around, and surely someone can quickly make a template or macro out of this. But seeing as I only need to churn out a report every 6 months or so, I haven't bothered as yet. The resulting table looks like the below.
| Username | LoginEvent | LoginTime | LogoutEvent | LogoutTime | SessionID | SessionTime | SessionLength | Short | Medium | Long | Month | Year |
| asingleton | 1794 | 30/6/14 10:58 PM | 1796 | 30/6/14 11:07 PM | 7f6bfad3_7000_4d69_88d2_1c17e7276e02 | 24:08:34 | 0:08:34 | 1 | 0 | 0 | 6 | 2014 |
| gorchard | 1790 | 30/6/14 8:49 PM | 1800 | 1/7/14 12:36 AM | ceb28d1a_8bad_47cb_80c4_a793e9dc42ce | 27:47:22 | 3:47:22 | 0 | 0 | 1 | 6 | 2014 |
This told me everything I need to know, but it's not really in "Management language" - by which I mean a pretty graph! The last part is to quickly turn this into a PivotChart using the Excel wizards. The only difference being that I interpret "short" sessions to be "Quick demo", "medium" to be "Full demo", and "long" to be "Event or workshop".
The pretty output of my report is shown below. Vertical axis is session count, and horizontal axis is the first six months of this year.
This did in fact result in some money being spent on our environment, and partly because I was able to show the usage frequency, and indicate what kind of thing people are doing in our View environment. I hope you've found this useful, and that you can do this directly yourself in your environment, or enhance this report even further with a bit of tinkering. Please contact me if you'd like the sample file that goes with this (although I'm sure you can re-create), or if you have a better version you'd like to contribute back!
