SELECT sur un intervalle de minutes

05 déc. 2008, 12:00

Bonjour,

je cherche à trouver sur des fichiers de logs les adresses IP qui demandent plus de 40 pages par minutes. J'utilise MySQL.
Ma table est la suivante :

Code : Tout sélectionner

CREATE TABLE IF NOT EXISTS `logs` (
  `id_logs` int(11) NOT NULL auto_increment,
  `date` datetime default NULL,
  `ip` varchar(50) default NULL,
  `uri` varchar(250) default NULL,
  PRIMARY KEY  (`id_logs`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

Pour l'instant voici ma requête :

Code : Tout sélectionner

SELECT COUNT( ip ) AS nbre, ip, date
FROM `logs` 
WHERE MINUTE( `date` ) = '10'
GROUP BY ip
HAVING nbre > 40
ORDER BY nbre DESC

La requête ci-dessus selectionne les ip qui demandent plus de 40 pages pour la minute "10" quelque soit l'heure, la seconde ou le jour. Comment modifier ma requête pour que ceci n'importe quelle intervalle de 1 minute ?

Merci à tous de votre aide.

05 déc. 2008, 12:04

Essaye d'utiliser un GROUP BY.

05 déc. 2008, 18:53

????
J'utilise déjà le Group by !

[Note : ce message a été posté de manière anonyme avant d'être réattribué à son auteur]

05 déc. 2008, 19:38

Désolé, j'ai pas compris ta question, veux-tu reformuler ? Parce que : "comprendre la question" fait déjà 90% de la réalisation d'une requête.

08 déc. 2008, 10:44

Merci de ton aide Sadeq...

En fait, à la base, je souhaite selectionner dans ma table 'logs' les adresses IP qui "floodent" mon serveur avec plus de 40 requêtes par minute quelque soient, bien sûr, le jour et l'heure...

Je ne sais pas si je me fais très bien comprendre...

J'ai vu qu'il existait une instruction INTERVAL en SQL mais je ne sais pas si elle peut m'être utile dans ce cas là.

08 déc. 2008, 11:13

Alors, le principe de base est le suivant :
"Pour chaque minute et pour chaque IP, je veux le nombre de requête présente dans ma table."

Le principe est donc de grouper par date (jusqu'a la minute) et par IP, puis de compter le nombre de ligne.

Le début de ta requête est donc semblable à ceci :

Code : Tout sélectionner

SELECT COUNT(ip) as nb_occurence
FROM logs 
GROUP BY
  DATE_FORMAT(`date`, '%Y-%m-%d %h:%i') AS minute, 
  ip

Ce 1er jet nous permet d'avoir le nombre de requêtes par minute et par IP.
Maintenant, il nous faut récupérer que les IP qui font plus de X requêtes par minutes

Code : Tout sélectionner

SELECT ip, `date`, COUNT(ip) as nb_occurence
FROM logs 
GROUP BY
  DATE_FORMAT(`date`, '%Y-%m-%d %h:%i') AS minute, 
  ip
HAVING COUNT(ip) > X
ORDER BY COUNT(ip) DESC, `date` DESC

Et cette requête te donnera les Ip qui dépasse X requêtes par minutes, triées par les requêtes qui font le plus de connexions, puis par date la plus récente.

08 déc. 2008, 16:35

Merci de tes infos...

J'ai testé ta requête mais elle ne fonctionne pas

La 1re raison que j'ai identifiée est l'utilisation de l'alias minute qui est un terme réservé dans MySQL : je l'ai donc remplacé par la_minute.

Ensuite voici le message d'erreur que j'obtiens :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS la_minute, ip
HAVING COUNT(ip) > 20
ORDER BY COUNT(ip) DESC, `date` DESC' at line 4

Je vais exporter quelques logs en local sur ma version plus récente de MySQL et voir si ça fontionne mieux...

08 déc. 2008, 16:57

normal, on a pas le droit d'utiliser un alias dans les opérations de traitement (WHERE, GROUP BY, ...)

08 déc. 2008, 17:24

D'après ce que je comprends du nouveau message d'erreur il est impossible d'executer l'instruction DATE_FORMAT sur un GROUP BY

MySQL a répondu:

#1111 - Invalid use of group function

Il faut peut être que je cherche du coté des requêtes imbriquées...

08 déc. 2008, 18:17

Non, c'est encore de ma faute.
Je t'explique, une fois que tu as groupé des champs, SQL ne te permet l'accès qu'au champ groupé, ou alors au opérations sur les autres champs (SUM, COUNT).

Dans ma requête, je fait un DATE_FORMAT sur le champ `date`, puis je tente de l'utiliser seul. Il faut que le SELECT contienne les mêmes infos que le GROUP BY, soit le même DATE_FORMAT

Code : Tout sélectionner

SELECT ip, DATE_FORMAT(`date`, '%Y-%m-%d %h:%i') AS date_requete, COUNT(ip) as nb_occurence 
FROM logs 
GROUP BY 
  date_requete, 
  ip 
HAVING COUNT(ip) > X 
ORDER BY COUNT(ip) DESC, date_requete DESC

PS : ça m'apprendre à prendre 2mn pour tester une requête que je propose

09 déc. 2008, 10:29

Merci, merci, merci !!!! Ca marche !!

Il faut cependant écrire :

Code : Tout sélectionner

SELECT ip, adrip, DATE_FORMAT(`date`, '%Y-%m-%d %h:%i') AS date_requete, COUNT(ip) as nb_occurence 
FROM logs 
GROUP BY DATE_FORMAT(`date`, '%Y-%m-%d %h:%i'), `ip` 
HAVING COUNT(ip) > 20 
ORDER BY nb_occurence DESC

SELECT sur un intervalle de minutes

Qui est en ligne