Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <#####
- XenDesktop VDI Session Report
- Written by David Ott
- Set-AlternatingRows function credit in the comments of the function
- PAY ATTENTION TO ANY COMMENTS MARKED WITH "####" - you may need to change something for your environment
- Description:
- This script must be run as a user who has at least read rights to the XenDesktop database with the monitordata tables
- It will query the database for the information on desktop sessions over the past week or month (see $dur parameter below)
- then it will calculate each users connected time per session displaying -
- User = samaccountname
- Name = fullname
- Session Count = number of unique desktop sessions in the period (week or month)
- Total Hours = total number of hours connected (no good way to determine if they are actually "active" or not)
- Average Hours = total hours/session count = average hours per session
- Once it has completed gathering that information it will email the information in a nice html list ordered by username
- #####>
- <####
- duration (anything other than a week "w" will process the last month of sessions
- call the script: "powershell.exe -file <path to script> -dur w"
- ####>
- Param(
- [string]$dur
- )
- Function Set-AlternatingRows {
- <#
- .SYNOPSIS
- Simple function to alternate the row colors in an HTML table
- .DESCRIPTION
- This function accepts pipeline input from ConvertTo-HTML or any
- string with HTML in it. It will then search for <tr> and replace
- it with <tr class=(something)>. With the combination of CSS it
- can set alternating colors on table rows.
- CSS requirements:
- .odd { background-color:#ffffff; }
- .even { background-color:#dddddd; }
- Classnames can be anything and are configurable when executing the
- function. Colors can, of course, be set to your preference.
- This function does not add CSS to your report, so you must provide
- the style sheet, typically part of the ConvertTo-HTML cmdlet using
- the -Head parameter.
- .PARAMETER Line
- String containing the HTML line, typically piped in through the
- pipeline.
- .PARAMETER CSSEvenClass
- Define which CSS class is your "even" row and color.
- .PARAMETER CSSOddClass
- Define which CSS class is your "odd" row and color.
- .EXAMPLE $Report | ConvertTo-HTML -Head $Header | Set-AlternateRows -CSSEvenClass even -CSSOddClass odd | Out-File HTMLReport.html
- $Header can be defined with a here-string as:
- $Header = @"
- <style>
- TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
- 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>
- "@
- This will produce a table with alternating white and grey rows. Custom CSS
- is defined in the $Header string and included with the table thanks to the -Head
- parameter in ConvertTo-HTML.
- .NOTES
- Author: Martin Pugh
- Twitter: @thesurlyadm1n
- Spiceworks: Martin9700
- Blog: www.thesurlyadmin.com
- Changelog:
- 1.1 Modified replace to include the <td> tag, as it was changing the class
- for the TH row as well.
- 1.0 Initial function release
- .LINK
- http://community.spiceworks.com/scripts/show/1745-set-alternatingrows-function-modify-your-html-table-to-have-alternating-row-colors
- .LINK
- http://thesurlyadmin.com/2013/01/21/how-to-create-html-reports/
- #>
- [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) { #### function to query sql
- $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]
- }
- #### if the -dur parameter is not included, or does not = w it will process the last month (might take a while)
- if ($dur -eq "w") {
- $sdate = [datetime]::today # 12am today
- $ldate = ([datetime]::today).AddDays(-7) # 7 days ago
- } else {
- $sdate = [datetime]::today #12am today
- $ldate = ([datetime]::today).AddMonths(-1) # 1 month ago
- }
- <####
- additional sql filter info based on the duration. Note I wrote this script to run as a scheduled task at 12am Sunday morning and
- 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
- variables above to:
- $sdate = get-date
- or
- $sdate = [datetime]::now
- ####>
- $filter = "and sessiontype = '0' #### 0 = desktop session 1 = application session (from what I am seeing anyways)
- 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 = "yoursqlserver" #### dns name of the sql server
- $SQLDBName = "monitoringdatabase" #### name of the database with the monitordata tables
- $emailserver = "smtp server" #### email server
- $mailto = "emailaddress" #### email addresses to send the email to ie "email1@domain.com","email2@domain.com"
- $mailfrom = "emailaddress" #### from email address
- $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
- $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; MultipleActiveResultSets = True"
- <####
- The actual SQL query - note there is a lot more information that this query grabs than is really needed in this script
- I may plan on using the data in future scripts, and it really doesn't take up much more overhead to have that info
- This part should run really fast
- ####>
- [System.Collections.ArrayList]$sessions = sqlquery -q `
- "select username
- ,fullname
- ,hostedmachinename
- ,monitordata.session.sessionkey
- ,startdate
- ,logonduration
- ,enddate
- ,exitcode
- ,connectionstate
- ,connectionstatechangedate
- ,sessiontype
- ,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
- ,monitordata.connection.CreatedDate
- ,monitordata.session.ModifiedDate
- ,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.connection on monitordata.session.sessionkey = monitordata.connection.SessionKey
- where monitordata.machine.LifecycleState = 0 and username <> ''
- $filter
- order by startdate,SessionKey" | ?{$_ -notlike "*[0-9]*"}
- <####
- Here we look through all the sessions that the query brought back and look for any that have a sessionlength of "dbnull"
- this indicates the session segment is logged off, or is still active, so we have to calculate based on $sdate or $enddate
- to populate the session length
- ####>
- $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)
- }
- }
- #### sort all sessions by username
- $allsessions = $sessions | sort username
- #### get all users who have had sessions
- $usernames = $allsessions.username | sort -unique
- #### define array
- [System.Collections.ArrayList]$info = @()
- $z = 0 #### for the progress bar
- $ucount = ($usernames | measure).count #### for the progress bar
- <####
- foreach loop that calculates the total/avg hours for each user in the given period. Depending on the number
- of users/sessions this could take a few minutes
- ####>
- foreach ($user in $usernames) {
- $z ++
- Write-Progress -Activity "Calculating" -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
- }
- $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 = $info | ConvertTo-Html -head $header -Title "XenDesktop Usage Report" -PreContent "<h1>XenDesktop Usage Report $s -- $e</h1>" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
- 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