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

  61 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. […]

  17. Hi, the script works for us but is it possible to add connection count in the output file for xendesktop? Great work!

  18. Do you want run the script on DDC’s?

    • IS it work on Desktop version7.12?

      • Yes it should still work. I have variations of this script running against a 7.15 environment.

        • I did not got any information through email.If possible please share scripts for how to get Weekly report of both Xenapp and Xendesktop 7.12 version to email in the format.CSV file…..

    • This grabs information directly from the monitoring database. You can run it anywhere really.

      • When ran the script i am getting below error,Please help me on this…

        Exception calling “Fill” with “1” argument(s): “A network-related or instance-specific error occurred while establishin
        g a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct
        and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not o
        pen a connection to SQL Server)”
        At C:\Users\sa_SReddy3\desktop\xendesktop.ps1:42 char:17
        + $SqlAdapter.Fill <<<< ($DataSet)
        + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

        You cannot call a method on a null-valued expression.
        At C:\Users\desktop\xendesktop.ps1:136 char:39
        + $sessions | ?{$_.sessionlength.gettype <<<< ().name -eq "dbnull"} | %{
        + CategoryInfo : InvalidOperation: (gettype:String) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

        Attempted to divide by zero.
        At C:\Users\desktop\xendesktop.ps1:157 char:90
        + Write-Progress -Activity "Calculating" -Status "User $z of $ucount" -PercentComplete ($z/ <<<< $ucount*100)
        + CategoryInfo : NotSpecified: (:) [], RuntimeException
        + FullyQualifiedErrorId : RuntimeException

        Set-AlternatingRows : The input object cannot be bound to any parameters for the command either because the command doe
        s not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
        At C:\Users\desktop\xendesktop.ps1:177 char:159
        + $message = $info | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "XenDesktop Usage Re
        port $s — $e” | Set-AlternatingRows <<<< -CSSEvenClass even -CSSOddClass odd
        + CategoryInfo : InvalidArgument: (XenDesktop …8 00:00:00:PSObject) [Set-AlternatingRows], Par
        ameterBindingException
        + FullyQualifiedErrorId : InputObjectNotBound,Set-AlternatingRows

        The term ‘Pause’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spell
        ing of the name, or if a path was included, verify that the path is correct and try again.
        At C:\Users\desktop\xendesktop.ps1:179 char:6
        + Pause <<<<
        + CategoryInfo : ObjectNotFound: (Pause:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

  19. David this is great work and thank you for putting forth the effort! Is this supposed to also work for XenApp published apps as well? I’m running this with the delivery group modification above and the only output I see is desktop session stats – no app usage.

  20. Hi,

    Maybe a stupid question, but how do I create the “CitrixDatabaseMonitoring” with the correct tables etc… in my SQL server

    Regards,
    Steven

  21. Hi,

    sorry fot he question about creating the database, i realize now it’s XA7.x 🙂
    when I use your script, I get an error on the $appsession sql statement:

    Cannot convert the “System.Data.DataRow” value of type “System.Data.DataRow” to type “System.Collections.ArrayList”.
    At C:\Install\XA7.x_Monitoring\xendesktop_desktop_and_app_usage_report.ps1:157 char:1
    + [System.Collections.ArrayList]$appsessions = sqlquery -q `
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

    Can you point me to the wright direction?

    Thanks,
    Steven

    • When I run the sql statement below on my Citrix Monitoring SQL DB, I got a result, so the sql query is fine.

      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 ” and sessiontype = ‘1’

      Greetings,
      Steven

  22. Hi

    I’m using XA7.14, is the script for the $appsession part is working with this version?
    I keep getting the error “You cannot call a method on a null-valued expression.” on the $appsession part. But when I run the $appsession
    part as a SQL query on the Monitoring DB, I can see the result.

    Can you help me out here?

    Thanks,
    Steven

    • It sounds like something isn’t formatted correctly. Also, the script was written for XenDesktop, so it may not be 100% compatible with XA.

  23. […] 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. […]

  24. How do I modify this script to include hosted machine name? It’s so hard

  25. Hi.. when i am running the script i am getting below error –

    PS C:\temp\Test_Migration> .\Usage_report.ps1 -dur w
    The “=” operator is missing after a named argument.
    At C:\temp\Test_Migration\Usage_report.ps1:32 char:30
    + [Parameter(Mandatory, <<<< ValueFromPipeline)]
    + CategoryInfo : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEqualsInNamedArgument

    Any help?

  26. Hi,
    Thanks a lot for the script. It works and I’m Able to have an emailed report as shown in the picture on the top of this article. But, I would kindly ask you, if it’s possible to have the name of the application and the last time when it has been accessed.

    Thank you so much

    • Hi,
      I think this is the error why I cannot have application listed:
      measure : Input object “” is not numeric.
      At C:\Users\xxx\Desktop\xendesktop_desktop_and_app_usage_report_FRESH.ps1:246 char:74
      + $activetime = (($allsessions | ?{$_.username -eq $user}).sessionlength | measure …
      + ~~~~~~~
      + CategoryInfo : InvalidType: (:DBNull) [Measure-Object], PSInvalidOperationException
      + FullyQualifiedErrorId : NonNumericInputObject,Microsoft.PowerShell.Commands.MeasureObjectCommand

      May you help me please?

      Thank you

  27. […] 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. […]

  28. I’ve been using this report for a long while now for Virtual Apps, Desktops, Published Desktops, Pooled Desktops, etc and works great even on latest 7.15CU4. But for the life of me I cannot figure out the SQL syntax for RemotePC. Any ideas?

    The Sessions Table is linked to the Machine Table and that can link to DesktopGroup Table for any group where IsRemotePC is true, and the users that connect to RemotePC show in SessionActivitySummary Table, but I can’t figure out all the links/keys back to the Session Table in powershell (I used Excel PowerData to poke around for the data). Maybe I’m over complicating it?

  29. Hi,
    I was able to run the script without error, but the Total Hours and Average Hours did not populate. Can you tell me how to get this info to populate?
    I’m very new to this, so I apologize if the answer should be obvious.

    Also is there a way to add their machine name to the report?

    Thanks in advance.

  30. I have this running on 7.15 CU4, my only issue is the time/date parameter.
    If i run the script with -dur w it throws an error.
    If i run it without it, its shows an entire month

    Here are my date range settings, if someone can please tell me what to adjust. I just want to show today from midnight, not a month ago from midnight.

    if ($dur -eq “w”) {
    $sdate = [datetime]::now
    $ldate = ([datetime]::today).AddDays(-1) #### 1 week ago
    } else {
    $sdate = [datetime]::now
    $ldate = ([datetime]::today).AddMonths(-1) #### 1 month ago
    }

    Thank you

  31. I have this running on 7.15 LTSR CU4 without issue, but I want to run this every night, not every week or month, as we are a few thousand users.
    Can someone look at my settings briefly and tell me what I’m doing wrong?

    $dur = “w”
    $sdate = [datetime]::now # 12am today
    $ldate = ([datetime]::today).AddDays(-7) # 7 days ago

    Thank you

  32. […] 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. […]

  33. Hi David,

    I’ve had this running in my 7.15 prod environment and it’s configured to gather the last 30 days of session/user data. I’ve noticed that if I compare the results to Directors session data for the last 30 days as well (exported as CSV), Director lists many more user sessions (considerably more) than I get from your script – any idea what’s going on?

    Thank you again!

    • Meant to ask, if there was a way to configure the script to collect 90 days (or more) of data? I have this changed to .AddDays(-90) but it has no impact on the resulting data.

      Thank you!

  34. I would like to see this report on Published Applications only. Would that be possible

  35. […] 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 to Steven Van Hauwermeiren 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.