Custom SQL / SWQL for alerts history - SLA reporting

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

Thank you mesverrum  for quick response. This solves the purpose. Just need one more thing to add in both the queries.

Can you help me appending these columns?

1. Acknowledged (Yes/No), Acknowledged By in Active Alerts query.

2. For Alert SLA Query: Instead of daydiff(ah.timestamp,GETUTCDATE())<30 how can I use Between function for figuring out specific duration of alerts history please?

Thank you....

You can't use the between function for calculating durations, it is just a shorthand for saying where timestamp > A and timestamp < B, its not a math function.

My Alert SLA query has the calculations for comparing the time of the trigger event to the time of the close event to calculate the duration. Lines 19-21 cover that.

And the Acknowledged is covered there in 15-17, right now its displaying N/A or a timestamp, but if you just want it to be t/f you change the case to be False where I have the N/A and change the the else to "True"

mesverrum on your script for the Active Alerts could it be possible to reduce this from all active alerts for all nodes to active alerts on nodes within a custom property variable to allow it to be added to dashboards based on those specific custom property nodes? 

Yeah, lots of ways to make that happen.  For example you could modify the query to have something like where p.customproperties.yourproperty = 'yourvalue'

If I'm using them in dashboards i hate having to edit every query one by one so I'd most likely just apply a dashboard limitation based on the property or a group membership.