Custom SQL Alerting & Aggregate Bandwidth for Multiple Interfaces on Different Devices
Applies To: Solarwinds Orion NPM 11.01
The Challenge
Create an alert when the total transmit or total receive exceeds 5G for two interfaces on two different devices. The circuit is able to burst up to 10G but we want an alert to trigger at 5G.
The Advanced Alert Manager is unable to do any mathematics preventing us from creating a simple alert. Custom pollers were an alternative option and then transforming that value but the transform has to be assigned to a single node as well as the two pollers that would be used for the total value that would determine the alert trigger and reset but because we want the aggregate on multiple devices this isn’t an option. Transformers can support custom SQL but it has limitations as some key words cannot be used.
Custom SQL Alert
The best option for us was to create a custom SQL alert and do a SUM of the transmit and receive data that is already being polled by SNMP for those devices. But this brought another problem. To do an aggregate SUM function this has to be in the select statement of the SQL but Solarwinds Advanced Alert Manager has a predefined SELECT and FROM statement based on the item that is selected for the trigger query. This field cannot be edited in any way within the Advanced Alert Manager but if you export the alert and edit its quite simple that the unedited field is ended with a comment.
This field cannot be edited in any way within the Advanced Alert Manager but if you export the alert and edit its quite simple that the unedited field is ended with a comment.
After exporting we can edit the .AlertDefinition file that was created.
The comment we are looking for is /*SplitMarker*/ We can move this to the beginning of the Query and that will allow for us to edit the entire query included the predefined SELECT and FROM statement.
The SQL query needed to create this alert there was only one field that was required, the original select statement was the following:
SELECT sum(Interfaces.Outbps) AS SUM
After verifying the entire query was valid within the Database Manager I created a new alert using custom SQL that adds the Interfaces.Outbps for the two interfaces then filters based on a value using the SQL function HAVING SUM.
But the alert was not triggering as intended. After looking into it further and exporting predefined alerts I discovered there was one similarity among all of them. They all contained two columns called “ObjectID” and “Name”. We hard coded these values into the SQL statement.
Here is the final query.
Trigger
SELECT ‘570’ as NetObjectID, ‘NODE NAME’ as Name, sum(Interfaces.Inbps) as TOTAL
FROM
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE
(
((Nodes.Caption = ‘NODENAME1’) AND
(Interfaces.Caption LIKE ‘TenGigabitEthernet0/1/0%’))
OR
((Nodes.Caption = ‘NODENAME2’) AND
(Interfaces.Caption LIKE ‘TenGigE0/0/2/0%’))
)
HAVING SUM(Interfaces.Inbps) > 5000000000
Reset
SELECT ‘570’ as NetObjectID, ‘NODE NAME’ as Name, sum(Interfaces.Inbps) as TOTAL
FROM
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE
(
((Nodes.Caption = ‘NODENAME1’) AND
(Interfaces.Caption LIKE ‘TenGigabitEthernet0/1/0%’))
OR
((Nodes.Caption = ‘NODENAME2’) AND
(Interfaces.Caption LIKE ‘TenGigE0/0/2/0%’))
)
HAVING SUM(Interfaces.Inbps) < 5000000000