Custom Report for Active Alerts

sarathi_rangan

Hello THWACKians,

Can someone give me SQL /SWQL Query to fetch a report for current Active Alerts with below columns in SW NPM 2020.2.6

Alert Name

Alert Message

Object Triggered the Alert

Node Name

Interface Name on the Alert

Node Status

Interface Status

Custom Property (ex: Region or Location etc)

Find more posts tagged with

swql query

Reports

network_monitoring

feature_request

sql

SWQL

npm

Alerts

report

Accepted answers

All comments

amer10286

to query all active alert, please find below the SQL query, hope it will work since its working with me.

SELECT DISTINCT
AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,
AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,
ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,
AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,
Case
When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then
ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then
ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then
ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
Else ''
End AS ActiveTime
FROM Orion.AlertObjects (nolock=true) AlertObjects
INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID
Order By AlertConfigurations.Name, AlertObjects.EntityCaption

sarathi_rangan

Thank you @amer10286, this is working as expected...

lakshmidevi2194

Hi @amer10286,

I need Responsible Team custom value in this report, Is it possible to add ..?

Thanks in advance.!

deepak_sh41

how can we add custom property in above query

patstat

This is what I got for anyone else that is trying to get SWQL for Unacknowledged Alerts by ResponsibleTeam

SELECT DISTINCT
AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name, AlertConfigurations.Severity, AlertConfigurations.ObjectType,
AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption,
ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,
AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged AS Acknowledged, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedNote,
Case
When Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400)>0 Then
ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) +0.0)/86400))+'d '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600)>0 Then
ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 86400*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+
ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
When Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60)>0 Then
ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) - 3600*(Floor((SecondDiff(AlertActive.TriggeredDateTime,GetUtcDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
Else ''
End AS ActiveTime
FROM Orion.AlertObjects (nolock=true) AlertObjects
INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID
INNER JOIN Orion.AlertConfigurationsCustomProperties (nolock=true) AlertConfigurationsCustomProperties ON AlertConfigurationsCustomProperties.AlertID=AlertObjects.AlertID
WHERE IsNULL(Acknowledged, 0) = 0 AND ResponsibleTeam LIKE '%NetOps%'

Order By AlertConfigurations.Name, AlertObjects.EntityCaption