Average Session Count by Day and Hour: The Report

In my previous article: Average Session Count by Day and Hour: The Query,  I dove into a query that shows the number of sessions on a farm at any given hour.  Next, we go onto the creation of the report. For this I use SQL Server Business Intelligence Development Studio (BIDS).  To begin, I created a new report to contain a matrix which runs the query that I created above.  For this report to run in BIDS and EdgeSight, the entirety of the following should be placed in the query window:

DECLARE @UTCNow DATETIME
DECLARE @LocalStartDate SMALLDATETIME
DECLARE @LocalEndDate SMALLDATETIME
DECLARE @CompId INTEGER
DECLARE @Filter INTEGER

SET @CompID = 1
SET @Filter = 1

SET @UTCNow = GETUTCDATE()
SET @LocalStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 0, @UTCNow)
SET @LocalEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 0, @UTCNow)

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

In the “Design the Matrix Wizard” Add Time2 to the Columns, Date to the Rows, and Total Sessions to the Details value.  Click finish.
Edgesight Matrix wizard

The first necessary items that need to be added to the report in order for this report to run successfully are parameters.  On the data or layout tab of your new report, open the Report Parameters window.  Two parameters need to be added to the report “Start” and “End”.  They both need to be string values.  EdgeSight reads these parameters on the reports and allows you to choose what dates you want to run the report for.  Go ahead and preview the new report.  If it doesn’t work, take a break, watch an episode of Dexter, and be glad you work on computers, where everyone gets a second shot.  Let’s continue down the steps to making this a report with a bit more useful data.

In the rows where the Date is, I needed to add the day of the week to display days like the following: 8/1/2011 – Monday.  To do this I changed the formula for the cell to:  =Fields!Date.Value & ” – ” & WeekdayName(Weekday(Fields!Date.Value))

In the columns where time2 resides I needed to show the hours and not show the integer.  I’m not a big VB guy and am still working on growing my coding.  For that reason I have change the cell to the impeccableformula of: =Fields!Time2.Value & “:00:00”. This shows the hours of the day in military format.

Good enough for me.

In BIDS, to show the average of rows and columns, a bit of code needs to be added to the report.  In the Report Layout window, you can add the subtotals to the rows and columns by simply right clicking on any row and column and selecting “Subtotal”.  BIDS will add a new row or column to your report that shows the subtotal of the row or column.  The problem is that BIDS 2005, I don’t know about any newer version, only subtotals are shown and not the average.  To show the average of the columns instead of the subtotals, open the “report properties” and go to the code tab.  Based on a how-to from All About Business Intelligence add the following code:

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
        If InScope Then
            Return sumValue
        Else
            If Not avgValue Is Nothing Then
                Return String.Format(avgFormat, CDbl(avgValue))
            Else
                Return Nothing
            End If
        End If
    End Function

Next you have to change the details cell to the following code:


=Code.MatrixCellValue(
Sum(Fields!TotalSessions.Value),
Avg(Fields!TotalSessions.Value),
InScope("matrix1_Date") and InScope("matrix1_Time"),
"{0,6:N0}",
"{0,6:N1}")

Go ahead and preview the new results of your report.

After a few aesthetic changes, you can find an example data output of my finished report here:
Download – Session Count by Date and Hour – Please note that this file is set to a .doc and needs to be renamed to a .rdl in order to be uploaded into EdgeSight.

***Update 8/19/11 – I did some playing around with this report and query and have noticed that I only applied the timezone offset to the time and not the date structure.  If a user runs the report on GMT-5:00, then instead of Friday 7:00PM showing up as the time, it will show Saturday 7:00PM.  I am correcting the query as soon as I can, and will update this post and the report once it is corrected.

****Update 8/20/11 – I fixed the query and the report.  The new version of the report converts time correctly.  You can download it here

