SELECT text AS "N x (Tipo de mensaje)" , severity AS "Gravedad" ,
CASE WHEN (compl = 'succeeded' OR compl IS NULL) THEN TRUE ELSE FALSE END AS "Completado" ,
CASE WHEN (s_addr) IS NULL OR s_addr = ' ' THEN '127.0.0.1' ELSE s_addr END AS "IP Origen" ,
CASE WHEN t_addr IS NULL THEN (SELECT path FROM Prelude_File AS t8 LEFT JOIN Prelude_Alert AS top ON (t8._message_ident = top._ident) WHERE t8._parent0_index = -1 AND t8._index = -1 GROUP BY 1) ELSE t_addr END AS "IP Destino" ,
CASE WHEN iana_protocol_number IS NULL THEN '0' ELSE iana_protocol_number END AS "Protocolo" ,
CASE WHEN s_port IS NULL THEN '0' ELSE s_port END AS "Puerto Origen" ,
CASE WHEN port is null THEN '0' ELSE port END AS "Puerto Destino" ,
name AS "Sensorito" , time AS "Tiempo", _ident AS "Log ID"
FROM
(
SELECT tlast._ident AS _ident,
t1.text AS text,
t7.completion AS compl,
MAX(t11.port) AS s_port,
t5.port AS port,
t4.iana_protocol_number AS iana_protocol_number,
t7.severity AS severity,
t2.address AS s_addr,
t3.address AS t_addr,
t6.name AS name,
t0.time AT TIME ZONE 'GMT' AS "time"
FROM
(
SELECT t._ident FROM prelude_alert as t WHERE t._ident BETWEEN 6481 AND 16239
) AS top
LEFT JOIN Prelude_DetectTime AS t0 ON (t0._message_ident =top._ident AND t0._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Address AS t2 ON (t2._index = 0 AND t2._parent_type = 'S' AND t2._parent0_index = 0 AND t2._message_ident =top._ident AND t2._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Address AS t3 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t3._index = 0 AND t3._parent_type = 'T' AND t3._parent0_index = 0 AND t3._message_ident =top._ident AND t3._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Service AS t4 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t4._parent0_index = -1 AND t4._parent_type='T' AND t4._message_ident =top._ident AND t4._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Service AS t5 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t5._parent_type='T' AND t5._parent0_index = -1 AND t5._message_ident =top._ident AND t5._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Service AS t11 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t11._parent_type='S' AND t11._parent0_index = -1 AND t11._message_ident =top._ident AND t11._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Classification AS t1 ON ( t1.text = 'SNMP trap tcp' and ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t1._message_ident =top._ident AND t1._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Analyzer AS t6 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t6._index = -1 AND t6._parent_type = 'A' AND t6._message_ident=top._ident AND t6._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Impact AS t7 ON ( ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND t7._message_ident =top._ident AND t7._message_ident BETWEEN 6481 AND 16239)
LEFT JOIN Prelude_Alert AS tlast ON (t1.text = 'SNMP trap tcp' and ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND ((t2.address = '192.168.100.72') AND (t3.address = '192.168.100.61') ) AND tlast._ident =top._ident AND tlast._ident BETWEEN 6481 AND 16239)
GROUP BY 1,2,3,5,6,7,8,9,10,11
ORDER BY 2 DESC LIMIT 9758
) AS tlast
WHERE tlast._ident != 0
ORDER BY 4 DESC
e-REdING. Biblioteca de la Escuela Superior de Ingenieros de Sevilla.
