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

  30 Responses to “UPDATED XenDesktop Desktop and Application Usage Report”

  1. […] querying OData can be slow and it’s sometimes faster to query the actual Monitoring database. Updated Report. […]

  2. Hello, we are receiving the following error when running your script, can you please assist where we may be going worng? our DDC’s are version 7.9.0.43

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

    Many Thanks..

    • Are you sure you’re getting the data correctly from SQL? If you have the script in ISE, run it -ignore the error, and then run $sessions… does it display data?

  3. […] querying OData can be slow and it’s sometimes faster to query the actual Monitoring database. Updated Report. […]

  4. Hi
    Like the script a lot.
    I’ve tried to update the script so it includes the Delivery Group but don’t succeed in doing this.
    Do you have any tips on how I can do this?
    Thanks!

    • In the SQL query add the following line to the select area
      “,desktopgroup.Name”
      no quotes of course

      • Hi, sorry for the late reply. Didn’t have time to test it before.

        I added that line, without quotes.
        When I execute the script, I get this error:

        Exception calling “Fill” with “1” argument(s): “The multi-part identifier “Desktopgroup.name” could not be bound.”
        At C:\Scripts\XenDesktop_Usage_V2_RunFromServer2012 – Copy.ps1:119 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_V2_RunFromServer2012 – Copy.ps1:211 char:15
        + $sessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

        Any idea?

        • [System.Collections.ArrayList]$sessions = sqlquery -q `
          “select
          monitordata.session.SessionKey
          ,desktopgroup.Name
          ,startdate
          ,logonduration
          ,enddate
          ,connectionstate
          ,username
          ,fullname
          ,monitordata.machine.HostedMachineName
          ,monitordata.desktopgroup.Name
          ,IsRemotePC
          ,DesktopKind
          ,SessionSupport
          ,SessionType
          ,DeliveryType
          ,ClientName
          ,ClientAddress
          ,ClientVersion
          ,ConnectedViaHostName
          ,ConnectedViaIPAddress
          ,LaunchedViaHostName
          ,LaunchedViaIPAddress
          ,IsReconnect
          ,Protocol
          ,LogOnStartDate
          ,LogOnEndDate
          ,BrokeringDuration
          ,BrokeringDate
          ,DisconnectCode
          ,DisconnectDate
          ,VMStartStartDate
          ,VMStartEndDate
          ,ClientSessionValidateDate
          ,ServerSessionValidateDate
          ,EstablishmentDate
          ,HdxStartDate
          ,HdxEndDate
          ,AuthenticationDuration
          ,GpoStartDate
          ,GpoEndDate
          ,LogOnScriptsStartDate
          ,LogOnScriptsEndDate
          ,ProfileLoadStartDate
          ,ProfileLoadEndDate
          ,InteractiveStartDate
          ,InteractiveEndDate
          ,Datediff(minute,logonenddate,DisconnectDate) as ‘SessionLength’
          from monitordata.session
          join monitordata.[user] on monitordata.session.UserId = monitordata.[user].Id
          join monitordata.Machine on monitordata.session.MachineId = monitordata.machine.Id
          join monitordata.DesktopGroup on monitordata.machine.DesktopGroupId = monitordata.desktopgroup.Id
          join monitordata.connection on monitordata.session.SessionKey = monitordata.connection.SessionKey
          where UserName <> ” and SessionType = ‘0’
          $filter
          order by logonenddate,SessionKey” | ?{$_ -notlike “*[0-9]*”}

          [System.Collections.ArrayList]$appsessions = sqlquery -q `
          “select monitordata.session.SessionKey
          ,desktopgroup.Name
          ,monitordata.session.StartDate
          ,LogOnDuration
          ,monitordata.session.EndDate
          ,ConnectionState
          ,UserName
          ,FullName
          ,monitordata.application.Name
          ,PublishedName
          ,monitordata.machine.HostedMachineName
          ,monitordata.DesktopGroup.Name
          ,IsRemotePC
          ,DesktopKind
          ,SessionSupport
          ,DeliveryType
          ,ClientName
          ,ClientAddress
          ,ClientVersion
          ,ConnectedViaHostName
          ,ConnectedViaIPAddress
          ,LaunchedViaHostName
          ,LaunchedViaIPAddress
          ,IsReconnect
          ,Protocol
          ,LogOnStartDate
          ,LogOnEndDate
          ,BrokeringDuration
          ,BrokeringDate
          ,DisconnectCode
          ,DisconnectDate
          ,VMStartStartDate
          ,VMStartEndDate
          ,ClientSessionValidateDate
          ,ServerSessionValidateDate
          ,EstablishmentDate
          ,HdxStartDate
          ,AuthenticationDuration
          ,GpoStartDate
          ,GpoEndDate
          ,LogOnScriptsStartDate
          ,LogOnScriptsEndDate
          ,ProfileLoadStartDate
          ,ProfileLoadEndDate
          ,InteractiveStartDate
          ,InteractiveEndDate
          ,Datediff(minute,logonenddate,DisconnectDate) as ‘SessionLength’
          from monitordata.Session
          join monitordata.[user] on monitordata.session.UserId = monitordata.[user].Id
          join monitordata.Machine on monitordata.session.MachineId = monitordata.machine.Id
          join monitordata.DesktopGroup on monitordata.machine.DesktopGroupId = monitordata.desktopgroup.Id
          join monitordata.connection on monitordata.session.SessionKey = monitordata.connection.SessionKey
          join monitordata.applicationinstance on monitordata.ApplicationInstance.SessionKey = monitordata.session.SessionKey
          join monitordata.application on monitordata.application.id = monitordata.ApplicationInstance.ApplicationId
          where UserName <> ”
          $filter
          order by logonenddate,SessionKey” | ?{$_ -notlike “*[0-9]*”}

          • Thanks!
            No more error, but now I can’t get that info into the report…

            I added this where you compile the $info:
            $dg = ($allsessions | ?{$_.username -eq $user}).monitordata.DesktopGroup.Name

            Then this inside the $info.add part:
            @{n=’Desktop Group’;e={$dg}},

            The column is added to the report, but is empty for each session?

          • Ok, forget the post above. I forgot I already have the desktop group name in there, so delete the “,desktopgroup.name” line from both queries as it is not needed.

            In the loop that adds information to $info add this prior to the “$info.add” line
            $dg = ($allsessions | ?{$_.username -eq $user}).name | sort -Unique
            then edit the “$info.add” line
            $info.add(($t | select @{n=’User’;e={$user}},@{n=’Name’;e={$un}},@{n=’Session Count’;e={$sescount}},@{n=’Total Hours’;e={$totalhrs}},@{n=’Average Hours’;e={$avghrs}},@{n=’Delivery Group’;e={$dg}})) | Out-Null

            In the loop that adds information to $info1 before the “$info1.add” line
            $dg = ($allappsessions | ?{$_.username -eq $auser -and $_.publishedname -eq $app}).name1 | sort -Unique
            then edit the “$info1.add” line
            $info1.add(($t | select @{n=’Published Application’;e={$app}},@{n=’Delivery Group’;e={$dg}},@{n=’User’;e={$auser}},@{n=’Name’;e={$un1}},@{n=’Session Count’;e={$sescount1}},@{n=’Total Hours’;e={$totalhrs1}},@{n=’Average Hours’;e={$avghrs1}})) | Out-Null

          • Thanks alot! Now it works correctly.

  5. When I run this script the results returned are only from connections to our VDi PVS Desktop machines?
    We are on XenDesktop 7.5 with 14 Delivery groups serving up 10 seperate Published server desktops. We also have a small VDi environment as a pilot with 10 Win7 VM’s provisioned via PVS.

    I’d like to get a report on the use of the published Server desktops.

    Thanks, John

    • On line 151 in the script change it from:
      where UserName <> ” and SessionSupport = ‘1’
      to:
      where UserName <> ” and (SessionSupport = ‘1’ or SessionSupport = ‘2’)

      This will get desktop sessions and published desktop sessions – lumping them into the same variable.

      I’ve updated the script. I shouldn’t have been looking at the “sessionsupport” value… needs to be “sessiontype” – 0=desktop, 1=app

  6. The script takes a while to run so I didn’t see the change to sessiontype you made. However switching it to sessionsupport=2 did work and produced a report of over 1000 users. Great! I will edit the script again and use sessiontype.

    My plan here is to use this to create weekly reports for archive as we are on Enterprise licensing and so only get 7 days before grooming 🙁 This will allow me to collect historical user data and more!

    Thanks, John

    • sessionsupport=2 will report desktops and apps on application servers. if you want apps separate from desktops use my updated script.

  7. Hi David,

    Thanks for this great Powershell script. It works in my XenDesktop 7.6 but I have a problem with it :

    1) When I launch it with : powershell.exe -file -dur w (it works without any error message) but the report give me only XenDesktop Desktop Session only not XenDesktop Applications Session.

    2) When I launch it normally or with admin rights : I have this error message and I don’t have the report XenDesktop Applications Session as before.

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

    Do you have an idea, please ? Thanks again.

    Regards
    MD

    • Mo,

      I updated the script on pastebin making the below change fixes it.

      Line 210 change it to:
      where UserName <> ” and sessiontype = ‘1’

  8. Hi David,

    Thanks again. I have use your update script but I have again only desktop session and not application session. It’s very strange and the error is the same if I launch it :

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

    It is only $appsession because $sessions works fine…

    Thanks for your help.

    Regards
    MD

  9. Hello

    I was receiving the same error as Mo Dan before. Output only shows the XenDesktop Desktop Sessions.

    When using the updated script from Pastebin, I get another error:

    Cannot convert the “System.Data.DataRow” value of type “System.Data.DataRow” to type “System.Collections.ArrayList”.
    At \XenDesktop_Usage_V3.5_RunFromServer2012.ps1:168 char:1
    + [System.Collections.ArrayList]$appsessions = sqlquery -q `
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : ConvertToFinalInvalidCastException

    Any idea?

  10. Hello.

    Very good job!

    but, if $appsessions is empty, ps1 show a error:

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

    To resolve it, before line $appsessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{

    insert line if (($appsessions | measure).count -gt 0) {

    and a “}” char (Without quote) after 7 lines.

    Example:

    Original:
    $appsessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
    if ($_.connectionstate -eq “5”) {
    $_.sessionlength = [math]::Round(($sdate – (get-date $_.logonenddate).ToLocalTime()).totalminutes,0)
    } elseif ($_.connectionstate -eq “3”) {
    $_.sessionlength = [math]::Round(($_.enddate – $_.logonenddate).totalminutes,0)
    }
    }

    Modify:
    if (($appsessions | measure).count -gt 0) {
    $appsessions | ?{$_.sessionlength.gettype().name -eq “dbnull”} | %{
    if ($_.connectionstate -eq “5”) {
    $_.sessionlength = [math]::Round(($sdate – (get-date $_.logonenddate).ToLocalTime()).totalminutes,0)
    } elseif ($_.connectionstate -eq “3”) {
    $_.sessionlength = [math]::Round(($_.enddate – $_.logonenddate).totalminutes,0)
    }
    }
    }

    But the question is: Why $appsessions is empty?

    In my env (5 years of life with upgrade), XenDesktop 7.8 publish Desktops (VDI) + Apps&Desktop (Server) and if query monitordata.session i see all session info, with sessiontype 0 and sessiontype 1.

    But, the ApplicationInstances, ApplicationInstanceSummaries and Applications tables on DB are Empty.

    Well, the question,now, is: Why these tables are empty?

    Any idea?

    Thanks.

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

  12. could you please let me know, how to get the html report saved to the local drive instead of receiving it as email attachment.

  13. how to save the report to local hard drive instead of sending it as an email attachment

    • The $message variable is already in html format… you should be able to pipe it to out-file.

      • Am sorry, do i need to modify on the below lines?

        $message = $null
        if (($info | measure).count -gt 0 -and ($info1 | measure).count -gt 0){
        $message = $info | ConvertTo-Html -head $header -Title “XenDesktop Usage Report” -PreContent “XenDesktop Desktop Sessions $s — $e” | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
        $message = $message + ($info1 | ConvertTo-Html -head $header -Title “XenDesktop Usage Report” -PreContent “XenDesktop Application Sessions $s — $e” | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd)
        } elseif (($info | measure).count -gt 0 -and ($info1 | measure).count -eq 0) {
        $message = $info | ConvertTo-Html -head $header -Title “XenDesktop Usage Report” -PreContent “XenDesktop Desktop Sessions $s — $e” | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
        } elseif (($info | measure).count -eq 0 -and ($info1 | measure).count -gt 0) {
        $message = $info1 | ConvertTo-Html -head $header -Title “XenDesktop Usage Report” -PreContent “XenDesktop Application Sessions $s — $e” | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
        }
        if ($message -ne $null) {
        Send-MailMessage -From $mailfrom -To $mailto -Subject “XenDesktop Usage Report $s — $e” -Body “$message” -SmtpServer $emailserver -BodyAsHtml
        }

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

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

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

 Leave a Reply

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

(required)

(required)