Tag Archives: Citrix

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!

EdgeSight: BRIFORUM Content

Intro
To everyone who attended my session at Briforum, thank you.  It was an honor to present and to meet so many Virtualization rock stars.  As promised, I am posting most of the content from my presentation at Briforum.  There were a few new queries that were not yet part of the site in addition to a few that I did not have time to get to.  Also, I meant what I said about converting ESUTH to a forum so that we can have multiple authors.  If you have some good Edgesight Queries to share, let me know and I will make you an author on the site!

Holistic Query
This query is designed to give you your basic system performance including Memory and Disk Queue.  You can use this to query specific metrics of a system during a reporting period (by hour).  The only value you have to manually enter is the date in the format you see above.  You will take the “Machine Name” and “Time Stamp” column values and enter them into the query below to get more detail.

The Query:

select machine_name, dateadd(hh,-5,time_stamp), disk_time_sum/disk_time_cnt as "Disk Time", total_processor_time_sum/total_processor_time_cnt as "Processor Time",
(committed_Kbytes_sum/committed_kbytes_cnt)/1000.0 as "Committed memory", Context_switches_sec_sum/Context_switches_sec_cnt as "Context Switches",
current_disk_queue_length_sum/current_disk_queue_length_cnt as "Disk Queue Length"
from c3edgesight.citgop_edgesightxa.dbo.vw_es_archive_system_performance
where  machine_name like '%ComputerName%'
and CONVERT(varchar(10),dateadd(hh,-5,time_stamp),101) = '07/14/2011'
order by dateadd(hh,-5,time_stamp)

Gives us:

machine_name Time Disk Time Processor Time Committed memory Context Switches Disk Queue Length
SVR-DS81 7/14/11 13:00 0.2470124 6.543246415 4140.62 9686 0
SVR-DS66 7/14/11 13:00 0.1102058 26.54753931 2491.53 3814 0
SVR-DS68 7/14/11 13:00 0.2426049 5.727161964 3956.425 9090 0
SVR-DS69 7/14/11 13:00 0.140912 3.041551342 4347.409 8636 0
SVR-DS83 7/14/11 13:00 0.2029572 4.014888507 4259.335 7362 0
SVR-DS67 7/14/11 13:00 0.1474974 29.02722529 3434.519 6479 0
SVR-DS80 7/14/11 13:00 0.1451845 3.177582946 4403.747 8482 0
SVR-DS74 7/14/11 13:00 0.1923944 3.841824931 4391.01 7557 0
SVR-DS78 7/14/11 13:00 0.1418489 2.496465329 4249.419 6625 0


Drilling Down:
So, if looking at the results of the query above uncovers an issue of high CPU, RAM or Disk Que you can drill down with the following query.  In this example we will copy the Machine_name column and Time_Stamp column and paste them in between the single quotes to populate the variable.  So we will copy SVR-DS81 and 2011-07-14 13:00:00.000 which will give us a list of every processor owned by every user on the system during that reporting period.

declare @MachineName nvarchar (255)
declare @timestamp datetime
set @MachineName= 'SVR-DS81'
set @timestamp = '2011-07-14 13:00:00.000'
select account_name, exe_name, App_description, total_processor_time_sum/total_processor_time_cnt as "Processor Time",(Private_kbytes_sum/private_kbytes_cnt)/1000 as "Private Memory(megs)", pages_sec_sum/Pages_sec_cnt as "Pages/Sec"
from vw_es_archive_application_performance
where DATEADD(hh,-5,time_stamp) = @timestamp
and machine_name = @MachineName
order by "pages/sec" desc
account_name exe_name App_description Processor Time Private Memory(megs) Pages/Sec
JSmith Excel.exe Microsoft Office Excel 0.00021222 194 2
FJones Outlook.exe Microsoft Office Outlook 1.12339166 91 60
SStills Cutefiller.exe CutePDF Form Filler 1.66994562 87 127
NYoung Wfica32.exe Citrix 0.00010988 83 0
SPickens Outlook.exe Microsoft Office Outlook 0.05320751 79 14
CWebster Wfica32.exe Citrix 0.01940179 78 0
SBASS Outlook.exe Microsoft Office Outlook 2.86540794 65 99
SYSTEM Pmagent.exe Performance Manager Agent 0.13332299 47 44
Jsmith Outlook.exe Microsoft Office Outlook 0.00087658 45 0
SYSTEM Icalm server.exe iCALM Server 0.01223421 44 3
DFeller Explorer.exe Windows Explorer 0.12927156 43 27
JOlsen Outlook.exe Microsoft Office Outlook 0.51810852 43 142

