Are you familiar with joins? I didn't see the NodesCustomProperties table joined in your statement above.
Let me know if you're not and I can mock something up using a modified version of your above query.
No I'm not. Like I said, I'm very new to SQL and SWQL. Any help you can provide would be awesome.
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.
1 of 1 people found this helpful
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.
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]
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.NodesCustomProperties NCP
WHERE o.AlertActive.TriggeredMessage <> ''
AND o.AlertConfigurations.Severity = 1
--the below is an example of me limiting the results using my CUSTOMER custom property.
AND NCP.Customer like 'HAL%'
ORDER by o.AlertActive.TriggeredDateTime DESC
Sweet! Thank you. I'll have to try that and report back.