Category Archives: Edge Sight

ExtraHop’s Citrix Solution Architecture Bundle Walk-Thru

I recorded a walk-thru of the Citrix Alpha Bundle now integrated with our latest 4.0 release.  Below is an example of the dashboard features.  Keep in mind, all of this can be done with NO AGENTs installed on your system and NO WMI walking or interrogating your systems.  We are completely passive and can similar detailed information for all of your environments (Database, SOAP/REST, Web and pretty much anything with an IP Addresses).

In the video below I will discuss some of the application containers and how they can be leveraged for troubleshooting (some overlap in information)

If you are interested in checking this out, we offer a free discovery edition or reach out to me at johnsmith@wiredata.net and I will put you in contact with your area team.

Thanks

John

 

EdgeSight: Timezone offsets

Intro

If you have implemented any of the ad hoc SQL queries available on this site, you may have noticed that most time queries are offset by –4 or –5 hours. This is due to the fact that the EdgeSight database uses GMT to record time and John and I are located in the U.S. Eastern Time Zone.

In this post we will take a look at some tables in the EdgeSight database that you can utilize to make your queries more local and portable.

seamonsterThere Be Monsters Here!

Most of my experience with EdgeSight has been with the database views that summarize and organize the vast amounts of data that EdgeSight collects. On occasion I’ve gone where few dare to tread to look directly at the tables for the data I need.

EdgeSight’s views are dizzying enough, but the table structure of the EdgeSight database is intimidating to the SQL neophyte. Despite this, I was inspired to look deeper after David did his post on session counts. His query uses the ‘timezone’ table to determine the time offset for his query and this got me curious. How can I utilize this to make my queries easier to maintain and more portable?

Timezone table

Lets take a look at the timezone table

SELECT *
FROM timezone

image

The above picture is only a portion of the table. It consists of 74 rows. Yeah makes  total sense right? Naturally, I had to do some more checking. If you check the company table, we get a clue.

SELECT *
FROM company

image

As you can see in the above picture, each company in the EdgeSight database has an associated Time Zone and Language. In this case, we have a timezone id (tzid) of 13 and a culture_name of en-US. If we cross reference the tzid with the timezone table we get:

image

Looking at the result above, we can see that this is for the U.S. Eastern time zone and includes daylight savings time as well. You can configure this in the EdgeSight console by clicking on the Configure tab. Look under the Server Configuration section and click on Companies to see where to add/edit company information.

image

So for the example above, I have the language set to English and the time zone set to U.S. Eastern Time which has a GMT offset of –5 hours.

How does this help me?

Let’s take a look at a query I’ve posted on this site before:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username'

As you can see above, all the timedate fields are offset by –4 hours. To keep from having to change the offset to –5 or –4 depending on what time of year it was (standard vs. daylight savings time), I developed a simple select query that determines the current offset by checking the timezone table.

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13

In layman’s terms, look at the timezone table where the timezone id (tzid) is equal to 13. If the field ‘use_daylight’ is equal to zero, use the ‘standard_bias’ otherwise use the ‘daylight_bias’.

I’m setting whichever bias this query returns equal to the variable @tzbias. I then use the @tzbias variable in my timedate fields in my queries. If we rewrite the above query with the tzbias variable, we get the following:

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl
WHERE apptbl.exe_name like '%'+@app+'%'
and apptbl.account_name <> 'UNKNOWN'
and serv.client_address not like '192%'
and icatbl.client_directory not like '\%'
and convert(varchar(10),dateadd(mi,@tzbias,apptbl.time_stamp), 111) >= @today-30
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid
and CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,serv.time_stamp), 111)
ORDER BY CONVERT(VARCHAR(10),DATEADD(mi,@tzbias,apptbl.time_stamp), 111), 'username'

Since the timezone bias is in minutes, I had to change the DATEADD functions to use mi for minutes. Now I can use my queries year around without worrying about daylight savings time changes.

I hope this provides you some options when doing ad hoc queries against the EdgeSight database. As always, I welcome all comments and questions.

Thanks,
Alain

Are You There EdgeSight? It’s me Worker

