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
XenDesktop 5: Change Icon of a Catalog
Change Clientdrive Name and Icon (Example: Mein IGEL)
XenDesktop Troubleshooting – Part 1 – Overview

Leave Your Comment

Your Comment*

Your Name*
Your Webpage