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

  24 Responses to “XenDesktop Usage Report”

  1. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  2. […] PowerShell script to query XenDesktop monitoring database and display session count and total/avg se… – CTA David Ott […]

  3. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  4. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  5. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  6. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  7. I need to show session usage for last 24 hours based on Delivery groups. Please suggest

  8. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  9. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual Monitoring […]

  10. i am getting below error

    Exception calling “Fill” with “1” argument(s): “Login failed. The login is from an untrusted domain and cannot be used
    with Windows authentication.”
    At C:\scripts\xendesktop_usage_report.ps1:123 char:1
    + $SqlAdapter.Fill($DataSet)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    You cannot call a method on a null-valued expression.
    At C:\scripts\xendesktop_usage_report.ps1:223 char:15
    + $sessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

  11. i am getting below error

    At C:\scripts\xendesktop_usage_report.ps1:123 char:1
    + $SqlAdapter.Fill($DataSet)

    You cannot call a method on a null-valued expression.
    At C:\scripts\xendesktop_usage_report.ps1:223 char:15
    + $sessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  12. how to get it to write to a html file?

  13. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  14. RHEL + oVirt

    That’s another option!

  15. Hello
    the scripts calculate also disconnected time inside the session as usage time.
    there is way to customize not include it in the usage ?

    Thanks

  16. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  17. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  18. Hi! I have an environment of 25000+ users.

    The script takes ages (more than 1 month) to fetch the data of 1 week.

    Is there any way to speed it up? All I want is just the user name and login time in an automated report.

  19. Hi,
    your script is very good for session export!
    I fill the informations in row 142-146 but I receive the following errors
    Can you help?
    The problem is not the authentication

    Exception calling “Fill” with “1” argument(s): “Incorrect syntax near ‘####’.”
    At C:\report_1.ps1:119 char:1
    + $SqlAdapter.Fill($DataSet)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    You cannot call a method on a null-valued expression.
    At C:\report_1.ps1:213 char:15
    + $sessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Thanks for your time!

  20. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

  21. […] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]

Leave a Reply to candy Goldsmith Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

(required)

(required)

This site uses Akismet to reduce spam. Learn how your comment data is processed.