Intro

If you rely on EdgeSight to provide accurate and timely information about your farm you have to assume that all your EdgeSight Worker Agents are functioning as expected.  Or do you?  In this post, we will review the information that the EdgeSight console provides you as well as creating a dashboard that can give you detailed information on your EdgeSight Worker agents.

EdgeSight Console: Configuration Tab

The first place you can check the health of your EdgeSight server and its agents in under the Configuration Tab

image

Along the left-hand side of this screen you will see way to configure your workers, alerts, and other server settings. We’re going to spotlight some items under Server Configuration and Server Status.

image

Server Configuration: Status
Your first overview of server health comes when you click on Status under Server Configuration.

image

The first line lists the workers that were and were not updated in the current 24 hour period as well as newly added workers.  Right away you see (in this case) that 48 workers updated and 32 did not.  That’s a large number of EdgeSight agents that have not uploaded their data into your database and therefore any reports you are running will not include these systems.  The question becomes which systems did not update and why?

ES_ZQUEUE…Gesundheit!

The service on the EdgeSight server that processes payloads from the worker agents is the es_zqueue (seen under Server Script Host Status).  This process is not reporting any issues and there are no pending payloads to process (we’ll look at this more later).

Server Status: Messages
Message Status lists all the system messages generated by EdgeSight.  This includes Agent errors, payload errors, and new agents alerts to name a few.

image

Here you will see which servers had a payload issue (Data Upload), but not a reason why systems have not updated the database.

Server Status: Server Script Host
Clicking on this in your EdgeSight Console will show you the following screen:

image

The es_zqueue manages the modules that keep the EdgeSight database updated, cleaned, and running smoothly. The core_zpd_loader 1 and 2 manage the data payloads from devices with the EdgeSight agent including errors. Clicking on the triangle will show the following menu.

image

Clicking on View Log will allow us to investigate why a payload might have failed or created an error.

4/13/2012 5:18:03 AM: PayloadLoader: Starting payload load for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd
4/13/2012 5:18:11 AM: PayloadLoader: Payload load completed with errors for C:\Program Files (x86)\Citrix\System Monitoring\Server\EdgeSight\Data\WebLoad\Inst_33.zpd. Error: -2146233088: Citrix.EdgeSight.Loader. System.Exception

As we can see if this example, the payload completed with an error and we can try searching Citrix to see if there is a resolution related to this error, but we do not see which server failed to upload any data.

I’ve walked through the diagnostic information that is available in the EdgeSight console to show that we still do not have a clear sign of which servers have updated the EdgeSight database recently. To address this issue, I did some digging around in the EdgeSight database and created a query that links the instance, machine, and OS_version tables.

The Query

DECLARE @tzbias INT
SELECT @tzbias = case when use_daylight = '0' then standard_bias else daylight_bias end from timezone where tzid = 13
SELECT    i.instid, m.name as 'System', ip_address AS 'IP', product_version AS 'ES Version',
CASE dept_set_type    WHEN 1 THEN 'XenApp' WHEN 2 THEN 'Endpoint' END AS 'ES Agent',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_sync),100) AS 'Last Sync',
CONVERT(VARCHAR,DATEADD(mi,@tzbias,last_config_start),100) AS 'Last Config Check',
CONVERT(DECIMAL(19, 2),(last_db_size/1048576.0)) AS 'Last FBDB Size (MB)',
CASE o.short_name    WHEN 'Windows Server 2008' THEN 'W2K8'
WHEN 'Windows Server 2008 R2' THEN 'W2K8R2'
WHEN 'Windows Server 2003' THEN 'W2K3'
WHEN 'Windows XP'           THEN 'XP'
ELSE 'Other' END AS 'OS',
CASE o.ptype        WHEN 'Standard x64 Edition' THEN 'Std x64'
WHEN 'Professional'         THEN 'Pro'
WHEN 'Enterprise Edition'   THEN 'Ent'
WHEN 'Standard Edition'     THEN 'Std'
WHEN 'Enterprise x64 Edition' THEN 'Ent x64'
ELSE 'Other' END AS 'Edition',
sp_level,
CONVERT(VARCHAR,DATEADD(mi,@tzbias,tstamp),111) AS 'Date Added',
cps_farm_name,i.cps_product_name, i.cps_product_version, i.cps_product_service_pack
FROM instance i,machine m, os_version o
WHERE m.machid=i.machid and i.osid = o.osid
ORDER BY dateadd(mi,@tzbias,last_sync) DESC

