Jump to content
Welcome to our new Citrix community!

  • cugcblogs

    alainassaf22rnd.png 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.

    newusers1-3.png.e3f74197438f441174806b4903be0374.png

    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]
    newusers2.png.19a001e89dc76c28916ef08722ef94bc.png

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

    SELECT CreatedDate, UsernameFROM MonitorData.[user]
    newusers3.png.d69bf2723427755dadf64bb2cf9ff8af.png

    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', UsernameFROM 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).

    newusers4.png.c08e3492c82d954090aa357d945b5563.png

    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', UsernameFROM 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.

    newusers5.png.20c8c7177e3968a0f4e0741a6d66351c.png

    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.

    newusers6.png.afa764d79ab490739205065b5de11a09.png

    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.

    newusers7.png.2daf3dbdee91d7afa1ea0740bae32741.png

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

    newusers8.png.0a805fda58890355de752c17dffd3315.png

    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

    newusers1.png.e50aa803395de00ab7ecbdd4e6bb2c5f.png

    newusers1-1.png.2a92c911dc055de22510ff632d1cd88a.png

    newusers1-2.png.e951ea1868ff55c11c43a233ca97038f.png


    User Feedback

    Recommended Comments

    Guest Be a Metrics Master: Daily Unique Users and Total Sessions – BLOGS

    Posted

    […] Be a Metrics Master: New Users […]
    Link to comment
    Share on other sites



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×
×
  • Create New...