Activité : Extraire des informations d’un log

Situation

Dans votre entreprise, tout le trafic HTTP passe par un serveur mandataire (proxy) et seules les personnes authentifier peuvent y accéder. Il est ainsi possible de connaître la date et l’heure de chaque accès en consultant le fichier de journalisation (log file). Toutefois, en raison de leur nombre, l’interprétation des données brutes qui s’y trouvent est mal aisée. Pour nous tenter d’y voir plus clair, on vous charge de faire des statistiques.

Consigne

Pour ce travail, on vous demande de réaliser les tâches décrites ci-après.

Objectif

À la fin de ce travail, vous devez :

  1. Être capable d’utiliser un tableau croisé dynamique.

Résultat attendu

Un compte rendu de l’activité et les réponses aux questions posées dans une section de votre rapport du module.

Ressources

Documents :

Mise en route

Dans cette mise en route, nous allons réaliser la première des tâches qui vous ont été assignées.

Commencez par télécharger le fichier proxy.log, ouvrir Excel et importez les données qu’il contient dans une nouvelle feuille de calcul. Une fois que c’est fait, n’oubliez pas d’enregistrer le classeur.

Fig. 1 – Importation des données
Fig. 1 – Importation des données

À la fin de cette opération, vous devriez avoir un tableau comme celui de la figure 2. Changer le nom de la feuille de calcul pour afficher : « Données brutes ». Avant de poursuivre, remarquez l’onglet « Requêtes et connexions » qui vous montre que ces données auraient également pu provenir d’une autre source comme le résultat d’une requête sur une base de données SQL par exemple.

Fig. 2 – Tableau de données brutes
Fig. 2 – Tableau de données brutes

La première tâche que nous avons à accomplir est la présentation de la fréquence relative (le pourcentage) du nombre de connexions pour chaque heure de la journée. Dans les données brutes, on remarque qu’on dispose de l’heure (le champ « Time »). À partir de cette valeur, on peut calculer l’heure de la journée avec la fonction heure. Insérez une colonne dans le tableau, intitulez-la « Houre », écrivez la formule = heure(E2). Si les valeurs de la colonne sont 00:00:00, sélectionnez la colonne et appliquez le format « standard ».

Fig. 3 – Ajout de la colonne « Houre »
Fig. 3 – Ajout de la colonne « Houre »

Placez la sélection n’importe où dans le tableau de données et sélectionnez « Graphique croisé dynamique » dans le ruban « Insérer » (figure 4).

Fig. 4 – Ajout d’un graphique croisé dynamique
Fig. 4 – Ajout d’un graphique croisé dynamique

Excel ouvre une nouvelle feuille de calcul avec un tableau croisé dynamique et un graphique croisé dynamique. Dans l’onglet de droit, vous pouvez voir les champs du tableau. La variable que nous voulons représenter est le champ « Houre ». Sélectionnez le champ et glissez-le dans la case « Axe (Catégorie) » (1). Glissez ensuite le champ « Time » dans la case « Valeurs » (2) et enfin, vous pouvez glisser le champ « Date » dans la case filtre (3). La dernière opération n’est pas nécessaire, mais elle permet de faire le graphique pour un jour en particulier.

Fig. 5 – Configuration du graphique croisé dynamique
Fig. 5 – Configuration du graphique croisé dynamique

Nous avons maintenant un graphique, mais celui-ci montre le nombre de connexions par heure et non pas la fréquence relative par rapport au nombre total de connexions. Pour cela, on doit modifier le calcul de la valeur en procédant comme le montre la figure 6.

Fig. 6 – Calculer la fréquence relative (pourcentage du total)
Fig. 6 – Calculer la fréquence relative (pourcentage du total)

Enfin, pour notre graphique soit un histogramme, il ne doit pas y avoir d’espace entre les barres. Utilisez les propriétés du graphique pour obtenir un résultat similaire à celui de la figure 7.

Fig. 7 – Histogramme terminé
Fig. 7 – Histogramme terminé

Lorsque cela est fait, vous pouvez poursuivre avec les tâches 2 et 3.

Tâches

  1. Présenter la fréquence relative (le pourcentage) du nombre de connexions pour chaque heure de la journée (indépendamment de la date) dans un histogramme.
  2. Présenter la fréquence relative des accès pour chaque site web dans un diagramme camembert. Vous pouvez utiliser la variable « Destination Name ».
  3. Si vous avez terminé les autres tâches, faites une statistique du nombre de connexions par type navigateur web, en utilisant éventuellement une ou plusieurs formules pour manipuler les données brutes comme vous l’avez fait pour obtenir l’heure de la journée.