The Report

image

Click on the image to see a larger version..

With this dashboard (I created it based on the query above in SQL Reporting Services) you can quickly see which servers have updated (Last Config Ck) and which have not. Armed with this information you can review your EdgeSight Agent worker schedules or check the agent on the system in question to make sure it is communicating with the EdgeSight server.

As always I welcome all questions and comments.

Thanks,
Alain

ESUTH Briforum Session now online

My Briforum Session is online for anyone who wants to go check it out.  After viewing the session it appears I said “UH..” about 637 times?  Anyway it was a great experience and an honor to speak in front of so many talented individuals.  If you are curious of what I do when I go in and run queries you can watch this session and basically see how it’s done.  The session also has some custom queries show integrating malware and geospatial data with your Edgesight data to check for infected systems that your antivirus may have missed.

My presentation was somewhat hampered by being told I could not use any of my customer data in the presentation.  One of the hard tasks in teaching Edgesight is that most companies do not want their data made public so doing the presentation in a public forum was a bit tough.  This would be much easier if it were done in a corporate classroom using the customer’s data. 

Anyway, please have a look, if you have never run a SQL query you will see some of that here.

Take care and thanks for watching.  I cannot recommend enough, attending Briforum, it is a true geek-to-geek, vender nuetral conference and was, hands down, the most informative virtualization conference I have ever gone to. 

Thanks guys

John

http://www.brianmadden.com/blogs/videos/archive/2011/08/16/edgesight-under-the-hood_2C00_-a-video-from-BriForum-2011.aspx

 

Finding Users On Your Network, Using PC’s, and Running a Certain Application

Intro

Recently I was asked to determine which users were using a certain application in our Citrix Farm.  We are using a published desktop and while EdgeSight has reports to show published applications, few built-in reports to show what users are running in their session.  In addition, I was only looking for users who were on our internal network and not using a thin client.  Unless your network team has created a very segregated network, and you have set up user groups based on various subnets and devices, this sort of information is impossible to pull out of EdgeSight.   In this post I will show you a query that gathers this information.

The Query

DECLARE @today datetime 
DECLARE @app varchar(20) 
SET @today = convert(varchar,getdate(),111) 
SET @app = 'notepad.exe' 
SELECT DISTINCT CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) AS 'Date', serv.machine_name AS 'Server', serv.[user] AS 'Username', serv.client_name, serv.client_address, serv.client_version, icatbl.client_directory, apptbl.app_description, apptbl.exe_name, apptbl.exe_version 
FROM vw_es_archive_application_usage apptbl, vw_ctrx_archive_server_start_perf serv, vw_es_usergroup_ica_users icatbl 
WHERE apptbl.exe_name like '%'+@app+'%' 
and apptbl.account_name <> 'UNKNOWN' 
and serv.client_address not like '192%' 
and icatbl.client_directory not like '\%' 
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30 
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid 
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111) 
ORDER BY CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111), 'username' 

The Query Explained

Let’s review the criteria we are looking for in this query:

  1. Users accessing a certain application
  2. Users who are not using thin clients
  3. Users who are on the internal LAN

To gather this information, I’m using 3 different views in the EdgeSight database:

  1. vw_es_archive_application_usage – aliased as “apptbl”
    • This will give me Application Description, the EXE name, the EXE version
  2. vw_ctrx_archive_server_start_perf – aliased as “serv”
    • This will give me the XenApp server, the Username, the Client Name, IP Address, and ICA Version
  3. vw_es_usergroup_ica_users – aliased as “icatbl”
    • This will give me the ICA Client Directory

These 3 views will be linked by the SESSID (session id) column with is present in all the views.

and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid

First we declare some variables and assign them values:

