I'm trying to sort my alerts by department, then by node, then by alerts associated to the node...and I don't mean the alerts that are currently active, I mean alerts which are enabled per node. So far I've got an SQL query which works somewhat, but is incomplete. I can't figure out why some alerts are reported correctly (per node) but others are simply omitted.
Here is the SQL code I've got so far:
SELECT
AlertConfigurations.Name AS 'ALERT'
,AlertObjects.EntityCaption AS 'ALERT OBJECT'
,AlertObjects.RelatedNodeCaption AS 'RELATED NODE'
,AlertConfigurationsCustomProperties.Alert_Type AS 'ALERT TYPE'
,NodesCustomProperties.Department AS 'DEPARTMENT'
FROM AlertObjects
JOIN AlertConfigurations ON AlertConfigurations.AlertID = AlertObjects.AlertID
JOIN AlertConfigurationsCustomProperties ON AlertConfigurationsCustomProperties.AlertID = AlertObjects.AlertID
JOIN NodesData ON NodesData.Caption = AlertObjects.RelatedNodeCaption
JOIN NodesCustomProperties ON NodesCustomProperties.NodeID = NodesData.NodeID
WHERE AlertConfigurations.Enabled = 1
ORDER BY AlertConfigurations.Name, AlertObjects.EntityCaption