Discussion :

[Gannox]

Bonjour,

Je me suis récemment vu confier un projet informatique dans lequel il est spécifié que je dois établir des statistiques sur l'exploitation d'un portail captif (chillispot). On m'a fait savoir qu'il faudrait que je puisse compter des logs, etc...

Pour ce faire, on utilise des requêtes SQL. Je voulais juste savoir si quelqu'un pouvait me dire comment compter les logs d'un serveur Web.

Merci d'avance.

[CinePhil]

Si 1 log = une ligne dans une table de la BDD, un simple COUNT(*) suffit !

[Gannox]

C'est exactement en tout points ce que j'ai pensé au départ. Le souci est que dans ce projet, je dois en gros me servir à 50% de requêtes SQL et à 50% de réseau.

Je vais tenter d'être plus clair :

J'ai une base de données sous la main (visible dans tout ses recoins par phpmyadmin) avec comme champs les id de paquets envoyés, timestamp ("temps de connexion"), et une vingtaine d'autres champs mais ils sont tous enregistrés comme NULL.

Je dois fournir à mes supérieurs :

- le nombre total de connexions au chillispot depuis une date donnée
- le nombre total de connexions différentes
- le total de la bande passante consommée par le chillispot depuis une date donnée
- le nombre de créations de comptes depuis une date donnée
- etc...

Je dois fournir tout ça sur l'exploitation d'une base de données de plus de 8 000 000 d'enregistrements avec les trois quarts des valeurs à NULL. Je voudrais seulement avoir la certitude qu'il est juste nécessaire d'avoir la base de données sous la main ET DE NE PAS recourir à une action liée au réseau pour trouver ces résultats.

Je sais je suis difficile mais bon.

[CinePhil]

Si tous les logs s'enregistrent en BDD, tu peux interroger la BDD.
Si une partie des stats est à faire sur des données non présentes en BDD, il va falloir trouver une méthode hors SGBD donc hors SQL pour interroger les logs absents.
Mais s'il y a moyen d'importer en BDD les logs absents, revenir au premier Si !

[Gannox]

Je viens de me renseigner avec mon tuteur de stage et c'est bien ce que je pensais : c'est plus compliqué qu'un simple COUNT(*) !

En effet, si un utilisateur se connecte et fait 50 recherches en 2 heures (admettons), il n'y aura eu qu'une connexion au chillispot. Et c'est cette connexion que l'on veut et pas les 50 recherches.

Du coup je suis un peu bouche bée et je me demande comment procéder.

[CinePhil]

Donne la structure des tables concernées avec explication éventuelle et petit jeu de données ainsi que le résultat attendu.

Il y a différentes manières de compter :
- COUNT(*) compte les lignes ;
- COUNT(DISTINCT une_colonne) compte les valeurs différentes d'une colonne ;
- SUM( CASE CASE WHEN une_colonne = une valeur THEN 1 ELSE 0 END) compte les lignes pour une valeur d'une colonne ;
- COUNT associé à GROUP BY compte les lignes pour chaque valeur de la colonne
- COUNT associé à WHERE compte les lignes qui répondent à la condition donnée dans le WHERE...

Pour plus d'explication sur les possibilités de calcul sur des groupages, voir le cours de SQLPro.

[ericd69]

salut,

tant que tu en es à la réflexion encore, il serait bon de penser à la gestion générale de ton log:

• suppression automatique ou non des entrées ayant une certaine ancienneté pour réguler la taille de la(des) table(s)
• utilisation de partition avec/ou à la place d'index
• utilisation du moteur archive au lieu de myisam ou innodb
• etc...

car il est bon d'y penser avant plutôt que de pleurer à modifier

[CinePhil]

J'ajoute :
- modélisation de la BDD pour supprimer les colonnes NULLables.

[Gannox]

Voici quelques renseignements sur la table qui, selon moi, me permettrait de trouver ces informations :

TABLE radacct :

- radacctid
- acctsessionid
- acctuniqueid
- username
- groupname
- nasipadress
- acctstarttime
- acctstoptime
- acctsessiontime
- etc...

Il manque encore une dizaine de champs dans cette table que je représente ici par "etc" car ils sont tous à NULL. J'ai 4 bases de données avec une multitude d'enregistrements et "radius" contient la table radacct.

Je pensais utiliser cette table pour avoir le nombre de connexions et le nombre de connexions différentes pour commencer.

[CinePhil]

Et bien prends les besoins un par un et, à l'aide du cours de SQLPro et des indications que je t'ai données dans une de mes réponses précédentes, commence à faire les requêtes, teste-les et, si ça ne donne pas ce que tu veux, propose-les nous avec la description du problème rencontré.

[Gannox]

J'ai cependant un peu de mal à percevoir la différence entre "le nombre total de connexions au chillispot" et "le nombre total de connexions différentes au chillispot". Mes requêtes fonctionnent mais je ne sais pas si c'est le résultat attendu et mon tuteur ne peut pas m'aider car il ne connaît pas toute la disposition de la base de données de l'entreprise.

De plus, quel(s) paramètre(s) faut-il utiliser pour calculer "la bande passante consommée par le chillispot" pour un mois/une année/le total de la consommation depuis une date donnée...

Mon problème n'est pas vraiment lié aux requêtes SQL mais plutôt à la base de données elle-même.

Par exemple : j'ai fait SELECT COUNT(DISTINCT radacctid) FROM radacct; pour avoir le nombre de connexions mais j'ai l'impression que c'est trop simple et j'essaie de faire une recherche plus poussée pour voir si c'est pas plus subtil que ça.

[CinePhil]

Si radacctid est la clé primaire de la table radacct, inutile de faire un COUNT(DISTINCT ), un simple COUNT est suffisant.

Je dois fournir à mes supérieurs :

- le nombre total de connexions au chillispot depuis une date donnée
- le nombre total de connexions différentes
- le total de la bande passante consommée par le chillispot depuis une date donnée
- le nombre de créations de comptes depuis une date donnée
- etc...

TABLE radacct :

- radacctid
- acctsessionid
- acctuniqueid
- username
- groupname
- nasipadress
- acctstarttime
- acctstoptime
- acctsessiontime
- etc...

Qu'est-ce qui, dans la table, représente une connexion ? La bande passante consommée ? La création d'un compte ?

As-tu toutes les infos nécessaires au besoin exprimé dans cette seule table ?

[Gannox]

As-tu toutes les infos nécessaires au besoin exprimé dans cette seule table ?

C'est justement moi qui doit déterminer la réponse à cette question, mon tuteur n'ayant pas la réponse sur lui. Je dirais que la plupart des champs importants sont dans cette table mais il doit surement manquer des choses.

J'ai également une table contenant un champ nommé ulog répertoriant toutes les requêtes exécutées via un navigateur au sein de l'entreprise. Je ne pense pas cependant que ce soit utile pour répondre aux attentes précédentes.

[Gannox]

J'en sais un peu plus sur l'histoire de la bande passante consommée. Il s'avère que tout ce joli capharnaüm est accompagné de Munin qui est un outil de surveillance système et réseau. J'ai accédé à ce logiciel et j'ai découvert plusieurs graphiques avec une dizaine de paramètres pour chacun

Seulement, lequel est celui qui s'occupe de la bande passante et comment récupérer les valeurs ? Si quelqu'un s'y connaît, qu'il m'en fasse part le plus vite possible

[CinePhil]

C'est plutôt sur un forum dédié à ce domaine qu'il faudrait poser la question !