DECLARE @today datetime
DECLARE @app varchar(20)
SET @today = convert(varchar,getdate(),111)
SET @app = 'notepad.exe'

If you have the several requests with different criteria you can declare some variables to help you.  In this case, I’ve created a variable called @app that I can set to any executable that I’m reporting on. To refer to this variable in the query, I use it in the WHERE clause using a LIKE operator and a regular expression.

WHERE apptbl.exe_name like '%'+@app+'%'

The rest of the WHERE clause helps us find the users we are looking for.

 
and apptbl.account_name <> 'UNKNOWN' 
and serv.client_address not like '192%' 
and icatbl.client_directory not like '\%' 
and convert(varchar(10),dateadd(hh,-4,apptbl.time_stamp), 111) >= @today-30 
and apptbl.sessid = serv.sessid and icatbl.sessid = serv.sessid 
and CONVERT(VARCHAR(10),DATEADD(hh,-4,apptbl.time_stamp), 111) = CONVERT(VARCHAR(10),DATEADD(hh,-4,serv.time_stamp), 111) 

I have filtered out user IP addresses that start with “192” as this is typical of home-based routers.  Obviously, you can modify this to reflect your own network.  To filter out thin-clients, I’m not selecting any client directories that start with “\”.  I’ve found that thin clients (in my case Wyse) have file systems that begin with a “\” and you can refer to my post that covered finding non-PC devices in EdgeSight here. Finally, I’m only looking at entries for the past 30 days, where the sessid’s match, and where the time_stamps match.

I always welcome comments and questions.

Thanks,
Alain

EdgeSight: BRIFORUM Content

Intro
To everyone who attended my session at Briforum, thank you.  It was an honor to present and to meet so many Virtualization rock stars.  As promised, I am posting most of the content from my presentation at Briforum.  There were a few new queries that were not yet part of the site in addition to a few that I did not have time to get to.  Also, I meant what I said about converting ESUTH to a forum so that we can have multiple authors.  If you have some good Edgesight Queries to share, let me know and I will make you an author on the site!

Holistic Query
This query is designed to give you your basic system performance including Memory and Disk Queue.  You can use this to query specific metrics of a system during a reporting period (by hour).  The only value you have to manually enter is the date in the format you see above.  You will take the “Machine Name” and “Time Stamp” column values and enter them into the query below to get more detail.

The Query:

select machine_name, dateadd(hh,-5,time_stamp), disk_time_sum/disk_time_cnt as "Disk Time", total_processor_time_sum/total_processor_time_cnt as "Processor Time",
(committed_Kbytes_sum/committed_kbytes_cnt)/1000.0 as "Committed memory", Context_switches_sec_sum/Context_switches_sec_cnt as "Context Switches",
current_disk_queue_length_sum/current_disk_queue_length_cnt as "Disk Queue Length"
from c3edgesight.citgop_edgesightxa.dbo.vw_es_archive_system_performance
where  machine_name like '%ComputerName%'
and CONVERT(varchar(10),dateadd(hh,-5,time_stamp),101) = '07/14/2011'
order by dateadd(hh,-5,time_stamp)

Gives us:

machine_name Time Disk Time Processor Time Committed memory Context Switches Disk Queue Length
SVR-DS81 7/14/11 13:00 0.2470124 6.543246415 4140.62 9686 0
SVR-DS66 7/14/11 13:00 0.1102058 26.54753931 2491.53 3814 0
SVR-DS68 7/14/11 13:00 0.2426049 5.727161964 3956.425 9090 0
SVR-DS69 7/14/11 13:00 0.140912 3.041551342 4347.409 8636 0
SVR-DS83 7/14/11 13:00 0.2029572 4.014888507 4259.335 7362 0
SVR-DS67 7/14/11 13:00 0.1474974 29.02722529 3434.519 6479 0
SVR-DS80 7/14/11 13:00 0.1451845 3.177582946 4403.747 8482 0
SVR-DS74 7/14/11 13:00 0.1923944 3.841824931 4391.01 7557 0
SVR-DS78 7/14/11 13:00 0.1418489 2.496465329 4249.419 6625 0


