EdgeSight Under the Hood: Part 2

Okay, so in this blog posting I want to continue covering a few more views in Edgesight that I like to run ad hoc queries against.  Today’s view is called   vw_es_archive_application_network_performance.  This view provides information network delay, server delay, xenapp server, process name and downstream hosts that your XenApp servers communicate with.  I have used this table to check delays of the executables such as winlogon.exe to check delay between this process and our domain controllers.  I will cover checking delays by process name, xen_app server and downstream host.

The first part will be to demonstrate how to find Network and Server delay of specific downstream hosts as well as how to measure the average XenAPP Servers delay.  Then in the second part I want to answer one of the questions from the first posting.

Down Stream Delay:
I actually got to present on Edgesight during Synergy 2008 and one of the key points that I tried to drive home is how Edgesight helps you with the never ending B.S. Witch hunts that always seem to occur when someone’s application is “running slow on Citrix”.  I would say that less than 30 % of what I actually investigate ends up being an actual XenAPP issue.  I will go over a few ad hoc queries that will give you the average delay of your down stream hosts and will give you the average delay experienced by each XenAPP Server allowing you to see if you have a specific XenAPP box that may be having some issues.

The first ad hoc query has to do with downstream hosts, this will return the downstream host and the Network/Server delay.  I have set this query to filter any downstream host that does not have at least 100 records and a server delay of at least 300 miliseconds.  You can edit/remove the “Having” clause to suit your environment.

SELECT distinct hostname, sum(network_delay_sum)/sum(record_count) as "Network Delay", sum(server_delay_sum)/sum(record_count) as "Server Delay"
FROM vw_es_archive_application_network_performance
GROUP BY hostname HAVING sum(record_count) > 100
and sum(server_delay_sum)/sum(record_count) > 300
ORDER BY sum(server_delay_sum)/sum(record_count) desc

In English: “Give me the Network and Server delay of every downstream host that has at least 100 records (packets?) and a server latency of at least 300ms”

XenAPP Server Delay:
It is a good idea to monitor your XenAPP Server delay, this will tell you if there is a particular XenAPP Server that is having a layer 1 or layer 2 issue.  This is a quick query that will show you the average delay of your XenAPP Servers.

SELECT distinct machine_name, sum(network_delay_sum)/sum(record_count) as "Network Delay", sum(server_delay_sum)/sum(record_count) as "Server Delay"
FROM vw_es_archive_application_network_performance
GROUP BY machine_name
ORDER BY sum(server_delay_sum)/sum(record_count) desc

Note: You will also see “Edgesight for Endpoints” client data in this table as well.

Executable  Delay:
This query shows the delay associated  individual executables.  You may check outlook.exe to see if you have a delay in a downstream Exchange server or, in my case, check winlogon.exe for delays to domain controllers.

SELECT distinct exe_name, sum(network_delay_sum)/sum(record_count) as "Network Delay", sum(server_delay_sum)/sum(record_count) as "Server Delay"
FROM vw_es_archive_application_network_performance
GROUP BY exe_name
ORDER BY sum(server_delay_sum)/sum(record_count) desc

Session Statistics:
Last week I got a a question about session counts and I wanted to answer it in this post, here was the question:

“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.”

The view I used for this was vw_ctrx_archive_client_start_perf 

declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
set @begin = '00'
set @end = '23'
set @app = '%Outlook%'
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-1
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'

In English: Give me every application on an hourly basis for a specific application.  On this report substitute %APPNAME% for whichever app you want to see.  Note that this is an hourly report so the time format is set to 108.

Daily Application Usage:
In the same view I change the query above just a little to accommodate a query by day.

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
set @app = '%Outlook%'
SELECT convert(varchar(10),dateadd(hh,-4,time_stamp), 111) as "Date", count(distinct sessid)
FROM vw_ctrx_archive_client_start_perf
WHERE convert(varchar(10),dateadd(hh,-4,time_stamp), 111) > @today-30
and published_application like <a href="mailto:%27%%27+@app+%27%%27">'%'+@app+'%'</a>
GROUP BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111)
ORDER BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111)

Monthly Application Usage:
Depending on how long you have your retention set (min is 30 days) this query may or may not work for you but this is the number of unique sessions per application for a month.

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
set @app = '%Outlook%'
SELECT convert(varchar(7),dateadd(hh,-4,time_stamp), 111) as "Date", count(distinct sessid)
FROM vw_ctrx_archive_client_start_perf
WHERE convert(varchar(10),dateadd(hh,-4,time_stamp), 111) > @today-30
and published_application like <a href="mailto:%27%%27+@app+%27%%27">'%'+@app+'%'</a>
GROUP BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111)
ORDER BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111)

