Custom Report for Active Alerts

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)

  • 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

  • Hi ,

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

    Thanks in advance.!

  • 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