Tag Archives: MS SQL Reporting Services

EdgeSight: 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

Advertisements

EdgeSight: Reporting on a user’s bad experience

Intro (Soapbox)

I have stated it before, EdgeSight is one of your most powerful tools you have in your XenApp environment.  Tons of information is gathered and stored in the database that never sees the light of day because it’s difficult to get the information out with the built-in reports.  If you are a CIO/IT Manager/Team Lead you have to either develop the DBA/SQL skills of one of your XenApp administrators or form a workgroup with the DBA team to really leverage EdgeSight to benefit your customers.

In this post, we will create a query that summaries a user’s bad login experience and then use SSRS (SQL Server Reporting Services) to dill down to a more detailed report of just one user.

NOTE: Please review my series on Making Citrix Stats Work for You to get familiar with creating custom SQL queries and SSRS reports from those queries. Jump to [part 1] [part 2] [part 3] [part 4] [part 5] [part 6].

EdgeSight Under the Hood

My colleague, John Smith, has a terrific blog where he pulls back the veil on EdgeSight called EdgeSight Under the Hood.  After reading this post, I put together a more detailed query on my user’s session startup experience.

User’s Bad Login Experience Query

SELECT [user] as 'Userid',
CAST(session_startup_server/1000.0 AS decimal(8,2))as 'Session Startup (sec)',
CAST(profile_load_server_duration/1000.0 as decimal(8,2)) as 'Profile Load (sec)',
CAST(credentials_obtention_server_duration/1000.0 as decimal(8,2)) as 'Obtain Creds (sec)',
CAST(login_script_execution_server_duration/1000.0 as decimal(8,2)) as 'Logon Script (sec)',
client_address as 'Client IP',
client_version as 'ICA Client Ver',
machine_name as 'Citrix Server',
CONVERT(varchar(30),DATEADD(hh,-5,start_time),0) as 'Session Start Time'
FROM vw_ctrx_archive_server_start_perf
WHERE DATEADD(hh,-5,start_time) > dateadd(dd,-1,getdate()) and DATEADD(hh,-5,start_time) < getdate()
GROUP BY session_startup_server, profile_load_server_duration, credentials_obtention_server_duration, login_script_execution_server_duration, client_address, client_version, machine_name, start_time, [user] having session_startup_server/1000.0 > 60
ORDER BY 'Session Startup (sec)' desc, 'Userid'

As I stated above, EdgeSight records a lot of data and thankfully, Citrix combines most of the information in the form of views in the EdgeSight database.  The above query takes information from the VW_CTRX_ARCHIVE_SERVER_START_PERF view which contains user session startup information.  Specifically we are looking at the following data points (from the EdgeSight v5.3 help):

session_startup_server
This is the high level server connection startup metric. This includes the time spent on the Presentation Server performing the entire start-up operation. In the event of an application starting in a shared session, this metric is expected to be much smaller, as starting a completely new session involves potentially high cost tasks such as profile loading and login script execution.
profile_load_server_duration
The time spent on the server loading the users’ profile.
credentials_obtention_server_duration
The time taken for the server to obtain the user credentials. This is only likely to be a significant amount of time if manual login is being used and the server-side credentials dialog is displayed (or if a legal notice is displayed before login commences).
login_script_execution_server_duration
The time spent on the server running the users’ login script(s).
client_address
Address of the client associated with the session.
client_version
Version of the client associated with the session.
start_time
The time when the session creation started

Here’s some example data:

Userid Session Startup (sec) Profile Load (sec) Obtain Creds (sec) Login Script (sec) Client IP ICA Client Ver Citrix Server Session Start Time
user1 1236.25 23.31 1.03 1209.13 192.168.50.1 10.08.55362 CITRIX1 Mar 4 2011 4:48AM
user2 1222.83 14.17 1.09 1204.50 127.0.0.1 11.2.0.31560 CITRIX2 Mar 4 2011 2:45AM

First of all, the query is getting the ‘Session Start Time’ offset for Eastern Standard Daylight Savings Time (-5) with these 2 statements:

CONVERT(varchar(30),DATEADD(hh,-5,start_time),0) as 'Session Start Time'

and

WHERE DATEADD(hh,-5,start_time) > dateadd(dd,-1,getdate()) and DATEADD(hh,-5,start_time) < getdate()

The WHERE statement is set to give me all sessions that started from the last EdgeSight worker upload to 24 hours ago.  Also, EdgeSight returns times in milliseconds, so dividing the results by 1000 and ensuring we only display 2 decimal places gives us the seconds to perform each action.

Everyday you visit this SSRS report, you will get a list of users who have had terrible (as recorded by EdgeSight) login experiences, but how do you know if this was a fluke or due to the user connecting from an unusual location?

Drill baby drill!

One of the neat things available in SSRS is the ability to click on a result and use that data to generate a different report.  In other words, you can drill-down to a more detailed report.  This report should show the login history of the user.  That way we can tell if their bad experience is typical or was just a one-time problem.  We will modify the above query to display the same information, but we’ll remove the time restrictions from the WHERE clause to get more data.  We’re also dropping Userid from the table, because this query is about a specific user who we already know.

