This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Query for Muted / Unmanaged Entities

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

  • First, I think you should upgrade.
    Second, you can try this one:

    --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],
        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 = 27
            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],
        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 (102,103)  --  '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

  • Hi,

    Thank you for sharing it. Yes I am planning for an upgrade. Meanwhile this came up as a requirement.

    Tried with your query but seems there's something missing. It doesn't show up the accountID in Suppressed by. Its blank.

    Also, I could see it is not calculating the suppress duration properly. Unfortunately I am not able to attach the screenshot here.

  • Ok, understand. I have seen that in some installations the ID's for "actionTypeID" are not the same. Might be that you have other numbers in your installation. Run below to make sure your id's are the same as I had in this installation:

    SELECT ActionTypeID, ActionType, ActionTypeDisplayName FROM Orion.AuditingActionTypes WHERE ActionType IN ('Orion.NodeUnmanaged','Orion.AlertSuppressionAdded','Orion.AlertSuppressionChanged' )
  • I ran the query and got the below output.

    Action TypeID ActionType ActionTypeDisplayName
    34 Orion.NodeUnmanaged Node unmanaged
    64 Orion.AlertSuppressionAdded Alerts muted
    65 Orion.AlertSuppressionChanged

    Schedule for muting alerts changed

  • Hi 

    I have used below query and got the output as mentioned in the screenshot below. It seems there's something wrong in query leading to incorrect Suppressed days. 

    --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],
        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
            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],
        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

  • Great!!
    Suppressed days in not how many days it will be suppressed but how many days it has already been suppressed. 

  • Oh okay. Can I you help me modifying the query to get the output for how many days it will be suppressed considering the screenshot I shared above please?

  • ok, do you want "suppressed for x more days" or "suppress for x days in total"?
    In case there is no end date, the value will be null

  • Please help me with the following.

    1. If I am suppressing for next x days (3 days) then it should show me Suppressed Until as X days.

    2. Is it possible to keep the existing query and append point 1? I mean, it has two columns, one for Suppressed days which shows since how long it has been suppressed and one for Suppressed until which shows till how many days it has been suppressed.

    4. If I am simply clicking on Mute Now that means there is no end date. In that case I am expecting null, you can name it as 'Not set'.

    3. I also found some glitch may be a bug. When I tried to mute alert for lets say 10 minutes, then after 10 minutes of suppressed window, it should get disappear from this custom widget. However, it was still visible. Later I manually unmuted it and it got cleared.

  • 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