How to use Excel to analyse IIS Logs

  • Article

Many times customers are asking us how we analyse IIS logs. We usually use two different approaches.
The first one is to use LogParser to parse the IIS logs using a programmation language similar to SQL. This one is not the easiest ways as you need to develop your own queries or find existing ones on Internet.
I'll surely come back to develop this part later in another blog post.

The other way is to use Excel. It's the easiest and quickest way to analyse IIS logs even when you don't know anything about working with Excel.

The first step is to format the IIS Log file to be able to parse it in Excel. Effectively, some entries can prevent a correct parsing of the IIS log file, which could lead to an incomplete analysis.
In the example below, you can see many entries starting with a # character. It's a header added by IIS.

#Software: Microsoft Internet Information Services 8.0
#Version: 1.0
#Date: 2013-09-11 14:00:39
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2013-09-11 14:00:39 ::1 GET / - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 2 5 151
2013-09-11 14:00:39 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 4
2013-09-11 14:00:39 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 4
2013-09-11 14:00:39 ::1 GET /favicon.ico - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 404 0 2 1
#Software: Microsoft Internet Information Services 8.0
#Version: 1.0
#Date: 2013-09-11 14:03:29
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2013-09-11 14:03:29 ::1 GET /favicon.ico - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 1 2148074254 77
2013-09-11 14:03:31 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 2
2013-09-11 14:03:31 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
#Software: Microsoft Internet Information Services 8.0
#Version: 1.0
#Date: 2013-09-11 14:05:06
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2013-09-11 14:05:06 ::1 GET / - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 2 5 75
2013-09-11 14:05:06 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 2
2013-09-11 14:05:06 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 2

These entries have to be removed and only kept for the first field description like below:

date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2013-09-11 14:00:39 ::1 GET / - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 2 5 151
2013-09-11 14:00:39 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 4
2013-09-11 14:00:39 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 3
2013-09-11 14:00:39 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 4
2013-09-11 14:00:39 ::1 GET /favicon.ico - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 404 0 2 1
2013-09-11 14:03:29 ::1 GET /favicon.ico - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 1 2148074254 77
2013-09-11 14:03:31 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 2
2013-09-11 14:03:31 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:03:31 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET / - 80 - ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 401 2 5 75
2013-09-11 14:05:06 ::1 GET / - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) - 200 0 0 2
2013-09-11 14:05:06 ::1 GET /iis-8.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /msweb-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /w-brand.png - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 1
2013-09-11 14:05:06 ::1 GET /bkg-blu.jpg - 80 Domain\User ::1 Mozilla/5.0+(compatible;+MSIE+10.0;+Windows+NT+6.2;+WOW64;+Trident/6.0) https://localhost/ 200 0 0 2

I usually perform this steps using notepad:
1- Open the file in notepad
2- Remove the first entry from #Software to #Fields: in order to only keep the field definitions at the beginning of the file
3- Search for #
4- Remove any entries you'll find of the following type to only get in the log file the first field definitions and a list of requests as above:

#Software: Microsoft Internet Information Services 8.0
#Version: 1.0
#Date: 2013-09-11 14:05:06
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken

 

Once you've done this, save the file and open it using Excel.
All the text is regrouped in the column A. Select the entire column, go in the "Data" tab and click on "Text to Columns".
In the window "Text Import Wizard – Step 1 of 3", select "Delimited" and click on "Next > ":

 

In the next window, select "Space" and click on "Finish":

 

Your IIS log is now fully formatted for Excel and you can use the Filter option to filter the log as you wish:

In addition, if you are skilled in Excel usage, you can now generate graphs and stats for your IIS logs.

I hope this article will be useful.
Sylvain Lecerf and the French Microsoft Support Team