Active alert dashboard with for specific nodes

I want to have a dashboard which shows Active alerts from a specific nodes having custom property abc. Is this is possible?If yes how can i achieve it via SQL query. I tried many queries but as we dont have any parameter common in active alerts table and node custom property table.

Any one help me on this?

Find more posts tagged with

Comments

terryk

Try this.

SELECT

o.AlertConfigurations.Name AS [ALERT NAME]
,n.Caption AS [NODE NAME]

,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]

,CASE
WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
END AS [_iconfor_ALERT NAME]

,case when o.entitycaption = o.RelatedNodeCaption then o.EntityCaption
else concat(o.RelatedNodeCaption, ' - ', o.entitycaption) end AS [ALERT OBJECT]
,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

,CASE
when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
end as [Time Active]

From Orion.AlertActive aa
join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
LEFT join Orion.Nodes n on n.nodeid=relatednodeid

-- Change City below for your CP name <abc>
where n.CustomProperties.City LIKE 'London'
AND n.Caption LIKE 'YourNode Name'

ORDER by o.AlertActive.TriggeredDateTime DESC

rrichmond

I think I'm following what is going on here, but when I try to use it I get an error.
"This tile query requires the following fields: Name, Priority, InstanceSiteId"
There a more appropriate way to implement this than the Status Summary Widget?

marlief22

Hey there,

You'll want to put this directly into a 'Custom Query' Widget, it'll do everything for you:

https://support.solarwinds.com/SuccessCenter/s/article/Use-Custom-Query-to-show-Node-Name-and-IP-Address?language=en_US

In this link above it shows the process of creating a custom query widget, just replace the example query with the one from @terryk

Kind regards,

Marlie Fancourt | SolarWinds Pre-Sales Manager

Prosperon Networks | SolarWinds Partner since 2006