Category Archives: Edgesight Custom Reports

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

 

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

Headless Edgesight: Using Stored procedures to have key metrics emailed to you.

If you have been coming to the site now for the last few years you have probably noticed that we kind of specialize in a “Headless” Edgesight deployment. Dave, Alain and I all tend to steer away from the Edgesight console and focus more on running ad hoc queries or specialized reports. In this entry, I want to focus on leveraging stored procedures to provide you with the data you need via an email every morning so that you can validate a few things in your environment and perhaps put yourself in a position to “right the ship” before your onrush of users pile on the to the system at 8am.

What we will be covering today is coupling a stored procedure with an HTML formatted SQL Mail. To do this we require a few pre-requisites.

1.) You have SQL Mail configured (if you do not, there are better blogs than mine to walk you through that).

2.) That you have enough of a basic understanding of SQL Server to schedule a stored procedure to run. (or the wherewithal to follow the directions I provide).

While there are a number of canned reports in Edgesight, navigating through them or getting the exact metric you are looking for can be painstaking. Using the stored procedure method, you can schedule them to run and provide you with key metrics made up of those custom queries you choose in a format that will work both on your desktop outlook as well as be readable on your smart phone.

First Stored Procedure:

Title: SP_CTX_REBOOTS

Purpose: Most of us have a reboot regimen that we put our XenAPP Servers through. This query/stored proc will email you the status of your nightly reboots. It will be up to you and your operations staff to know which servers need to get rebooted but you can at least find out that 10 of your 25 xenapp servers IMA service did not start before you get your morning rush of users.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_REBOOTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
 --Author:        <John Smith>
-- Create date: <04/02/2012>
-- Description:   <Email Reboot Records>
-- =============================================
CREATE PROCEDURE [dbo].[SP_CTX_REBOOTS]
-- Add the parameters for the stored procedure here
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
declare @Result NVARCHAR(MAX) ;
declare @today datetime ;
set @today = convert(varchar, getdate(),111) ;
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +
 N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +
N'<th>Time</th>'+

N'<th>Machine</th>'+

N'<th>Status</th>'+

