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:

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

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