Alert when SNMP polling stops for any node

This is from a related discussion here. I want to send out Alerts when a particular node stops responding to SNMP polls for whatever reason. Obviously this needs to exclude Nodes managed by Non-SNMP means. I found CPULoad_Detail table gets updated once every polling cycle for each node. Is it possible to alert if it has not been updated for more than 10 minutes? This is a rough SQL query (which doesn't work). I need SQL guru's help on this to convert to meaningful alert.

SELECT Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name
FROM Nodes
inner join CPULoad_Detail on CPULoad_Detail.NodeID = Nodes.NodeID
WHERE
(
(DATEDIFF(mi, (Select Top 1 DateTime from CPULoad_Detail where CPULoad_Detail.NodeID = Nodes.NodeID), getdate()) > 10) AND
(
NOT (Nodes.Status = '9') AND
NOT (Nodes.Status = '11'))
)

Find more posts tagged with

sql_alert

Accepted answers

All comments

bleearg13

Maybe I'm under-thinking this, but what we do is simply have an alert for when a devices becomes "unknown". The "unknown" status does not seem to trigger under any other circumstance, at least from the perspective of what we alert on.

adatole

In 10.4 (and earlier, IIRC) there is a sample alert that does just that. Two, in fact - one that tells you when a node has failed 5 polling cycles, and another that tells you when the last poll time is over xx minutes ago. Let me know if you can't find it.

thamizh85

I had investigated that option earlier, but those alerts rely on LastSycn value in Nodes table, which gets updated even for a successful ICMP poll. As you can ping the node, these alerts will not be triggered even if SNMP polls fail. And this is the exact scenario I am keen to monitor.

thamizh85

Guys,

Any ideas? I figured out I need a custom SQL query but need some SQL Guru guidance to get it done.

adatole

Still looking into it. I thought LastSync was different than LastPoll. Gotta test to validate.

marunderwood

My sql may be a little rusty. Try this.

SELECT
-- Nodes.NodeID AS NetObjectID,
Nodes.Caption AS Name,
Nodes.Status,
Nodes.MachineType,
max(cpu.datetime) as Last_Poll

FROM Nodes
left join CPULoad_Detail cpu on cpu.NodeID = Nodes.NodeID

WHERE

(cpu.NodeID is null
OR
-- (DATEDIFF(mi, DateTime, getdate()) > 10)
(DATEDIFF(mi, (Select Top 1 DateTime from CPULoad_Detail cpu where cpu.NodeID = Nodes.NodeID order by DateTime desc), getdate()) > 10)

)

AND

( NOT (Nodes.Status = '9') AND
NOT (Nodes.Status = '11')
)

Group by
Nodes.Caption,
Nodes.NodeID,
Nodes.Status,
Nodes.MachineType

Order by
Nodes.Caption,
Nodes.NodeID,
Nodes.Status,
Nodes.MachineType

mwb

I changed to just "join" instead of "left join" to exempt NULL Last_Polls for nodes like old ESXi boxes and cluster IP instances with no CPU polls.

marunderwood

OK. But a word of caution: I had it this way, but the CPULoad_Detail only contained x days worth of data. So, after "x" days elapsed, the node was no longer in the results. (I think x was 7 days. CPULoad contained a year.)

To get around some of your concerns with no CPU, I did have in my where clause ....

NOT(ObjectSubType = 'ICMP' AND UPPER(Nodes.MachineType) <> 'VMWARE ESX SERVER') AND /* exclude ICMP (except ESX) because they don't pull CPU info */

UPPER(Nodes.MachineType) not in ('EQUALLOGIC', 'PARADYNE')

          AND
          (cpu.NodeID is null
            OR
            (DATEDIFF(mi, (Select Top 1 DateTime from CPULoad_Detail cpu where cpu.NodeID = Nodes.NodeID order by DateTime desc), getdate()) > 30)

)

         AND
        ( NOT (Nodes.Status = '9') AND
            NOT (Nodes.Status = '11')
         )

But, I kept having to add Machine Types. So I eventually changed it to only include nodes that had a CPU poll within the last year. So, now the WHERE clause (in my report) is ...

FROM Nodes

left join (select NodeId, max(datetime) AS datetime from CPULoad group by NodeId) cpul on cpul.NodeID = Nodes.NodeID

WHERE

   NOT (UPPER(Nodes.Status) in ('2', '9', '11' )) /* 2=Down, 9 = Unmanaged & 11=External */
   AND
   DATEDIFF(mi, cpul.DateTime, getdate()) > 30 /* It's been 30 minutes or more */

p.s. We had an issue installing Solarwinds a while back, which caused the polling to stop for every node. So I also added a limit to only report the first 10 nodes. Hopefully, this feature will be added and you won't have to code for it.