Utiliser Excel pour analyser des logs IIS

Au support, une question revient régulièrement : comment faites-vous pour analyser les logs IIS ? Généralement, nous utilisons deux méthodes différentes.

La première est basée sur l'utilisation de LogParser qui permet d'analyser les logs IIS avec un langage d'interrogation similaire à SQL. Cette méthode n'est pas la plus simple car il faut développer ses propres requêtes (ou en trouver des déjà faites sur internet) et les adapter en fonction des besoins de l'analyse. Je reviendrai certainement sur ce point dans un autre article de blog.

L'autre méthode qui est beaucoup plus simple et plus rapide est l'utilisation d'Excel, même si vous ne connaissez rien à Excel.

Pour ce faire, la première étape consiste à formater le log IIS. En effet, s'il n'est pas correctement formaté, il est possible que certaines entrées ne soient pas prises en compte par Excel, engendrant ainsi une analyse incomplète. Dans l'exemple ci-dessous, vous pouvez voir de nombreuses entrées commençant par un # . Ce sont des headers ajoutés par 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

Il faut supprimer ces entrées pour garder uniquement la première description des champs :

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

Il suffit d'utiliser Notepad pour réaliser cette étape :

1- Ouvrez le fichier dans Notepad
2- Supprimez la première entrée de #Software à #Fields: afin de ne garder que la définition des champs au début du fichier
3- Recherchez le caractère #
4- Supprimez toutes les entrées du type suivant que vous trouverez afin de n'avoir dans le fichier que la première définition des champs ainsi que la liste des requêtes sans saut de ligne comme montré ci-dessus :

#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

Une fois que cette opération effectuée, sauvez le fichier et ouvrez-le avec Excel. L'ensemble du texte est regroupé dans la colonne A. Sélectionnez cette colonne, allez dans l'onglet "DONNEES" et cliquez sur "Convertir". Dans la fenêtre "Assistant Conversion – Etape 1 sur 3", sélectionnez "Délimité " et cliquez sur "Suivant > " :

Dans la fenêtre suivante, sélectionnez "Espace" et cliquez sur "Terminer" :

Votre log IIS est maintenant entièrement formaté pour Excel et vous pouvez utiliser l'option de filtre afin de filtrer le log comme vous le souhaitez :

De plus, pour ceux qui savent utiliser Excel de manière un peu plus approfondie, vous pouvez maintenant générer des graphiques et des statistiques pour vos logs IIS.

J'espère que cet article vous aura été utile.
Sylvain Lecerf et l'équipe de Support Microsoft France