Help Creating Custom Report

Question

I'm fairly new to SQL and SWQL.

I'm trying to build a custom report that run daily and capture all ACTIVE CRITICAL alerts for our production systems while ignoring certain nodes that contain certain custom properties.

We have what we label "Alert_Groups" in custom properties and for this particular report, I'm trying to ignore a certain alert group.

I found a SWQL query on here that gives me all the the critical alerts which is great but everything I'm trying is failing to ignore those nodes that fall within that certain "Alert_Group".

The SWQUL query I'm using is this:

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]

FROM Orion.AlertObjects o

WHERE o.AlertActive.TriggeredMessage <> ''

AND o.AlertConfigurations.Severity = 1 ORDER by o.AlertActive.TriggeredDateTime DESC

I've tried adding "AND" statements under the case but either the results come back blank or I get an error on the query.

Can someone please help me out?

alphabits · Answer

No worries. In the query below I put in bold the additions.

With a join you're just adding more tables to the query. There are different types of joins, but the default type used will work fine here.

Once you join the tables on a like value you can delimit the results from both.

You can also do multiple joins if you need to.

the_ben_keen · Answer

Sweet!  Thank you.  I'll have to try that and report back.

alphabits · Answer

Here is the modified version of your query with the join added, and a delimiter for a node custom property.

Be sure to put your AND statements above your ORDER BY.

Hopefully this works out for you.