Chia sẻ qua

How to use Excel to analyse IIS Logs

  • Bài viết

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

Comments

  • Anonymous
    January 21, 2016
    Wonderful!this has helped me a lotttt... :)

  • Anonymous
    July 13, 2016
    Nice tutorial to illustrate a simple way to approach the problem. Thanks,

    • Anonymous
      August 25, 2016
      Happy the post was useful to you.

  • Anonymous
    July 14, 2016
    hopefully this is automated using macros.

  • Anonymous
    February 02, 2017
    Very helpful. Did not know about this Excel feature at all. Helped to analyze logs in 5 minutes. Thanks!

    • Anonymous
      May 04, 2017
      Happy to be of service :)

  • Anonymous
    February 10, 2017
    Nice. You can use powershell to filter out lines starting with '#':$lines = Get-Content -Path path_to_log_file | ? { $_ -notlike '#*' }$lines > temp.logAnd copy the content of temp.log to Excel.

    • Anonymous
      May 04, 2017
      Good catch. You can certainly do that.

  • Anonymous
    April 05, 2017
    The comment has been removed

    • Anonymous
      May 04, 2017
      This should make a nice topic for a future article. Thank you for the suggestion.