cast ((Select distinct td=  dateadd(hh,-5,time_stamp),           '               ',

td= Machine_name,             '               ',

td= Service_State_name,             '               '

from vw_ctrx_archive_service_state

where dateadd(hh,-5,time_stamp) > getdate()-1

order by machine_name

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Server Reboots',

@importance = 'High' ;

end ;

Second Stored Procedure:

Title: SP_CTX_Alerts

Purpose: This is actually a very valuable alert, this will provide you with the top errors/popups users receive when they are on your system. This is made up of both Windows and Application level alerts. I have noted a number of application (custom written errors) that show up here because they popped up during the users session. This can serve as a very valuable QA tool for both Systems Admins and Developers to run down the errors that they see.

USE [EDGESIGHT]
GO
/****** Object:  StoredProcedure [dbo].[SP_CTX_ALERTS]    Script Date: 04/05/2012 12:12:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Author:        <John Smith>

-- Create date: <04/02/2012>

-- Description:   <Email top Errors and popups>

-- =============================================

CREATE PROCEDURE [dbo].[SP_CTX_ALERTS]

-- Add the parameters for the stored procedure here

AS

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Begin

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @Result NVARCHAR(MAX) ;

declare @today datetime ;

set @today = convert(varchar, getdate(),111) ;

SET @tableHTML =

N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:10px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;}th{background-color:#99CCFF;}</style>' +

N'<table border=0 width=90% cellspacing=0 cellpadding=3>' +

N'<th>EXE-Name(If Any)</th>'+

N'<th>Alert Text</th>'+

N'<th>Context</th>'+

N'<th>Total</th>'+

cast ((Select td= CASE

WHEN exe_name IS NULL Then 'None'

Else exe_name

END,          '               ',

td= replace(Alert_text,'°',''),             '               ',

td= Context_text,             '               ',

td= count(alert_text),             '               '

from vw_es_archive_alert

where dateadd(hh,-5,time_stamp) > getdate()-1

group by exe_name, alert_text, context_text

having count(alert_text) > 20

order by count(alert_text) desc

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'XenStats',

@recipients = 'john.smith@xentrifuge.com',

@body = @tableHTML,

@body_format = 'HTML',

@Subject = 'Alerts and Popups',

@importance = 'High' ;

end ;

Scheduling the Stored Procedure:

Step 1: Add a new SQL Server Agent Job.

 

Step 2: Enter in the name of the Stored Procedure


Step 3: Got to “Steps” Configure the Steps by telling it to execute the stored procedure. (Set the Database Name to the Edgesight Database)


Step 4: Schedule the job to run so that the report is waiting for you when you get in that morning.

What does it look like: (You may have saw this at Briforum ’11)

Basically it is a very simple table that can auto size for your smart phone.

Time

Machine

Status

2012-04-05T02:03:15 SERVER19 Machine Boot
2012-04-05T02:05:06 SERVER19 Service Started
2012-04-05T02:03:48 SERVER20 Machine Boot
2012-04-05T02:05:40 SERVER20 Service Started
2012-04-05T02:15:33 SERVER21 Machine Boot
2012-04-05T02:17:27 SERVER21 Service Started
2012-04-05T02:04:28 SERVER22 Machine Boot
2012-04-05T02:06:19 SERVER22 Service Started
2012-04-05T02:05:55 SERVER23 Machine Boot
2012-04-05T02:07:47 SERVER23 Service Started
2012-04-05T02:06:44 SERVER24 Machine Boot
2012-04-05T02:08:36 SERVER24 Service Started
2012-04-05T02:07:38 SERVER25 Machine Boot
2012-04-05T02:09:33 SERVER25 Service Started
2012-04-05T02:10:08 SERVER27 Machine Boot
2012-04-05T02:12:07 SERVER27 Service Started
2012-04-05T02:10:38 SERVER28 Machine Boot
2012-04-05T02:12:31 SERVER28 Service Started
2012-04-05T02:11:46 SERVER29 Machine Boot
2012-04-05T02:13:35 SERVER29 Service Started
2012-04-05T02:12:55 SERVER30 Machine Boot
2012-04-05T02:14:45 SERVER30 Service Started
2012-04-05T02:02:28 SERVER35 Machine Boot
2012-04-05T02:04:18 SERVER35 Service Started
2012-04-05T02:04:59 SERVER35 Service Started
2012-04-05T02:10:50 SERVER35 Service Started

Conclusion:
So, the two queries included in the stored procedures are very useful but the point of the post this time is not to provide a useful query so much as it is to provide a useful method. If you have some rudimentary SQL Skills you should be able to reverse engineer this to include any custom Edgesight Query that you see on this post or that you have made up yourself. Sometimes it is nice to be able to have a simple, formatted report automatically sent to you, a customer or your boss so that they can get the key metrics they need on a consistent basis. I have found Edgesight to actually be more useful in a headless configuration (ad hoc and these stored procs) than to use the console.

Thanks for reading! If you are at Synergy and you see me (big ugly guy w/thick glasses) say “heya”

John

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

 

Average Session Count by Day and Hour: The Report

In my previous article: Average Session Count by Day and Hour: The Query,  I dove into a query that shows the number of sessions on a farm at any given hour.  Next, we go onto the creation of the report. For this I use SQL Server Business Intelligence Development Studio (BIDS).  To begin, I created a new report to contain a matrix which runs the query that I created above.  For this report to run in BIDS and EdgeSight, the entirety of the following should be placed in the query window:

DECLARE @UTCNow DATETIME
DECLARE @LocalStartDate SMALLDATETIME
DECLARE @LocalEndDate SMALLDATETIME
DECLARE @CompId INTEGER
DECLARE @Filter INTEGER

SET @CompID = 1
SET @Filter = 1

SET @UTCNow = GETUTCDATE()
SET @LocalStartDate = dbo.udf_core_get_offset_date(1, @Start, 1, 0, @UTCNow)
SET @LocalEndDate = dbo.udf_core_get_offset_date(1, @End, 0, 0, @UTCNow)

SELECT     SUM(vw_ctrx_archive_system_perf.active_sessions_peak + vw_ctrx_archive_system_perf.inactive_sessions_peak) AS TotalSessions,
                      CONVERT(varchar(10), DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 111) AS Date, CONVERT(varchar(10),
                      DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp), 108) AS Time
FROM         vw_ctrx_archive_system_perf INNER JOIN
                      company ON vw_ctrx_archive_system_perf.compid = company.compid INNER JOIN
                      timezone ON company.tzid = timezone.tzid
GROUP BY vw_ctrx_archive_system_perf.time_stamp, timezone.standard_bias
HAVING      (DATEADD(mi, timezone.standard_bias, vw_ctrx_archive_system_perf.time_stamp) >= @LocalStartDate) AND (DATEADD(mi, timezone.standard_bias,
                      vw_ctrx_archive_system_perf.time_stamp) < DATEADD(d, 1, @LocalEndDate))
ORDER BY Date, Time

In the “Design the Matrix Wizard” Add Time2 to the Columns, Date to the Rows, and Total Sessions to the Details value.  Click finish.
Edgesight Matrix wizard

The first necessary items that need to be added to the report in order for this report to run successfully are parameters.  On the data or layout tab of your new report, open the Report Parameters window.  Two parameters need to be added to the report “Start” and “End”.  They both need to be string values.  EdgeSight reads these parameters on the reports and allows you to choose what dates you want to run the report for.  Go ahead and preview the new report.  If it doesn’t work, take a break, watch an episode of Dexter, and be glad you work on computers, where everyone gets a second shot.  Let’s continue down the steps to making this a report with a bit more useful data.

In the rows where the Date is, I needed to add the day of the week to display days like the following: 8/1/2011 – Monday.  To do this I changed the formula for the cell to:  =Fields!Date.Value & ” – ” & WeekdayName(Weekday(Fields!Date.Value))

In the columns where time2 resides I needed to show the hours and not show the integer.  I’m not a big VB guy and am still working on growing my coding.  For that reason I have change the cell to the impeccableformula of: =Fields!Time2.Value & “:00:00”. This shows the hours of the day in military format.

Good enough for me.

In BIDS, to show the average of rows and columns, a bit of code needs to be added to the report.  In the Report Layout window, you can add the subtotals to the rows and columns by simply right clicking on any row and column and selecting “Subtotal”.  BIDS will add a new row or column to your report that shows the subtotal of the row or column.  The problem is that BIDS 2005, I don’t know about any newer version, only subtotals are shown and not the average.  To show the average of the columns instead of the subtotals, open the “report properties” and go to the code tab.  Based on a how-to from All About Business Intelligence add the following code:

Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
        If InScope Then
            Return sumValue
        Else
            If Not avgValue Is Nothing Then
                Return String.Format(avgFormat, CDbl(avgValue))
            Else
                Return Nothing
            End If
        End If
    End Function

Next you have to change the details cell to the following code:


=Code.MatrixCellValue(
Sum(Fields!TotalSessions.Value),
Avg(Fields!TotalSessions.Value),
InScope("matrix1_Date") and InScope("matrix1_Time"),
"{0,6:N0}",
"{0,6:N1}")

Go ahead and preview the new results of your report.

After a few aesthetic changes, you can find an example data output of my finished report here:
Download – Session Count by Date and Hour – Please note that this file is set to a .doc and needs to be renamed to a .rdl in order to be uploaded into EdgeSight.

***Update 8/19/11 – I did some playing around with this report and query and have noticed that I only applied the timezone offset to the time and not the date structure.  If a user runs the report on GMT-5:00, then instead of Friday 7:00PM showing up as the time, it will show Saturday 7:00PM.  I am correcting the query as soon as I can, and will update this post and the report once it is corrected.

****Update 8/20/11 – I fixed the query and the report.  The new version of the report converts time correctly.  You can download it here

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