I had a hard time figuring out how to do this so I thought I would share. I work for a systems infrastructure team and we share a single NPM/SAM platform with our network team. We get quite a few alerts throughout the night and it takes time to go through each one to see if it cleared out or not. I wanted a scheduled morning report that would tell my team which alerts were still active, and it had to exclude any of the network team's alerts. We separate all of our nodes with a Custom Property called MainRole (ex. Server, Switch, Storage, etc..).
It's easy enough to write a SQL query that shows you all of the active alerts from the AlertStatus table, but what if you want to relate that Alert to the originating node, and even more... filter on a Custom property within that node? Unfortunately, the ActiveObject field only equals NodeID if the ObjectType (Alert type) = Node. You have to find the NodeID in the table that matches the ObjectType. For instance... if the ObjectType is Volume, you have to find that Volume object in the Volumes table to get the NodeID.
I hope this helps anyone looking for something similar... I still haven't figured out what to do if the ObjectType is APM: Component, but I only have one Alert based on an application Component and it's not critical. I also don't have any alerts based on groups, so I don't know what that would look like. It works great for me, but it's probably just a starting point for most
SELECT Lkp.Node AS NodeID, AlertStatus.TriggerTimeStamp AS TriggerTime, Nodes.Caption AS SystemName, AlertStatus.ObjectName AS AlertName, AlertStatus.ObjectType AS AlertCategory, 'Click to View Node Details' AS NodeDetails FROM (SELECT CASE WHEN AlertStatus.ObjectType ='Node' THEN AlertStatus.ActiveObject WHEN AlertStatus.ObjectType ='APM: Hardware Sensor' THEN APM_HardwareItem.NodeID WHEN AlertStatus.ObjectType ='Volume' THEN Volumes.NodeID WHEN AlertStatus.ObjectType ='APM: Application' THEN APM_Application.NodeID WHEN AlertStatus.ObjectType ='Interface' THEN Interfaces.NodeID ELSE NULL END as Node, ActiveObject FROM AlertStatus LEFT OUTER JOIN APM_HardwareItem ON APM_HardwareItem.ID = AlertStatus.ActiveObject LEFT OUTER JOIN Volumes ON Volumes.VolumeID = AlertStatus.ActiveObject LEFT OUTER JOIN APM_Application ON APM_Application.ID = AlertStatus.ActiveObject LEFT OUTER JOIN Interfaces ON Interfaces.InterfaceID = AlertStatus.ActiveObject ) Lkp INNER JOIN AlertStatus on AlertStatus.ActiveObject = Lkp.ActiveObject INNER JOIN Nodes on Nodes.NodeID = Lkp.Node WHERE Nodes.MainRole = 'Server' OR Nodes.MainRole = 'F5' OR Nodes.MainRole = 'Fabric' OR Nodes.MainRole = 'Storage' OR Nodes.MainRole = 'Tape Library' OR Nodes.MainRole = 'VIP' ORDER BY TriggerTime DESC