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