User’s Bad Login Experience Detail Query

SELECT CONVERT(varchar(30),DATEADD(hh,-5,start_time),0) as 'Session Start Time',
CAST(session_startup_server/1000.0 AS decimal(8,2))as 'Session Startup (sec)',
CAST(profile_load_server_duration/1000.0 as decimal(8,2)) as 'Profile Load (sec)',
CAST(credentials_obtention_server_duration/1000.0 as decimal(8,2)) as 'Obtain Creds (sec)',
CAST(login_script_execution_server_duration/1000.0 as decimal(8,2)) as 'Logon Script (sec)',
client_address as 'Client IP',
client_version as 'ICA Client Ver',
machine_name as 'Citrix Server'
FROM vw_ctrx_archive_server_start_perf
WHERE [user] = (@userid) and session_startup_server is not null and login_script_execution_server_duration > 0
GROUP BY session_startup_server, profile_load_server_duration, credentials_obtention_server_duration, login_script_execution_server_duration, client_address, client_version, machine_name, start_time, [user]
ORDER BY 'Session Start Time' desc

This line is where we create a parameter called @userid. Remember this for later.

where [user] = (@userid) and session_startup_server is not null and login_script_execution_server_duration > 0

So how do we create the drill-down?

NOTE: The following screenshots are from Visual Studio 2005.

In Visual Studio, open the report for the User’s Bad Login Experience Query.
image

Open the Layout tab. We want to use Userid as the parameter we pass to our drill-down report.  Right-click on the field and select properties.  You select the field and not the column name because we want to turn the results of the query into the parameter for the drill-down report.

image

In the Textbox Properties window, click on the Navigation Tab.  Under Hyperlink action, click “Jump to report:” and choose the BadLoginExpDetail report (NOTE: BadLoginExpDetail is available because I had added it to the SSRS_User_Bad_Login_Exp project above).

image

Now click the Parameters… button.  When you click the drop-down arrow in the “Parameter Name” field, you will see userid.

image

Huh?  Yeah, look back at the “User’s Bad Login Experience Detail Query” above.  You will recall that we created a parameter called userid by setting a condition in the WHERE cause that [user] = @userid.  That ‘@’ symbol is not just for show, but how you create parameters (variables) in SQL.  When you chose the BadLoginExpDetail report and clicked on the Parameter button, you could select any parameters you created in the query for that report.  Okay, now that we are on the same page, we have to associate a value with the parameter.  Click the drop-down arrow in the “Parameter Value” column.

image

You will see all the fields present in the BadLoginExp report.  We want to pick “=Fields!Userid.Value” so that when we click on a user’s name in the BadLoginExp report, it will send that name as the parameter to the BadLoginExpDetail report.  Now, click OK and OK again to close the properties box.  Let’s click on the Preview tab to see what happens.

image

As you can see, the user1 value is now a hyperlink that will pass user1 as the ‘userid’ parameter to the BadLoginExpDetail report.  When we click it, we get the following:

image

You can modify the userid field to make it look like a hyperlink by using underline and/or changing the text color.

I always welcome questions and comments.  Again, I cover the creation of SSRS reports in my Making Citrix Stats Work for you series. Jump to [part 1] [part 2] [part 3] [part 4] [part 5] [part 6]

Thanks,
Alain

Creating a Green Dashboard – Part 2

In the first part of this series, we covered gathering data on external users and how to parse that data into a database.  To generate the data to show how your Citrix farm positively impacts the environment and quality of life for your teleworkers, you need to get the average statistics for them.  Governments gather this data as it pertains to their own telework programs.

The U.S. government site, telework.gov, provides a one-stop-shop for general information, but no hard data except for number of participants at the various agencies.  We need a site that will give us savings on money, fuel, and pollutants. 

The Telework Exchange has a lot of papers and studies, and it provides statisics for yourself, based on location and miles driven.

The most detailed information I’ve been able to find so far, is the Undress4Success site.  This site has quite of bit of information as well as links to job boards and details of “work at home” scams.  They also have done a lot of the work of analyzing many telework studies and making it available to visitors.

For this example dashboard, I’ll track gas saved and green house gas savings.

For gas saved in my geographic area, this is 2,847,724 gallons saved per year or approximately 177 gallons per teleworker (16,058) or roughly 0.50 gallons saved per person/per day teleworking.

Following the same method for greenhouse gases, 26,991 metric tons are saved annually.  Using Google to convert this to pounds, gives us 59,504,969 pounds saved.  This breaks down to 10 pounds saved per person/per day teleworking.

Using these amounts to start with we can show the amount of gas and greenhouse gas emissions someone is saving per day when they use our Citrix farm.   This is just a starting point, we could expand this to other cost savings for the teleworker and for the company as well which I’ll cover at the end of this series.