Drilling Down:
So, if looking at the results of the query above uncovers an issue of high CPU, RAM or Disk Que you can drill down with the following query.  In this example we will copy the Machine_name column and Time_Stamp column and paste them in between the single quotes to populate the variable.  So we will copy SVR-DS81 and 2011-07-14 13:00:00.000 which will give us a list of every processor owned by every user on the system during that reporting period.

declare @MachineName nvarchar (255)
declare @timestamp datetime
set @MachineName= 'SVR-DS81'
set @timestamp = '2011-07-14 13:00:00.000'
select account_name, exe_name, App_description, total_processor_time_sum/total_processor_time_cnt as "Processor Time",(Private_kbytes_sum/private_kbytes_cnt)/1000 as "Private Memory(megs)", pages_sec_sum/Pages_sec_cnt as "Pages/Sec"
from vw_es_archive_application_performance
where DATEADD(hh,-5,time_stamp) = @timestamp
and machine_name = @MachineName
order by "pages/sec" desc
account_name exe_name App_description Processor Time Private Memory(megs) Pages/Sec
JSmith Excel.exe Microsoft Office Excel 0.00021222 194 2
FJones Outlook.exe Microsoft Office Outlook 1.12339166 91 60
SStills Cutefiller.exe CutePDF Form Filler 1.66994562 87 127
NYoung Wfica32.exe Citrix 0.00010988 83 0
SPickens Outlook.exe Microsoft Office Outlook 0.05320751 79 14
CWebster Wfica32.exe Citrix 0.01940179 78 0
SBASS Outlook.exe Microsoft Office Outlook 2.86540794 65 99
SYSTEM Pmagent.exe Performance Manager Agent 0.13332299 47 44
Jsmith Outlook.exe Microsoft Office Outlook 0.00087658 45 0
SYSTEM Icalm server.exe iCALM Server 0.01223421 44 3
DFeller Explorer.exe Windows Explorer 0.12927156 43 27
JOlsen Outlook.exe Microsoft Office Outlook 0.51810852 43 142

How long did a user’s session last:
So this is one of my cross-tab queries that you can copy and paste and just change the username for.  This will give you the date, sessoin ID, logon and logoff time as well as the length of the session for a specific user.  In this instance I am NOT pulling data from the views, rather I am pulling the data from two tables, the session table and the principal table.  This query can be handy when you are trying to audit the time a user is on the system.

The Query:

declare @today datetime
set @today = convert(varchar(10),getdate(),111)
select convert(varchar(10),dateadd(hh,-5,logon_time),111), inst_sessid, dateadd(hh,-5,logon_time),
dateadd(hh,-5,logoff_time),
convert(varchar,floor(datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time))/60))+ ':' +Right('0' + convert(varchar,datediff(mi,dateadd(hh,-5,logon_time) , dateadd(hh,-5,Logoff_time)) % 60), 2) as HrMinfrom session s, principal p
where convert(varchar(10),dateadd(hh,-5,logon_time),111) > @today-30
and account_name = '%AccountName%'
and p.prid = s.prid

Average Start/End Time:
The idea for this Query came about after listening to Chris Nickerson (one of the top Social Engineers in the world) discuss how, as part of penetration testing, they can call a helpdesk and try to get them to change a passoword after finding a  user ID for a particular company.  I tend to think of this query as a social engineering counter measure.  What it gives me is the average logon and logoff time for a particular user.  How this can be valuable would be if you could set it up as part of the helpdesk solution so that when someone calls in, the helpdesk knows the average logon and logoff time for the person they are talking to.  If a person who normally works M-F 8AM to 5PM calls in at 2:15AM wanting to change their password, the helpdesk technician can maybe ask a few more questions or be more alerted to a possible social engineering attempt.  This query is still not entirely vetted but as I have changed employers, I may not get a chance to vet it further.  I have noticed it is accurate for standard users but for Admins who may have that “special” ICA Timeout, it my show some skewed results because lets face it, we are on the system pretty much 24×7.  This query gives the average logon and logoff time for the last 7 days.

The Query:

