Help with SNMP Failure SQL based alert.

Question

I build an Advanced SLQ alert with the following where clause:

WHERE(  (DATEDIFF(mi,  Nodes.LastSystemUpTimePollUtc, getutcdate()) > 60) AND  (Nodes.Status = '1'))

this should alert on any node that has not responded to SNMP in the past 60 minutes. however I'm getting some false positives.

If I execute the following query the nodes that are producing false positives have a LastSystemUpTimePollUtc in the future.

SELECT IP_Address, Caption, LastSystemUpTimePollUtcFROM NodesWHERE ((DATEDIFF(mi, Nodes.LastSystemUpTimePollUtc, getutcdate()) > 60) AND (Nodes.Status = '1'))ORDER BY LastSystemUpTimePollUtc DESC

I'm knocking this one up to my lack of experience with SQL but I'm hopping I can get someone to point out what I'm missing as well as a fix to my code to correct the issue.. Thanks!

Manilyn_Ramos · Answer

Hello, I tried your suggestion for report. However, some of the report information is incorrect. Question, is that also applicable for WMI? Thanks

ecklerwr1 · Answer

I was thinking it might have something to do with the clocks on the nodes not being in sync or in different time zone as well.

RichardLetts · Answer

three things:

a) node status is determined by ICMP-PING

b) if you have the wrong SNMP community set then the node will respond to PING, but the LastSystemUpTimePollUtc will be wrong

c) if there is some other problem with SNMP polling then LastSystemUpTimePollUtc will not be updated

Also, you should avoid doing math on database columns wherever you can; once you do that you prevent the use of indices (if they exists). It doesn't make any difference in this case since there is not an index on LastSystemUpTimePollUtc, but it will make a difference on other queries.

WHERE  (Nodes.LastSystemUpTimePollUtc < DATEADD(mi,  -60, getutcdate())) AND  (Nodes.Status = '1')