SQL Help, Alert History filtered by custom property

Question

I could use some assistance on this. I'm pretty rusty on doing anything SQL, and have never really done joins, and I don't have much experience manually digging around the SolarWinds database. I'm looking to create a report that shows the last 30 (or maybe 90?) days of alerts with for a specific client. We have a custom property for the client name. I was able to get a query to pull most of what I need from the Alert History View, but I want the report to show the time the alert triggered, when it was acknowledged, and ideally when it reset. That's where I'm getting stuck. That info isn't in the AlertHistoryView so I'm assuming I need to another table. The AlertActive table seems to have the data I need and the AlertActiveID appears to link the two but I'm apparently missing something.

SELECT ah.EventTypeWord, ah.Message, ah.TimeStamp, ah.RelatedNodeCaption FROM AlertHistoryView ah

INNER JOIN NodesCustomProperties ncp

ON ah.RelatedNodeID =ncp.NodeID

WHERE ncp.CustomerName = 'Client Name' AND ah.EventTypeWord = 'Triggered' AND ah.TimeStamp >= DATEADD(month,-1,GETDATE())

ORDER BY ah.TimeStamp

But when I add a second join, I get no results.

INNER JOIN AlertActive aa

ON ah.AlertActiveID=aa.AlertActiveID

If I manually search for a specific AlertActiveID number in both tables, I get the data I would expect, so what am I not understanding about doing multiple joins? Or am I completely approaching this the wrong way entirely?

mesverrum · Accepted Answer

Use this in a custom query resource in the web console, sounds like it has everything you are looking for

--report on alerts triggered 


select ac.Name
,ah.Message
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]
,EntityCaption as [Trigger Object]
,EntityDetailsUrl as [_linkfor_Trigger Object]
,case 
WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
End as [Parent Node]
,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]
,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
,case when ack.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,ack.timestamp))
end as [Minutes Until Acknowledged]
,ack.Message as [Note]
,case when reset.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,reset.timestamp))
end as [Minutes Until Reset]


FROM Orion.AlertHistory ah
left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
left join Orion.Actions a on a.actionid=ah.actionid
left join Orion.Nodes p on p.nodeid=RelatedNodeID
left join (select timestamp, AlertActiveID, AlertObjectID,message from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID


WHERE 
daydiff(ah.timestamp,GETUTCDATE())<30
and ah.eventtype=0
--and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')
--and p.customproperties.customername like 'whatever'

order by ah.timestamp desc

SQL Help, Alert History filtered by custom property

Top Replies