Monday, August 25, 2014

Report on View session history - How busy is my lab?

I was asked a while ago to help justify spending some money on our shared lab environment, which we use for customer demonstrations.  The question really was "How much do people use the demonstration lab, really?"  So, I thought there must be a way within VMware View to help figure it out.  There was, and it got turned into a pretty little graph, as I'll show you below.

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)
There was no natural way to show session duration, but the data can be derived from the session ID being attached to both the CONNECT event and the DISCONNECT event.  These show up as login/logout events on the broker - "BROKER_USERLOGGEDIN" and "BROKER_USERLOGGEDOUT". Have a long look at my query below, and it'll make sense when you compare it with the results.
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!

Monday, January 20, 2014

VCAP-CID and VCAP-DCD exam experiences

I recently studied and passed the VMware Advanced Certified Professional (VCAP) level exams for both Cloud Infrastructure Design (VCAP-CID) and Data Center Design (VCAP-DCD).  It was an interesting experience, and well worth the process for any considering them.

I was part of a study group with @GrantOrchard and @Josh_Odgers, and those guys really helped me focus on a study structure.  In both exams, our general approach was to work through the Exam Blueprint, which has been the strong recommendation by VMware Education and pretty much everyone else who cares to comment!  I can validate that approach.  Using the Blueprint, you know exactly what you're going to be examined on, and it will draw your attention to your weaker areas.

My natural behaviour was to skim through the blueprint and ummm and ahhh at each section, thinking about what it meant.  In my more productive moments, I would feel a little nervous and be prompted into a sideline of reading the Best Practice papers for the area.  When getting into the group, however, this casual attitude was firmed up into some very useful whiteboarding exercises.  Some of the exercises were drawing up a table - like a permission or role matrix.  This was the content which I just had to memorise, as the "logic" you might use in your own workplace could be irrelevant for the exam.  The exam is looking for certain organisational roles, and choices of permission management, which may have no bearing on how YOU would do it in real life.

For the VCAP-CID, almost everything you need to know is in the vCAT (plus a little bit of Chargeback docs).

The absolute BEST exercises were modelling the design scenarios, of which there are a few in each exam.  These take some time to answer, and are not always easy to draw in the exam, so having them clear in your mind is a great start.  For each of the study areas, we would try to imagine "What would be a design exercise for this?" - and then try to draw out a scenario.  This was awesome in a group, because inevitably it would start a discussion or argument about exactly what might be asked, and exactly what a "good answer" might contain.  For me, this is where the rubber hit the road.  Once we had a good set of scenarios we had worked through, we would then be in a good position to wonder how it might expand/change, or if we were missing a scenario so far.

My VCAP Design study tips


  • Find some colleagues, and study in a group.  If you're semi-confident, then a minimum of 5-6 sessions would be a good idea.  
  • Follow the Exam Blueprint as your study blueprint.  It tells you what you will be asked.  Pore over it and make sure you could answer questions about all the areas.
  • If you're feeling weak in areas, download the Best Practice whitepaper for the topic and make sure you understand the content, and why recommendations are given.  I found the below ones the most useful for me.
  • Get a whiteboard.  A big one!
  • For each study area, pick two design scenarios you think might come up and work through them in the group.  Nut it out, argue about it, ask what else they might want.  Don't forget you'll be starting from Business Requirements, so that has to be the start of the scenario!

Are you ready?

Well, that is something you will only know AFTER the exam!  However, my guidance on the exams is below.  Do take it with a grain of salt, as everyone will have a different experience.

VCAP-DCD

In my opinion, this exam was quite good, and not particularly scary.  Trying to sit down and focus for 3.5 hours is the biggest problem for candidates.  I reckon I was mentally "done" by about 2 hours into it.  So a VERY good sleep the night before is recommended - don't sit up studying late the night before.  It will hurt you.

Anyone that has a VCP-DV has the vSphere technical knowledge, I think.  In addition to this, the rest of the knowledge comes from using that feature set with customers' real problems.  I would say that if you have been spending the last 2-3 years implementing vSphere or acting in a (modestly detailed) technical pre-sales capacity for vSphere solutions, then you should be right to go.

I have recommended that ALL my VMware SE colleagues just go for it.  Both @GrantOrchard and @DemitasseNZ recommended that I just book it without study.  While I did study beforehand, it probably didn't help much - they were right.

VCAP-CID

Now, this one is a different kettle of fish.  This exam was challenging on more ways than one.  I believe it is perhaps still a little new in the sense that the exam content is nowhere near as refined/evolved as the DCD exam.  I found quite a few of the questions were either (a) unanswerable because of vagueness or ambiguity, or (b) unanswerable because the answers presented all seemed wrong.  I am happy to trust that the exam could be 100% correct and I am a bit thick, but for some questions I was still unable to derive a correct answer even several days later, when thinking back on it.

The other challenge was that at least one of the design scenarios seemed to be broken. That is, I couldn't correctly connect up the elements, no matter what I tried.  Perhaps I was marked correctly anyway, but I doubt it - and the drawing tool just would not play ball.

I failed my first attempt at this one (VCAP-CID), and so I can verify that it was not a one-off problem.  I also noticed that the question pool must be quite small, so unfortunately my second sitting was probably a bit unfair as I found myself in front of a lot of familiar (and previously considered) questions.

Failure?

My biggest factors in failing the VCAP-CID exam the first time around were two that are well known among all other candidates:

  • Lack of sleep the night before.  I had trouble sleeping due to an unrelated event, so while I got to bed early, I spent a lot of time listening to the night pass by!  This meant my brain was struggling to concentrate, and most questions took 2-3 reads before I understood what was needed.
  • Time management.  I ran out of time.  In fact, on a "pro-rata" basis, I got about the same score on both attempts, for the questions I got to.  The first time I got nearly three quarters through, and just failed by a few points.  The second attempt I finished quite early, and passed pretty well.  I thought I was managing OK the first time, but obviously my brain was operating at an entirely too slow rate!  (Due to the first factor above).

Conclusion

If you've been a "vSphere guy/gal" for a while, and kept your VCP status up to date, just do the VCAP-DCD.  It's a fine exam, and is a good test of what you're probably doing day to day anyway.

If you attempt the VCAP-CID, be prepared for a poorer quality exam, and a lower achievable score (I think).  And keep the vCAT close...

Good luck!