Internet Information (IIS) / Log Parser – Queries – String Pattern Matching

Background

Looking for File I/O Exceptions in the Event Viewer.

 

Query

Sample

Sample 001

Code


SELECT TOP 100 
 
         TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Mesg
       , Strings as Strings
       , EXTRACT_TOKEN(Strings,1,'|') AS AppName
       , EXTRACT_TOKEN(Strings,2,'|') AS AppVersion
       , EXTRACT_TOKEN(Strings,3,'|') AS S3
       , EXTRACT_TOKEN(Strings,4,'|') AS Module
       , INDEX_OF(Message, 'System.IO.IOException') as indexOf
       , case INDEX_OF(Message, 'System.IO.IOException') 
            when 0 then 'N'
            when NULL then 'N'
            else 'Y'
         end as IOE
       , CASE strcnt(Message, 'System.IO.IOException')
             when 0 then 'No'
             else 'Yes'   
         end as IOException
 
from  '[LOGFILEPATH]'
 
WHERE ( EventType = 1 OR EventType = 2 )

and    INDEX_OF(Message, 'System.IO.IOException') > 0

 
ORDER BY
         TimeGenerated DESC


Output

 

Explanation

  1. INDEX_OF
    • We use INDEX_OF to find the position of the sought string in the Message column
      • When the column contains System.IO.IOException the query returns the starting position of the found pattern
      • When not found, null is returned
  2. STRCNT
    • We invoke STRCNT to count number of matches
      • When String not found, 0 return
      • When matched, number of matches

 

References

  1. StackOverflow
    • Log Parser Case Statement
      Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s