What is Logparser?
logparser is a flexible command line utility that was first included with the IIS 6.0 Resource Kit Tools around 2002). The default behavior of logparser works like a "data processing pipeline" by taking an SQL expression on the command line and outputting the lines containing matches for the SQL expression.
Microsoft describes Logparser as a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files.
There are several applications and tools that are used to parse all type of logs but some are paid and others are not flexible enough to perform the required queries.
A very common request is how can we track the occurrence of 404s, 500s and other custom error tracking within Sitecore.
A specific occurrence of this recently when a Sitecore client’s developers had several errors, issues, and many custom logs. Using the common log analyzer tools wouldn’t yield the answers we were looking for. Traveling back in time a bit, we found a blast from the past! Utilizing the logparser enables us to extract much more information and detail quickly.
I will demonstrate one case where the client had a custom redirect module that kept generating custom logs with a flag file not found and will show the basic commands I used to get my answers.
9420 03:02:00 INFO RedirectManager: Redirect for the page: "/redirect.asp" was not found
- As a start I wanted to know, how many errors we have in one specific log and in all the provided logs (to measure the severity of the problem).
Remember you can use SQL like expressions to filter and get the results, for example the below command is used to find how many errors we have in the log file “log.20180824.txt” that has a text contain the phrase “not found”
LogParser.exe "SELECT count(*) from e:\logs\log.20180824.txt to merged.log where Text like '%not found%'"
The result file looks like:
- Next I wanted to merge all the logs in one csv file, so I can manipulate it and query it easily, I only wanted the log.x.txt files, I was not worried about the exported file size as I was just querying one specific case.
LogParser.exe "SELECT * from e:\logs\log.*.txt to e:\logs\merged.csv where Text like '%not found%'"
From the screenshot, you can see how fast the logparser was, it was able to handle 720311 elements in 3.5 seconds!
- I did a quick clean up to the file merge.csv, added column title, and removing some and ended up with a file with the following structure:
4552 03:01:17 ,RedirectManager,: Redirect for the page:, '/events' ,was not found
11276 03:01:17 ,RedirectManager,: Redirect for the page:, '/redirect.asp' ,was not found
8908 03:01:17 ,RedirectManager,: Redirect for the page:, '/login.asp' ,was not found
- Now I was able to do more querying, grouping, etc. For example, the below command I used to get a list of the pages descended by its hits frequency in the logs.
LogParser.exe "SELECT count(*) as hits,page from e:\logs\merged.csv to e:\logs\merged-count.csv group by page order by hits desc"
Powerful, simple and fast!