Application Matrix:
SQL Server Reporting Services will let you create a matrix, these two queries are for daily and monthly which will let you sort as follows:

Date 1 Date2 Date3 Date4 Date5
Outlook Count1 Count2 Count3 Count4 Count5
Word Count1 Count2 Count3 Count4 Count5
Oracle Financials Count1 Count2 Count3 Count4 Count5
Statistical APP Count1 Count2 Count3 Count4 Count5
Custom APP-A Count1 Count2 Count3 Count4 Count5

This has been the report method that has made my management the happiest so I use the Matrix tool with SSRS as often as possible.  Remember, if you have Edgesight, you have SSRS and setting up reports is no harder than an Access Database.

Here are the queries

First The Daily Matrix:

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
SELECT convert(varchar(10),dateadd(hh,-4,time_stamp), 111) as "Date", published_application, count(distinct sessid)
FROM vw_ctrx_archive_client_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), published_application
ORDER BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111), count(distinct sessid) desc

Then the Monthly Matrix:

declare @today datetime
set @today = convert(varchar,getdate(),111)
SELECT convert(varchar(7),dateadd(hh,-4,time_stamp), 111) as "Date", published_application, count(distinct sessid)
FROM vw_ctrx_archive_client_start_perf
WHERE convert(varchar(10),dateadd(hh,-4,time_stamp), 111) > @today-30
GROUP BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111), published_application
ORDER BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111), count(distinct sessid) desc

Concurrent Session Statistics:
A colleague of mine, Alain Assaf, set up a system that gives you this info every five minutes and is almost in real time, go to wagthereal.wordpress.com to see it.  Keep in mind that Edgesight is not real time data so if you set up a private dashboard for it, you may have to wait for it to refresh.

The vw_ctrx_archive_client_start_perf view appears to give us only start times of specific published applications.  Perhaps the most used view of any of my reports is vw_ctrx_archive_ica_roundtrip_perf.  For this set of queries, I will count concurrent sessions but I will also go into ICA Delay’s for clients in my last post on Edgesight Under the Hood:

I will try to answer the users question on concurrent sessions with three pretty basic queries for hourly, daily and monthly usage:

Hourly Users:

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
set @begin = '00'
set @end = '23'
SELECT convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+':00' as "Time", count(distinct [user])
FROM vw_ctrx_archive_ica_roundtrip_perf
WHERE convert(varchar(10),dateadd(hh,-4,time_stamp), 111) = @today-3
GROUP BY convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+':00'
ORDER BY convert(varchar(2),dateadd(hh,-4,time_stamp), 108)+':00'

Daily Users:

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
SELECT convert(varchar(10),dateadd(hh,-4,time_stamp), 111) as "Date", count(distinct [user])
FROM vw_ctrx_archive_ica_roundtrip_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)

Monthly Users:

declare @begin varchar
declare @end varchar
declare @today datetime
declare @app varchar
set @today = convert(varchar,getdate(),111)
SELECT convert(varchar(7),dateadd(hh,-4,time_stamp), 111) as "Date", count(distinct [user])
FROM vw_ctrx_archive_ica_roundtrip_perf
WHERE convert(varchar(10),dateadd(hh,-4,time_stamp), 111) > @today-30
GROUP BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111)
ORDER BY convert(varchar(7),dateadd(hh,-4,time_stamp), 111)

Conclusion:
For the most part, I have vetted all of these queries, you may get varying results, if so, check for payload errors, licensing, etc.  I would really like to see some better documentation on the data model, most of these were basically done by running the query and checking it against the EdgeSight canned reports to see if my SWAG about how they did their calculations was correct.  All of the queries I ran here I checked and looked to be accurate.  If you are going to bet the farm on any of these queries to the brass in your organization, vet my numbers….

My next post will deal with ICA latency and delay issues for individual users and servers.

Thanks for reading!
John

