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.
24 Responses to “XenDesktop Usage Report”
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] PowerShell script to query XenDesktop monitoring database and display session count and total/avg se… – CTA David Ott […]
NICE
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
I need to show session usage for last 24 hours based on Delivery groups. Please suggest
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual Monitoring […]
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
That looks to be like an authentication issue with your SQL server
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”} | %{
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
That’s because you weren’t able to pull data from SQL due to a bad logon.
how to get it to write to a html file?
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
RHEL + oVirt
That’s another option!
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
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
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.
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!
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]
[…] David Ott XenDesktop Usage Report shows that querying OData can be slow and it’s sometimes faster to query the actual […]