Average Session Count by Day and Hour: The Query

A common support question that my clients have asked is, “On what days of the week do we have the greatest number of people in the system?”  Or “How many users do we have on the farm?” The User Logon Counts report in EdgeSight helps you see the total number of session for each day or device, but this report doesn’t really have the answer I was looking for.  What I needed was a report that showed the number of sessions on my farm for each hour of the day. In order to do this, I needed not only a query but a report that could get me the requested information.

The first part of this solution was to create the query that will be used in the report.  This query I decided on was a combination of a standard view provided by Citrix in EdgeSight: vw_ctrx_archive_system_perf, and two tables; Company and Timezone.  You will find the query below (please note that if you run this query in SQL, you must input a start and end time for data to be returned):

SELECT     SUM(vw_ctrx_archive_system_perf.active_sessions_peak + vw_ctrx_archive_system_perf.inactive_sessions_peak) AS TotalSessions, 
                      CONVERT(varchar(10), DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 111) AS Date, CONVERT(varchar(10), 
                      DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 108) AS Time
FROM         vw_ctrx_archive_system_perf INNER JOIN
                      company ON vw_ctrx_archive_system_perf.compid = company.compid INNER JOIN
                      timezone ON company.tzid = timezone.tzid
GROUP BY vw_ctrx_archive_system_perf.time_stamp, timezone.standard_bias
HAVING      (DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp) >= @LocalStartDate) AND (DATEADD(mi, timezone.standard_bias, 
                      vw_ctrx_archive_system_perf.time_stamp) < DATEADD(d, 1, @LocalEndDate))
ORDER BY Date, Time

Lets talk about the output real fast:

The first column of data is labeled “TotalSessions”.   This data combines the total number of inactive and active sessions for any given hour of the day.  Since we want the total number of Citrix sessions on the farm and not just the active sessions,  I needed to combine both the inactive and active sessions.

The second column of data is the Date:  The dataset’s date is split out of the vw_ctrx_archive_system_perf.time_stamp data field to supply you with only the YYYY/MM/DD value.  This is much easier to use in the report that we are going to be creating.

The third column of data Time:  This is the hour of the day in GMT that the data is recorded for.  If you are on east coast time (-5), and the Time column says 00:00:00, your local time is 7:00 PM.  Because we don’t want out times to show up in GMT, we have to connect the company and timezone tables to calculate the correct time of the day, which show up in the fourth column

The fourth column of data is Time2: This column shows the time of the day with integers.  This data is converted to your timezone (but does not adjust for DST.  I will try to spend some time to work on DST at a later date).

Before I go any further with this data and creating a report, we have to dive into the grooming policies in EdgeSight.  Because this query runs off vw_ctrx_archive_system_perf, the data is subject to the EdgeSight grooming policy.  By default, this view is groomed to only hold 30 days of data.  This is an out of the box nightmare.   As most of the general public knows, are at least one or two months in the year with more than 30 days in them.  For this reason we are going to update the grooming policy to groom every 45 days.  Why 45? So that you can run this report on last month’s data, and it can be ran up to 14 days into the current month and still pull last month’s data..

To update this grooming policy: In EdgeSight go to Configure: Server Configuration: Data Maintenance: Grooming.  The policy that you will need to adjust is the one on the very bottom called: XenApp System Performance: ctrx_system_perf:  vw_ctrx_archive_system_perf.

As stated earlier, I put this figure on 45.  You can do more, but note that the view will hold more data in your EdgeSight Database.  Tune this as you see fit.

Please follow up with my next article to the creation of the Average Session Count by Day and Time report.

Or… if you would rather just play with the report and don’t feel like reading any more, Download the Report here.  Simply rename the file to a .rdl and upload it to your custom reports on your EdgeSight web server!

5 thoughts on “Average Session Count by Day and Hour: The Query”

    1. As I find time, I am working on adding parameters to my custom reports. Right now this report is for the root only. Hopefully soon I can add this type of thing to the reports I make.

  1. I love this sessions query and use it quite a bit. What I would additionally like to have is a similar query that goes against the licensing table(s). I’ve been unsuccessful in directly querying for licensing usage information in the past and find I have to resort to the Edgesight GUI and wade through its cumbersome Track Usage reporting function. The data is obviously there as the GUI will show you max license usage for about every 20 minutes throughout the day. But I would like to be able run a SQL query/report that would show one result per day – the max total license usage for that day. Any ideas?

  2. This is a great article, thanks for putting this together. I was wondering if you have a query that will pull “all” information from the database between certain dates like 01-01-2014 – 07-01-2014. It would really help me out. thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s