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 |
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.
Value for Value
If you received any value from reading this post, please help by becoming a supporter.
Thanks for reading,
Alain