select account_name, right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logon_time)as float) - floor (cast(dateadd(hh,-5,logon_time) as float)))as datetime)),100),7),
right(convert(varchar(20),(cast(AVG(cast(dateadd(hh,-5,logoff_time)as float) - floor(cast(dateadd(hh,-5,logoff_time) as float)))as datetime)),100),7)
from session] s, principal p
where s.prid = p.prid
and account_name = '%UserName%'
and  dateadd(hh,-5,logon_time) >= GETDATE()-7
group by account_name

EdgeSight: Filtering a Query by IP Subnet

es_logoPrologue
Many of EdgeSight’s tables and views have a field for the client’s IP address, and this is stored as variable-length character string (varchar or nvarchar). In order to sort or filter on this field you must use a complex regular expression or find a way to split the field into different octets. In this blog post, we will do just that by presenting a problem that requires finding users based on their subnet…

Intro
Thanks to the vibrant competition present in the virtualization space, many Engineers find themselves always transitioning to the next version of their virtualization solution. During such a transition, management (and hopefully the engineers) want to know who’s using the new system and if users are still accessing the old one. In many cases this can be a trivial exercise, but for this scenario we’ll make it more complex.

Scenario
The networking team has intelligently organized its users’ locations by subnet. In fact, due to number of users and available IP’s, each floor at the main location has it’s own subnet. Recently, Citrix users at the main location were transitioned to the new environment, except for a subset who had legacy applications that would not work in the new Citrix farm. Management wants to know many of the transitioned users are using the new system.

Problem
Since we are using published desktops in both the old and new Citrix environments, EdgeSight (version 5.3) does not provide an easy way to query desktop launches (see this post on EdgeSight Under the Hood for how to get a query of published desktop launches). In this case, we have a different naming schema for the servers in the new farm, but since there are many different locations connecting back to our Citrix farms, we need to just select the users at the main location. This will require us to filter the users based on their IP subnet.

PARSENAME
While researching this issue I found that dealing with IP addresses in Transact-SQL is a common problem. Luckily there is a built-in function called PARSENAME that parses object names like ‘servername.databasename.schemaname.objectname’. Since IPv4 addresses follow the same convention, you can reference each part of the octet in an IP address.

For example:

DECLARE @IP nvarchar(15)
SET @IP = '192.168.1.1'
SELECT PARSENAME(@IP,4) AS 'Octet 1',
PARSENAME(@IP,3)AS 'Octet 2',
PARSENAME(@IP,2)AS 'Octet 3',
PARSENAME(@IP,1)AS 'Octet 4'

Gives us:

Octet 1    Octet 2    Octet 3    Octet 4
---------- ---------- ---------- ----------
192        168        1          1

(1 row(s) affected)


The query

For this query we will use vw_ctrx_archive_server_start_perf which has become my goto view for client related information and just sort by one subnet: 192.168.1.0 – 192.168.1.101 and look at the last 3 days of data

DECLARE @today datetime
SET @today = CONVERT(varchar(10),getdate(),111)
--we are using DATEADD and offsetting by minus four hours due to Eastern Daylight Time
SELECT CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111) >= @today-3 --past 3 days
and [user] <> 'UNKNOWN'
--Gets NEWSERVER01, NEWSERVER02, etc
and machine_name like 'NEWSERVER%
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'  and (PARSENAME(client_address,2) = 1 and PARSENAME(client_address,1) between 0 and 101
GROUP BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111), [user]
ORDER BY CONVERT(varchar(10),DATEADD(hh,-4,time_stamp), 111)

For our second example, we’ll sort with 11 sub-nets:
192.168.1.0 – 192.168.1.101
192.168.2.0 – 192.168.2.102
192.168.3.0 – 192.168.3.103
192.168.4.0 – 192.168.4.104
192.168.5.0 – 192.168.5.105
192.168.6.0 – 192.168.6.106
192.168.7.0 – 192.168.7.107
192.168.8.0 – 192.168.8.108
192.168.9.0 – 192.168.9.109
192.168.10.0 – 192.168.10.110
192.168.11.0 – 192.168.11.121

