Advertisements

Digital Epidemiology: Edgesight Under the hood (Will be moved to EdgesightUndertheHood.com)

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

@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) > @today2
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) > @today1
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) > @today1
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)= @today3
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

Advertisements

3 responses

  1. Hi John,
    interesting read!

    I’m looking for a custom report showing the application usage (Published Apps, not processes) on a hourly, daily and monthly base and a custom report showing the concurrent sessions on a hourly, daily and monthly base.

    Do you have any of these?

    Regards,
    Peter

    1. Here is the Hourly:(Note the change I just made!
      There are four variables you need to edit in this query. The @Begin and @End need to have the two digit times based on a 24 hour clock. The @App needs to have the published application and the @Today needs to include the day(s) you want to query. Basically @Today is the current day, @Today-1 is yesterday, @today-2 is the day before yesterday….etc…etc…etc.

      Also, I subtract four hours in my query to make the time match where I am (EST). That time_stamp column may need to be changed to accomodate your time zone.

      *Note I changed (Count published_application) to count(distinct sessid). This looks like better results and will serve you better when we look at user sessions. I will make the remainder of your requests My next post if that works?

      Paste this into query analyzer and let me know if it works. I will work on the daily and monthly tonight.

      declare @begin varchar
      declare @end varchar
      declare @today datetime
      declare @app varchar
      set @today = convert(varchar,getdate(),111)
      set @begin = ’00’
      set @end = ’23’
      set @app = ‘%APPNAME%’
      select convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+’:00′ as “Time”, count(distinct sessid)
      from vw_ctrx_archive_client_start_perf
      where convert(varchar(10),dateadd(hh,-4,time_stamp), 111) = @today
      and published_application like ‘%’+@app+’%’
      group by convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+’:00′
      order by convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+’:00′

  2. Hi John,

    thanks for your posting regarding the Edge Sight Reporting
    if you have this knowlege for the Reports (RDL) .xml file
    like exactly for an report “application; usage; version number; Session time”
    it would be very beautiful to received your help for it,

    If you have this file ready to run could i ask you to sent me this ?

    Would be very fine,

    With best regards,

    Dirk Timm

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

%d bloggers like this: