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:

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.

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.

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).

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

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 clause 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.

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.

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:

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]

Value for Value

If you received any value from reading this post, please help by becoming a supporter.

Thanks for reading,
Alain