All posts by David Rowe

Custom User Groups based on Domains

In an hosting environment, you may have multiple customers logging into your Citrix environment. If you have domain trusts set up, the users may be authenticating and launching applications based on their domain credentials. In edgesight you can run reports based on user groups configured based on each individual domain, but first it has to be configured correctly.

The first thing that needs to happen is you have to get the list of domains from the Edgesight database.

SELECT     domain_name
FROM         vw_es_usergroup_ica_users
GROUP BY domain_name
ORDER BY domain_name

You are then presented with results like the following:

Getdomains

The information you need from this query is the text that is presented in the results. Next you will need to go into your edgesight website. GO to Configure –> Company Configuration –> User Groups. On this window you will need to click New User Group. Enter a name for the domain group you are about to create and make sure that “Take me to the Add Members Page” is checked.

CreateGroup

On the next page make sure you select “Queries” and click “Next” A new page will open and ask you to select a query. Here you will want to select “New Query”. This is where you need to remember your results from the first query. Once you click “New Query”, you will have to type in a name for the query, and in the query text window enter the following:

SELECT     sessid
FROM         vw_es_usergroup_ica_users
WHERE     (domain_name = 'Customer')

createquery
Save the query and test it out. You should see all the users in the domain you listed. In the bottom window make sure you click “Add Query” so that the group uses the query you just created. Congratulations! You can now use this user group to run reports.

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

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!