cugcblogs

Be a Metrics Master: New Users

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 refresh 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’ll discuss how to record and display new users connecting to your Citrix environment. Unlike the other articles in this series, we will not use Director to collect data. Instead we will query the Citrix Site database directly.

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 New Users connecting to your Citrix environment can track the growth of your company and your Citrix environment. This metric is important to track when you’ve created a new Citrix site or recently launched a new Work-From-Home venture or any time you might have new users connecting to your environment. This could be the acquisition of another company or having to move a large percentage of your workforce home due to a major weather or pandemic event.


How to collect the data

When a new user connects to Citrix for the first time, an entry is made in the User table.

The columns in this table are as follows:

  • Id – Primary key.
  • Sid – Active Directory Security Identifier
  • Upn – Active Directory User Principal Name
  • UserName – Active Directory samAccountName
  • FullName – Active Directory Name
  • Domain – Active Directory Domain Name
  • CreatedDate – The date the user entry was made
  • ModifiedDate – The date the user entry was modified

As I’m collecting this data monthly, I’ll write my query to group new users by month/year. Here is the final SQL query I’m using:

SELECT CONVERT(char(4),DatePart(year,CreatedDate)) + ' ' + CONVERT(char(9),datename(month,CreatedDate)) as 'Month', COUNT (Username) as 'New Users'
FROM MonitorData.[User]
GROUP BY CONVERT(char(4),DatePart(year,CreatedDate)) + ' ' + CONVERT(char(9),datename(month,CreatedDate))

To show how this query transforms the data, we’ll start with the raw table:

SELECT *
FROM MonitorData.[User]

We just need the date and the usernames so we can count them by month. This query selects just these two columns.

SELECT CreatedDate, Username
FROM MonitorData.[User]

Now that we have these two columns, let’s make the result look better. First, we’ll convert the date to show the month.

SELECT CONVERT(CHAR(9),DateName(Month,CreatedDate)) as 'Month', Username
FROM MonitorData.[User]

We’re taking the CreatedDate column and using DateName to select the Month that’s in the CreatedDate value. In addition, I’m using the Convert function to change the data value into text (or CHAR).

We see the month, but that’s all we see. If your Citrix site lasts longer than a year, we must work on this query some more so we can count the number of users accurately.

SELECT CONVERT(CHAR(4),DatePart(Year,CreatedDate) + ' ' + CONVERT(CHAR(9),DateName(Month,CreatedDate)) as 'Month', Username
FROM MonitorData.[User]

Now I’ve added the Year using the DatePart function to select the year. Again, I’m using Convert to make this all text. This allows me to ‘add’ the 2 queries together into one field.

For the final part of the query, we will total the number of users by month. We use the Count function. We will also have to use the Group By statement otherwise we’ll get an SQL error.


How to record/display the data

There are many tools that can be used to display this information, but we’ll use Excel in this example as it’s the one I’m most familiar with and probably readily available to most Citrix Administrators. This metric only records initial new connections to your environment. I record the number monthly by year in a simple table.

This can be easily compared year-on-year using a line graph.


Conclusion

Tracking new users to your environment can show you new hires connecting, a new company acquisition or a change that pushed employees to work from home. Hopefully 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

One comment

Leave a Reply