19 thoughts on “EdgeSight Under the Hood: Part 2”

  1. Very good. I’m new to the game and have been trying to customize the “Active Application reponse” report, but haven’t figured it out. When I export this report into .csv file, it has about 12 fields and I am trying to eliminate all but 4. Have you tied to customize this report?

      1. Hi Guys, I’m not good in SQL Reporting services. I’ve been using all the default reports for my compnay.
        Recently my boss asked me to look into the custom reports and from then I’ve been searching everything
        on the internet. I did get all of your scripts, I’m just wondering where should I start inserting
        your queries from? I know its a stupid question but please help me.
        kashifahsan@gmail.com
        Thanks.

    1. Hey, thanks for getting back to me, I am looking in my AAM table, I don’t see a value for “PASSED and FAILED”, I do see a column for status so it could be that you set your jobs up differently than I did.

      Can you paste this into Query Analyzer and see what results you get? Let me know if it gives you what you want. You can also enter this into SQL Reports I believe via the web console.

      BTW, I cut me teeth in this Industry working for EDS years ago!!

      The timestamp value I am subtracting is for the east coast (Atlanta) but double check the time stamps to make sure they are accurate.

      select Monitoring_point, dateadd(hh,-4,Monitoring_point_tstamp) as “TimeStamp”, monitoring_point_status as “Status”
      from vw_ctrx_archive_application_response
      order by monitoring_point

      If you just want to show the actual time and not the full date/tiime you can use the following:

      select Monitoring_point, convert(varchar(5),dateadd(hh,-4,Monitoring_point_tstamp),108) as “TimeStamp”, monitoring_point_status as “Status”
      from vw_ctrx_archive_application_response
      order by monitoring_point

      Let me know

      Take care

      John

    1. Bruce, WordPress uses different ascii charecters for double-quotes. Where you see “Network Delay” and “Server Delay” substitute WordPress quotes for quotes from your keyboard and try it and let me know.

      Sorry about that, still working on getting it fixed.

    2. That worked! Now I need to report on Application RTT. Do you know the string I need to insert into the Executable delay queary to pull that data back?

      1. Application or Executable? Can you give me the play by play of what exactly you need?

        Just the RTT of a specific Exe file?

      2. Bruce, I have matched up the session ID’s in a cross-tab query but I have not verified these results, if you can compare them to some of your existing reports and see if they match up or talk to someone at Citrix and see if this query is accurate (I would check but I am burried for at least another week).

        I was able to get RTT based on the session and I just matched up the session ID for the app response and the vw_ctrx_ica_rt_perf views.
        As always, fix the quotes to get it to work, copy this in and run it and test it against the canned report and I’d love to know if it is what you are looking for.

        Thanks!

        John

        ######################
        declare @begin datetime
        declare @end datetime
        set @Begin = ‘2010/01/09’
        set @end = ‘2010/01/10’
        select distinct account_name, exe_name, hostname, roundtrip_time_sum/roundtrip_time_cnt as “Overall RTT”
        from vw_ctrx_ica_rt_perf, vw_es_archive_application_network_performance
        where vw_es_archive_application_network_performance.sessid = vw_ctrx_ica_rt_perf.sessid
        and account_name ‘System’
        and account_name not like ‘Network%’
        and account_name not like ‘unknown%’
        and convert(varchar(10),dateadd(hh,-4,time_stamp),111) between @Begin and @end
        order by roundtrip_time_sum/roundtrip_time_cnt desc

  2. Looks like it works. Whats the possibility I could get that in a RDL format so I can import into ES server? I’m not a great rdl writer at all.

    1. Let me see what I can do,

      BTW, Linked in shows you working for Citrix? Is that true? If so, I’d like to talk to their Edgesight team to get a copy of a test database. I can’t use mine and I want to set up edgesightunderthehood.com with videos of not only how to run the adhoc reports but how to create the custom RDL’s as well. Also, Citrix is going to create a tool for this as well.

      FYI

      Thanks

      John

      1. Very cool. Yes. I am a SE for Citrix, but a noob at rdl’s :). Contact me at my email off the comment list and I can get you in touch with the right people on the product management team.

  3. Hi John,

    Just wondering if the published applications usage reports will work also with ES 5.3 basic addition (the one that comes with XA ENT edition).
    It seems like there is no built-in report for published applications usage at the basic edition…

    Thnx,
    Gadi

    1. Sorry for the late reply, I was on leave. I wish I couldc omment on the XA ENT edition but we are running platinum.

      Have you tried the custom query with your vesion of edgesight? There is an APP Usage query on the blog site.

      Thanks

    2. Gadi,

      The table used in the query is not populated unless you are running the EdgeSight agents in Advanced mode. This data is part of the End User Experience Monitoring that is utilized when in Advanced mode. Here’s a Citrix PDF that lists the differences between EdgeSight Basic and Advanced.

      http://support.citrix.com/article/CTX124427

      Thanks for reading,
      Alain

  4. in your statement sum(record_count)
    i’m assuming record_count is a keyword? or some kind of function
    i can’t seem to find any references to it in the table or database.

    thanks, this whole post is awesome!

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