How long did a user’s session last:
So this is one of my cross-tab queries that you can copy and paste and just change the username for.  This will give you the date, sessoin ID, logon and logoff time as well as the length of the session for a specific user.  In this instance I am NOT pulling data from the views, rather I am pulling the data from two tables, the session table and the principal table.  This query can be handy when you are trying to audit the time a user is on the system.

The Query:

declare @today datetime
set @today = convert(varchar(10),getdate(),111)
select convert(varchar(10),dateadd(hh,-5,logon_time),111), inst_sessid, dateadd(hh,-5,logon_time),
dateadd(hh,-5,logoff_time),
convert(varchar,floor(datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time))/60))+ ':' +Right('0' + convert(varchar,datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time)) % 60), 2) as HrMinfrom session s, principal p
where convert(varchar(10),dateadd(hh,-5,logon_time),111) > @today-30
and account_name = '%AccountName%'
and p.prid = s.prid

Average Start/End Time:
The idea for this Query came about after listening to Chris Nickerson (one of the top Social Engineers in the world) discuss how, as part of penetration testing, they can call a helpdesk and try to get them to change a passoword after finding a  user ID for a particular company.  I tend to think of this query as a social engineering counter measure.  What it gives me is the average logon and logoff time for a particular user.  How this can be valuable would be if you could set it up as part of the helpdesk solution so that when someone calls in, the helpdesk knows the average logon and logoff time for the person they are talking to.  If a person who normally works M-F 8AM to 5PM calls in at 2:15AM wanting to change their password, the helpdesk technician can maybe ask a few more questions or be more alerted to a possible social engineering attempt.  This query is still not entirely vetted but as I have changed employers, I may not get a chance to vet it further.  I have noticed it is accurate for standard users but for Admins who may have that “special” ICA Timeout, it my show some skewed results because lets face it, we are on the system pretty much 24×7.  This query gives the average logon and logoff time for the last 7 days.

The Query:

select account_name, right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logon_time)as float) - floor (cast(dateadd(hh,-5,logon_time) as float)))as datetime)),100),7),
right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logoff_time)as float) - floor(cast(dateadd(hh,-5,logoff_time) as float)))as datetime)),100),7)
from session] s, principal p
where s.prid = p.prid
and account_name = '%UserName%'
and  dateadd(hh,-5,logon_time) >= GETDATE()-7
group by account_name

EdgeSight: Filtering a Query by IP Subnet

es_logoPrologue
Many of EdgeSight’s tables and views have a field for the client’s IP address, and this is stored as variable-length character string (varchar or nvarchar). In order to sort or filter on this field you must use a complex regular expression or find a way to split the field into different octets. In this blog post, we will do just that by presenting a problem that requires finding users based on their subnet…

Intro
Thanks to the vibrant competition present in the virtualization space, many Engineers find themselves always transitioning to the next version of their virtualization solution. During such a transition, management (and hopefully the engineers) want to know who’s using the new system and if users are still accessing the old one. In many cases this can be a trivial exercise, but for this scenario we’ll make it more complex.

Scenario
The networking team has intelligently organized its users’ locations by subnet. In fact, due to number of users and available IP’s, each floor at the main location has it’s own subnet. Recently, Citrix users at the main location were transitioned to the new environment, except for a subset who had legacy applications that would not work in the new Citrix farm. Management wants to know many of the transitioned users are using the new system.

