A frequent request of Citrix administrators is the activity in the environment. In this post, we will show you how to create a report of user logons within the past x days.
The script will accept a parameter specifying the number of days (i.e. List user logons for the past X days). In addition, you will also 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).
param ([string]$daysCovered)
$SQLServer = "SQL Server"
$SQLDBName = "CitrixMonitorDB"
There are also some optional variables to change, such as the location to save the report, the file name, and the title of the report.
$reportLocation = ".\"
$reportFileName = "UserLogons"
$title = "User Logon Report"
We begin by defaulting to a span of a week if the number of days was not specified. Then we calculate the beginning and ending dates and create a date filter for our SQL query.
if ($daysCovered -eq '') {
# default to a week - back 6 days
$daysCovered = 6
}
# set the starting and ending dates
$eDate = [datetime]::now
$sDate = $eDate.AddDays(-$daysCovered)
# create the date filter
$filter = "and monitordata.session.StartDate > convert(datetime,'"+(get-date ($sDate).ToUniversalTime() -Format "MM/dd/yyyy HH:mm:ss")+"') "
We create a SQL query to retrieve desktop sessions.
$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
,monitordata.connection.establishmentDate
,monitordata.session.StartDate
,logonduration
,enddate
,connectionstate
,username
,fullname
,monitordata.machine.HostedMachineName
,monitordata.desktopgroup.Name as appDeskName
,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]*"}
We then create a query to retrieve XenApp sessions.
[System.Collections.ArrayList]$appsessions = sqlquery -q `
"select monitordata.session.SessionKey
,monitordata.connection.establishmentDate
,monitordata.session.StartDate
,LogOnDuration
,monitordata.session.EndDate
,ConnectionState
,UserName
,FullName
,monitordata.application.Name as appDeskName
,PublishedName
,monitordata.machine.HostedMachineName
,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]*"}
We create an array to contain both XD and XA sessions, filtering by our date criteria above, and sorting by the user's name
$allsessions = $sessions |
Select-Object @{n='startdate';e={'{0:MM/dd/yy hh:mm tt}' -f $_.startdate.toLocalTime()}},
username, fullname,
@{n='enddate';e={'{0:MM/dd/yy hh:mm tt}' -f $_.enddate.toLocalTime()}},
sessionLength, appDeskName
$allsessions += $appsessions |
Select-Object @{n='startdate';e={'{0:MM/dd/yy hh:mm tt}' -f $_.startdate.toLocalTime()}},
username, fullname,
@{n='enddate';e={'{0:MM/dd/yy hh:mm tt}' -f $_.enddate.toLocalTime()}},
sessionLength, appDeskName
$sortedSessions = $allsessions | sort fullname
We calculate the session duration (in minutes).
$sortedSessions | %{
if ($_.enddate -eq $Null) {
$_.sessionlength = [math]::Round(((Get-Date) - (get-date $_.startDate)).totalminutes,0)
} else {
$_.sessionlength = [math]::Round(((Get-Date $_.enddate) - (Get-Date $_.startDate)).totalminutes,0)
}
}
Finally, we create an HTML report of the sessions, as well as a .CSV file that you can slice and dice to your specifications.
$Header = @"
<style>
body, TH, TD { font-family: Segoe UI, tahoma, Arial, sans-serif ; font-size:14px; }
h2 { font-family: tahoma; font-size:20px; }
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>
"@
$e = '{0:MM/dd/yy hh:mm tt}' -f $eDate
$s = '{0:MM/dd/yy hh:mm tt}' -f $sDate
$message = $null
$formattedsessions = $sortedSessions |
Select-Object -unique @{n='Start Date/Time';e={'{0:MM/dd/yy hh:mm tt}' -f $_.startdate}},
@{n='User Name';e={$_.userName}}, @{n='Full Name';e={$_.fullName}},
@{n='End Date/Time';e={'{0:MM/dd/yy hh:mm tt}' -f $_.enddate}},
@{n='Duration';e={duration($_.sessionLength)}},
@{n='Application/Desktop Group';e={$_.appDeskName}}
if ($formattedsessions.Count -gt 0) {
$sessionsHTML = $formattedsessions | ConvertTo-Html -head $header -Title $title -PreContent "<h2>$($title): $s - $e</h2> $($formattedSessions.Count.ToString('N0')) Sessions" | Set-AlternatingRows -CSSEvenClass even -CSSOddClass odd
}
if ($sessionsHTML -ne $null) {
$timeStamp = (Get-Date -Format u).Replace(":",".").Replace("Z","").Replace(" ","_")
$filePathName = "$($reportLocation)$($reportFileName)_$($timeStamp).html"
$sessionsHTML | Out-File $filePathName
$filePathName = "$($reportLocation)$($reportFileName)_$($timeStamp).csv"
$formattedsessions | ConvertTo-Csv -NoTypeInformation | Out-File $filePathName
}
We also make use of 4 functions:
Set-AlternatingRows - to color code the HTML report, making it easier to read
Convert-UTCtoLocal - to convert start/end times (stored in UTC) to local time
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 Convert-UTCtoLocal
{
param(
[parameter(Mandatory=$true)]
[String] $UTCTime
)
$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
$LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($UTCTime, $TZ)
}
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-UserLogons.ps1. Save the file on any machine with access to the monitoring database. Open a PowerShell command prompt or the PowerShell ISE, and execute:
<path to script>\Get-UserLogons.ps1 nn
where nn is the number of days to report on. If you omit nn, the script defaults to 7 days.
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.
