I'm having a bit of trouble with this SWQL query I'm writing. Currently, it's displaying active alerts that have a ServiceNow ticket attached to it with other information (some custom properties, who it's assigned to, the node name, etc.)
Where i'm having trouble is pulling in the alert count for the last 30 days. I'm using the COUNT function on the message in the alert history table. It only displays a 1, no matter what I try. I think it's because it's looking for orion events with a status of 2 or 3 (active). since active alerts only post one message when it's active, we will only get a 1 each time.
What am I doing wrong?!?! Is there a work around for this? Thank you all in advance!
Here is my query:
SELECT
o.EntityCaption AS [OBJECT], '/Orion/images/StatusIcons/Small-' + p.StatusIcon as [_IconFor_OBJECT], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + tostring(p.NodeID) as [_LinkFor_OBJECT]
,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
--Time Active--
,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]
--End time active--
,c.TimeZone as [TIME ZONE]
, c.State
,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
,a.AssignedTo AS [OWNER]
, a.IncidentNumber as [Incident], 'domain.service-now.com/incident.do?sys_id=' + tostring(a.IncidentID) + '&sysparm_record_target=incident&sysparm_record_row=2&sysparm_record_rows=568&sysparm_record_list=123TEXTQUERY321%3Dc0925%5EORDERBYDESCsys_created_on' +' target="_blank"' as [_LinkFor_Incident]
,count(ah.message) as [Alert Count Last 30 Days]
From Orion.AlertActive aa
join orion.ServiceNow.AlertIncident a on a.AlertObjectID = aa.AlertObjectID
join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
LEFT join Orion.Nodes p on p.nodeid=relatednodeid
join Orion.NodesCustomProperties c on c.NodeID = p.NodeID
left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)
where daydiff(ah.timestamp,GETUTCDATE())<30 and (p.Caption like '%-FW%' or p.Caption like '%-CGX%' or p.Caption like '%-AP%' or p.Caption like '%-VS01%' or p.Caption like '%-Switch%' or c.storeVM = True)
and a.State not in ('Closed Complete', 'Complete') and a.IncidentUrl not like '%dev%' and a.IncidentUrl not like '%test%' and p.UnManaged = False
group by [OBJECT]
--having count(ah.message) >= 0
ORDER by o.AlertActive.TriggeredDateTime DESC
