custom query for fetching active alerts

 I am trying to query the below parameters (Active Alarms , relevant Node details , its Severity and Region etc.) ...From the documentation active alerts can be fetched from this details 

[Orion.AlertActive ] 

in the above table , i am not able to query relevant node details , severity ,, can some one help me which this creating custom query ...to fetch these properties [Active alerts , respective node id , alert severity , and region ]

Parents Reply
  • , so this might work. I don't have an instance i can test out in. I'm fairly certain there's a navigational property that can be used in place of the join but as i said i don't have an instance i can test out in to confirm. This is also dependent on the name of your custom property, you need to make sure the line ",N.CustomProperties.NodeRegion" is accurate.

    ---------------------------
    -- Name:   SWQL - Active Alerts Report
    -- Desc:   This query is used to create a table showing all active alerts
    -- Note:   This query requires that all Active Alerts have an Alert Message
    --         in order to only show active alerts
    -- Auth:   Zach Mutchler
    -- Date:   October 29, 2015
    ----------------------------
    -- Change History
    ----------------------------
    -- PR   Date	      Author         Purpose	
    -- --   -----------   -------        ------------------------------------
    -- 1    10/29/2015    Zach Mutchler  Initial development.
    ----------------------------
    
    SELECT
    	o.AlertConfigurations.Name AS [ALERT NAME]
    	,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
    	,o.EntityCaption AS [ALERT OBJECT]
    	,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
    	,o.RelatedNodeCaption AS [RELATED NODE]
    	,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
    	,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
    	,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]
    	,CASE
    		WHEN o.AlertConfigurations.Severity = 1 then 'Critical'
    		WHEN o.AlertConfigurations.Severity = 2 then 'Serious'
    		WHEN o.AlertConfigurations.Severity = 3 then 'Warning'
    		WHEN o.AlertConfigurations.Severity = 4 then 'Informational'
    		WHEN o.AlertConfigurations.Severity = 5 then 'Notice'
    	END AS [Severity]
    	,N.CustomProperties.NodeRegion
    FROM Orion.AlertObjects o
    LEFT JOIN Orion.Nodes N on N.Caption = o.RelatedNodeCaption
    WHERE o.AlertActive.TriggeredMessage <> ''
    ORDER by o.AlertActive.TriggeredDateTime DESC

Children