EdgeSight: Trending User Login Detail

My co-worker John Smith has started a second blog titled: EdgeSight Under the hood.  He delves into the vast amount of data that EdgeSight collects and uses SQL queries to present this information in meaningful ways. Inspired by him, I’m going to write about averaging user login metrics that EdgeSight collects, but only reports them on a per user basis.

EdgeSight Views

The EdgeSight database schema is nightmarish, but Citrix collects most of the data in database views (which are basically permanent queries).  The particular view we’re going to look at is the vw_ctrx_archive_server_start_perf view.  Here’s a breakdown of the columns and what they represent:

View Table: vw_ctrx_archive_server_start_perf

Startup detail Server Session Startup Column
Credentials Authentication credentials_authentication_server_duration
Credentials Obtention credentials_obtention_server_duration
Device Mapping device_mapping_server_duration
Login Script Execution login_script_execution_server_duration
Profile Load profile_load_server_duration
Session Creation session_creation_server_duration
Printer creation printer_creation_server_duration
Session Startup Duration Session_startup_server

 

Scenario

Users are reporting that it’s taking longer to log into your Citrix farm than a week or two ago.  Suspecting that your AD team has been messing around with the domain login script again you want to see if there’s been a measurable change in how long it takes to process the login script.

Here’s the query:


declare @today datetime
set @today = convert(varchar,getdate(),111)
select convert(varchar(10),dateadd(hh,-4,time_stamp), 111) as [Date], convert(decimal(19,2),avg(login_script_execution_server_duration)/1000.0) as 'Login Script (sec)',  convert(decimal(19,2),avg(Session_startup_server)/1000.0) as 'Session StartUp Total (sec)'
from vw_ctrx_archive_server_start_perf
where convert(varchar(10),dateadd(hh,-4,time_stamp), 111) > @today-30
group by convert(varchar(10),dateadd(hh,-4,time_stamp), 111)
order by convert(varchar(10),dateadd(hh,-4,time_stamp), 111)

This takes the average for all logins (captured by EdgeSight) and averages the login script processing time and the total session startup time.

NOTE: In the dateadd functions you’ll notice a (-4).  This is necessary to offset the data stored in EdgeSight with your time zone.  EdgeSight stores its data based on UTC time.  Using the -4 will move it to Eastern (U.S.) daylight savings time (normally it’s UTC -5).  Keep this in mind if you do ad hoc queries with EdgeSight data.

EdgeSight will, by default, only store this type of information for 30 days.  If you want to report on longer periods, you’ll have to save the information in another form or change the EdgeSight worker to not purge data as often.

Thanks,
Alain

Advertisements

2 thoughts on “EdgeSight: Trending User Login Detail”

  1. Hey very nice blog!!….I’m an instant fan, I have bookmarked you and I’ll be checking back on a regular….See ya

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s