XenDesktop Desktop and App Usage Report

<#####
Title: XenDesktop Desktop/Application usage report (tested on XD 7.6 and 7.9)
Written By: David Ott
Outside Credit: Set-Alternating function
                    Martin Pugh
                    @theurlyadmin
                    www.theurlyadmin.com
Description: Script to query the XenDesktop monitordata tables in SQL, and email a report on desktop/application usage

PAY ATTENTION TO ANYTHING COMMENTED WITH #### - it is either something you need to change for your environment, or something to pay attention to

Execution example
powershell.exe -file <pathto.ps1> -dur w

this will query the sql database for everything in the past week ending on midnight the day you are on (this can be edited - noted in a comment below)

anything other than "w" or blank will query the last month - which could take a little bit to sift through if you have a lot of session information

Once the SQL queries bring back all the information it will go through each user's desktop sessions, and calculate their "active" time (depending on the number of users/sessions this could take a few minutes).
The monitordata tables don't actually keep track of idle time, so the only thing we can see is user1 logged on at x time, disconnected at x1 time, reconnected at x2, and logged off at x3.  We can then calculate (x1-x) + (x3-x2) to get the time the user actually has a desktop or application up and running.
After all the calculations are done it converts the data into html, runs them through the set-alternatingrows function, and emails the report

NOTE: you will notice that the sql queries bring back a lot more information than is needed to run this report.  That is so you and I don't have to re-invent the wheel if we wanted to do a different report - like logon duration.
#####>

Param(
  [string]$dur
)
Function Set-AlternatingRows {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory,ValueFromPipeline)]
        [string]$Line,

        [Parameter(Mandatory)]
        [string]$CSSEvenClass,

        [Parameter(Mandatory)]
        [string]$CSSOddClass
    )
    Begin {
        $ClassName = $CSSEvenClass
    }
    Process {
        If ($Line.Contains("<tr><td>"))
        {   $Line = $Line.Replace("<tr>","<tr class=""$ClassName"">")
            If ($ClassName -eq $CSSEvenClass)
            {   $ClassName = $CSSOddClass
            }
            Else
            {   $ClassName = $CSSEvenClass
            }
        }
        Return $Line
    }
}
function sqlquery ($q) {
$SqlQuery = $q
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
return $DataSet.tables[0]
}

<####
Below is where we decide the date range based on the "dur" parameter (or lack there of)
I plan on using this report to run at midnight on a weekly/monthly basis, so I set the $sdate
variable to today at midnight.  If you want it to be the time you actually run the script change
$sdate = [datetime]::today
to
$sdate = [datetime]::now
or
$sdate = get-date
####>
if ($dur -eq "w") {
$sdate = [datetime]::today
$ldate = ([datetime]::today).AddDays(-7) #### 1 week ago
} else {
$sdate = [datetime]::today
$ldate = ([datetime]::today).AddMonths(-1) #### 1 month ago
}

$filter = "and logonenddate >= convert(datetime,'"+(get-date ($ldate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')
and logonenddate <= convert(datetime,'"+(get-date ($sdate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')"
$SQLServer = "your sql server" #### your sql server fqdn or ip address - if it is an instance do server\instance
$SQLDBName = "CitrixDatabaseMonitoring" #### the database containing the monitordata tables
$emailserver = "smtpserver" #### your smtp server
$mailto = "youremail@domain.com" #### your email address - if multiple do this "email1@domain.com","email2@domain.com"
$mailfrom = "notification@yourdomain.com"  #### from address
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; MultipleActiveResultSets = True"
[System.Collections.ArrayList]$sessions = @()
[System.Collections.ArrayList]$appsessions = @()

[System.Collections.ArrayList]$sessions = sqlquery -q `
"select
monitordata.session.SessionKey
,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
,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'
$filter
order by logonenddate,SessionKey" | ?{$_ -notlike "*[0-9]*"}

$sessions | ?{$_.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)
}
}
$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)
}
}

$allsessions = $sessions | sort username
$allappsessions = $appsessions | sort username
$allapps = ($appsessions).publishedname | sort -unique | sort

$usernames = $allsessions.username | sort -unique
$appusernames = $allappsessions.username | sort -Unique

[System.Collections.ArrayList]$info = @()
[System.Collections.ArrayList]$info1 = @()
$z = 0
$ucount = ($usernames | measure).count
foreach ($user in $usernames) {
$z ++
Write-Progress -Activity "Calculating Desktop Sessions" -Status "User $z of $ucount" -PercentComplete ($z/$ucount*100)
$t = @("1")
$un = ($allsessions | ?{$_.username -eq $user}).fullname | sort -Unique | select -First 1
$sescount = (($allsessions | ?{$_.username -eq $user}).sessionkey.guid | sort -Unique | measure).count
$activetime = (($allsessions | ?{$_.username -eq $user}).sessionlength | measure -Sum).sum
$avghrs = [math]::round(($activetime/$sescount/60),2)
$totalhrs = [math]::round(($activetime/60),2)
$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}})) | Out-Null
}
$z = 0
$ucount = ($appusernames | measure).count
foreach ($auser in $appusernames) {
$z ++
Write-Progress -Activity "Calculating Application Sessions" -Status "User $z of $ucount" -PercentComplete ($z/$ucount*100)
$t = @("1")
$un1 = ($allappsessions | ?{$_.username -eq $auser}).fullname | sort -Unique | select -First 1
$apps = ($allappsessions | ?{$_.username -eq $auser}).publishedname | sort -unique
foreach ($app in $apps) {
$sescount1 = (($allappsessions | ?{$_.username -eq $auser -and $_.publishedname -eq $app}).sessionkey.guid | sort -Unique | measure).count
$activetime = (($allappsessions | ?{$_.username -eq $auser -and $_.publishedname -eq $app}).sessionlength | measure -Sum).sum
$avghrs1 = [math]::round(($activetime/$sescount/60),2)
$totalhrs1 = [math]::round(($activetime/60),2)
$info1.add(($t | select @{n='Published Application';e={$app}},@{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
}
}

#### if you don't like the alternating row colors of the report you can change them below in the header variable
$Header = @"
<style>
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 95%}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
.odd { background-color:#ffffff; }
.even { background-color:#dddddd; }
</style>
"@
$e = $sdate.tostring()
$s = $ldate.tostring()
$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 "<h2>XenDesktop Desktop Sessions $s -- $e</h2>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
$message = $message + ($info1 | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h2>XenDesktop Application Sessions $s -- $e</h2>" | 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 "<h2>XenDesktop Desktop Sessions $s -- $e</h2>" | 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 "<h2>XenDesktop Application Sessions $s -- $e</h2>" | 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
}