Problem
Since we are using published desktops in both the old and new Citrix environments, EdgeSight (version 5.3) does not provide an easy way to query desktop launches (see this post on EdgeSight Under the Hood for how to get a query of published desktop launches). In this case, we have a different naming schema for the servers in the new farm, but since there are many different locations connecting back to our Citrix farms, we need to just select the users at the main location. This will require us to filter the users based on their IP subnet.

PARSENAME
While researching this issue I found that dealing with IP addresses in Transact-SQL is a common problem. Luckily there is a built-in function called PARSENAME that parses object names like ‘servername.databasename.schemaname.objectname’. Since IPv4 addresses follow the same convention, you can reference each part of the octet in an IP address.

For example:

DECLARE @IP nvarchar(15)
SET @IP = '192.168.1.1'
SELECT PARSENAME(@IP,4) AS 'Octet 1',
PARSENAME(@IP,3)AS 'Octet 2',
PARSENAME(@IP,2)AS 'Octet 3',
PARSENAME(@IP,1)AS 'Octet 4'

Gives us:

Octet 1    Octet 2    Octet 3    Octet 4
---------- ---------- ---------- ----------
192        168        1          1

(1 row(s) affected)


The query

For this query we will use vw_ctrx_archive_server_start_perf which has become my goto view for client related information and just sort by one subnet: 192.168.1.0 – 192.168.1.101 and look at the last 3 days of data

DECLARE @today datetime
SET @today = CONVERT(varchar(10),getdate(),111)
--we are using DATEADD and offsetting by minus four hours due to Eastern Daylight Time
SELECT CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) >= @today-3 --past 3 days
and [user] <> 'UNKNOWN'
--Gets NEWSERVER01, NEWSERVER02, etc
and machine_name like 'NEWSERVER%
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'  and (PARSENAME(client_address,2) = 1 and PARSENAME(client_address,1) between 0 and 101
GROUP BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111), [user]
ORDER BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111)

For our second example, we’ll sort with 11 sub-nets:
192.168.1.0 – 192.168.1.101
192.168.2.0 – 192.168.2.102
192.168.3.0 – 192.168.3.103
192.168.4.0 – 192.168.4.104
192.168.5.0 – 192.168.5.105
192.168.6.0 – 192.168.6.106
192.168.7.0 – 192.168.7.107
192.168.8.0 – 192.168.8.108
192.168.9.0 – 192.168.9.109
192.168.10.0 – 192.168.10.110
192.168.11.0 – 192.168.11.121

DECLARE @today datetime
SET @today = convert(varchar(10),getdate(),111)
SELECT CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) >= @today-3
and [user] <> 'UNKNOWN'
and machine_name like 'NEWSERVER%'
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'
and (PARSENAME(client_address,2) =1 and PARSENAME(client_address,1) between 0 and 101
or PARSENAME(client_address,2) =2 and PARSENAME(client_address,1) between 0 and 102
or PARSENAME(client_address,2) =3 and PARSENAME(client_address,1) between 0 and 103
or PARSENAME(client_address,2) =4 and PARSENAME(client_address,1) between 0 and 104
or PARSENAME(client_address,2) =5 and PARSENAME(client_address,1) between 0 and 105
or PARSENAME(client_address,2) =6 and PARSENAME(client_address,1) between 0 and 106
or PARSENAME(client_address,2) =7 and PARSENAME(client_address,1) between 0 and 107
or PARSENAME(client_address,2) =8 and PARSENAME(client_address,1) between 0 and 108
or PARSENAME(client_address,2) =9 and PARSENAME(client_address,1) between 0 and 109
or PARSENAME(client_address,2) =10 and PARSENAME(client_address,1) between 0 and 110
or PARSENAME(client_address,2) =11 and PARSENAME(client_address,1) between 0 and 121)
GROUP BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111), [user]
ORDER BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111)

Hopefully this will provide you with some more options when you need to present data from your EdgeSight database. As always I welcome any and all questions and comments.

Thanks,
Alain