Multiple UnDP's in a Single Alert

Question

We are trying to monitor two air conditioners in a data center. They run alternating one week on, one week standby and it is not unusual for the AC that is on standby to go above 30 degrees. Therefore, I need to create an alert that goes as follows:

IF AC Running = Yes

AND

IF Temp > 30 degrees = Yes

THEN

Create alert

I tried creating the following trigger condition which doesn't work:

After logging ticket 00439660 with tech support, I was advised that it's not possible to run this via the GUI with two UnDP's and two AND statements (OR statements would work but do not meet my requirements). As such, I have asked for this to be raised as a feature request.

In the meantime, I was wondering if it would be possible to create the same trigger using a Custom SQL Query instead?

I got the base of the following code from another Thwack thread, however I'm not sure how to search multiple fields within the query. The following query doesn't error out, but doesn't return any results. If I remove one of the two WHERE searches, it then returns the correct results.

SELECT Nodes.NodeID, Nodes.Caption FROM Nodes  

INNER JOIN
    (
        SELECT  CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,  
        CustomPollerAssignmentView.AssignmentName AS Name,  
        CustomPollerStatus.Status AS Status,  
        CustomPollerAssignmentView.NodeID  

        FROM
        (
            CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON 
            (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID)
        )   
        LEFT OUTER JOIN CustomPollers ON 
        (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
        LEFT OUTER JOIN Nodes ON 
        (CustomPollerAssignmentView.NodeID = Nodes.NodeID)

        WHERE  
        (  
            ((CustomPollers.UniqueName LIKE 'LiebertACSystemPowerState%') AND (CustomPollerStatus.Status = 'ON')) 
            AND 
            ((CustomPollers.UniqueName LIKE 'LiebertACReturnAirTemp%') AND (CustomPollerStatus.Status > '30'))
        )  
GROUP BY CustomPollerAssignmentView.NodeID, CustomPollerStatus.Status, CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID  
) CM  
ON CM.NodeID = NODES.NodeID

If anyone could help, it would be greatly appreciated!

Thanks!

---matt--- · Accepted Answer

I got this sorted out today. Solarwinds alerting doesn't appear to like the INTERSECT function, so we had to re-write it again and came up with the following:

SELECT Nodes.NodeID, Nodes.Caption FROM Nodes  
INNER JOIN
 (
 SELECT CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,  
              CustomPollerAssignmentView.AssignmentName AS Name,  
              CustomPollerStatus.Status AS Status,  
              CustomPollerAssignmentView.NodeID  
 
 FROM
 (
 CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON 
                     (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID)
 )   
              LEFT OUTER JOIN CustomPollers ON 
              (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
 LEFT OUTER JOIN Nodes ON 
              (CustomPollerAssignmentView.NodeID = Nodes.NodeID)
 
 WHERE  
              (CustomPollers.UniqueName LIKE 'LiebertACSystemPowerState%' AND CustomPollerStatus.Status = 'ON')
GROUP BY CustomPollerAssignmentView.NodeID, CustomPollerStatus.Status, CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID  
) CM1  
ON CM1.NodeID = NODES.NodeID  
INNER JOIN
 (
 SELECT CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,  
              CustomPollerAssignmentView.AssignmentName AS Name,  
              CustomPollerStatus.Status AS Status,  
              CustomPollerAssignmentView.NodeID  
 
 FROM
 (
 CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON 
                     (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID)
 )   
              LEFT OUTER JOIN CustomPollers ON 
              (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
 LEFT OUTER JOIN Nodes ON 
              (CustomPollerAssignmentView.NodeID = Nodes.NodeID)
 
 WHERE  
              (CustomPollers.UniqueName LIKE 'LiebertACReturnAirTemp%' AND CustomPollerStatus.Status > '0')
GROUP BY CustomPollerAssignmentView.NodeID, CustomPollerStatus.Status, CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID  
) CM2  
ON CM2.NodeID = NODES.NodeID

Multiple UnDP's in a Single Alert

Top Replies