Consulta MySQL

[usuario90210]

Hola a todos!!

Nose como hacer la siguiente consulta, es un poco lio pero espero que me entendais. Tengo una tabla con los siguientes campos:
IP_o, IP_d, puerto_o, puerto_d, sid, hora

Las tuplas que me interesan son todas las que tengan el sid=3. Ahora necestio saber si estan realizando escaneos, para ello quiero hacer:
seleccionar todas las IP_origen cuando las IP_destino sean distintas y el sid=3 y la hora tenga un intervalo de 5 minutos entre ellas. SI esas IP_origen aparece 4 veces o mas que me diga la IP_origen, si no que no me la diga.

Ejemplo:
IP_origen IP_destino puerto_o puerto_d sid hora
215 10 - - 3 13:00
215 11 - - 3 13:01
215 10 - - 3 13:04 (este no me interesaria xq es el mismo origen y destino que la primera fila)
200 10 - - 3 13:02
215 12 - - 3 13:03
215 14 - - 3 13:03



en este caso me tendria que dar la IP_origen 215, xq tiene diferentes IP_destinos, con el mismo sid y con un intervalo de hora de 5 minutos o menos entre ellos.


Espero que me puedan ayudar!!!

Un saludo

[snort_el_cerdete]

Hola usuario90210. Me parece una pregunta interesante. No se si algun internauta por estos lares sabrá contestarte con exactitud, pero según mis conocimientos creo que lo del intervalo de 5 minutos esta complicadete.
De todas formas, lo de las ip's destino diferentes se solucionaria con una subconsulta, algo de este estilo:

select distinct ip_origen "ipo", ip_destino
from tu_tabla
where (sid=3 and ip_destino in
(select distinct ip_destino where sid=3 and ip:origen=ipo));

Perdona si no es del todo correcta la consulta, pero mi computadora esta estropeada y no la puedo comprobar. De todas formas la idea sería esa. Espero haber podido ayudarte.

Un saludo desde el frikiespacio!

[gnzsoloyo]

El caso es complicado y requiere sentencias anidadas de varios niveles. si no quieres resolverlo por medio de un store procedure.
Primero tienes que lograr agrupar los registros de inicio y fin del cálculo, es decir, un mismo registro conteniendo el horario anterior y el siguiente de conexion, de modo que se agrupen uno a uno:

Código sql:

SELECT
    ORIGEN, DESTINO, PORIG, PDEST, SID, INI, FIN
  FROM
    (SELECT
      IP_origen ORIGEN,
      IP_destino DESTINO,
      s1.puerto_o PORIG,
      s1.puerto_d PDEST,
      sid SID,
      s1.hora INI
      s2.hora FIN
FROM  tu_tabla s1 INNER JOIN tu_tabla s2 
      USING (IP_origen, IP_destino, sid)
WHERE s1.hora<s2.hora AND s1.sid=3
ORDER BY  s1.IP_origen, s2.hora ASC) T1
GROUP BY ORIGEN, SID, FIN;
 

El agrupamiento hace desaparecer los registros no validos.

Luego hay que hacer el verdadero cálculo, es decir, que te devuelva los datos de los registros cuya diferencia de tiempos es de cinco minutos, y que además se hayan hecho al menos cuatro intentos:

Código sql:

SELECT ORIGEN, PORIG, PDEST, SID
FROM (
  SELECT
    ORIGEN, DESTINO, PORIG, PDEST, SID, INI, FIN
  FROM
    (SELECT
      IP_origen ORIGEN,
      IP_destino DESTINO,
      s1.puerto_o PORIG,
      s1.puerto_d PDEST,
      sid SID,
      s1.hora INI
      s2.hora FIN
    FROM  tu_tabla s1 INNER JOIN tu_tabla s2
      USING (IP_origen, IP_destino, sid)
    WHERE s1.hora<s2.hora AND s1.sid=3
    ORDER BY  s1.IP_origen, s2.hora ASC) T1
  GROUP BY ORIGEN, SID, FIN) T2
WHERE MINUTE(TIMEDIFF(INI,FIN))>=5
GROUP BY ORIGEN
HAVING COUNT(*)>=4;
 

Aclaro que lo estoy reconstruyendo sobre la base de casos que he programado antes, pero que de todos modos hay que ir probando las selecciones de a una, desde adentro hacia afuera.
Como no tengo los datos ni la estructura, no he podido comprobarlo, pero el esquema es esencialmente correcto. Si hay errores estarán en los ordenamientos intermedios o los campos de agrupación.