Tag Archives: Citrix SQL Reporting

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!

Reporting On Non-PC Devices

Intro

Today’s workplace no longer follows a strict standard in terms of endpoint devices.  Despite the efforts of your infrastructure, network, and security teams users are connecting non-approved devices to your network and your Citrix farm.  A lot has been said about the “Consumerization of IT” and it is a reality for any Citrix administrator/engineer.  In this blog post we will explore how to find these types of devices using EdgeSight.

The Query

We will use the VW_ES_USERGROUP_ICA_USERS view for this query.  Here are the columns in this view:

image

Here is a sample of data in this view (customer specific information hidden):

image

Mobile Devices

The following query will select mobile devices that connected to your farm in the last 30 days.

SELECT CONVERT(VARCHAR,dtlast,111) AS 'Date', account_name, client_buildnum, client_productid, client_disp_horiz, client_disp_vert
FROM vw_es_usergroup_ica_users
WHERE client_name = 'mobile'
and account_name <> 'UNKNOWN'
and CONVERT(VARCHAR,dtlast,111) >= getdate() - 30
ORDER BY 'Date' DESC

Here’s a sample of the output:
image

The new Citrix Receiver sets the client name to ‘mobile’. On a PC this is typically the environment variable %COMPUTERNAME%.  To find the devices that are connecting, you can use the horizontal (client_disp_horiz) and vertical (client_disp_vert) resolutions and compare them to current resolutions of mobile devices.  I found a nice reference list here.  This can get you half-way there.  The only other way that I’ve been able to distinguish the client that is connecting are the ‘client_buildnum’ and ‘client_productid’ fields.  Unfortunately, finding an updated list of ICA/Receiver build numbers is not easyStephane Thirion at Archy.net provides a recently updated list.

Thin Clients

We can also use VW_ES_USERGROUP_ICA_USERS to report on thin client devices.  It is unlikely that thin clients will be an unapproved device on your network, but we can get some useful data on them from this view.  The following query will select thin client devices that connected to your farm in the last 30 days.

SELECT  CONVERT(VARCHAR,dtlast,111) AS 'Date', account_name, client_directory, client_version, client_buildnum, client_productid, client_disp_horiz, client_disp_vert
FROM vw_es_usergroup_ica_users
WHERE client_directory like '\%'
and account_name <> 'UNKNOWN'
and CONVERT(VARCHAR,dtlast,111) >= getdate() - 30
ORDER BY 'Date' DESC

Here’s a sample of the output:
image

If the thin client is windows-based, chances are the client_version field will give you the currently installed ICA client on the device.  You can use this information to pester the person in charge of thin client’s to update them or replace them (just kidding – but really you need to get them updated).  For this example, we examined the ‘client_directory’ column and determined that if it started with a  ‘\’, it was a thin client.  You may have to experiment with this field depending on which thin clients you have in your environment.

I hope this post has shown you how to track down non-pc devices connecting to your Citrix farm.  Once you have determined the ICA/Java client versions connecting to your farm (see the ICA Client Version report in EdgeSight!) you can modify these queries to find Java client users and Macintosh users.

As always, I welcome all comments and questions.

Thanks,
Alain

Reporting on Alerts

Intro

EdgeSight allows to you to create alerts that trigger on many criteria.  In this post, we will configure an alert and show how to query the database directly to get this information.

Creating an alert

For the purposes of this post, I have created a Process Hung alert for outlook.exe.  This is a built-in Application Error alert that can trigger on the EXE file name, the application description, the process file version, and/or the process company name.  The actual alert will show up in the Farm Monitor and Alert List view under the Monitor Tab in the EdgeSight console.

Now you will get a near real-time alert in the console that looks like this:
image

I found that this alert triggered quite often and while you can use the “Process Not Responding Alert” report, this blog is all about pulling back the veil.

The Query

We will use the VW_ES_ARCHIVE_ALERT view for this query.  Here is an example of all the columns in this view (customer specific information hidden):

image

For our purposes, I want to get the date of the alert, the machine name, the username, the process name, the process description, and the actual text of the alert.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name,alert_text
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This gives me:
image

If you look at the alert_text field, you will see some information that doesn’t look right.  You can see “Microsoft Office Outlook”, a weird character, and a series of numbers.  These numbers are in fact the actual process hang measured in milliseconds.  You can see this if you go back to the farm monitor and select the detail for an alert:

image

You have the information you need to determine who is having a real long delay, but how can we sort or organize this delay information.  There is no built-in MSSQL function to break this column up into two useful fields.  A Google search pointed me to a user-written function that will strip non-alphanumeric from a column.

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression =  '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END

Once you execute this in the MSSQL Management Studio, you can reference the function in your query:

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, dbo.fn_StripCharacters(alert_text, '^a-z0-9')
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

This now gives us:
image

Now the special character is gone, but how can you split the process delay out of the column?  You can use a built-in MSSQL function call SUBSTRING.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6) AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)

Now we get:

image

To finish up, we’ll divide the Delay by 1000 to get the delay in seconds.

