Three new Edgesight Queries: (Find XenDesktop Candidates and Problem users)

I will start by saying I have always been an SBC guy, even when VDI moved out of vaporware into the enterprise solution that it is today I still felt like the industry was giving up on SBC prematurely. While some of you are going to jump feet first into Citrix’s XenDesktop solution, a number of you will likely wait awhile and ease into the new VDI solution. If you are currently a XenAPP Shop, you, like me, probably have a hard time keeping developers happy with their XenAPP Environment. In fact, were I to do everything that the Developers wanted in my XenAPP Environment to make them happy, the o ther 90% of my user base would have no memory or CPU left for their sessions. One key advantage that XenDesktop has over XenAPP is that you can allocate specific resources to a user’s VDI session and minimize the impact their actions have on other end users. Even if you have decided that XenAPP is the way to go for your agency/company, you can still provide the same secure, robust and stable environment to your Developers by setting up XenDesktop for them as a “last mile” in your overall remote access strategy.

Currently, most developers take their corporate issued laptops home and with it, your company’s intellectual property. This means that while they are away, none of the work is being backed up (at least not likely) and you are at the mercy of their own security regimen if any of the data is sensative. I remember blowing my top about ten years ago when a developer who refused to put his souce in PVCS had his laptop crash and a years worth of development, source code, etc was lost….LOST!!! ARRRRRRG!!!!!!!! My goal is to provide the Developers with a XenDesktop solution that they can use instead of RDP to their corporate desktop.

While most of our staff is very happy and satisfied with our XenAPP deployment, the developers basically use it launch Remote Desktop then hop over to their machine. This creates problems because if there is a connectivity issue between your Citrix farm and the developers workstation it will land in your lap. You also trade a nice, smal ICA protocol for a more bulky, less efficient RDP protocol. Most developers have a sort of “meh” attitude toward XenAPP but XenDesktop will provide them secure remote access that will snap seemlessly into your existing AGEE/ICAProxy solution eliminating using your XenAPP farm as a jump box to their desktop.

This latest query is how to use Edgesight to root out high resource users and peg them as XenDesktop candidates. For us, we are also making a big push toward XenDesktop and we will leverage this query as one of the ways to put together the first batch of recruits.

The Query:

SELECT account_name, sessid, round((avg(total_processor_time_sum)/avg(total_processor_time_cnt)),2) as "%Processor", avg(working_set_sum)/avg(working_set_cnt)/1000.0 as "RAM(Megs)"
FROM vw_es_archive_application_performance
GROUP BY account_name, sessid
ORDER BY "RAM(Megs)" desc

This will give me the UserName, average CPU and average RAM usage for a specific session and userID. You will get some system accounts in this query but if your order by RAM(MEGS) desc this will likely keep the problem end users near the top.

Other helpful Queries that will root out high impact users are:

For anyone who allows ICA Channel you have likely noted the occassional “FTP over ICA” user who is copying three gigs of data over the ICA channel. This query will also give you the bandwidth usage for that end user’s ICA Channel.

SELECT CONVERT(varchar(10),dateadd(hh,-5,time_stamp),101), [user],
(sum(input_session_bps_sum)/sum(input_session_bps_cnt))/1000.0 as "Session Input",
(sum(output_session_bps_sum)/sum(output_session_bps_cnt))/1000.0 as "Session Output",
(sum(input_clipboard_bps_sum)/sum(input_clipboard_bps_cnt))/1000.0 as "Clipboard"
FROM vw_ctrx_archive_channel_perf
GROUP BY convert(varchar(10),dateadd(hh,-5,time_stamp),101), [user]
ORDER BY (sum(input_session_bps_sum)/sum(input_session_bps_cnt))/1000 desc, (sum(output_session_bps_sum)/sum(output_session_bps_cnt))/1000 desc

And Finally, this is a query I call “User Hell” , this query will give you a list of users who are having a problems starting up their Citrix sessions. This is one of my favorite queries!

SELECT [user],count(distinct convert(varchar(10),dateadd(hh,-5,time_stamp),101)) as "Days on System",
avg(session_startup_server)/1000.0 as "Session Startup",
avg(profile_load_server_duration)/1000.0 as "Profile Load",
avg(credentials_obtention_server_duration)/1000.0 as "Obtain Creds",
avg(login_script_execution_server_duration)/1000.0 as "Logon Script"
FROM vw_ctrx_archive_server_start_perf
GROUP BY [user] HAVING avg(session_startup_server)/1000.0 > 40
and count(distinct convert(varchar(10),dateadd(hh,-5,time_stamp),101)) > 5
ORDER BY "Session Startup" desc

This will produce the following query and you can see that several of these users are taking in excess of ten minutes to start their ICA session. Here you see a user’s logon script taking way to long that could point out a drive mapping command that is pointing to a server that does not exist or WINS database may be corrupt. You also see an “Obtain Creds” value of over two minutes which could indicate Domain Controller performance related issues. And finally, we see a profile load time of over 400 seconds which could be an issue with the profile size or the performance of the profile server.

