Discover What Citrix Director Won’t Show You: Which User/Machine Had Long Log on Duration in XenDesktop 7 5

Citrix Director is great for showing you trends for logon performance, but you can’t yet “drill down” to determine which user specifically had the long log on duration.  Luckily enough, the SQL database does have this information.  The query below will show you all logons from the current day and sort them by LogOnDuration.

SELECT MonitorData.Session.StartDate, MonitorData.Session.LogOnDuration, MonitorData.Session.EndDate, MonitorData.Session.ExitCode, 
 MonitorData.Session.FailureDate, MonitorData.Session.ConnectionState, MonitorData.Session.ConnectionStateChangeDate, MonitorData.Machine.DnsName, 
 MonitorData.Machine.HostingServerName, MonitorData.Machine.HostedMachineName, MonitorData.[User].Upn
FROM MonitorData.Session INNER JOIN
 MonitorData.[User] ON MonitorData.Session.UserId = MonitorData.[User].Id INNER JOIN
 MonitorData.Machine ON MonitorData.Session.MachineId = MonitorData.Machine.Id
WHERE (CONVERT(varchar(10), MonitorData.Session.StartDate, 102) = CONVERT(varchar(10), GETDATE(), 102)) AND (MonitorData.Session.LogOnDuration > 0)
ORDER BY MonitorData.Session.LogOnDuration DESC

Alternatively, you can use this query for a more streamlined output with key data – Machine name, username, logon time (in seconds) with local time rather than UTC

SELECT CONVERT(datetime,switchoffset(convert(datetimeoffset,MonitorData.Session.StartDate),datename(tzoffset,sysdatetimeoffset()))) as StartDateLocalTime,cast((cast(MonitorData.Session.LogOnDuration as decimal(8,2))/1000.0) as decimal(8,2)) as LogOnDurationSeconds, MonitorData.Machine.DnsName, 
 MonitorData.Machine.HostingServerName, MonitorData.Machine.HostedMachineName, MonitorData.[User].Upn
FROM MonitorData.Session INNER JOIN
 MonitorData.[User] ON MonitorData.Session.UserId = MonitorData.[User].Id INNER JOIN
 MonitorData.Machine ON MonitorData.Session.MachineId = MonitorData.Machine.Id
WHERE (CONVERT(varchar(10), MonitorData.Session.StartDate, 102) = CONVERT(varchar(10), GETDATE(), 102)) AND (MonitorData.Session.LogOnDuration > 0) and EndDate IS NULL
order by StartDateLocalTime desc