Query for Muted / Unmanaged Entities

Question

Hi 
 
 I am trying to find out a short query that can give me the list of devices which are either muted or unmanaged along with the AccountID. I tried below query as per article Report on Muted and Unmanaged Entities . 
 
 But this works only on v2023.1. When I tried the same query on v2020.2.6 on custom query widget, it says query is not valid. 
 Can anyone please help here? 
 
 SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node]

,tostring(tolocal(n.UnManageFrom)) AS [From]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(n.UnManageUntil)) end AS [Until]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

,CASE

WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'

ELSE ae.AccountID

END AS [Account]

FROM

Orion.Nodes n

JOIN (

 SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

 FROM Orion.AuditingEvents rec

 WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'

 group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID

JOIN (

 SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

 FROM Orion.AuditingEvents ae

 WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

WHERE n.Status = 9

and (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

union all

(SELECT

'Muted' as [Status]

,n.caption

,tostring(tolocal(SuppressFrom)) as [From]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(SuppressUntil )) end AS [Until]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup

join orion.nodes n on asup.entityuri=n.uri

join (

 SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent

 FROM Orion.AuditingEvents ae

 WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

 group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID

join (

 SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

 FROM Orion.AuditingEvents ae

 WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

 Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

where (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

)

ORDER BY [node] asc, [status] desc

Seashore · Accepted Answer

Hi, I added both options. Remove the once you don't want. Change the "ActionTypeID" so it matches what you have in your system. --Unmanaged
SELECT
 N.Caption AS [Node],
 DATETRUNC('minute', N.UnManageFrom) AS [Suppressed From],
 CASE
 WHEN ToString(N.UnManageUntil)='Jan 1 9999 12:00AM' THEN ' - Forever - '
 ELSE ToString(N.UnManageUntil)
 END AS [Suppressed TO],
 CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
 CASE
 WHEN ToString(N.UnManageUntil)='Jan 1 9999 12:00AM' THEN ' - Forever - '
 ELSE CONCAT(DayDiff(N.UnManageFrom,N.UnManageUntil), ' days')
 END as [Total Suppressed days],
 CASE
 WHEN ToString(N.UnManageUntil)='Jan 1 9999 12:00AM' THEN ' - Forever - '
 ELSE CONCAT(DayDiff(GETDATE(),N.UnManageUntil), ' days')
 END as [Days until not Suppressed],
 AE.AccountID AS [Supressed By],
 'Unmanaged' AS [Type],
 CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
 N.DetailsUrl AS [_LinkFor_Node]
 --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon
FROM Orion.Nodes N
LEFT OUTER JOIN (
 SELECT
 AE.NetObjectID,
 Max(AE.AuditEventID) as [AuditEventID]
 FROM Orion.AuditingEvents AE
 where ActionTypeID = 34 -- 'Orion.NodeUnmanaged'
 group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE
 N.UnManaged=1
 

UNION

( --Muted
SELECT
 N.Caption AS [Node],
 Datetrunc('minute',Supp.SuppressFrom) AS [Suppressed From],
 ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
 CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
 CASE
 WHEN Supp.SuppressUntil IS NULL THEN ' - Forever - '
 ELSE CONCAT(DayDiff(Supp.SuppressFROM,Supp.SuppressUntil), ' days')
 END as [Total Suppressed days],
 CASE
 WHEN Supp.SuppressUntil IS NULL THEN ' - Forever - '
 ELSE CONCAT(DayDiff(GetDate(),Supp.SuppressUntil), ' days')
 END AS [Days until not Suppressed],
 AE.AccountID AS [Supressed By],
 'Muted' AS [Type],
 CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
 --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
 N.DetailsUrl AS [_LinkFor_Node]
FROM Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri=N.Uri
LEFT OUTER JOIN (
 SELECT
 AE.NetObjectID,
 Max(AE.AuditEventID) as [AuditEventID] 
 FROM Orion.AuditingEvents AE
 where ActionTypeID in (64,65) -- 'Orion.AlertSuppressionAdded' and 'Orion.AlertSuppressionChanged' 
 group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid

)
ORDER BY [Suppressed From] DESC

Query for Muted / Unmanaged Entities

Top Replies