I have this query:
--838
SELECT
o.EntityCaption AS [DEVICE], '/Orion/images/StatusIcons/Small-' + p.StatusIcon as [_IconFor_DEVICE], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + tostring(p.NodeID) as [_LinkFor_DEVICE]
,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 Number]
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 (p.Caption like '%-FW%' or p.Caption like '%-CGX%' or p.Caption like '%-AP%' or p.Caption like '%-VS01%' or p.Caption like '%-Switch%') and o.AlertConfigurations.Name not like '%Store APC Self-test Failed%' and a.State not in ('Closed Complete', 'Complete') and a.IncidentUrl not like '%dev%' and a.IncidentUrl not like '%test%' or p.CustomProperties.storeVM = TRUE
ORDER by o.AlertActive.TriggeredDateTime DESC
And this query:
--report on alerts triggered for the last 30 days
select
--,ah.Message
EntityCaption as [Trigger Object]
,EntityDetailsUrl as [_linkfor_Trigger Object]
,count(ah.message) as [Alert Count]
,case
WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
End as [Parent Node]
,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]
,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]
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
WHERE
--last 30 days:
daydiff(ah.timestamp,GETUTCDATE())<30
and eventtype=0
and ac.Name = 'TCC 7x24x365 Alert'
group by name, [Trigger Object], RelatedNodeCaption
--where alert count is greater than or equal to 3
having count(ah.message) >= 3
order by [Alert Count] desc
I want to bring in the [Alert Count] column from the second query to the first.
I tried the following query, but I am getting a "mismatched input 'having' expecting 'EOF'" Error:
SELECT
o.EntityCaption AS [DEVICE], '/Orion/images/StatusIcons/Small-' + p.StatusIcon as [_IconFor_DEVICE], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + tostring(p.NodeID) as [_LinkFor_DEVICE]
,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 Number]
,count(ah.message) as [Alert Count]
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 (p.Caption like '%-FW%' or p.Caption like '%-CGX%' or p.Caption like '%-AP%' or p.Caption like '%-VS01%' or p.Caption like '%-Switch%') and o.AlertConfigurations.Name not like '%Store APC Self-test Failed%' and a.State not in ('Closed Complete', 'Complete') and a.IncidentUrl not like '%dev%' and a.IncidentUrl not like '%test%' or p.CustomProperties.storeVM = TRUE and daydiff(ah.timestamp,GETUTCDATE())<30
having count(ah.message) >= 0
ORDER by o.AlertActive.TriggeredDateTime DESC
I can't figure out what i'm doing wrong. any ideas?
