SHARE
TWEET

XenDesktop Desktop and App Usage Report

david62277 Jul 25th, 2016 (edited) 2,131 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <#####
  2. Title: XenDesktop Desktop/Application usage report (tested on XD 7.6 and 7.9)
  3. Written By: David Ott
  4. Outside Credit: Set-Alternating function
  5.                     Martin Pugh
  6.                     @theurlyadmin
  7.                     www.theurlyadmin.com
  8. Description: Script to query the XenDesktop monitordata tables in SQL, and email a report on desktop/application usage
  9.  
  10. PAY ATTENTION TO ANYTHING COMMENTED WITH #### - it is either something you need to change for your environment, or something to pay attention to
  11.  
  12. Execution example
  13. powershell.exe -file <pathto.ps1> -dur w
  14.  
  15. 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)
  16.  
  17. 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
  18.  
  19. 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).
  20. 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.
  21. After all the calculations are done it converts the data into html, runs them through the set-alternatingrows function, and emails the report
  22.  
  23. 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.
  24. #####>
  25.  
  26. Param(
  27.   [string]$dur
  28. )
  29. Function Set-AlternatingRows {
  30.     [CmdletBinding()]
  31.     Param(
  32.         [Parameter(Mandatory,ValueFromPipeline)]
  33.         [string]$Line,
  34.        
  35.         [Parameter(Mandatory)]
  36.         [string]$CSSEvenClass,
  37.        
  38.         [Parameter(Mandatory)]
  39.         [string]$CSSOddClass
  40.     )
  41.     Begin {
  42.         $ClassName = $CSSEvenClass
  43.     }
  44.     Process {
  45.         If ($Line.Contains("<tr><td>"))
  46.         {   $Line = $Line.Replace("<tr>","<tr class=""$ClassName"">")
  47.             If ($ClassName -eq $CSSEvenClass)
  48.             {   $ClassName = $CSSOddClass
  49.             }
  50.             Else
  51.             {   $ClassName = $CSSEvenClass
  52.             }
  53.         }
  54.         Return $Line
  55.     }
  56. }
  57. function sqlquery ($q) {
  58. $SqlQuery = $q
  59. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  60. $SqlCmd.CommandText = $SqlQuery
  61. $SqlCmd.Connection = $SqlConnection
  62. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  63. $SqlAdapter.SelectCommand = $SqlCmd
  64. $DataSet = New-Object System.Data.DataSet
  65. $SqlAdapter.Fill($DataSet)
  66. return $DataSet.tables[0]
  67. }
  68.  
  69. <####
  70. Below is where we decide the date range based on the "dur" parameter (or lack there of)
  71. I plan on using this report to run at midnight on a weekly/monthly basis, so I set the $sdate
  72. variable to today at midnight.  If you want it to be the time you actually run the script change
  73. $sdate = [datetime]::today
  74. to
  75. $sdate = [datetime]::now
  76. or
  77. $sdate = get-date
  78. ####>
  79. if ($dur -eq "w") {
  80. $sdate = [datetime]::today
  81. $ldate = ([datetime]::today).AddDays(-7) #### 1 week ago
  82. } else {
  83. $sdate = [datetime]::today
  84. $ldate = ([datetime]::today).AddMonths(-1) #### 1 month ago
  85. }
  86.  
  87. $filter = "and logonenddate >= convert(datetime,'"+(get-date ($ldate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')
  88. and logonenddate <= convert(datetime,'"+(get-date ($sdate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')"
  89. $SQLServer = "your sql server" #### your sql server fqdn or ip address - if it is an instance do server\instance
  90. $SQLDBName = "CitrixDatabaseMonitoring" #### the database containing the monitordata tables
  91. $emailserver = "smtpserver" #### your smtp server
  92. $mailto = "youremail@domain.com" #### your email address - if multiple do this "email1@domain.com","email2@domain.com"
  93. $mailfrom = "notification@yourdomain.com"  #### from address
  94. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  95. $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; MultipleActiveResultSets = True"
  96. [System.Collections.ArrayList]$sessions = @()
  97. [System.Collections.ArrayList]$appsessions = @()
  98.  
  99. [System.Collections.ArrayList]$sessions = sqlquery -q `
  100. "select
  101. monitordata.session.SessionKey
  102. ,startdate
  103. ,logonduration
  104. ,enddate
  105. ,connectionstate
  106. ,username
  107. ,fullname
  108. ,monitordata.machine.HostedMachineName
  109. ,monitordata.desktopgroup.Name
  110. ,IsRemotePC
  111. ,DesktopKind
  112. ,SessionSupport
  113. ,SessionType
  114. ,DeliveryType
  115. ,ClientName
  116. ,ClientAddress
  117. ,ClientVersion
  118. ,ConnectedViaHostName
  119. ,ConnectedViaIPAddress
  120. ,LaunchedViaHostName
  121. ,LaunchedViaIPAddress
  122. ,IsReconnect
  123. ,Protocol
  124. ,LogOnStartDate
  125. ,LogOnEndDate
  126. ,BrokeringDuration
  127. ,BrokeringDate
  128. ,DisconnectCode
  129. ,DisconnectDate
  130. ,VMStartStartDate
  131. ,VMStartEndDate
  132. ,ClientSessionValidateDate
  133. ,ServerSessionValidateDate
  134. ,EstablishmentDate
  135. ,HdxStartDate
  136. ,HdxEndDate
  137. ,AuthenticationDuration
  138. ,GpoStartDate
  139. ,GpoEndDate
  140. ,LogOnScriptsStartDate
  141. ,LogOnScriptsEndDate
  142. ,ProfileLoadStartDate
  143. ,ProfileLoadEndDate
  144. ,InteractiveStartDate
  145. ,InteractiveEndDate
  146. ,Datediff(minute,logonenddate,DisconnectDate) as 'SessionLength'
  147. from monitordata.session
  148. join monitordata.[user] on monitordata.session.UserId = monitordata.[user].Id
  149. join monitordata.Machine on monitordata.session.MachineId = monitordata.machine.Id
  150. join monitordata.DesktopGroup on monitordata.machine.DesktopGroupId = monitordata.desktopgroup.Id
  151. join monitordata.connection on monitordata.session.SessionKey = monitordata.connection.SessionKey
  152. where UserName <> '' and SessionType = '0'
  153. $filter
  154. order by logonenddate,SessionKey" | ?{$_ -notlike "*[0-9]*"}
  155.  
  156. [System.Collections.ArrayList]$appsessions = sqlquery -q `
  157. "select monitordata.session.SessionKey
  158. ,monitordata.session.StartDate
  159. ,LogOnDuration
  160. ,monitordata.session.EndDate
  161. ,ConnectionState
  162. ,UserName
  163. ,FullName
  164. ,monitordata.application.Name
  165. ,PublishedName
  166. ,monitordata.machine.HostedMachineName
  167. ,monitordata.DesktopGroup.Name
  168. ,IsRemotePC
  169. ,DesktopKind
  170. ,SessionSupport
  171. ,DeliveryType
  172. ,ClientName
  173. ,ClientAddress
  174. ,ClientVersion
  175. ,ConnectedViaHostName
  176. ,ConnectedViaIPAddress
  177. ,LaunchedViaHostName
  178. ,LaunchedViaIPAddress
  179. ,IsReconnect
  180. ,Protocol
  181. ,LogOnStartDate
  182. ,LogOnEndDate
  183. ,BrokeringDuration
  184. ,BrokeringDate
  185. ,DisconnectCode
  186. ,DisconnectDate
  187. ,VMStartStartDate
  188. ,VMStartEndDate
  189. ,ClientSessionValidateDate
  190. ,ServerSessionValidateDate
  191. ,EstablishmentDate
  192. ,HdxStartDate
  193. ,AuthenticationDuration
  194. ,GpoStartDate
  195. ,GpoEndDate
  196. ,LogOnScriptsStartDate
  197. ,LogOnScriptsEndDate
  198. ,ProfileLoadStartDate
  199. ,ProfileLoadEndDate
  200. ,InteractiveStartDate
  201. ,InteractiveEndDate
  202. ,Datediff(minute,logonenddate,DisconnectDate) as 'SessionLength'
  203. from monitordata.Session
  204. join monitordata.[user] on monitordata.session.UserId = monitordata.[user].Id
  205. join monitordata.Machine on monitordata.session.MachineId = monitordata.machine.Id
  206. join monitordata.DesktopGroup on monitordata.machine.DesktopGroupId = monitordata.desktopgroup.Id
  207. join monitordata.connection on monitordata.session.SessionKey = monitordata.connection.SessionKey
  208. join monitordata.applicationinstance on monitordata.ApplicationInstance.SessionKey = monitordata.session.SessionKey
  209. join monitordata.application on monitordata.application.id = monitordata.ApplicationInstance.ApplicationId
  210. where UserName <> '' and sessiontype = '1'
  211. $filter
  212. order by logonenddate,SessionKey" | ?{$_ -notlike "*[0-9]*"}
  213.  
  214. $sessions | ?{$_.sessionlength.gettype().name -eq "dbnull"} | %{
  215. if ($_.connectionstate -eq "5") {
  216. $_.sessionlength = [math]::Round(($sdate - (get-date $_.logonenddate).ToLocalTime()).totalminutes,0)
  217. } elseif ($_.connectionstate -eq "3") {
  218. $_.sessionlength = [math]::Round(($_.enddate - $_.logonenddate).totalminutes,0)
  219. }
  220. }
  221. $appsessions | ?{$_.sessionlength.gettype().name -eq "dbnull"} | %{
  222. if ($_.connectionstate -eq "5") {
  223. $_.sessionlength = [math]::Round(($sdate - (get-date $_.logonenddate).ToLocalTime()).totalminutes,0)
  224. } elseif ($_.connectionstate -eq "3") {
  225. $_.sessionlength = [math]::Round(($_.enddate - $_.logonenddate).totalminutes,0)
  226. }
  227. }
  228.  
  229. $allsessions = $sessions | sort username
  230. $allappsessions = $appsessions | sort username
  231. $allapps = ($appsessions).publishedname | sort -unique | sort
  232.  
  233. $usernames = $allsessions.username | sort -unique
  234. $appusernames = $allappsessions.username | sort -Unique
  235.  
  236. [System.Collections.ArrayList]$info = @()
  237. [System.Collections.ArrayList]$info1 = @()
  238. $z = 0  
  239. $ucount = ($usernames | measure).count
  240. foreach ($user in $usernames) {
  241. $z ++
  242. Write-Progress -Activity "Calculating Desktop Sessions" -Status "User $z of $ucount" -PercentComplete ($z/$ucount*100)
  243. $t = @("1")
  244. $un = ($allsessions | ?{$_.username -eq $user}).fullname | sort -Unique | select -First 1
  245. $sescount = (($allsessions | ?{$_.username -eq $user}).sessionkey.guid | sort -Unique | measure).count
  246. $activetime = (($allsessions | ?{$_.username -eq $user}).sessionlength | measure -Sum).sum
  247. $avghrs = [math]::round(($activetime/$sescount/60),2)
  248. $totalhrs = [math]::round(($activetime/60),2)
  249. $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
  250. }
  251. $z = 0
  252. $ucount = ($appusernames | measure).count
  253. foreach ($auser in $appusernames) {
  254. $z ++
  255. Write-Progress -Activity "Calculating Application Sessions" -Status "User $z of $ucount" -PercentComplete ($z/$ucount*100)
  256. $t = @("1")
  257. $un1 = ($allappsessions | ?{$_.username -eq $auser}).fullname | sort -Unique | select -First 1
  258. $apps = ($allappsessions | ?{$_.username -eq $auser}).publishedname | sort -unique
  259. foreach ($app in $apps) {
  260. $sescount1 = (($allappsessions | ?{$_.username -eq $auser -and $_.publishedname -eq $app}).sessionkey.guid | sort -Unique | measure).count
  261. $activetime = (($allappsessions | ?{$_.username -eq $auser -and $_.publishedname -eq $app}).sessionlength | measure -Sum).sum
  262. $avghrs1 = [math]::round(($activetime/$sescount/60),2)
  263. $totalhrs1 = [math]::round(($activetime/60),2)
  264. $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
  265. }
  266. }
  267.  
  268. #### if you don't like the alternating row colors of the report you can change them below in the header variable
  269. $Header = @"
  270. <style>
  271. TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 95%}
  272. TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
  273. TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
  274. .odd { background-color:#ffffff; }
  275. .even { background-color:#dddddd; }
  276. </style>
  277. "@
  278. $e = $sdate.tostring()
  279. $s = $ldate.tostring()
  280. $message = $null
  281. if (($info | measure).count -gt 0 -and ($info1 | measure).count -gt 0){
  282. $message = $info | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h2>XenDesktop Desktop Sessions $s -- $e</h2>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
  283. $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)
  284. } elseif (($info | measure).count -gt 0 -and ($info1 | measure).count -eq 0) {
  285. $message = $info | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h2>XenDesktop Desktop Sessions $s -- $e</h2>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
  286. } elseif (($info | measure).count -eq 0 -and ($info1 | measure).count -gt 0) {
  287. $message = $info1 | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h2>XenDesktop Application Sessions $s -- $e</h2>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
  288. }
  289. if ($message -ne $null) {
  290. Send-MailMessage -From $mailfrom -To $mailto -Subject "XenDesktop Usage Report $s -- $e" -Body "$message" -SmtpServer $emailserver -BodyAsHtml
  291. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top