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 NodeDetailsFROM (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.NodeWHERE 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
