I have this following query:
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]
,aa.AcknowledgedBy AS [OWNER]
--ticket number here:
--end ticket number
From Orion.AlertActive aa
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 '%-AP%' or p.Caption like '%-VS01%' or p.Caption like '%-Switch%') and o.AlertConfigurations.Name not like '%Store APC Self-test Failed%'
ORDER by o.AlertActive.TriggeredDateTime DESC
I want it to display the status icon and a clickable link for DEVICE. this is what it looks like in the widget:
i'm not sure where i'm going wrong here.
can someone please point me in the right direction?
Thank you.
