SELECT date_trunc('minute',time) AS "time" , COUNT(_ident) AS "total" FROM
(
SELECT * FROM
(
SELECT t6.name, tlast._ident, t0.time AT TIME ZONE 'GMT' AS "time"
FROM Prelude_Alert AS top
LEFT JOIN Prelude_DetectTime AS t0 ON (t0._message_ident =top._ident AND t0._message_ident BETWEEN 443 AND 855 AND t0.time >= '2006-03-13 11:00:56' AND t0.time <= '2006-03-13 13:30:59')
LEFT JOIN Prelude_Analyzer AS t6 ON (t6.name = 'snort' AND t6._message_ident=top._ident AND t6._message_ident BETWEEN 443 AND 855 AND t6._index = -1 and t6._parent_type = 'A')
LEFT JOIN Prelude_Alert AS tlast ON (t6.name = 'snort' AND tlast._ident =top._ident AND tlast._ident BETWEEN 443 AND 855 AND t0.time >= '2006-03-13 11:00:56' AND t0.time <= '2006-03-13 13:30:59')
GROUP BY t6.name, tlast._ident, t0.time ORDER BY tlast._ident DESC
) AS taux
EXCEPT
SELECT * FROM
(
SELECT t6.name, tlast._ident, t0.time AT TIME ZONE 'GMT' AS "time"
FROM Prelude_Alert AS top
LEFT JOIN Prelude_DetectTime AS t0 ON (t0._message_ident =top._ident AND t0._message_ident BETWEEN 443 AND 855 AND t0.time >= '2006-03-13 11:00:56' AND t0.time <= '2006-03-13 13:30:59')
LEFT JOIN Prelude_Analyzer AS t6 ON (t6.name = 'snort' AND t6._message_ident=top._ident AND t6._message_ident BETWEEN 443 AND 855 AND t6._index = -1 and t6._parent_type = 'A')
LEFT JOIN Prelude_Alert AS tlast ON (t6.name = 'snort' AND tlast._ident =top._ident AND tlast._ident BETWEEN 443 AND 855 AND t0.time >= '2006-03-13 11:00:56' AND t0.time <= '2006-03-13 13:30:59')
GROUP BY t6.name, tlast._ident, t0.time ORDER BY tlast._ident DESC
) AS taux WHERE taux._ident IS NULL
) AS tlast
GROUP BY 1 ORDER BY 1 DESC LIMIT 25
#Cambia respecto de Top sensor el getSqlVars y el getSqlOrderBy del SELECT más global
e-REdING. Biblioteca de la Escuela Superior de Ingenieros de Sevilla.
