Importer ses logs Apache dans Excel

importer log excel apache

Cet article est la suite du tutoriel expliquant comment récupérer les logs d’un mutualisé OVH. Je vous conseille de le lire pour comprendre la suite.

Si vous voulez analyser finement ce qu’il se passe sur votre site web, il n’y a pas pas 36 solutions : il faut aller fouiller dans les logs. C’est là que l’on trouve l’intégralité de l’information, mais évidemment brute de décoffrage. Et c’est bien cela qui pose problème. Il n’y a rien de plus imbuvable que les logs d’un serveur web… Si on veut analyser les données, pas d’autre choix que d’importer les logs Apache dans Excel.
Je mentionne Apache et Excel, mais cela fonctionne tout aussi bien pour Nginx et LibreOffice par exemple.

Importer des données dans Excel ? Facile !
Facile, oui, mais quand on a un fichier à importer… Pas 30… Et surtout, quand le fichier source est formaté et délimité. Comme un fichier CSV par exemple.
Évidemment, les logs Apache ne sont pas formatés par défaut. Pour vous en rendre compte, prenez l’un de vos fichier et importez-le directement dans Excel, sans plus de traitement. Vous allez vite comprendre 🙂

Sur la base du script de l’article précédent, je vais ajouter des instructions qui vont préparer et formater les logs pour les rendre facilement importables par Excel. Mais pas que ! Quitte à manipuler les données, on va en profiter pour se simplifier la vie encore un peu plus.

A l’arrivée le script va générer :

  • Un fichier de log unique de l’ensemble
  • Un fichier spécifique au crawl de Google
  • Un fichier spécifique pour les erreurs 404
  • Un fichier spécifique pour les redirections 301

Pourquoi autant de fichiers ?
Analyser un mois de log entier peut être très lourd et demander beaucoup de ressources machine. Au point même qu’Excel ne soit plus suffisant.
A l’usage, je me suis rendu compte que le plus souvent je regardais 3 choses : ce que Google venait consulter sur mon site, qu’elles étaient les erreurs 404 pour pouvoir les corriger, et enfin, le suivi de mes redirections 301.
Donc si j’isole ces 3 cas dans des fichiers distincts, ils seront beaucoup plus léger et facile à traiter. Et dans tous les cas on a toujours le fichier complet si on veut pousser plus loin l’analyse.

Remarque importante
Je vous propose un script bash qui fonctionne sans problème sur une machine Linux, sur une machine Windows via Cygwin, mais pose problème sur les machines sous Mac OS X à cause de l’utilisation de la commande SED.
Sur les Mac, SED n’est pas implémenté correctement et les commandes qui vont s’exécuter vont retourner des erreurs.
Pas de problème, il suffit d’installer la version de GNU de SED disponible via Homebrew. Cela entrainera une petite modification du script.

Le script

A la fin du premier tuto, on se retrouvait avec un dossier contenant X fichiers de logs. Comme ceci :

logs mutu téléchargés

On reprend le script pour maintenant traiter ces fichiers.

#!/bin/bash

# Usage :	./script-name.sh domaine mm-aaaa
# ex : 		./script-name.sh memoinfo.fr 12-2014

# votre domaine, sera passé en paramètre de la commande
SITE=$1
# période, sera passée en paramètre de la commande
PERIODE=$2
# login de votre compte OVH
LOGIN=votre-login-ovh
# mot de passe de votre compte OVH
PASS=votre-mot-de-passe
# Répertoire de destination. Par défaut sur le bureau (mac). Changez pour ce que vous voulez
DOSSIER=$HOME/Desktop/$SITE/$PERIODE
# En-tête qui sera ajouté à chaque fichier
ENTETE='"HOST" "SITE" "REMOTE-USER" "DATE" "FUSEAU" "ADRESSE" "STATUT" "POIDS" "REFERER" "USER-AGENT"'

# Créé le dossier qui va récupérer les logs. Etape obligatoire sinon wget retourne une erreur concernant ses logs
echo 'Création du dossier'
mkdir -p $DOSSIER

# Téléchargement des logs via wget avec les options suivantes :
# -nv : logs non verbeux
# -nd : Ne pas créer la hiérarchie distante en local
# -r  : Active le téléchargement récursif
# -A  : Spécifie les extensions de fichiers à télécharger. Séparé par des virgues si plusieurs
# -P  : Répertoire racine de référence
# -o  : fichier de log de wget
echo 'Téléchargement des logs...'
wget -nv -nd -r -A.gz -P$DOSSIER https://logs.ovh.net/$SITE/logs-$PERIODE/ --http-user=$LOGIN --http-password=$PASS -o $DOSSIER/logs.wget.txt

