Advertisement
david62277

XenDesktop Usage Report

Jul 21st, 2016
2,309
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <#####
  2. XenDesktop VDI Session Report
  3. Written by David Ott
  4. Set-AlternatingRows function credit in the comments of the function
  5. PAY ATTENTION TO ANY COMMENTS MARKED WITH "####" - you may need to change something for your environment
  6. Description:
  7. This script must be run as a user who has at least read rights to the XenDesktop database with the monitordata tables
  8. It will query the database for the information on desktop sessions over the past week or month (see $dur parameter below)
  9. then it will calculate each users connected time per session displaying -
  10. User = samaccountname
  11. Name = fullname
  12. Session Count = number of unique desktop sessions in the period (week or month)
  13. Total Hours = total number of hours connected (no good way to determine if they are actually "active" or not)
  14. Average Hours = total hours/session count = average hours per session
  15.  
  16. Once it has completed gathering that information it will email the information in a nice html list ordered by username
  17. #####>
  18.  
  19. <####
  20. duration (anything other than a week "w" will process the last month of sessions
  21. call the script: "powershell.exe -file <path to script> -dur w"
  22. ####>
  23. Param(
  24.   [string]$dur
  25. )
  26. Function Set-AlternatingRows {
  27.     <#
  28.     .SYNOPSIS
  29.         Simple function to alternate the row colors in an HTML table
  30.     .DESCRIPTION
  31.         This function accepts pipeline input from ConvertTo-HTML or any
  32.         string with HTML in it.  It will then search for <tr> and replace
  33.         it with <tr class=(something)>.  With the combination of CSS it
  34.         can set alternating colors on table rows.
  35.        
  36.         CSS requirements:
  37.         .odd  { background-color:#ffffff; }
  38.         .even { background-color:#dddddd; }
  39.        
  40.         Classnames can be anything and are configurable when executing the
  41.         function.  Colors can, of course, be set to your preference.
  42.        
  43.         This function does not add CSS to your report, so you must provide
  44.         the style sheet, typically part of the ConvertTo-HTML cmdlet using
  45.         the -Head parameter.
  46.     .PARAMETER Line
  47.         String containing the HTML line, typically piped in through the
  48.         pipeline.
  49.     .PARAMETER CSSEvenClass
  50.         Define which CSS class is your "even" row and color.
  51.     .PARAMETER CSSOddClass
  52.         Define which CSS class is your "odd" row and color.
  53.     .EXAMPLE $Report | ConvertTo-HTML -Head $Header | Set-AlternateRows -CSSEvenClass even -CSSOddClass odd | Out-File HTMLReport.html
  54.    
  55.         $Header can be defined with a here-string as:
  56.         $Header = @"
  57.         <style>
  58.         TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
  59.         TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
  60.         TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
  61.         .odd  { background-color:#ffffff; }
  62.         .even { background-color:#dddddd; }
  63.         </style>
  64.         "@
  65.        
  66.         This will produce a table with alternating white and grey rows.  Custom CSS
  67.         is defined in the $Header string and included with the table thanks to the -Head
  68.         parameter in ConvertTo-HTML.
  69.     .NOTES
  70.         Author:         Martin Pugh
  71.         Twitter:        @thesurlyadm1n
  72.         Spiceworks:     Martin9700
  73.         Blog:           www.thesurlyadmin.com
  74.        
  75.         Changelog:
  76.             1.1         Modified replace to include the <td> tag, as it was changing the class
  77.                         for the TH row as well.
  78.             1.0         Initial function release
  79.     .LINK
  80.         http://community.spiceworks.com/scripts/show/1745-set-alternatingrows-function-modify-your-html-table-to-have-alternating-row-colors
  81.     .LINK
  82.         http://thesurlyadmin.com/2013/01/21/how-to-create-html-reports/
  83.     #>
  84.     [CmdletBinding()]
  85.     Param(
  86.         [Parameter(Mandatory,ValueFromPipeline)]
  87.         [string]$Line,
  88.        
  89.         [Parameter(Mandatory)]
  90.         [string]$CSSEvenClass,
  91.        
  92.         [Parameter(Mandatory)]
  93.         [string]$CSSOddClass
  94.     )
  95.     Begin {
  96.         $ClassName = $CSSEvenClass
  97.     }
  98.     Process {
  99.         If ($Line.Contains("<tr><td>"))
  100.         {   $Line = $Line.Replace("<tr>","<tr class=""$ClassName"">")
  101.             If ($ClassName -eq $CSSEvenClass)
  102.             {   $ClassName = $CSSOddClass
  103.             }
  104.             Else
  105.             {   $ClassName = $CSSEvenClass
  106.             }
  107.         }
  108.         Return $Line
  109.     }
  110. }
  111. function sqlquery ($q) { #### function to query sql
  112. $SqlQuery = $q
  113. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  114. $SqlCmd.CommandText = $SqlQuery
  115. $SqlCmd.Connection = $SqlConnection
  116. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  117. $SqlAdapter.SelectCommand = $SqlCmd
  118. $DataSet = New-Object System.Data.DataSet
  119. $SqlAdapter.Fill($DataSet)
  120. return $DataSet.tables[0]
  121. }
  122. #### if the -dur parameter is not included, or does not = w it will process the last month (might take a while)
  123. if ($dur -eq "w") {
  124. $sdate = [datetime]::today # 12am today
  125. $ldate = ([datetime]::today).AddDays(-7) # 7 days ago
  126. } else {
  127. $sdate = [datetime]::today #12am today
  128. $ldate = ([datetime]::today).AddMonths(-1) # 1 month ago
  129. }
  130. <####
  131. additional sql filter info based on the duration.  Note I wrote this script to run as a scheduled task at 12am Sunday morning and
  132. 12am on the first day of the month so $sdate will be 12am.  if you wish to have it be as of the time the script runs change the $sdate
  133. variables above to:
  134. $sdate = get-date
  135. or
  136. $sdate = [datetime]::now
  137. ####>
  138. $filter = "and sessiontype = '0' #### 0 = desktop session 1 = application session (from what I am seeing anyways)
  139. and logonenddate >= convert(datetime,'"+(get-date ($ldate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')
  140. and logonenddate <= convert(datetime,'"+(get-date ($sdate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"')"
  141.  
  142. $SQLServer = "yoursqlserver" #### dns name of the sql server
  143. $SQLDBName = "monitoringdatabase" #### name of the database with the monitordata tables
  144. $emailserver = "smtp server"  #### email server
  145. $mailto = "emailaddress" #### email addresses to send the email to ie "email1@domain.com","email2@domain.com"
  146. $mailfrom = "emailaddress" #### from email address
  147.  
  148. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  149. $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; MultipleActiveResultSets = True"
  150. <####
  151. The actual SQL query - note there is a lot more information that this query grabs than is really needed in this script
  152. I may plan on using the data in future scripts, and it really doesn't take up much more overhead to have that info
  153. This part should run really fast
  154. ####>
  155. [System.Collections.ArrayList]$sessions = sqlquery -q `
  156. "select username
  157. ,fullname
  158. ,hostedmachinename
  159. ,monitordata.session.sessionkey
  160. ,startdate
  161. ,logonduration
  162. ,enddate
  163. ,exitcode
  164. ,connectionstate
  165. ,connectionstatechangedate
  166. ,sessiontype
  167. ,clientname
  168. ,clientaddress
  169. ,clientversion
  170. ,connectedviahostname
  171. ,connectedviaipaddress
  172. ,launchedviahostname
  173. ,launchedviaipaddress
  174. ,isreconnect,protocol
  175. ,logonstartdate
  176. ,logonenddate
  177. ,brokeringduration
  178. ,brokeringdate
  179. ,disconnectcode
  180. ,disconnectdate
  181. ,vmstartstartdate
  182. ,vmstartenddate
  183. ,ClientSessionValidateDate
  184. ,ServerSessionValidateDate
  185. ,EstablishmentDate
  186. ,HdxStartDate
  187. ,HdxEndDate
  188. ,AuthenticationDuration
  189. ,GpoStartDate
  190. ,GpoEndDate
  191. ,LogOnScriptsStartDate
  192. ,LogOnScriptsEndDate
  193. ,ProfileLoadStartDate
  194. ,ProfileLoadEndDate
  195. ,InteractiveStartDate
  196. ,InteractiveEndDate
  197. ,monitordata.connection.CreatedDate
  198. ,monitordata.session.ModifiedDate
  199. ,Datediff(minute,logonenddate,DisconnectDate) as 'SessionLength'
  200. from monitordata.Session
  201. join monitordata.[user] on monitordata.session.userid = monitordata.[user].Id
  202. join monitordata.machine on monitordata.session.MachineId = monitordata.machine.Id
  203. join monitordata.connection on monitordata.session.sessionkey = monitordata.connection.SessionKey
  204. where monitordata.machine.LifecycleState = 0 and username <> ''
  205. $filter
  206. order by startdate,SessionKey" | ?{$_ -notlike "*[0-9]*"}
  207.  
  208. <####
  209. Here we look through all the sessions that the query brought back and look for any that have a sessionlength of "dbnull"
  210. this indicates the session segment is logged off, or is still active, so we have to calculate based on $sdate or $enddate
  211. to populate the session length
  212. ####>
  213. $sessions | ?{$_.sessionlength.gettype().name -eq "dbnull"} | %{
  214. if ($_.connectionstate -eq "5") {
  215. $_.sessionlength = [math]::Round(($sdate - (get-date $_.logonenddate).ToLocalTime()).totalminutes,0)
  216. } elseif ($_.connectionstate -eq "3") {
  217. $_.sessionlength = [math]::Round(($_.enddate - $_.logonenddate).totalminutes,0)
  218. }
  219. }
  220. #### sort all sessions by username
  221. $allsessions = $sessions | sort username
  222. #### get all users who have had sessions
  223. $usernames = $allsessions.username | sort -unique
  224. #### define array
  225. [System.Collections.ArrayList]$info = @()
  226. $z = 0  #### for the progress bar
  227. $ucount = ($usernames | measure).count #### for the progress bar
  228. <####
  229. foreach loop that calculates the total/avg hours for each user in the given period.  Depending on the number
  230. of users/sessions this could take a few minutes
  231. ####>
  232. foreach ($user in $usernames) {
  233. $z ++
  234. Write-Progress -Activity "Calculating" -Status "User $z of $ucount" -PercentComplete ($z/$ucount*100)
  235. $t = @("1")
  236. $un = ($allsessions | ?{$_.username -eq $user}).fullname | sort -Unique | select -First 1
  237. $sescount = (($allsessions | ?{$_.username -eq $user}).sessionkey.guid | sort -Unique | measure).count
  238. $activetime = (($allsessions | ?{$_.username -eq $user}).sessionlength | measure -Sum).sum
  239. $avghrs = [math]::round(($activetime/$sescount/60),2)
  240. $totalhrs = [math]::round(($activetime/60),2)
  241. $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
  242. }
  243. $Header = @"
  244. <style>
  245. TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;width: 95%}
  246. TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
  247. TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
  248. .odd { background-color:#ffffff; }
  249. .even { background-color:#dddddd; }
  250. </style>
  251. "@
  252. $e = $sdate.tostring()
  253. $s = $ldate.tostring()
  254. $message = $info | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h1>XenDesktop Usage Report $s -- $e</h1>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
  255. Send-MailMessage -From $mailfrom -To $mailto -Subject "XenDesktop Usage Report $s -- $e" -Body "$message" -SmtpServer $emailserver -BodyAsHtml
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement