MSCOM OPS March Debug Madness…1st Session Webcast Commands and Scripts
Microsoft.com Operations: Real World Debugging - Determining When You Have A Problem and Initial Debugging
As promised here are the commands, scripts and the Console Debugger log file from Monday March 27th MSCOM Ops webcast
Here is the link available to download from the Microsoft Download Center. This download is a winzip file that contains five files:
1. Log_parser_commands.txt This contains all of the Log parser commands used in the webcast.
2. Webcast_1_Cdb_log.log This is the console debugger log file that captures what wad presented in the CDB demo.
3. The next three files are Log Parser query files, the syntax is:
cmd>logparser file:\\<path for where you have stored this file>\<filename.sql>
The files are:
W3WPUptime_default_view.sql
W3WPUpTime_Pivot_view.sql
W3WPUPtime_last_recorded.sql
All of these are simple notepad files.
For you instant gratification folks, here are the content of the three *.sql files with sample output.
Here are three examples of how to view info from an IIS Log file:
W3WPUptime_default_view.sql
This first one is default view, columns: AppPoolID, EventID, Count(*) and LastTime.
/*Default View*/
SELECT
AppPoolID,
EventID,
Count(*) as Total,
MAX(TimeGenerated) AS MaxTime
USING
CASE EventID
WHEN 1074 THEN EXTRACT_TOKEN(strings,1,'|')
ELSE EXTRACT_TOKEN(strings,0,'|')
END AS AppPoolID
FROM system
WHERE SourceName = 'W3SVC'
GROUP BY AppPoolID,EventID
Output from W3WPUptime_default_view.sql
AppPoolID EventID Total MaxTime
-------------- ------- ----- -------------------
DefaultAppPool 1039 21 2006-03-09 15:40:24
DefaultAppPool 1002 4 2006-03-09 15:40:24
DefaultAppPool 1074 1 2006-01-20 22:27:57
DefaultAppPool 1010 5 2006-03-20 16:57:48
DefaultAppPool 1082 9 2006-03-20 17:32:20
DefaultAppPool 1013 4 2006-03-20 17:32:20
DefaultAppPool 1009 3 2006-03-24 15:16:59
DefaultAppPool 1011 6 2006-03-27 13:58:05
W3WPUpTime_Pivot_view.sql
The second is pivoted view, with columns listed under the select.
/*Pivoted View*/
SELECT
AppPoolID,
SUM(_Rapid-Fail) AS Rapid-Fail,
SUM(_Terminated) AS Terminated,
SUM(_Ping-Fail) AS Ping-Fail,
SUM(_Communication-Fail) AS Communication-Fail,
SUM(_TimeLimit-ShutDown) AS TimeLimit-ShutDown,
SUM(_Reported-Fail) AS Reported-Fail,
SUM(_ProcessingTime) AS ProcessingTime,
SUM(_UnderDebugger) AS UnderDebugger
USING
CASE EventID
WHEN 1074 THEN EXTRACT_TOKEN(strings,1,'|')
ELSE EXTRACT_TOKEN(strings,0,'|')
END AS AppPoolID,
CASE EventID
WHEN 1002 THEN 1
ELSE 0
END AS _Rapid-Fail,
CASE EventID
WHEN 1009 THEN 1
ELSE 0
END AS _Terminated,
CASE EventID
WHEN 1010 THEN 1
ELSE 0
END AS _Ping-Fail,
CASE EventID
WHEN 1011 THEN 1
ELSE 0
END AS _Communication-Fail,
CASE EventID
WHEN 1013 THEN 1
ELSE 0
END AS _TimeLimit-ShutDown,
CASE EventID
WHEN 1039 THEN 1
ELSE 0
END AS _Reported-Fail,
CASE EventID
WHEN 1074 THEN 1
ELSE 0
END AS _ProcessingTime,
CASE EventID
WHEN 1082 THEN 1
ELSE 0
END AS _UnderDebugger
FROM system
WHERE SourceName = 'W3SVC'
GROUP BY AppPoolID
Output from W3WPUpTime_Pivot_view.sql
*/AppPoolID Rapid-Fail Terminated Ping-Fail Communication-Fail TimeLimit-ShutDown Reported-Fail ProcessingTime UnderDebugger
-------------- ---------- ---------- --------- ------------------ ------------------ ------------- -------------- -------------
DefaultAppPool 4 3 5 6 4 21 1 9
W3WPUPtime_last_recorded.sql
The third one replaces count(*) with max(timegenerated) – the last time it was recorded…
/*Last Time instead of Count*/
SELECT
AppPoolID,
MAX(_Rapid-Fail) AS Rapid-Fail,
MAX(_Terminated) AS Terminated,
MAX(_Ping-Fail) AS Ping-Fail,
MAX(_Communication-Fail) AS Communication-Fail,
MAX(_TimeLimit-ShutDown) AS TimeLimit-ShutDown,
MAX(_Reported-Fail) AS Reported-Fail,
MAX(_ProcessingTime) AS ProcessingTime,
MAX(_UnderDebugger) AS UnderDebugger
USING
CASE EventID
WHEN 1074 THEN EXTRACT_TOKEN(strings,1,'|')
ELSE EXTRACT_TOKEN(strings,0,'|')
END AS AppPoolID,
CASE EventID
WHEN 1002 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _Rapid-Fail,
CASE EventID
WHEN 1009 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _Terminated,
CASE EventID
WHEN 1010 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _Ping-Fail,
CASE EventID
WHEN 1011 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _Communication-Fail,
CASE EventID
WHEN 1013 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _TimeLimit-ShutDown,
CASE EventID
WHEN 1039 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _Reported-Fail,
CASE EventID
WHEN 1074 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _ProcessingTime,
CASE EventID
WHEN 1082 THEN TimeGenerated
ELSE TO_TIMESTAMP('2001-01-01 01:01:01','yyyy-MM-dd hh:mm:ss')
END AS _UnderDebugger
FROM system
WHERE SourceName = 'W3SVC'
GROUP BY AppPoolID
Output from W3WPUPtime_last_recorded.sql
AppPoolID Rapid-Fail Terminated Ping-Fail Communication-Fail TimeLimit-ShutDown Reported-Fail ProcessingTime
UnderDebugger
-------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- --------------
----- -------------------
DefaultAppPool 2006-03-09 15:40:24 2006-03-24 15:16:59 2006-03-20 16:57:48 2006-03-27 13:58:05 2006-03-20 17:32:20 2006-03-09 15:40:24 2006-01-20 22:
27:57 2006-03-20 17:32:20