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!
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.
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)
|machine_name||Time||Disk Time||Processor Time||Committed memory||Context Switches||Disk Queue Length|
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|
|SPickens||Outlook.exe||Microsoft Office Outlook||0.05320751||79||14|
|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|
|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.
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.
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