One of my customers came up to me and asked me how to query the Citrix XenDesktop Database using SQL queries to get a list of all connections that have been made.
The first thing to keep in mind is, that XenDesktop stores the information only for a couple of days – depending on your license (Enterprise or Platinum licenses). So if you’re interested in a long-term solution, then you should put the output / result of my query into another database or file for archiving.
You need to run this query against your Citrix XenDesktop Monitoring Database:
SELECT MonitorData.Connection.Id AS LogId, MonitorData.Session.SessionKey, MonitorData.Session.StartDate, MonitorData.Connection.BrokeringDate, MonitorData.Connection.ClientName, MonitorData.Connection.ClientVersion, MonitorData.Connection.ConnectedViaIPAddress, MonitorData.[User].Upn FROM MonitorData.Connection LEFT OUTER JOIN MonitorData.Session ON MonitorData.Connection.SessionKey = MonitorData.Session.SessionKey LEFT OUTER JOIN MonitorData.[User] ON MonitorData.Session.UserId = MonitorData.[User].Id WHERE (MonitorData.Connection.ClientName IS NOT NULL) AND (MonitorData.Connection.Protocol = 'HDX')
Please note that I’m not an SQL expert so maybe some of you guys will find a better way to do the job. The result of the query will be:
Here are some details on the columns that are shown:
|LogId||Citrix Log ID|
|SessionKey||Session ID to match connection to a Session|
|StartDate||Session start date|
|BrokeringDate||Time when the session was brokered|
|ClientName||Name of the Endpoint|
|ClientVersion||Citrix Receiver Version|
|ConnectedViaIPAddress||Citrix NetScaler IP Address if using ICA-Proxy|
The XenDesktop Monitoring database does contain a lot more details about the user sessions which you can access just by modifying the SQL query.