Why to have detailed logon information from Citrix?

Sometimes anyone is asking you, the performance is not what we like. Within a Citrix environment it is easy to get into the director to see the overview of the logon times. By default Citrix Director gives the following information about the logon:

However average is a good indication, average is not always the best method to measure something. Digging in to the numbers can be needed to get the exceptions clear. This blog will shortly explain how to get more detailed information from your database.

What kind of results do you get?

My final report shows the following:

This gives more detailed information about how long users need to wait for a logon. The data in this graph is based on the field InteractiveDesktop from the query below.

But also any table with detailed information can be made. Or query on user, query on VDI name, e.g.

My baseline tools

I used the following tools to create this graph:

Microsoft Excel
SQL Server Management Studio

The results of the query in SQL Server Management Studio can easily copied into Excel. With Pivot tables you can create this kind of graphs.

SQL code

When you run the following query on the Monitoring Database it will give a lot of output.

 1/* Citrix Director only gives Average information about logon. 
 2This query gives detailed and calculated information about the logontimes. */
 3 
 4/****** Script for SelectTopNRows command from SSMS  ******/
 5SELECT LogonDuration = DATEDIFF(Second, [LogOnStartDate], [LogOnEndDate])
 6      ,[BrokeringDuration]
 7      ,[AuthenticationDuration]
 8      ,GpoDuration = DATEDIFF(Second, [GpoStartDate], [GpoEndDate])
 9      ,InteractiveDesktop = DATEDIFF(Second, [BrokeringDate],[InteractiveEndDate])
10      ,FORMAT([BrokeringDate] , 'dd-MM-yyyy') AS EasyDate
11      ,FORMAT([BrokeringDate] , 'HH') AS EasyHour
12      ,[Name] AS VM
13      ,[ClientName]
14      ,[ClientAddress]
15      ,[ClientVersion]
16      ,[ConnectedViaHostName]
17      ,[ConnectedViaIPAddress]
18      ,[LaunchedViaHostName]
19      ,[LaunchedViaIPAddress]     
20      ,[LogOnStartDate]
21      ,[LogOnEndDate]
22      ,[BrokeringDuration]
23      ,[BrokeringDate]
24      ,[DisconnectCode]
25      ,[DisconnectDate]     
26      ,[ClientSessionValidateDate]
27      ,[ServerSessionValidateDate]
28      ,[EstablishmentDate]     
29      ,[AuthenticationDuration]
30      ,[GpoStartDate]
31      ,[GpoEndDate]
32      ,[LogOnScriptsStartDate]
33      ,[LogOnScriptsEndDate]
34      ,[ProfileLoadStartDate]
35      ,[ProfileLoadEndDate]
36      ,[InteractiveStartDate]
37      ,[InteractiveEndDate]     
38  FROM [MonitorData].[Connection]
39  LEFT JOIN [MonitorData].[Session]
40  ON ([MonitorData].[Connection].[SessionKey] = [MonitorData].[Session].[SessionKey] )
41  LEFT JOIN [MonitorData].[Machine]
42  ON ([MonitorData].[Session].[MachineId] = [MonitorData].[Machine].[Id])
43   
44   
45  WHERE Convert(date,[LogOnStartDate]) = Convert(date,GETDATE())

Conclusion

Citrix is logging a lot of information in the database, but unfortunately the Director is not showing the information. With SQL query’s there are a lot of options to query this information. All details about the values in the database can be found here: support.citrix.com Depending on the Citrix license which is purchased, more or less information is stored in the database. Please keep in mind that old records in the database will be cleared automatically. So make you’re report on time, or create something to save the data.