Query to report last 7 days events

[angelhc]

Hi all,
We're looking for a query to obtain last 7 days events do not care if they're ack or not.
Our first approach was query the events like mobile app do:

SELECT DISTINCT host, t.description, f.triggerid, e.acknowledged, t.value, i.lastvalue
FROM triggers t INNER JOIN functions f ON ( f.triggerid = t.triggerid )
INNER JOIN items i ON ( i.itemid = f.itemid )
INNER JOIN hosts ON ( i.hostid = hosts.hostid )
INNER JOIN events e ON ( e.objectid = t.triggerid )
WHERE (e.eventid DIV 100000000000000) IN (0)
AND (e.object-0)=0 AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) > date_add(now(), INTERVAL -7 DAY) ))
AND hosts.status =0 AND i.status =0 AND t.status =0 GROUP BY f.triggerid ORDER BY t.lastchange DESC;

But we think that it shows more events than real ones...
Any idea?
Thanks!

[angelhc]

If someone is interested on this issue, the correct query is:

SELECT DISTINCT h.name, t.description, f.triggerid,g.name, FROM_UNIXTIME(clock)
FROM triggers t INNER JOIN functions f ON ( f.triggerid = t.triggerid )
INNER JOIN items i ON ( i.itemid = f.itemid )
INNER JOIN hosts h ON ( i.hostid = h.hostid )
INNER JOIN hosts ON ( i.hostid = hosts.hostid )
INNER JOIN events e ON ( e.objectid = t.triggerid )
INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid )
INNER JOIN groups g ON ( hg.groupid = g.groupid )
WHERE (e.eventid DIV 100000000000000) IN (0)
AND (e.object-0)=0
AND t.lastchange < (FROM_UNIXTIME(clock) > date_add(now(), INTERVAL -7 DAY))
AND hosts.status =0 AND i.status =0 AND t.status =0
AND g.name like "%Linux servers%"
GROUP BY f.triggerid ORDER BY 3;

Change the g.name to obtain only events from this group or erase the line to obtain it from all groups.


Regards.