Query for Muted / Unmanaged Entities

WinterSoldier

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

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