DECLARE @mydate DATETIME
Set @mydate = GETDATE()
SELECT DISTINCT CONVERT(VARCHAR,time_stamp,111) AS 'Date', machine_name, account_name, exe_name, alert_name, CONVERT(INTEGER,SUBSTRING(dbo.fn_StripCharacters(alert_text, '^a-z0-9'),23,6),10)/1000.0 AS 'Delay'
FROM vw_es_archive_alert
WHERE alert_name = 'Process Hung'
and exe_name = 'Outlook.exe'
and CONVERT(VARCHAR,time_stamp,111) = CONVERT(VARCHAR,@mydate,111)
ORDER BY 'Delay' desc

Our end result:

image

With this information, you can do further manipulation including counting the number of alert instances for a user or tracking a single user over time.

As always I welcome all questions and comments.

Thanks,
Alain

Monitoring Advanced Persistent Threat, Malware and general punk busting with EdgeSight

Monitoring for Malware and zombies using Edgesight

Recent security breaches known as Advanced Persistent Threat rattled Security Experts at Google after finding out that a zero-day vulnerability in IE was exploited and allowed for Malware to be downloaded to key computers. This exploit details the half-measure of Security today and in my opinion was preventable, or at least could have been greatly mitigated with better logging and accountability.

I feel that too many IT Security teams are so overwhelmingly focused on ingress that the fact that the inmates are running the asylum goes largely unnoticed once Malware makes it into the network. A similar analogy would be to vigorously monitor who comes into a store, however, if out of the millions of people(packets) one or two make it through, they can take whatever the hell they want and they can leave unmolested. I’m just gonna say it….Security teams are flat out lazy when it comes to logging, in fact I read a great post from Dr. Anton Chuvakin called “Just Shut up and log!” Logging is NOT the short straw on the INFOSEC team duties, it’s the crown jewel, and if done properly, you can mitigate a number of Security Related issues by actually looking at EGRESS points and seeing where packets are going. This can be done monitoring outgoing build-up and tear-downs on Pix systems or, in the case with Edgesight, actually monitor the very Executables that are making remote calls on systems that have the Edgesight Agent installed. I think that we have a better chance of curing the common cold than of getting rid of malware and bots for the next few years. This business of completely or largely ignoring EGRESS points has got to change and depending on an INFOSEC group for your security on systems that they do not oversee on a day to day basis will get you nowhere. Unless you can provide your security group with a crystal ball or get them a million incident support agreement with Dionne Warwick and her physic friends Network , you need to take responsibility for your own security using INFOSEC as an escalation point. You know your systems, you know what an anomaly is for your boxes and you need to take responsibility.

That said, while Edgesight may not have been intended as a Security tool, it does have some extremely valuable data in it that can help you take responsibility for your systems.

It’s 3AM…do you know where your server is going?

I think it is fair to ask, why would any executable, with the exception of firefox.exe, iexplore.exe, etc need to make a TCP 443 session to an IP in China? Why would “someEXEthatIhaveNeverHeardOf.exe” make 200 TCP 443 connections to an IP in China or Russia?

I am going to cover a quick query that will allow you to interrogate all of your systems for where they were last night and what ports, protocols and destinations they were communicating with. You don’t need to look at Pix Firewall syslogs and you don’t need to “hope” your overworked INFOSEC staff sees suspicious behavior. This is low hanging fruit that is not a great deal of work that can rat out any suspicious activity that may or may not be Malware.

So STFU and give us the Query already:

Okay, this query will give you the UserID, Server/Desktop, Executable, downstream hostname and IP Address (If they are the same, it’s probably up to no good!), port and the count. I generally do not get excited about one or two connections but if you see a large number of them, you may want to investigate. If you see winword.exe phoning home to the Netherlands once? Could be web-content or portlet on a document. If you see 100 connections? Maybe check it out, maybe escalate? If your internal Network is 192.168.0.0/16 then you can exclude that in your query since, in this case, I am more concerned about “phone homes” to non-corporate sites. Likewise, you can exclude your DMZ block as well as any executables that you know make regular connections outside such as iexplore.exe and firefox.exe. You may notice Vendors phoning home or even root out some malware but if nothing else, you are not letting malware/bots/whatever leave unmolested and you can hunt down possible issues that occur in a much more timely manner than waiting for someone on your INFOSEC team to find it for you.

Example Scenario: If you see 1000 outlook.exe connections to a website in Russia that does not have a hostname, just an IP Address for both the hostname and IP_address column…that may be a possible phishing attempt.

The dateadd(hh,-5,inserted_Date),111) section is set to “-5” because I am on EST

declare @today datetime
set @today = convert(varchar,getdate(),111)
SELECT distinct account_name, machine_name, exe_name, hostname, ip_address, port, count(ip_address)
FROM vw_es_archive_application_Network_performance
WHERE convert(varchar(10),dateadd(hh,-5,inserted_Date),111) = @today
and exe_name <> 'firefox.exe'
and exe_name <> 'iexplore.exe'
and ip_address not like '192.168%'
GROUP BY account_name, machine_name, exe_name, hostname, ip_address, port
ORDER BY count(ip_address) desc

Like I said, this is low hanging fruit and since they are your systems, you will be in a position to tell odd behavior and not your Security team. Most of these queries I have set up to run automatically and SQLMail them to me daily. You can also just open up query analyzer and run the query “in the Morning” when you get in.

I know most of your shared services and hosting groups are not Security teams but this is one of many ways where we can get in the game a little and be a player in preventing breaches or at least detecting and mitigating them.

Thanks for reading

John