Could you tell me how to write a query for counting down and up events for a node in a day?
Like it,
@SolarRafic, something like below would get you all the node down events for the last day. You can modify the WHERE statement to scope it down to particular days or nodes if desired.
SELECT COUNT(EventID) as TotalFROM Orion.Eventswhere EventType = 1 and DAYDIFF(EventTime, GETDATE()) < 1
Thanks brother, I did adjust it for my convenient. Could you adjust my code for last 24 hours?. It does not show yesterday's down nodes. so i need to count the last 24 hours from max(e.eventtype=1).
select count(n.caption) as [Down Count], n.caption as Name,n.ip as IP
from Orion.Nodes N
join Orion.Events E on E.NetObjectID = N.NodeID and n.status=2 where DAYDIFF(e.EventTime, GETDATE()) < 1 and e.eventtype = 1 group by n.caption,n.ip
@SolarRafic, change DAYDIFF to HOURDIFF and increase the value of "1" to how many previous hours you want it to look at. so in your specific case it would be
HOURDIFF(e.EventTime, GETDATE()) < 24
it works. I check and confirm after 24h.
