My colleague John Smith has started a second blog at 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



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.

Value for Value

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

Thanks for reading,