DECLARE @today datetime
SET @today = convert(varchar(10),getdate(),111)
SELECT CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) as 'Date', ([user]) as 'User'
FROM vw_ctrx_archive_server_start_perf
WHERE CONVERT(varchar(10),dateadd(hh,-4,time_stamp), 111) >= @today-3
and [user] <> 'UNKNOWN'
and machine_name like 'NEWSERVER%'
and PARSENAME(client_address,4) = '192'
and PARSENAME(client_address,3) = '168'
and (PARSENAME(client_address,2) =1 and PARSENAME(client_address,1) between 0 and 101
or PARSENAME(client_address,2) =2 and PARSENAME(client_address,1) between 0 and 102
or PARSENAME(client_address,2) =3 and PARSENAME(client_address,1) between 0 and 103
or PARSENAME(client_address,2) =4 and PARSENAME(client_address,1) between 0 and 104
or PARSENAME(client_address,2) =5 and PARSENAME(client_address,1) between 0 and 105
or PARSENAME(client_address,2) =6 and PARSENAME(client_address,1) between 0 and 106
or PARSENAME(client_address,2) =7 and PARSENAME(client_address,1) between 0 and 107
or PARSENAME(client_address,2) =8 and PARSENAME(client_address,1) between 0 and 108
or PARSENAME(client_address,2) =9 and PARSENAME(client_address,1) between 0 and 109
or PARSENAME(client_address,2) =10 and PARSENAME(client_address,1) between 0 and 110
or PARSENAME(client_address,2) =11 and PARSENAME(client_address,1) between 0 and 121)
GROUP BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111), [user]
ORDER BY convert(varchar(10),dateadd(hh,-4,time_stamp), 111)

Hopefully this will provide you with some more options when you need to present data from your EdgeSight database. As always I welcome any and all questions and comments.

Thanks,
Alain

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

Edgesight Under the Hood: Emails and Questions from Readers

So, I thought for my next post I would include a few of the answers I gave to some emails that I received from people who viewed “Edgesight Under the Hood” 

I will start with four very easy queries that can answer some, what at times seemed to be, pretty tough questions. As I continue to digg deeper and deeper into edgesight, the amount of data it has is just staggering. 

First Question:
We deployed PNAgent a few months ago but we really don’t know if anyone is using it. How can I tell who is launching PNAgent? Can I get a count of how many would be impacted if the PNAgent server were to be taken down?

Here is the query for that: (Number of Launches)
select launch_type, count(launch_type)
from vw_ctrx_archive_client_start_perf
group by Launch_type
order by count(launch_type) desc
 

Perhaps more useful would be the number of users:
select launch_type, count(distinct [user])
from vw_ctrx_archive_client_start_perf
group by Launch_type
order by count(distinct [user]) desc

My next question is how to find out a user’s screen resolution. This is a very quick and easy query that will give you each user’s screen resolution.

Query for each user’s screen resolution:
select distinct Account_name, client_disp_horiz, client_disp_vert
from vw_es_usergroup_ica_users
 

And this query will give you the distribution of screen resolution(s) throughout your environment. This will help you plan your video memory settings on your default.ica files.

Query for distribution of screen resolution(s):
select client_disp_horiz, client_disp_vert, count(distinct account_name)
from vw_es_usergroup_ica_users
group by client_disp_horiz, client_disp_vert
order by count(distinct account_name) desc

 Then I got a question on how to root out mac and Linux/Unix users in your environment. I noted that in the vw_es_usergroup_ica_users view there is a column called “client_directory”. Since windows systems still use drive leters this was actually pretty easy. Simply query for any client_directory that does not have a colon in it. You will get the unix filesystem path (most of the time “/”…UHG!”), for the more savy users “/home/username”

Query for Non-Windows Hosts:
select distinct account_name, client_address, client_directory
from vw_es_usergroup_ica_users
where client_directory not like ‘%:%’

Thanks for coming over to the new sight, I am trying to move ALL edgesight queries, and eventually uploadable RDL’s to http://edgesightunderthehood.com

And thanks for reading!John M. Smith