So I'm trying to solve an alerting issue we are having and the only way I can think to do it right now is by creating an Alert Action to call a VB script that updates a custom property. So here is the alerting situation.
We have UPS for our building switches but not the floor switches within the building. So if they lose power we get a bunch of alerts(150+) for the floor switches going out, then when the building switch runs out of battery in 30 minutes, it dies and the floor switch alarms are then suppressed. What I'd like to have happen is that when a switch goes down it checks to see if the parent switch is running on UPS power.
Here is what I have so far.
Using the following SQL I can get from any child node(C) to it's parent node(P).
------------------
SELECT Nodes.Caption AS CCaption, PNodes.Caption AS PCaption
FROM Orion.Nodes AS Nodes
INNER JOIN Orion.NodesCustomProperties AS CCust ON Nodes.NodeID = CCust.NodeID
INNER JOIN Orion.Dependencies AS Dependencies ON Nodes.NodeID = Dependencies.ChildNetObjectID
INNER JOIN Orion.Nodes AS PNodes ON Dependencies.ParentNetObjectID = PNodes.NodeID
INNER JOIN Orion.NodesCustomProperties AS PCust ON PNodes.NodeID = PCust.NodeID
WHERE
Nodes.Caption LIKE '%<ChildDeviceCaption>%'
--------------------
When the building power dies I can alert on the UPS battery discharging and I can tell which network device this UPS is supporting using auto dependencies. The network connection for the UPS is always attached to the network device it is supporting. If I can get this alert to update an "OnBattery" custom property on the switch the UPS is supporting, then in my Network device down alert I'll check that custom property to see if it's set before generating an alert.
The default stuff in SW for changing Custom properties will only edit the properties of the source of the alert trigger (in this case the UPS) and there doesn't seem to be any way around this issue.
I'm new to SQL and I'm hoping someone has a rock solid example of a script making this kind of change that they can share with me. I'm already nervous messing around with the database directly.
