custom query for fetching active alerts

basilfernandaz, take a look at any of these articles, they should be able to get you started. If you still need help let me know and we can work through what you need.

Thanks Christopher for the info , Can you give some sample query format ..for alert active and severity and node id please ?

Thanks Christopher for the info , Can you give some sample query format ..for alert active and severity and node id please ?

basilfernandaz, all of those posts from the previous reply contain examples. Here's a another example.

Thanks for the information , what i failed to fetch is Active Alert and its severity in the same query ? i am stuck with this, Can you please help me with some query fetching active alerts and its severity and node pls

basilfernandaz, can you paste a copy of the query you are currently working with?

basilfernandaz, take a look at this, it seems to be what you are looking for.

Dear Christopher , 

Using this query (generated from SWQL studio) , able to fetch active alerts in the system , 

SELECT TOP 1000 AlertActiveID, AlertObjectID, Acknowledged, AcknowledgedBy, AcknowledgedDateTime, AcknowledgedNote, TriggeredDateTime, TriggeredMessage, NumberOfNotes, LastExecutedEscalationLevel FROM Orion.AlertActive

basilfernandaz

take a look at this, it seems to be what you are looking for.

Thanks a ton brother, How can i add node region in the query ? need to fetch node region details along with the existing data , can u advice please

basilfernandaz, is node region a custom property you have defined?

how do i incorporate custom property (node region ) with this existing query 

basilfernandaz, 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