SELECT cont||' x '||text AS "N x (Tipo de mensaje)" , severity AS "Gravedad" ,
CASE WHEN (completion = 'succeeded' OR completion 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 "Nombre" ,
tfts AS "Desde" , tlts AS "Hasta"
FROM
(
SELECT COUNT(tlast._ident) AS cont, t1.text AS text, t7.completion AS completion, MIN(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,
MIN(t0.time) AS "tfts" , MAX(t0.time) AS "tlts"
FROM (SELECT t._ident FROM prelude_alert as t WHERE t._ident BETWEEN 1286 AND 1427) AS top
LEFT JOIN Prelude_CreateTime AS t0 ON (t0._message_ident =top._ident AND t0._message_ident BETWEEN 1286 AND 1427)
LEFT JOIN (SELECT t2._message_ident ,t2.address FROM Prelude_Address AS t2 WHERE t2._index = 0 AND t2._parent_type = 'S' AND t2._parent0_index = -1
AND t2._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t2 ON (t2._message_ident =top._ident )
LEFT JOIN (SELECT t3._message_ident ,t3.address FROM Prelude_Address AS t3 WHERE t3._index = 0 AND t3._parent_type = 'T' AND t3._parent0_index = -1
AND t3._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t3 ON (t3._message_ident =top._ident )
LEFT JOIN (SELECT t4._message_ident ,t4.iana_protocol_number FROM Prelude_Service AS t4 WHERE t4._parent0_index = -1 AND t4._parent_type='T'
AND t4._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t4 ON (t4._message_ident =top._ident )
LEFT JOIN (SELECT t5._message_ident ,t5.port FROM Prelude_Service AS t5 WHERE t5._parent_type='T' AND t5._parent0_index = -1
AND t5._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t5 ON (t5._message_ident =top._ident )
LEFT JOIN (SELECT t11._message_ident ,t11.port FROM Prelude_Service AS t11 WHERE t11._parent_type='S' AND t11._parent0_index = -1
AND t11._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t11 ON (t11._message_ident =top._ident )
LEFT JOIN (SELECT t1._message_ident ,t1.text FROM Prelude_Classification AS t1
WHERE t1._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t1 ON (t1._message_ident =top._ident )
LEFT JOIN (SELECT t6._message_ident,t6.name FROM Prelude_Analyzer AS t6 WHERE t6._index = -1 AND t6._parent_type = 'A'
AND t6._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2) AS t6 ON (t6._message_ident=top._ident )
LEFT JOIN (SELECT t7._message_ident ,t7.severity,t7.completion FROM Prelude_Impact AS t7
WHERE t7._message_ident BETWEEN 1286 AND 1427 GROUP BY 1,2,3) AS t7 ON (t7._message_ident =top._ident )
LEFT JOIN (SELECT tlast._ident FROM Prelude_Alert AS tlast WHERE tlast._ident BETWEEN 1286 AND 1427 GROUP BY 1) AS tlast
ON (((t2.address = '192.168.100.10') AND (t3.address = '192.168.100.60')) AND (t0.time >= '2006-03-16 17:56:00' AND t0.time <= '2006-03-16 18:00:50')
AND tlast._ident =top._ident )
GROUP BY 2,3,5,6,7,8,9,10
ORDER BY 2 DESC LIMIT 142
) AS tlast WHERE tlast.cont != 0 ORDER BY 4 DESC
e-REdING. Biblioteca de la Escuela Superior de Ingenieros de Sevilla.
