The Forgotten “Log Parser” & Sitecore Logs

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.

Log Parser

Introduction

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

  1. 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%'"

.05 seconds

The result file looks like:

COUNT(ALL *)

------------

1702       

  1. 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%'"

3.56 Sec

From the screenshot, you can see how fast the logparser was, it was able to handle 720311 elements in 3.5 seconds!

  1. 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:

date,cat,info,page,comment

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

  1. 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!


About Author

NaimAl
Naim holds a Bachelor Degree in Information Technology and an MBA in Finance. He started his career in programming in 2001 building desktop financial applications. In 2004 he shifted his focus to develop websites. In 2016 Naim joined Americaneagle.com as a Senior Sitecore Developer. In 2020 Naim won the 1st place in Sitecore Hackathon 2020 competition with American Eagle XTeam Winner, over 82 teams from more than 23 countries were participating. When he is not coding, he likes to spend time with his wife and two kids, watching TV, or relax in his backyard.


Featured Posts