Custom SQL / SWQL for alerts history - SLA reporting

Question

Hi, 
 
 I am looking for a SQL or SWQL query that helps me to extract a report of alerts history / active alerts which contains the following information: 
 
 Caption (Hostname), IP address, Object (Interface or node or any resource), Alert trigger time, Alert close time, Alert active time / Total time duration (in minutes/hours/days), Acknowledged or not, Acknowledged by, Alert name, Alert severity, Object Type (Node, Volume, Application etc). 
 
 Can anyone please help me in the above requirement?

mesverrum · Accepted Answer

It looks like I have all the things you are asking for, but they arent in a single query. This should give you enough to piece it all together how you like. 
 Active alerts 
 SELECT 
 o.AlertConfigurations.Name AS [ALERT 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] 
--,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_ALERT OBJECT] 
,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE] 
,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] 
, o.AlertIncident.IncidentNumber as Incident 
, o.AlertIncident.IncidentUrl as [_linkfor_Incident] 
 
From Orion.AlertActive aa 
join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid 
LEFT join Orion.Nodes p on p.nodeid=relatednodeid 
left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3) 
 
-- WHERE o.AlertConfigurations.Name Like '%Restart%' 
 
ORDER by o.AlertActive.TriggeredDateTime DESC 
 
 Alert SLA 
 --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}%') 
 
order by ah.timestamp desc

Custom SQL / SWQL for alerts history - SLA reporting

Top Replies