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

Comments (2)

  1. Anonymous says:

    Wow! wonder how the folks managing microsoft.com uses log parser? They have been so kind, after first…