XenDesktop

Citrix XenDesktop SQL Database: Display all client connections

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:

XenDesktop_SQL-Query

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.

You may also like
Get running processes including CPU and memory usage
XenDesktop Troubleshooting – Part 1 – Overview
SESSIONID.EXE – Query Terminalserver Session ID

Leave Your Comment

Your Comment*

Your Name*
Your Webpage