CISCO UCS and alerting (using SQL query to show issue)

trilobite_rex

Case # - 01951508

So I spent the better part of two months trying to get an alert to trigger when a power supply failure is report on our UCS.  I used the OID and I can see it in the alert but when I try to get it to print out the information in the alert via a sql query it just shows the query itself and no data.

SELECT CPS.LabelFROM CustomPollerStatusTable AS CPSRIGHT JOIN CustomPollerAssignment AS CPA ON CPS.NodeID = CPA.NodeIDWHERE CPS.CustomPollerAssignmentID != TRY_CONVERT(uniqueidentifier, '${N=SwisEntity;M=CustomPollerAssignment.CustomPollerAssignmentID}')   AND CPA.AssignmentName LIKE '6cucsEquipmentPsuOperState96'AND CPA.NodeID = CAST('${N=SwisEntity;M=CustomPollerAssignment.Node.NodeID}' AS INT)   AND CPS.RowID = '${N=SwisEntity;M=RowID}'

or

 ${SQL: SELECT CPS.Label FROM CustomPollerStatusTable AS CPS RIGHT JOIN CustomPollerAssignment AS CPA ON CPS.NodeID = CPA.NodeID WHERE CPS.CustomPollerAssignmentID != '${N=SwisEntity;M=CustomPollerAssignment.CustomPollerAssignmentID}' AND CPA.AssignmentName LIKE '%entPhysicalDescr%' AND CPA.NodeID = ${N=SwisEntity;M=CustomPollerAssignment.Node.NodeID} AND CPS.RowID = '${N=SwisEntity;M=RowID}'} Status is ${N=SwisEntity;M=Status}

When these are ran it fails with incorrect syntax in the simulated alert, When I try to monitor the device via ucs that fails with no indicator as to why.  I have tried with an admin right account on the ucs as well as my own.  I have checked the ignore certificate errors message as well.

Ladies and Gentlemen,  I am stumped and I need help.

Lofstrand

Hi @trilobite_rex 

A couple of things I see is that the AssignmentName is often something like "ifHCOutMulticastPkts on lab-nexus-3000". Not just the UnDP's name. And all the conversions should not be needed. Also worth noting is that simulate alert does not always work. If in doubt, adjust thresholds so you get a "real alert".

I created two examples here for you that seem to work in my lab anyway.

First one is with SQL:

${SQL: SELECT 
	CPS.Label AS [Label]
FROM CustomPollerStatusTable AS CPS
INNER JOIN CustomPollerAssignment AS CPA ON CPS.NodeID = CPA.NodeID
INNER JOIN CustomPollers AS CP ON CPA.CustomPollerID=CP.CustomPollerID
WHERE 
	CP.UniqueName='ifHCOutMulticastPkts'  -- name of the UnDP
	AND CPA.NodeID = ${N=SwisEntity;M=CustomPollerAssignment.Node.NodeID}   
	AND CPS.RowID = ${N=SwisEntity;M=RowID}
}

Second one is with SWQL:

${N=SWQL;M=SELECT
    CPS.RowLabel AS Label
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS CPS
WHERE
    CPS.CustomPollerAssignment.CustomPoller.UniqueName='ifHCOutMulticastPkts'  -- name of the UnDP
    AND CPS.CustomPollerAssignment.NodeID=${N=SwisEntity;M=CustomPollerAssignment.Node.NodeID} 
    AND CPS.RowID=${N=SwisEntity;M=RowID} }

On both, just change the name of the UnDP you want this to look for. (I used 'ifHCOutMulticastPkts' in my examples) (You can probably add a variable there as well to make it more generic)