cugcblogs

Be a Metrics Master: Daily Unique Users and Total Sessions

by Alain Assaf, CTA and Raleigh-Durham CUGC Leader

Intro

I have been working with Citrix deployments for many years, and the primary aspect of these environments is change. You could add a new software package to thousands of users or fresh your server hardware. Collecting and tracking performance metrics over time helps you to understand how these changes affect your user capacity and user experience. In this post, we will discuss how to record and display daily unique users and sessions accessing your Citrix environment. We will query the Citrix Site database directly to get this data.

Note: This post assumes you are running MS SQL Management Studio (pics are from ver 18.4 and have access to Microsoft Excel (365 on-prem)

Other blogs in this series:


Why Track this Metric

Recording the unique users who connect to your Citrix environment daily can show the growth of your company and your Citrix environment over time. You can track seasonal usage, which can better prepare you to grow or shrink your environment depending on demand. You can also see how weekend usage varies from weekday usage. Tracking sessions also lets you measure your Citrix usage and (on average) how many sessions are generated by your users.


How to Collect the Data

Every time a user starts a session in Citrix, entries are made in several tables. Fortunately, the Monitoring database also uses Views to summarize data from multiple tables. We can get most of our information by querying the SessionV1 View and the rest by cross referencing the user information with the Session and User tables.


The columns in this View are as follows:

  • SessionKey – Primary key for View
  • StartDate – Session start date and time
  • LogonDuration – Time it takes for a session to start
  • EndDate – Active Directory samAccountName
  • ExitCode – Active Directory Name
  • FailureDate – Active Directory Domain Name
  • ConnectionState – The date the user entry was made
  • ConnectionStateChangeDate – The date the user entry was modified
  • LifecycleState – The date the user entry was modified
  • CurrentConnectionId – The date the user entry was modified
  • UserID – The date the user entry was modified
  • MachineId – The date the user entry was modified
  • CreatedDate – The date the user entry was modified
  • ModifiedDate – The date the user entry was modified


Here is how the SessionV1 view relates to the Connection and User Tables (this will be used in the SQL query below).


I record the unique users and session data monthly, but I want it for every day of the month. Here is the final SQL query I am using:

SELECT CONVERT(VARCHAR(10),LogOnStartDate,111) as 'Date', COUNT(DISTINCT MonitorData.SessionV1.sessionKey) as 'Total Session', COUNT(DISTINCT MonitorData.SessionV1.Userid) as 'Unique Users'
FROM MonitorData.SessionV1,MonitorData.Connection,MonitorData.[User]
WHERE MonitorData.SessionV1.SessionKey = MonitorData.Connection.SessionKey and MonitorData.[User].Id = MonitorData.SessionV1.userid
GROUP BY CONVERT(VARCHAR(10),LogOnStartDate,111)
ORDER BY ONVERT(VARCHAR(10),LogOnStartDate,111)

To show how I arrived at the final query, we’ll transform the data step-by-step. First, we’ll will start with the raw data from the 2 tables and the SessionV1 view.

SELECT *
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]


This generates a huge amount of rows and far too many columns (59 of them!). I had to cancel the query which had already returned over 5 million rows. To reduce the volume of data, let’s add some conditions to relate the tables and view together. We want the SessionKey column from the SessionV1 view and the Connection table to be the same. We also want the ID column from the User Table to match the UserID column from the SessionV1 view. Doing this relates the rows in the User and Connection table to the SessionV1 view, thus we should only get back data for users and connections that match in the 2 tables and the view. Let’s see if this change reduces the amount data we are querying.

SELECT *
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid



Our query finished under a minute and returned 800,000+ rows. Still too much information, but we’re on the right track. For this report, we just need the date and time the session started, the SessionKey, and UserID from the SessionV1 view. Let’s add these to the SELECT statement and see the results.

SELECT LogOnStartDate, MonitorData.SessionV1.sessionKey, MonitorData.SessionV1.Userid
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid



With just three columns, the query was 14 seconds. We can now start to manipulate the results to get data we can report on. First, we’ll convert the date to show the month.

SELECT CONVERT(VARCHAR(10),LogOnStartDate,111) as 'Date', MonitorData.SessionV1.sessionKey, MonitorData.SessionV1.Userid
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid

I’m using the Convert function to change LogOnStartDate to text (or CHAR) from DATETIME. I chose the formatting style of 111 which formats the date as yyyy/mm/dd and removes the time part of the DATETIME field. Finally, I set the column name to ‘Date’.



I want to count the total number of sessions and users per day. To do this, we have to use GROUP BY to group all the dates together. However, just adding Group By CONVERT(VARCHAR(10),LogOnStartDate,111) will not be enough and will result in an error. We must also add an aggregate function to the other two columns. Since I want a count of the total users and sessions by day, I will use the COUNT function. I’ll also name the other 2 columns to make them easier to identify.

SELECT CONVERT(VARCHAR(10),LogOnStartDate,111) as 'Date', 
       COUNT(MonitorData.SessionV1.sessionKey) as 'Sessions', 
	   COUNT(MonitorData.SessionV1.Userid) as 'Users'
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid
GROUP BY CONVERT(VARCHAR(10),LogOnStartDate,111)



We are getting closer to our final results. I am going to add an ORDER BY clause to sort the Date column.

SELECT CONVERT(VARCHAR(10),LogOnStartDate,111) as 'Date', 
       COUNT(MonitorData.SessionV1.sessionKey) as 'Sessions', 
	   COUNT(MonitorData.SessionV1.Userid) as 'Users'
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid
GROUP BY CONVERT(VARCHAR(10),LogOnStartDate,111)
ORDER BY CONVERT(VARCHAR(10),LogOnStartDate,111)



For the final part of the query, I want to count the distinct sessions and users. This will give us totals of unique sessions and users per day. If you wanted to get a raw total, you can leave the DISTINCT argument out of the query.

SELECT CONVERT(VARCHAR(10),LogOnStartDate,111) as 'Date', 
       COUNT(DISTINCT MonitorData.SessionV1.sessionKey) as 'Sessions', 
	   COUNT(DISTINCT MonitorData.SessionV1.Userid) as 'Users'
FROM MonitorData.SessionV1, MonitorData.Connection, MonitorData.[User]
WHERE MonitorData.SessionV1,SessionKey = MonitorData.Connection.SessionKey AND MonitorData.[User].Id = MonitorData.SessionV1.userid
GROUP BY CONVERT(VARCHAR(10),LogOnStartDate,111)
ORDER BY CONVERT(VARCHAR(10),LogOnStartDate,111)



From your query in SQL Management Studio, you can save the results as a CSV file.


How to Record/Display the Data

There are many tools that can be used to display this information, but we will use Excel in this example as it’s the one I’m most familiar with and probably readily available to most Citrix Administrators.

All Time
In our CSV file, we select the Date, Sessions and Users Columns. Click the Insert Menu and then Recommended Charts. Excel should choose Line.



Click OK and the graph is inserted into your worksheet.



The x-axis will show the dates. Every month, you export and paste the data to your worksheet and eventually, you will have a graph that is like the one below that spans several years.



You can add a Linear Trendline to track how your sessions change over time. Under the Design Menu, select your graph, then click (1) Add Chart Element, (2) then Trendline, (3) and finally Linear.



Another window will appear and let you select Users or Sessions for your trendline.



By Year
If you want to track changes year-to-year or month to month, you can create a dataset for each year/month. I find Excel’s 3D graphs best for showing this relationship. Using the same data, you create a dataset for each year and only select the users or sessions for that year. Order the datasets with the most recent year on top. The horizontal axis will be the same rage for each year. For example, for the 4 datasets, they all use the dates for 2018 as their horizontal axis. Selecting a 3-D Area Graph gives you the following.



You can duplicate this graph to show the Sessions Per Day by Year.

Conclusion

Tracking your users and their sessions on your servers can show you seasonal trends, overall growth, and unique situations like the COVID-19 pandemic and how it effects utilization of your Citrix environment. I hope the examples above give you an idea of how you want to collect and report on this information. Please comment if you have any questions.

Learn More


Thanks for reading,

Alain Assaf
Citrix Technology Advocate
Raleigh-Durham CUGC Leader

​​​

Leave a Reply