SELECT COUNT(tlast._ident)||' x '||t1.text AS "N x Classification" , t7.severity AS "Severity" , t7.completion AS "Completion" , CASE WHEN (t2.address IS NULL) THEN CASE WHEN t3.address IS NULL THEN 'n/a' ELSE '127.0.0.1' END WHEN (t2.address = ' ') THEN '127.0.0.1 ' ELSE t2.address END AS "Source Addr" , CASE WHEN t3.address 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) ELSE t3.address END AS "Target Addr" , CASE WHEN t5.iana_protocol_number IS NULL THEN '0' ELSE t5.iana_protocol_number END AS "Protocol" , CASE WHEN MIN(t4.port) IS NULL THEN '0' ELSE MIN(t4.port) END AS "Source Port" , CASE WHEN t5.port IS NULL THEN '0' ELSE t5.port END AS "Target Port" , t6.name AS "Sensor" , MIN(t0.time) AS "FTS" , MAX(t0.time) AS "LTS"
FROM Prelude_Alert AS top
LEFT JOIN Prelude_Analyzer AS t6 ON ( top._ident = t6._message_ident AND t6._parent_type = 'A' AND t6._index = -1)
LEFT JOIN Prelude_Service AS t4 ON ( top._ident = t4._message_ident AND t4._parent_type='S' AND t4._parent0_index = -1)
LEFT JOIN Prelude_Service AS t5 ON ( top._ident = t5._message_ident AND t5._parent_type='T' AND t5._parent0_index = -1)
LEFT JOIN Prelude_Classification AS t1 ON ( top._ident = t1._message_ident) LEFT JOIN Prelude_Impact AS t7 ON ( top._ident = t7._message_ident)
LEFT JOIN Prelude_Address AS t2 ON ( top._ident = t2._message_ident AND t2._parent_type = 'S' AND t2._parent0_index = -1 AND t2._index = -1)
LEFT JOIN Prelude_Address AS t3 ON ( ( (t3.address = '127.0.0.1') OR (t3.address = '127.0.0.1') ) AND top._ident = t3._message_ident AND t3._parent_type = 'T' AND t3._parent0_index = -1 AND t3._index = -1)
LEFT JOIN Prelude_CreateTime AS t0 ON ( t0._message_ident = top._ident AND t0._parent_type = 'A')
LEFT JOIN Prelude_Alert AS tlast ON (( (t2.address is null) OR (t2.address = ' ') ) AND ( (t3.address = '127.0.0.1') OR (t3.address = '127.0.0.1') ) AND top._ident = tlast._ident) WHERE t0.time >= '2005-12-12 10:19:44.0' AND t0.time <= '2005-12-21 09:54:30.0'
GROUP BY t1.text, 2, 3, 4, 5, 6, 8, 9
EXCEPT
SELECT COUNT(tlast._ident)||' x '||t1.text AS "N x Classification" , t7.severity AS "Severity" , t7.completion AS "Completion" , CASE WHEN (t2.address IS NULL) THEN CASE WHEN t3.address IS NULL THEN 'n/a' ELSE '127.0.0.1' END WHEN (t2.address = ' ') THEN '127.0.0.1 ' ELSE t2.address END AS "Source Addr" , CASE WHEN t3.address 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) ELSE t3.address END AS "Target Addr" , CASE WHEN t5.iana_protocol_number IS NULL THEN '0' ELSE t5.iana_protocol_number END AS "Protocol" , CASE WHEN MIN(t4.port) IS NULL THEN '0' ELSE MIN(t4.port) END AS "Source Port" , CASE WHEN t5.port IS NULL THEN '0' ELSE t5.port END AS "Target Port" , t6.name AS "Sensor" , MIN(t0.time) AS "FTS" , MAX(t0.time) AS "LTS"
FROM Prelude_Alert AS top
LEFT JOIN Prelude_Analyzer AS t6 ON ( top._ident = t6._message_ident AND t6._parent_type = 'A' AND t6._index = -1)
LEFT JOIN Prelude_Service AS t4 ON ( top._ident = t4._message_ident AND t4._parent_type='S' AND t4._parent0_index = -1)
LEFT JOIN Prelude_Service AS t5 ON ( top._ident = t5._message_ident AND t5._parent_type='T' AND t5._parent0_index = -1)
LEFT JOIN Prelude_Classification AS t1 ON ( top._ident = t1._message_ident) LEFT JOIN Prelude_Impact AS t7 ON ( top._ident = t7._message_ident)
LEFT JOIN Prelude_Address AS t2 ON ( top._ident = t2._message_ident AND t2._parent_type = 'S' AND t2._parent0_index = -1 AND t2._index = -1)
LEFT JOIN Prelude_Address AS t3 ON ( ( (t3.address = '127.0.0.1') OR (t3.address = '127.0.0.1') ) AND top._ident = t3._message_ident AND t3._parent_type = 'T' AND t3._parent0_index = -1 AND t3._index = -1)
LEFT JOIN Prelude_CreateTime AS t0 ON ( t0._message_ident = top._ident AND t0._parent_type = 'A')
LEFT JOIN Prelude_Alert AS tlast ON ( tlast._ident is null AND top._ident = tlast._ident)
WHERE t0.time >= '2005-12-12 10:19:44.0' AND t0.time <= '2005-12-21 09:54:30.0'
GROUP BY t1.text, 2, 3, 4, 5, 6, 8, 9
ORDER BY 11 DESC
e-REdING. Biblioteca de la Escuela Superior de Ingenieros de Sevilla.
