Alert Report with Node, Application, Interface details

Question

Hi Team,

I'm new to running SWQL/SQL queries. I would like to write a SWQL/ SQL query that shows you all of the enabled alerts with alert message,severity,ObjectType,ObjectName . If it is a Node,then I need to have the following info

- Node IP Address

- Node Name

- Node Status

- Severity

and if it is an Interface

- Interface Name

- Status

- Severity

I really appreciate any help you can provide.

thank you

muhammad.osama · Answer

To give you a single query that handles both Nodes and Interfaces with their specific details, we use a LEFT JOIN on the specific object tables.

The SWQL Query

You can run this in the SWQL Studio or directly in a Custom Query widget on your dashboard.

SQL

SELECT     AC.Name AS [Alert Name],    AC.AlertMessage,    CASE         WHEN AC.Severity = 2 THEN 'Critical'        WHEN AC.Severity = 3 THEN 'Serious'        WHEN AC.Severity = 1 THEN 'Warning'        WHEN AC.Severity = 0 THEN 'Informational'        ELSE 'Notice'    END AS [Alert Severity],    AO.ObjectType,    AO.EntityCaption AS [ObjectName],        -- Node Specific Info    N.IPAddress AS [Node IP],    N.Caption AS [Node Name],    N.StatusDescription AS [Node Status],        -- Interface Specific Info    I.Caption AS [Interface Name],    I.StatusDescription AS [Interface Status]FROM Orion.AlertConfigurations ACJOIN Orion.AlertObjects AO ON AC.AlertID = AO.AlertIDLEFT JOIN Orion.Nodes N ON AO.EntityNetObjectId = N.Caption AND AO.ObjectType = 'Node'LEFT JOIN Orion.NPM.Interfaces I ON AO.EntityNetObjectId = I.Caption AND AO.ObjectType = 'Interface'WHERE AC.Enabled = 'True'

How this fits your "Evidence" Model

Since you are focusing on moving from Telemetry to Operational Context, here is how this query helps:

* Logic Isolation: By pulling StatusDescription instead of just a numeric Status ID, the query provides the interpreted state (e.g., "Up", "Down", or "Warning") immediately.
* The Object Gap: The LEFT JOIN logic ensures that if an alert is for a "Volume" or "Application," the query still runs—it just leaves the Node/Interface columns blank. This helps you identify which alerts are missing the specific "Evidence" you need.
* Severity Mapping: Instead of showing you a 2, it tells you it's Critical, reducing the cognitive load on the engineer reading the report.

Pro-Tip for your Build

If you want to make this even more "Actionable," you can add a link to the Object Details page by adding this line to your SELECT statement:

SQL

AO.EntityDetailsUrl AS [_LinkFor_ObjectName]

In a SolarWinds Custom Query widget, this will turn the Object Name into a clickable link, allowing an engineer to go from State to Action with one click.

Lofstrand · Answer

Hi @Craghuwanshi

I think below SWQL query includes all you asked for.

SELECT 

    ISNULL(ToString(AA.Acknowledged),'No') AS [Acknowledged]

    ,AA.TriggeredDateTime

	,CASE AA.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: ', AA.AlertObjects.AlertConfigurations.Severity)

	END AS [Alert Severity]

    ,AA.TriggeredMessage

    ,AA.AlertObjects.EntityCaption AS [Alerting Entity]

    ,ISNULL(N.IP_Address,'-') AS [Entity IP]

    ,CASE AA.AlertObjects.EntityType 

        WHEN 'Orion.Nodes' THEN 'Node'

        WHEN 'Orion.NPM.Interfaces' THEN 'Interface' 

     END AS [Entity Type]

    ,SI.StatusName AS [Entity Status]

FROM Orion.AlertActive AS AA

LEFT OUTER JOIN Orion.Nodes AS N on AA.AlertObjects.EntityUri=N.Uri AND AA.AlertObjects.EntityType='Orion.Nodes'

LEFT OUTER JOIN Orion.NPM.Interfaces AS I on AA.AlertObjects.EntityUri=I.Uri AND AA.AlertObjects.EntityType='Orion.NPM.Interfaces'

LEFT OUTER JOIN Orion.StatusInfo AS SI ON (N.Status=SI.StatusId AND AA.AlertObjects.EntityType='Orion.Nodes') OR (I.Status=SI.StatusId AND AA.AlertObjects.EntityType='Orion.NPM.Interfaces') 

WHERE

    AA.AlertObjects.EntityType IN ('Orion.Nodes', 'Orion.NPM.Interfaces')