Custom 4 node cluster alert based on UNDP value

Question

I would like to create alerting that triggers an alert if  for 4 nodes UNDP value is different then 1 node Active and 3 Nodes Standby.

We have theoretical SWQL statement but how to implement it into Orion Alerting to only alert when condition is not met.

SELECT Nodes.NodeID, Nodes.Caption, PollerStatus.Status
FROM Orion.Nodes AS Nodes
JOIN Orion.NPM.CustomPollerStatus AS PollerStatus ON Nodes.NodeID = PollerStatus.NodeID
JOIN Orion.NPM.CustomPollers AS Pollers ON Pollers.CustomPollerID = PollerStatus.CustomPollerID
WHERE Pollers.UniqueName = 'Your_UNDP_Poller_Name'
AND (
 -- Count nodes with 'Active' status in UNDP is not 1
 (SELECT COUNT(*) 
 FROM Orion.NPM.CustomPollerStatus 
 WHERE Status = 'Active'
 AND CustomPollerID = Pollers.CustomPollerID) != 1
 OR
 -- Count nodes with 'Standby' status in UNDP is not 3
 (SELECT COUNT(*) 
 FROM Orion.NPM.CustomPollerStatus 
 WHERE Status = 'Standby'
 AND CustomPollerID = Pollers.CustomPollerID) != 3
)
AND Nodes.NodeID IN (
 -- List the 4 nodes in the cluster by NodeID (replace NodeID with actual IDs)
 SELECT NodeID 
 FROM Orion.Nodes
 WHERE NodeID IN (123, 124, 125, 126) -- replace with actual NodeIDs
);

FilRad · Answer

Is there a way to create alert to compare 4 UnDP values collected on 4 different nodes and alert on deviation form desired state?

Seashore · Answer

Hi, I think you can do something like this:

The full query, including the mandatory part is:

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

INNER JOIN (

SELECT N.Uri
FROM Orion.Nodes AS N
JOIN Orion.NPM.CustomPollerStatus AS PollerStatus ON N.NodeID = PollerStatus.NodeID
JOIN Orion.NPM.CustomPollers AS Pollers ON Pollers.CustomPollerID = PollerStatus.CustomPollerID
WHERE Pollers.UniqueName = 'Your_UNDP_Poller_Name'
AND 
    (
    -- Count nodes with 'Active' status in UNDP is not 1
    (SELECT COUNT(*)
    FROM Orion.NPM.CustomPollerStatus
    WHERE Status = 'Active'
    AND CustomPollerID = Pollers.CustomPollerID) != 1
OR
    -- Count nodes with 'Standby' status in UNDP is not 3
    (SELECT COUNT(*)
    FROM Orion.NPM.CustomPollerStatus
    WHERE Status = 'Standby'
    AND CustomPollerID = Pollers.CustomPollerID) != 3
    )
AND Nodes.NodeID IN (123, 124, 125, 126) -- replace with actual NodeIDs

) AS T ON Nodes.URI=N.Uri