Citrix administrators always need to know who is currently accessing their environment. In this post, we will show you how to create a real-time PowerShell listing of active and disconnected sessions. An HTML report may also be generated.
As usual, we will be using the Citrix Monitoring Database for our report, so you will need to modify the variables holding the name or IP address of the SQL Server hosting the monitoring database, as well as the database name (and you need permissions to read the database, of course).
The following variables need to be modified
$SQLServer = "SQL Server"
$SQLDBName = "MonitoringDB"
The following variables may be modified if you wish to change the location or title of your report.
$reportLocation = "c:\reports\"
$reportFileName = "CurrentSessions"
We create a SQL query to retrieve the sessions.
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; MultipleActiveResultSets = True"
#[System.Collections.ArrayList]$sessions = @()
$sessions = @()
$strQuery = `
"select
monitordata.session.SessionKey
,startdate
,logonduration
,enddate
,connectionstate
,username
,fullname
,monitordata.machine.Name as MachineName
,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'
, '00:00' as connectDuration
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 monitordata.session.CurrentConnectionId = monitordata.connection.Id
and enddate IS NULL
order by username,SessionKey"
$sessions = @(sqlquery -q $strQuery | ?{$_ -notlike "*[0-9]*"})
We calculate the session duration (in minutes).
$now = [datetime]::now
$sessions | %{
$_.sessionlength = [math]::Round(($now - (get-date $_.startdate).ToLocalTime()).totalminutes,0)
$_.connectDuration = $(duration $_.SessionLength)
#Write-Host "$($_.username): start: $((get-date $_.startdate).ToLocalTime()) now: $($now) length: $($_.sessionlength) $(duration $_.SessionLength)"
}
We prepare an HTML report of the sessions if desired, and we also display a list of the session in the PowerShell session.
$header = @"
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
h6 { font-family: tahoma; font-size:11px; }
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;color:white; background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
.odd { color:black; background-color:#ffffff; }
.even { color:black; background-color:#dddddd; }
</style>
"@
$message = $null
$XDsessions = @($sessions | sort username |
select @{n='User Name';e={$_.userName}}, @{n='Full Name';e={$_.fullName}},
@{n='Start Date/Time';e={$_.startdate.ToLocalTime()}},
@{n='State';e={if ($_.connectionState -eq 5) {'Act'} else {'Disc'}}},
@{n='Host';e={$_.MachineName.Split('\')[1]}},
@{n='Duration';e={duration $_.SessionLength}},
@{n='Protocol';e={$_.protocol}}, @{n='Desktop Group';e={$_.name}},
@{n='StoreFront';e={$_.LaunchedViaIPAddress}},
@{n='NetScaler';e={$_.ConnectedViaIPAddress}})
if ($XDsessions.Count -gt 0) {
$contentHead = "<h2>$($reportName): $now</h2><h6>$($XDsessions.Count) sessions</h6>"
$message = $XDsessions | ConvertTo-Html -head $header -Title $($reportName) -PreContent $contentHead |
Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
}
if ($message -ne $null) {
if ($reportFileName -ne "") {
$timeStamp = (Get-Date -Format u).Replace(":",".").Replace("Z","").Replace(" ","_")
$filePathName = "$($reportLocation)$($reportFileName)_$($timeStamp).html"
$message | Out-File $filePathName
}
$XDSessions | ft -AutoSize
$act = ($XDsessions | ? State -eq 'Act').Count
"$($XDsessions.Count) total sessions: $($act) active sessions, $($XDSessions.Count-$act) disconnected sessions."
}
We make use of 3 functions:
Set-AlternatingRows - to color code the HTML report, making it easier to read
duration - if the session duration is longer than a day, will display > x days instead of hours
sqlquery - the function that executes the SQL query and returns the results
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]
}
Function duration {
[CmdletBinding()]
Param(
[Parameter(Mandatory)]
[int]$mins
)
[string] $strDuration = ""
if ($mins -gt 2880) {
$strDuration = "> $([math]::Floor($mins/1400)) days"
} elseif ($mins -gt 1440) {
$strDuration = "> 1 day"
} else {
$hh = ([math]::Floor($mins/60)).ToString("00")
$mm = ($mins % 60).ToString("00")
$strDuration = "$($hh):$($mm)"
}
return $strDuration
}
You can download the complete script here: Get-CurrentSessions.ps1. You can open the file in a PowerShell session (or the ISE) and execute it there, or you can save the file on any machine with access to the monitoring database, and execute:
<path to script>\Get-CurrentSessions.ps1
Happy scripting!
Sam Jacobs is the Director of Technology at Newtek Technology Solutions (formerly IPM, the longest standing Citrix Platinum Partner on the East Coast). With more than 40 years of IT consulting, Sam is a Citrix NetScaler, StoreFront, and Web Interface customization and integration expert. He holds Microsoft Azure Developer and Citrix CCP-N certifications, and was a frequent contributor to the CUGC CTP blog. He has presented advanced breakout sessions at Citrix Synergy for 6 years on ADC (NetScaler) and StoreFront customizations and integration. He is one of the top Citrix Support Forum contributors, and has earned industry praise for the tools he has developed to make NetScaler, StoreFront, and Web Interface easier to manage for administrators and more intuitive for end users. Sam became a Citrix Technology Professional (CTP) in 2015, and can be reached at sjacobsCTP@gmail.com.
