Edgesight Under the Hood: Emails and Questions from Readers

So, I thought for my next post I would include a few of the answers I gave to some emails that I received from people who viewed “Edgesight Under the Hood” 

I will start with four very easy queries that can answer some, what at times seemed to be, pretty tough questions. As I continue to digg deeper and deeper into edgesight, the amount of data it has is just staggering. 

First Question:
We deployed PNAgent a few months ago but we really don’t know if anyone is using it. How can I tell who is launching PNAgent? Can I get a count of how many would be impacted if the PNAgent server were to be taken down?

Here is the query for that: (Number of Launches)
select launch_type, count(launch_type)
from vw_ctrx_archive_client_start_perf
group by Launch_type
order by count(launch_type) desc
 

Perhaps more useful would be the number of users:
select launch_type, count(distinct [user])
from vw_ctrx_archive_client_start_perf
group by Launch_type
order by count(distinct [user]) desc

My next question is how to find out a user’s screen resolution. This is a very quick and easy query that will give you each user’s screen resolution.

Query for each user’s screen resolution:
select distinct Account_name, client_disp_horiz, client_disp_vert
from vw_es_usergroup_ica_users
 

And this query will give you the distribution of screen resolution(s) throughout your environment. This will help you plan your video memory settings on your default.ica files.

Query for distribution of screen resolution(s):
select client_disp_horiz, client_disp_vert, count(distinct account_name)
from vw_es_usergroup_ica_users
group by client_disp_horiz, client_disp_vert
order by count(distinct account_name) desc

 Then I got a question on how to root out mac and Linux/Unix users in your environment. I noted that in the vw_es_usergroup_ica_users view there is a column called “client_directory”. Since windows systems still use drive leters this was actually pretty easy. Simply query for any client_directory that does not have a colon in it. You will get the unix filesystem path (most of the time “/”…UHG!”), for the more savy users “/home/username”

Query for Non-Windows Hosts:
select distinct account_name, client_address, client_directory
from vw_es_usergroup_ica_users
where client_directory not like ‘%:%’

Thanks for coming over to the new sight, I am trying to move ALL edgesight queries, and eventually uploadable RDL’s to http://edgesightunderthehood.com

And thanks for reading!John M. Smith


Digital Epidemiology: Edgesight Under the hood

Okay, so no flat files, parsing or kiwi syslogging today.  Today I want to talk about Edgesight 5.x.  If any of you have attempted to reverse engineer Edgesight yet you have probably noticed that the tables are a lost cause.  All of the key data that you will want to try to harvest is located in the “Views”.  I Want to do a few blog posts on each of my favorite views and how you can pull statistics from them instantly via query analyzer.  I will start by saying Citrix has created an outstanding front end delivered via the web interface.  I am in no way knocking that interface, there are just times when the canned reports just don’t do it for you.  Until the engineers at Citrix get their hands on a Crystal Ball, there will always be a use for good ole-fashion ad hoc queries.  I am going to go over a few key queries from the vw_ctrx_archive_ica_roundtrip_perf  view from your Edgesight Database and how you can open query analyzer and gather these statistics post haste, or, if you are adept with Reporting Services, set up reports for yourself.  I have pitched to the Synergy 2010 group that they let me host a breakout covering how to integrate some of what I do with SQL Server Reporting Services, I think I can cover a lot in a 90 minute session and let engineers take something away from the session that they can use in their own environments. So, as I stated, the view of the day is  “vw_ctrx_archive_ica_roundtrip_perf” so open your SQL Server Management Studio and log into the SQL Server hosting your database with an account that has “Datareader” privilages.  If you admin account does not work, your Edgesight service account will likely suffice if your organization allows services accounts to be  used in that manner.

@Today:
The @Today variable is for the existing day.  That means that if you want to check between yesterday and the day before you would change “convert(varchar(10),dateadd(hh,-4,time_stamp),111) > @today-2” to “convert(varchar(10),dateadd(hh,-4,time_stamp),111) between @today-2 and @today-1”

Find the number of ICA SEssions by server by time of day
About this query:
In this query we declare 3 variables, two of which you must edit.  The @begin and @end variables must have the time of day that you want to search.  So, if you wanted to know the number of unique users for each server between 8AM and 2PM, you would enter ’08’ for @begin and ’14’ for @end.

declare @begin varchar
declare @end varchar
declare @today datetime
set @today = convert(varchar,getdate(),111)
set @begin = '14'
set @end = '23'
select machine_name, count(distinct [user])
from vw_ctrx_archive_ica_roundtrip_perf
whereconvert(varchar(2),dateadd(hh,-4,time_stamp),108) between @begin and @end
and convert(varchar(10),dateadd(hh,-4,time_stamp),111) > @today-2
group by machine_name
order by count(distinct [user]) desc

Find ICA Lantency by user by day
About this query:
This query will show you the ICA Latency for each user and sort it by the user with the worst latency.  If you wanted to check sessions on a specific server, you would add the following above the “Group By” statement:  ‘and machine_name = ‘%netbiosNameOfXenAPPServer%’

declare @today datetime
set @today = convert(varchar,getdate(),111)
select [user], sum(network_latency_sum)/sum(network_latency_cnt) as "Latency"
from vw_ctrx_archive_ica_roundtrip_perf
where convert(varchar(10),dateadd(hh,-4,time_stamp),111) > @today-1
group by [user]
order by sum(network_latency_sum)/sum(network_latency_cnt) desc

ICA Latency by Server:
About this query:
This query will show you the latency by server for a given day.  This can be handy if you want to keep tabs on server health.  If you note high latency for a particular server for a specific day you may need to look and see if there was a user connection that skewed the results or if all sessions on that server had issues.

declare @today datetime
set @today = convert(varchar,getdate(),111)
select machine_name, sum(network_latency_sum)/sum(network_latency_cnt) as "Latency"
from vw_ctrx_archive_ica_roundtrip_perf
where convert(varchar(10),dateadd(hh,-4,time_stamp),111) > @today-1
group by machine_name
order by sum(network_latency_sum)/sum(network_latency_cnt) desc

Find total sessions by server by farm:
About this Query:
If you have more than one farm, than you can specify the farm name in this query to get the number of connections per server by farm name.  For users in very large environments with multiple farms may find it handy to query by farm name.

declare @today datetime
set @today = convert(varchar,getdate(),111)
select machine_name, count(distinct [user])
from vw_ctrx_archive_ica_roundtrip_perf
where convert(varchar(10),dateadd(hh,-4,time_stamp),111)= @today-3
and xen_farm_name = '%FarmName%'
group by machine_name
order by count(distinct [user]) desc

Conclusion:
There are at least four views that I like to work with directly, I also integrate all of my queries, including the variables, into SQL Server Reporting Services letting me customize my reports for my specific needs.  The eventual goal is to provide our operations and support team with a proactive list of users with high latency so that we can call them and let them know that we noticed they were having issues.  My next post will cover how to look at problematic downstream hosts that cause you to get a bunch of calls saying it’s Citrix’s fault!!  I apologize for the lack of examples, I am limited to w hat I can show in my environment.  As I stated, I am hoping to show all of this integration, including custom SQL Reports,  at Synergy 2010.

If you have a specific query that you want, post it as a comment and I will reply with the SQL Query that gets you as close as I can.

Thanks for reading!

John