# Création d'un fichier spécifique au crawl Google.
# -h : Supprime le préfixage des noms de fichier sur la sortie
echo 'Création des logs spécifiques Google...'
zgrep -h '66.249'.*'Googlebot/2.1' $DOSSIER/*.gz > $DOSSIER/logs-google.txt

# Création d'un fichier spécifique pour les erreurs 404
echo 'Création des logs spécifiques 404...'
zgrep -h ' 404 ' $DOSSIER/*.gz > $DOSSIER/logs-404.txt

# Création d'un fichier spécifique pour les redirections 301
echo 'Création des logs spécifiques 301...'
zgrep -h ' 301 ' $DOSSIER/*.gz > $DOSSIER/logs-301.txt

# Création du fichier avec l'ensemble des logs
zcat $DOSSIER/*.log.gz > $DOSSIER/logs-complets.txt

# Ajoute les en-têtes à chaque fichier de log pour le tableur
echo 'Ajout des en-têtes...'
sed -i "1i$ENTETE" $DOSSIER/logs-*.txt

# Supprime les crochets inutiles sur les champs date
echo 'Amélioration...'
sed -i 's/\[//g' $DOSSIER/logs-*.txt
sed -i 's/\]//g' $DOSSIER/logs-*.txt

echo 'terminé'

Quelques explications.
Jusqu’à la partie téléchargement avec Wget, vous avez les explications dans mon précédent tuto.
Après le téléchargement le script créé chaque fichier spécifique avec ZGREP qui va lire les fichiers contenus dans les archives .gz. Et pour chaque fichier on va lire une caractéristique spécifique qui va isoler les lignes à copier.
Ensuite il y a le fichier global. Lui c’est le plus simple, il ne suffit que de le lire, donc on utilise ZCAT, qui comme ZGREP va lire le contenu d’un .gz. Mais cette fois-ci, sans filtre.
Ensuite, on ajoute les en-têtes de colonnes pour Excel, car elles ne sont pas dans les logs originaux. On évite ainsi de devoir les recopier à chaque fois… Ces en-têtes permettront de faire des filtres dans Excel.
Et enfin, on va faire un peu de ménage dans les fichiers et supprimer les crochets qui entourent les champs dates et qui ne servent à rien.
Pour les deux dernières manipulation on utilise sed qui permet de manipuler le contenu des fichiers.

Pour les utilisateurs Mac
Comme je le disait plus haut, sed ne va pas fonctionner sans la version GNU de Homebrew.
Installez GNU-SED et il ne vous reste plus qu’à modifier les appels. En fait vous n’avez qu’à modifier 3 lignes… pour transformer sed en gsed. C’est tout !

gsed -i "1i$ENTETE" $DOSSIER/logs-*.txt
gsed -i 's/\[//g' $DOSSIER/logs-*.txt
gsed -i 's/\]//g' $DOSSIER/logs-*.txt

Utilisation

Pour exécuter le script je vous renvoi à mon précédent tuto puisqu’il fonctionne de la même manière.
Pour la faire courte :

./script-name.sh domaine mm-aaaa

Si tout s’est bien passé, vous devez obtenir ceci et vos fichiers sont prêts à être importés dans Excel :

script import logs apache

Import Excel

Dernière étape, on importe enfin nos logs dans Excel.

Allez dans l’onglet « Données », puis le groupe « Données externes » et choisissez l’option « A partir du texte », et sélectionnez un fichier :

import log excel

Une nouvelle boite de dialogue s’ouvre et choisissez les options « délimité » avec l’encodage « UTF8 » et cliquez sur « suivant » :

import log excel 2

Dans cette fenêtre activez les options comme ci-dessous, séparateur « espace » et identificateur de texte « guillemet ». Cliquez sur « suivant » :

import log excel 3

Laissez les options telles quelles sur cette fenêtre et cliquez sur « terminé » :

import log excel 4

Une dernière fenêtre vous demande à partir de quelle cellule copier les données. Si ce n’est pas déjà le cas, cliquez sur votre cellule « A1 », et cliquez sur « ok » :

import log excel 5

Laissez tourner. Cela peut être plus ou moins long en fonction du poids du fichier, et hop ! c’est fait !

import-log-excel-6

Et voilà, on y est ! Ne vous reste plus qu’à placer un filtre ou faire un tableau croisé dynamique et c’est partie pour l’analyse.

J’espère que ce script vous sera utile.
N’hésitez pas à proposer des améliorations.

Vous avez aimé cet article ? Réagissez !

Votre email ne sera pas publié. Les champs requis sont marqués d'une astérisque *