UserID Days on System Session Startup Profile Load Obtain Creds Logon Script
JSmith

10

2498.557

20.705

0.722

2477.325

FJones

9

372.647

409.561

0.534

2.129

JOswald

8

258.167

17.812

67.037

1.78

JKennedy

19

245.571

265.755

0.83

1.952

SGraham

9

214.438

226.698

0.572

2.709

JJones

8

205.766

14.924

32.612

2.257

AMargret

16

205.196

203.675

0.339

1.671

DManson

9

173.912

17.027

135.288

1.711

MMason

9

166.049

160.712

0.724

2.941

NYoung

9

162.291

23.504

32.704

1.893

16 thoughts on “Three new Edgesight Queries: (Find XenDesktop Candidates and Problem users)”

  1. Another great post packed with useful stuff for Edgesight users, thanks for keeping up the good work, as one Edgesight user with limited SQL I thank you!

  2. Environment : XenApp45 with No EdgeSight

    Currently we are using Resource Manager to monitor our farm and are thinking to introduce EdgeSight.

    The problem is we bill our clients for resource usage. Resource Manager has an option to create billing reports based on self defined costs. I browsed thru all the available reports in EdgeSight and did not come up with even one report close to what we want here

    I heard from one of my colleague that “EdgeSight has no equivalent of a billing report” so does that mean we should continue using RM forever and never upgrade our farm bcos we rely on that billing report

    My question is what options do we have if we want to generate that billing report from ES??

    If your suggestion is to build a custom report, then guide me on how to get started building the custom report

    Thanks

    Vaqar

    1. What metrics do you bill on? CPU, Memory?

      Have you asked Citrix if they are going to support billing reports? I will shoot an email to them but if you give me the metrics you are billing from we may be able to set up a custom report.

      Thanks

      John

      1. Thanks John

        Actually I am not the one having this problem. I was just trying to help one of my friend who once said to me that their company wants to continue using Resource Manager bcos of the billing report

        I will ask him to send those metrics to me or directly reply back here

        I posted the same question on the EdgeSight Support forum at Citrix but no one replied

        Thanks again for your offer

        Vaqar

  3. forgive my ignorance but just getting started with Edgesight, I have no SQL experience and my DBA is out this week. All i’m trying to do is create some custom user groups based on OU or AD Site name. is that possible?

    1. Jordan,

      Currently, you can only create user groups based on username, IP address, or IP address range. EdgeSight does not store any Active Directory related information except for the domain and the username.

      Thanks for reading,
      Alain

  4. John, here is a modification to the ICA query.

    It returns one record with a count of the total number of users who have average ICA latency > 100 ms during the previous 2.4 h, relative to Arizona time. It also returns the max average ICA latency from that set of users. This can be used for automated comprehensive monitoring (SiteScope or similar tool) to alert for problems occurring across all users at all sites. For example, it could be run every 15 min.

    DECLARE @today DATETIME
    SET @today = CONVERT(varchar,GETDATE(),111)
     
    SELECT 
      COUNT(*)
    , MAX(Latency)
    FROM 
    (
    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,-7,time_stamp),111) > @today - 0.1
    GROUP BY [user]
    HAVING SUM(network_latency_sum)/SUM(network_latency_cnt) > 100
    ) AS [Citrix_Data]
    
    1. Jim,

      I liked your query. I’ve made some modifications to it. It now shows latency over 300ms over a 24 hour period. I’ve alos inlcuded the username, latency, Client IP, Client Name, and Citrix Client Version.

      SELECT *
      FROM (
      	SELECT [user], SUM(network_latency_sum)/SUM(network_latency_cnt) AS 'Latency', client_address AS 'Client IP', client_name AS 'Client Name', client_version AS 'Citrix Client'
      	FROM vw_ctrx_archive_ica_roundtrip_perf
      	WHERE CONVERT(varchar(10),DATEADD(hh,-5,time_stamp),111) > dateadd(dd,-1,getdate())
      	GROUP BY client_name, client_address, client_version, [user] HAVING SUM(network_latency_sum)/SUM(network_latency_cnt) > 300
      ) AS [Citrix_Data]
      ORDER BY 'Latency' desc
      
  5. Hi John,

    I try your “User hell” query on my ES 5.3 that works but the result are not the same as the session start up detail times.

    I analyse the details in the view vw_ctrx_archive_server_start_perf and in the view vw_xa_vda_client_start_perf.

    The values in the vw_ctrx_archive_server_start_perf view don’t seem to be realistic (SSD value).

    I realize a query based on the vw_xa_vda_client_start_perf and on the query in the report :

    select p.account_name as [user],
    avg(c.net_server_startup_duration) as startup_server_duration,
    avg(c.casd) as credentials_authentication_server_duration,
    avg(c.consd) as credentials_obtention_network_server_duration,
    avg(c.pncosd) as pn_classic_credentials_obtention_server_duration,
    avg(c.dmsd) as device_mapping_server_duration,
    avg(c.lsesd) as login_script_execution_server_duration,
    avg(c.pcsd) as printer_creation_server_duration,
    avg(c.plsd) as profile_load_server_duration,
    avg(c.scsd) as session_creation_server_duration,
    avg(cc.net_client_startup_duration) as startup_client_duration,
    avg(cc.cfdcd) as config_file_download_client_duration,
    avg(cc.bucc) as backup_url_client_count,
    avg(cc.aecd) as application_enumeration_client_duration,
    avg(cc.ifdcd) as ica_file_download_client_duration,
    avg(cc.nrwd) as name_resolution_web_server_duration,
    avg(cc.recd) as reconnect_enum_client_duration,
    avg(cc.rewd) as reconnect_enum_web_server_duration,
    avg(cc.trwd) as ticket_response_web_server_duration,
    avg(cc.lpwd) as launch_page_web_server_duration,
    avg(cc.sccd) as session_creation_client_duration,
    avg(cc.nrcd) as name_resolution_client_duration,
    avg(cc.slcd) as session_lookup_client_duration,
    avg(cc.flags) as shared_session_flag

    from vw_xa_vda_server_start_perf c
    join session s on s.sessid = c.sessid
    join ctrx_session cs on cs.sessid = s.sessid
    join instance i on i.instid = c.instid
    join timezone t on t.tzid = i.tzid
    join machine m on m.machid = i.machid
    join principal p on p.prid = s.prid
    left join vw_xa_vda_client_start_perf cc on (cc.sessid = c.sessid and cc.startid = c.startid)
    left join ctrx_published_app pa on pa.appid = cc.appid
    where s.protocol_type != 0
    –and p.account_name LIKE ‘%o%’
    and from_client = 1
    and c.dtstart is not NULL –sssd is incorrect when dtstart is NULL
    and c.net_server_startup_duration is not NULL

    group by p.account_name
    order by avg(c.net_server_startup_duration) desc

    1. We have noticed that the SSD can be impacted by warning notices, etc. When a user takes a long time to click “OK” to the MOTD then that ends up impacting the SSD, we have stopped using that metric.

      I will try this query! Also I am considering converting edgesightunderthehood.com into a forum for other DBA’s to post queries. I am a bit of a poor-man’s DBA and I get a lot of comments with great queries but I am worried they go unnoticed at the bottom of the posts.

      Thanks for the query! Any other queries you have please share and I will post them.

      Hope you had a great holiday, I also love the Doctor-Citrix.com site!

      John

  6. John,

    I can’t help myself. I’ve revisted the User Hell query. I wanted to get more recent data and provide more information in the query for troubleshooting. I’m getting the same metrics as before, but I’m adding the client’s IP, the ICA version, which server they connect to and when the session started.

    select	[user] as 'Userid',
    		cast(session_startup_server/1000.0 AS decimal(8,2))as 'Session Startup (sec)',
    		cast(profile_load_server_duration/1000.0 as decimal(8,2)) as 'Profile Load (sec)',
    		cast(credentials_obtention_server_duration/1000.0 as decimal(8,2)) as 'Obtain Creds (sec)',
    		cast(login_script_execution_server_duration/1000.0 as decimal(8,2)) as 'Logon Script (sec)',
    		client_address as 'Client IP', 
    		client_version as 'ICA Client Ver',	machine_name as 'Citrix Server', 
    		CONVERT(varchar(30),DATEADD(hh,-5,start_time),0) as 'Session Start Time'
    from vw_ctrx_archive_server_start_perf
    where DATEADD(hh,-5,start_time) > dateadd(dd,-1,getdate()) and DATEADD(hh,-5,start_time) < getdate()
    group by session_startup_server, profile_load_server_duration, credentials_obtention_server_duration, login_script_execution_server_duration, client_address, client_version, machine_name, start_time, [user] having session_startup_server/1000.0 > 60
    order by 'Session Startup (sec)' desc, [user]
    

    Thanks,
    Alain

  7. How can i make this part as a report.

    SELECT [user],count(distinct convert(varchar(10),dateadd(hh,-5,time_stamp),101)) as “Days on System”,
    avg(session_startup_server)/1000.0 as “Session Startup”,
    avg(profile_load_server_duration)/1000.0 as “Profile Load”,
    avg(credentials_obtention_server_duration)/1000.0 as “Obtain Creds”,
    avg(login_script_execution_server_duration)/1000.0 as “Logon Script”
    FROM vw_ctrx_archive_server_start_perf
    GROUP BY [user] HAVING avg(session_startup_server)/1000.0 > 40
    and count(distinct convert(varchar(10),dateadd(hh,-5,time_stamp),101)) > 5
    ORDER BY “Session Startup” desc
    .

    1. Clinton, I don’t do much with the console of Edgesight so putting into their reporting too is something Alain or David can help you with. However, if do some googling you canfigure out how to send the data to you via SSRS. SSRS is part of the Edgesight installation so you can set up parallel reports on the same web server.

      I know this isn’t likely the best answer, maybe David or Alain can provide some advice.

      Thanks

      have a great day!

      John

  8. I have tried the query to get the report with the “Profile load” duration, but mostly the output if with “NULL” for that column.

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