CLICK HERE FOR UPDATED VERSION

My boss wanted a report to show the usage of our XenDesktop environment (desktop sessions only), so I wrote a script that queries the delivery controller once per minute to get sessions.  It then writes that information to an .xml file, and then I have another script that reads the .xml file to report on usage.  Well, that is a bit complicated, and all that data already exists in the ODATA in director.  I have demonstrated how to query that data in a previous script I wrote to get logon times, but gathering each web table and then combining can take a long time.

That ODATA information is also in the monitordata tables of your Citrix database (which is split off into a separate database from the main database after 7.8 I think), and querying SQL is A LOT faster!

The script I wrote needs to be run as a user who has at least read rights to the database.  It will query the monitordata tables and combine the information into an array which can output the information we want.  For example if you query monitordata.session it will give you all the sessions, but the only indication of which user launched that session is the UserID field… which is just a number.  You have to join monitordata.session and monitordata.[user] in order to figure out who it actually was in that session.  The query I wrote actually gets a lot more information than is needed for this script, so I can use it as a basis to write more scripts.  Feel free to use this as a base for your own scripts… if you do note that all the date/time values in the database are UTC time.

Here is the script!  Be sure to pay attention to anything marked with ####

Here is an example report email.