Microsoft's free command-line LogParser utility has many uses. I find it particularly helpful when I want to comb through IIS logs or Windows event logs for troubleshooting purposes. The SQL-based syntax isn't always easy to master. Whenever I have a helpful LogParser example to share, I'll add it to this post.
Today's example shows how to search the IIS logs. Here's the command line, followed by an explanation and some tips:
G:\Log Parser 2.2>logparser "select date, time, cs-uri-stem, sc-status from g:\logs\www.2l23l.com\W3SVC954531014\ex101110.log where cs-uri-stem like '/hungryboiler%' and time >= '02:00:00' and time < '03:00:00' and cs(User-Agent) like '%Gecko%' and sc-status >= 400"
The fields I wanted to output were date, time, URL (cs-uri-stem) and status (sc-status). Tip: to get a list of all available fields in a log file, use select top 1 * from...
I figured out which log file to search by looking at my website's properties in IIS Manager. In IIS6, on the Web Site tab, under the Logging section, click the Properties button. Then you just need to pick the desired log file according to date. Don't forget: by default, IIS logs use GMT, so something that occurred in the evening Eastern Time will be in the following day's log.
I knew the error occurred on a page whose name started with "hungryboiler", so I searched for cs-uri-stem like '/hungryboiler%'.
I knew a user experienced a "page not found" error, so I searched for sc-status >= 400.
I knew the error occurred between 2:00 and 3:00 AM GMT (10:00 to 11:00 PM the previous day EST), so I search for time >= '02:00:00' and time <= '03:00:00'.
One final tip for LogParser newbies like myself: The query itself goes inside double quotes. Any strings within the query go inside single quotes.
Here's a handy LogParser example. I wanted to find how often a particular error message, "Unexpected error ocurred in the database," had appeared in the Windows Application event log recently. This LogParser query did the trick:
ReplyDeletelogparser "select TimeWritten from Application where Message like '%Unexpected error ocurred in the database%'"
Here's a LogParser example showing:
ReplyDelete(a) How to write the output to a file, in thise case a CSV file named out.txt, and
(b) How to parse the "strings" value obtained from the Application event log. This value is a long, pipe-delimited string full of useful information. In this case, I wanted the client's IP address, which turns out to be the 21st element of that pipe-delimited value. EXTRACT_TOKEN does the trick. A tip of the hat to yellowdog.dave at http://forums.iis.net/t/1150717.aspx for that!
logparser -o:CSV "select TimeWritten, EXTRACT_TOKEN(strings,21,'|') into out.txt from Application where strings like '%restaurantserviceviewfax%' order by TimeWritten desc"