Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Looking to create a report that shows Alerts that have not been acknowledged

My goal is to have a scheduled report that shows only Alerts that have not been acknowledged. The below SWQL will show me the Acknowledged Alerts but I am looking for the opposite.

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]
,o.AlertActive.Acknowledged AS [Ack]
,CASE o.AlertConfigurations.Severity
WHEN 0 THEN 'Informational'
WHEN 1 THEN 'Warning'
WHEN 2 THEN 'Critical'
WHEN 3 THEN 'Serious'
WHEN 4 THEN 'Notice'
ELSE CONCAT('Unknown Severity: ', o.AlertConfigurations.Severity)
END AS [Severity]
-- ,N.CustomProperties.NodeRegion
FROM Orion.AlertObjects AS o
LEFT JOIN Orion.Nodes AS N ON N.Caption = o.RelatedNodeCaption
WHERE o.AlertActive.Acknowledged = 1 AND o.AlertConfigurations.Name LIKE 'SL%'
ORDER by o.AlertActive.TriggeredDateTime DESC

The " o.AlertActive.Acknowledged = 1" seems to select the Acknowledged Alerts but I cannot seem to find how to get the opposite of this. Tried 0, <> 1, Like '%null%' any help would be greatly apreciated.

Stephen

Find more posts tagged with

Accepted answers

All comments

bobmarley

Give this a try.

where IsNull(o.[AlertActive].[Acknowledged],'') like '%' AND o.AlertConfigurations.Name LIKE 'SL%'

slomond

Thank you it is still not working as hoped. I am still seeing the Acknowledged alerts and I am now also getting alerts showing that do not have a trigger time.

All suggestions welcome.

Stephen

KMSigma.SWI

Here's a rough way to do it.

SELECT [AlertObjects].AlertConfigurations.Name AS [Alert Name]
     , CONCAT (
          '/Orion/View.aspx?NetObject=AAT:'
          , [AlertObjects].AlertObjectID
          ) AS [_LinkFor_Alert Name]
     , [AlertObjects].EntityCaption AS [Alert Object]
     , [AlertObjects].EntityDetailsUrl AS [_LinkFor_Alert Object]
     , [AlertObjects].RelatedNodeCaption AS [Related Node]
     , [AlertObjects].RelatedNodeDetailsUrl AS [_LinkFor_Related Node]
     , ToLocal([AlertObjects].AlertActive.TriggeredDateTime) AS [Alert Trigger Time]
     , [AlertObjects].AlertActive.TriggeredMessage AS [Alert Message]
     , CASE 
          WHEN [AlertObjects].AlertActive.Acknowledged IS NULL
               THEN 'Unacknowledged'
          ELSE 'Acknowledged'
          END AS [AckState]
     , CASE [AlertObjects].AlertConfigurations.Severity
          WHEN 0
               THEN 'Informational'
          WHEN 1
               THEN 'Warning'
          WHEN 2
               THEN 'Critical'
          WHEN 3
               THEN 'Serious'
          WHEN 4
               THEN 'Notice'
          ELSE CONCAT (
                    'Unknown Severity: '
                    , [AlertObjects].AlertConfigurations.Severity
                    )
          END AS [Severity]
     , CASE [AlertObjects].AlertConfigurations.Severity
          WHEN 0
               THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
          WHEN 1
               THEN '/Orion/images/ActiveAlerts/Warning.png'
          WHEN 2
               THEN '/Orion/images/ActiveAlerts/Critical.png'
          WHEN 3
               THEN '/Orion/images/ActiveAlerts/Serious.png'
          WHEN 4
               THEN '/Orion/images/ActiveAlerts/Notice.png'
          ELSE '/Orion/images/ActiveAlerts/Unknown.png'
          END AS [_IconFor_Severity]
FROM Orion.AlertObjects AS [AlertObjects]
WHERE CASE 
          WHEN [AlertObjects].AlertActive.Acknowledged IS NULL
               THEN 'Unacknowledged'
          ELSE 'Acknowledged'
          END = 'Unacknowledged'
ORDER BY [AlertObjects].AlertActive.TriggeredDateTime DESC

I omitted your Custom Property connections and filters because I didn't know exactly where you were going with them.

KMSigma.SWI

Also - this query looks an awful lot like the ones used in SolarWinds Active Alert Dashboard - Modern Dashboards - The Orion Platform - THWACK.

bobmarley

Worked great!

slomond

Thanks folks this helped below is what seems to be working for me.

SELECT [AlertObjects].AlertConfigurations.Name AS [Alert Name]
     , CONCAT (
          '/Orion/View.aspx?NetObject=AAT:'
          , [AlertObjects].AlertObjectID
          ) AS [_LinkFor_Alert Name]
     , [AlertObjects].EntityCaption AS [Alert Object]
     , [AlertObjects].EntityDetailsUrl AS [_LinkFor_Alert Object]
     , [AlertObjects].RelatedNodeCaption AS [Related Node]
     , [AlertObjects].RelatedNodeDetailsUrl AS [_LinkFor_Related Node]
     , ToLocal([AlertObjects].AlertActive.TriggeredDateTime) AS [Alert Trigger Time]
     , [AlertObjects].AlertActive.TriggeredMessage AS [Alert Message]
     , CASE 
          WHEN [AlertObjects].AlertActive.Acknowledged IS NULL
               THEN 'Unacknowledged'
          ELSE 'Acknowledged'
          END AS [AckState]
     , CASE [AlertObjects].AlertConfigurations.Severity
          WHEN 0
               THEN 'Informational'
          WHEN 1
               THEN 'Warning'
          WHEN 2
               THEN 'Critical'
          WHEN 3
               THEN 'Serious'
          WHEN 4
               THEN 'Notice'
          ELSE CONCAT (
                    'Unknown Severity: '
                    , [AlertObjects].AlertConfigurations.Severity
                    )
          END AS [Severity]
     , CASE [AlertObjects].AlertConfigurations.Severity
          WHEN 0
               THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
          WHEN 1
               THEN '/Orion/images/ActiveAlerts/Warning.png'
          WHEN 2
               THEN '/Orion/images/ActiveAlerts/Critical.png'
          WHEN 3
               THEN '/Orion/images/ActiveAlerts/Serious.png'
          WHEN 4
               THEN '/Orion/images/ActiveAlerts/Notice.png'
          ELSE '/Orion/images/ActiveAlerts/Unknown.png'
          END AS [_IconFor_Severity]
FROM Orion.AlertObjects AS [AlertObjects]
WHERE CASE 
          WHEN [AlertObjects].AlertConfigurations.Name LIKE 'SL%' AND [AlertObjects].AlertActive.TriggeredMessage <> ''AND [AlertObjects].AlertActive.Acknowledged IS NULL
               THEN 'Unacknowledged'
          ELSE 'Acknowledged'
          END = 'Unacknowledged'
ORDER BY [AlertObjects].AlertActive.TriggeredDateTime DESC