Category Archives: featured

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

Monitoring Advanced Persistent Threat, Malware and general punk busting with EdgeSight

Monitoring for Malware and zombies using Edgesight

Recent security breaches known as Advanced Persistent Threat rattled Security Experts at Google after finding out that a zero-day vulnerability in IE was exploited and allowed for Malware to be downloaded to key computers. This exploit details the half-measure of Security today and in my opinion was preventable, or at least could have been greatly mitigated with better logging and accountability.

I feel that too many IT Security teams are so overwhelmingly focused on ingress that the fact that the inmates are running the asylum goes largely unnoticed once Malware makes it into the network. A similar analogy would be to vigorously monitor who comes into a store, however, if out of the millions of people(packets) one or two make it through, they can take whatever the hell they want and they can leave unmolested. I’m just gonna say it….Security teams are flat out lazy when it comes to logging, in fact I read a great post from Dr. Anton Chuvakin called “Just Shut up and log!” Logging is NOT the short straw on the INFOSEC team duties, it’s the crown jewel, and if done properly, you can mitigate a number of Security Related issues by actually looking at EGRESS points and seeing where packets are going. This can be done monitoring outgoing build-up and tear-downs on Pix systems or, in the case with Edgesight, actually monitor the very Executables that are making remote calls on systems that have the Edgesight Agent installed. I think that we have a better chance of curing the common cold than of getting rid of malware and bots for the next few years. This business of completely or largely ignoring EGRESS points has got to change and depending on an INFOSEC group for your security on systems that they do not oversee on a day to day basis will get you nowhere. Unless you can provide your security group with a crystal ball or get them a million incident support agreement with Dionne Warwick and her physic friends Network , you need to take responsibility for your own security using INFOSEC as an escalation point. You know your systems, you know what an anomaly is for your boxes and you need to take responsibility.

That said, while Edgesight may not have been intended as a Security tool, it does have some extremely valuable data in it that can help you take responsibility for your systems.

It’s 3AM…do you know where your server is going?

I think it is fair to ask, why would any executable, with the exception of firefox.exe, iexplore.exe, etc need to make a TCP 443 session to an IP in China? Why would “someEXEthatIhaveNeverHeardOf.exe” make 200 TCP 443 connections to an IP in China or Russia?

I am going to cover a quick query that will allow you to interrogate all of your systems for where they were last night and what ports, protocols and destinations they were communicating with. You don’t need to look at Pix Firewall syslogs and you don’t need to “hope” your overworked INFOSEC staff sees suspicious behavior. This is low hanging fruit that is not a great deal of work that can rat out any suspicious activity that may or may not be Malware.

So STFU and give us the Query already:

Okay, this query will give you the UserID, Server/Desktop, Executable, downstream hostname and IP Address (If they are the same, it’s probably up to no good!), port and the count. I generally do not get excited about one or two connections but if you see a large number of them, you may want to investigate. If you see winword.exe phoning home to the Netherlands once? Could be web-content or portlet on a document. If you see 100 connections? Maybe check it out, maybe escalate? If your internal Network is 192.168.0.0/16 then you can exclude that in your query since, in this case, I am more concerned about “phone homes” to non-corporate sites. Likewise, you can exclude your DMZ block as well as any executables that you know make regular connections outside such as iexplore.exe and firefox.exe. You may notice Vendors phoning home or even root out some malware but if nothing else, you are not letting malware/bots/whatever leave unmolested and you can hunt down possible issues that occur in a much more timely manner than waiting for someone on your INFOSEC team to find it for you.

Example Scenario: If you see 1000 outlook.exe connections to a website in Russia that does not have a hostname, just an IP Address for both the hostname and IP_address column…that may be a possible phishing attempt.

The dateadd(hh,-5,inserted_Date),111) section is set to “-5” because I am on EST

declare @today datetime
set @today = convert(varchar,getdate(),111)
SELECT distinct account_name, machine_name, exe_name, hostname, ip_address, port, count(ip_address)
FROM vw_es_archive_application_Network_performance
WHERE convert(varchar(10),dateadd(hh,-5,inserted_Date),111) = @today
and exe_name <> 'firefox.exe'
and exe_name <> 'iexplore.exe'
and ip_address not like '192.168%'
GROUP BY account_name, machine_name, exe_name, hostname, ip_address, port
ORDER BY count(ip_address) desc

Like I said, this is low hanging fruit and since they are your systems, you will be in a position to tell odd behavior and not your Security team. Most of these queries I have set up to run automatically and SQLMail them to me daily. You can also just open up query analyzer and run the query “in the Morning” when you get in.

I know most of your shared services and hosting groups are not Security teams but this is one of many ways where we can get in the game a little and be a player in preventing breaches or at least detecting and mitigating them.

Thanks for reading

John

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