UPDATED XenDesktop Desktop and Application Usage Report

 Powershell, XenDesktop  51 Responses »
Jul 252016
 

As in my previous post we are querying the Citrix monitordata tables in SQL instead of doing web queries against ODATA because it is super fast.

The script needs to run as a user who has at least read rights to the database.  Just like the original it will query the monitordata tables to gather the information, but I think I have improved on the queries a bit.  Also, just like the original it gets a lot more information than what is actually needed, so the queries can be used again for other information (logon duration reporting, accessing outside vs inside report, client version reports, etc.).  Feel free to edit and use this script as you see fit.

Here is the updated script!  Pay attention to anything marked with multiple hashes (####).  It is referencing something you need to change for your environment (ie: sql server name), or items of note.

Here is an example of the updated report.

Report

XenDesktop Usage Report

 Powershell, XenDesktop  20 Responses »
Jul 212016
 

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.

Capture