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 |
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!
Posted by Paul | April 29, 2010, 6:00 pmEnvironment : 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
Posted by Vaqar Hasan | July 19, 2010, 10:03 pmWhat 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
Posted by John M. Smith | July 20, 2010, 5:10 amThanks 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
Posted by Vaqar Hasan | July 23, 2010, 12:42 pmforgive 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?
Posted by Jordan | September 7, 2010, 11:40 amJordan,
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
Posted by Alain | April 22, 2011, 9:33 amJohn, 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.
Posted by Jim Berry | December 10, 2010, 1:16 pmJim,
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.
Posted by Alain | February 17, 2011, 2:13 pmHi 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
Posted by MaxB | December 29, 2010, 8:17 amWe 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
Posted by John M. Smith | December 29, 2010, 8:36 amJohn,
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.
Thanks,
Alain
Posted by Alain | March 3, 2011, 3:44 pm