SWQL select critical unacknowledged alerts

Question

Hi, im having trouble with my join statement, could anyone help me out? ive tried many ways of joining but im no expert and im missing something here

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 INNER JOIN Orion.AlertStatus ON (AlertStatus.Acknowledged = 0)

WHERE o.AlertActive.TriggeredMessage <> '' AND o.AlertConfigurations.Severity = 1

ORDER by o.AlertActive.TriggeredDateTime DESC

Thanks

mesverrum · Answer

So when I run this in my swql studio on my lab it seems to work, i just had to add the select and define the o as an alias for Orion.AlertObjects.

Unfortunately my lab is pretty freshly rebuilt and didnt have any alerts for me to confirm the criteria with but I would think instead of matching against the triggeredmessage I would use the acknowledged column like 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 INNER JOIN Orion.AlertStatus ON (AlertStatus.Acknowledged = 0)

WHERE o.AlertActive.acknowledged IS NULL AND o.AlertConfigurations.Severity = 1

ORDER by o.AlertActive.TriggeredDateTime DESC

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

*                 LinkedIN: Loop1 Systems
*                 Facebook: Loop1 Systems
*                 Twitter: @Loop1Systems

ajwill · Answer

ok this is what i look like now

FROM Orion.AlertObjects INNER JOIN Orion.AlertStatus ON Orion.AlertObjects.AlertObjectID = Orion.AlertStatus.AlertDefID

but i get error in SWQL studio "Entity Orion not found in FROM clause for navigation property Orion.AlertObjects.AlertObjectID"

below is a cap from our DB in SWQL studio, am i referencing this correctly?

Thanks

our DB is called

silverbacksays · Answer

Looking at your query, your Orion database is simply named 'Orion'?.

Generally, you need to specify:

INNER JOIN table2

ON table1.column_name=table2.column_name;

Looking at your query, you're trying to join on a static value, rather than telling SWQL which values in the two tables are equal.