8 thoughts on “Average Session Count by Day and Hour: The Report”

  1. Hi David, your query is great. I only realised that it only works with company ‘1’. I have at least two…
    Is there any quick change possible to change this? I did some search around but did not see anything helpfull. It looks like this is quite complicated, or am I wring?

    thx
    Dieter

    1. I haven’t yet found out how to add a department or a company to a report. I’ll research it some this week and update this post when I figure it out. Being able to select a department or company is definitely key to running some of these reports, so it is something I am very interested in figuring out.

      In the meantime while I work on this fix, if you want to run the report for another company, all you should have to do is change the lines:

      SET @UTCStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 1, @UTCNow) to SET @UTCStartDate = dbo.udf_core_get_offset_date(COMPANY_ID, @Start, 1, 1, @UTCNow)
      SET @UTCEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 1, @UTCNow) to dbo.udf_core_get_offset_date(COMPANY_ID, @End, 0, 1, @UTCNow)
      SET @LocalStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 0, @UTCNow) to dbo.udf_core_get_offset_date(COMPANY_ID, @Start, 1, 0, @UTCNow)
      SET @LocalEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 0, @UTCNow) to  dbo.udf_core_get_offset_date(COMPANY_ID, @End, 1, 0, @UTCNow)
      

      and put your number of the company you need to run this for in the COMPANY_ID space. To get your company id’s open the Company table on your sql server.

      1. I could not find the first two lines to change: SET @UTCStartDate and SET @UTCEndDate. I’ve only found the last two ones.

  2. Hi, I downloaded and ran this, and it seemed to run great – the only problem is it only seems to run for XenApp environments, and I am looking to get this type of information for my XenDesktop environment. Is there any (easy 🙂 ) was for a non-SQL expert to adapt this query to XenDesktop?

    1. This query is based off of a view the Citrix has created in the EdgeSight database. The view basically is a list of servers and the total active and inactive sessions on the server. I do not see any XenDesktop info in this view. Sorry! If i get more time, I may be able to look at the reports that Edgesight has installed by default and see what I can do for you.

  3. Love this report , its like seeing resource manager, we have a PS4, XenApp 6 Farm and are seeing some strange results.

    for Example -2147481843 and also -268,432,017.8 within the normal sort of numbers we would expect.

    not sure why its displaying this figures,as these are in the middle of the night, when the farm should be quieter.

    Average
    2011/10/29 – Saturday -2147481843 -2147481933 1571 1511 1478 1465 1471 1484 1500 1519 1540 1550 1550 1529 1505 1472 1472 1451 1433 1404 1400 1365 1367 1343 -178,955,474.8

    2011/10/30 – Sunday 1325 1319 1320 1339 1352 1382 1406 1444 1494 1520 1526 1545 1537 1505 1487 1467 1465 1457 1435 1395 1380 1369 1396 1396 1,427.5
    2011/10/31 – Monday 1514 -2147481952 1814 1981 2189 2424 2894 3472 3989 4360 -2147478894 4786 4985 5149 5110 5008 4739 4227 3798 3309 3025 2691 2367 -2147481411 -268,432,017.8
    2011/11/01 – Tuesday -2147481551 1979 2029 2118 2239 2446 2776 3165 3599 3877 -2147479504 4142 4393 4613 4607 4501 4220 3901 3566 3255 3094 2748 2415 2114 -178,953,719.1
    2011/11/02 – Wednesday 2050 -2147481406 2240 2352 2544 -21474

    1. This query runs of the view ctrx_archive_system_perf. The view runs most of its items off the table ctrx_system_perf. If you run this query:

      SELECT     ctrx_system_perf.instid, ctrx_system_perf.dtperiod, ctrx_system_perf.dtid, ctrx_system_perf.inserted_date, 
                            ctrx_system_perf.i1 AS active_sessions_sum, ctrx_system_perf.i2 AS active_sessions_cnt, ctrx_system_perf.i3 AS active_sessions_peak, 
                            ctrx_system_perf.i4 AS inactive_sessions_sum, ctrx_system_perf.i5 AS inactive_sessions_cnt, ctrx_system_perf.i6 AS inactive_sessions_peak, 
                            ctrx_system_perf.i7 AS total_sessions_sum, ctrx_system_perf.i8 AS total_sessions_cnt, ctrx_system_perf.i9 AS total_sessions_peak, 
                            machine.name
      FROM         ctrx_system_perf INNER JOIN
                            instance ON ctrx_system_perf.instid = instance.instid INNER JOIN
                            machine ON instance.machid = machine.machid
      ORDER BY active_sessions_peak DESC
      

      and check the column that is called “active_sessions_peak” you can see that there will be one or two columns that will have numbers totally out of whack, either much much higher, or much much lower than the rest of the cells in that column. In the past I had a column that had over 10 million active sessions. You can alternatively order by inactive_sessions_peak to see if there are any columns that look incorrect here too. You can change this cell in the query result window to 0 and re run the query and your report should look better.

      *This is a bug inside of how edgesight writes to the database. I have do not have info on how edgesight writes data to the table and will not provide support for editing the edgesight tables. Edit at your own risk.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s