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

Parents
  • 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

  • Yes. Excellent. This seems working. One last thing to add here. I need the timezone of local server. Can it be done?

Reply Children
  • On datetime values you can use "ToLocal(datetime)". Then it should be in the local browsers timezone.

  • --Unmanaged
    SELECT
        N.Caption AS [Node],
        DATETRUNC('minute', N.UnManageFrom) AS [Suppressed From],
        CASE
            WHEN ToString(tolocal(N.UnManageUntil))='Jan  1 9999 12:00AM' THEN ' - Forever - '
            ELSE ToString(tolocal(N.UnManageUntil))
        END AS [Suppressed TO],
        CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
        CASE
            WHEN ToString(tolocal(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(tolocal(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',tolocal(Supp.SuppressFrom)) AS [Suppressed From],
        ISNULL(ToString(tolocal(Supp.SuppressUntil)),' - Forever - ') AS [Suppressed TO],
        CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
        CASE
            WHEN tolocal(Supp.SuppressUntil) IS NULL THEN ' - Forever - '
            ELSE CONCAT(DayDiff(Supp.SuppressFROM,Supp.SuppressUntil), ' days')
        END as [Total Suppressed days],
        CASE
            WHEN tolocal(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

    Like this? I have added tolocal in few of the lines. Can you check? Sorry I am not good at SQL but trying my best.

  • Yes that will work. If you want to optimize a bit you only need to add "Tolocal" on the output values. So for example in below part of the query i removed it from the first datevalue:

    CASE
      WHEN ToString(N.UnManageUntil)='Jan 1 9999 12:00AM' THEN ' - Forever - '
      ELSE ToString(tolocal(N.UnManageUntil)) END AS [Suppressed TO],

    But your way will work, only take a ms longer :-)
  • Yes. That works. Thanks a ton for your help. :)

  • Can the "suppressed by" show ID that are from an AD group? Thanks.

  • Hi ,

    Hope you are keeping well. I was working on one of the below query for Unmanaged or Muted Nodes which is working perfectly fine.

    --Unmanaged
    SELECT
        N.Caption AS [Node],
        DATETRUNC('minute', N.UnManageFrom) AS [Suppressed From],
        CASE
            WHEN ToString(tolocal(N.UnManageUntil))='Jan  1 9999 12:00AM' THEN ' - Forever - '
            ELSE ToString(tolocal(N.UnManageUntil))
        END AS [Suppressed Till],
        CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
        CASE
            WHEN ToString(tolocal(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(tolocal(N.UnManageUntil))='Jan  1 9999 12:00AM' THEN ' - Forever - '
            ELSE CONCAT(DayDiff(GETDATE(),N.UnManageUntil), ' days')
        END as [Suppressed Days Remaining],
        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',tolocal(Supp.SuppressFrom)) AS [Suppressed From],
        ISNULL(ToString(tolocal(Supp.SuppressUntil)),' - Forever - ') AS [Suppressed TO],
        CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed Till],
        CASE
            WHEN tolocal(Supp.SuppressUntil) IS NULL THEN ' - Forever - '
            ELSE CONCAT(DayDiff(Supp.SuppressFROM,Supp.SuppressUntil), ' days')
        END as [Total Suppressed days],
        CASE
            WHEN tolocal(Supp.SuppressUntil) IS NULL THEN ' - Forever - '
            ELSE CONCAT(DayDiff(GetDate(),Supp.SuppressUntil), ' days')
        END AS [Suppressed Days Remaining],
        AE.AccountID AS [SupressedBy],
        '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 (66,67)  --  '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

    However, on the another below query which is for Unmanaged or Muted Nodes, interfaces and Applications while I execute the query it is only showing just Unmanaged Node or Interface but not any muted entities.

    SELECT
    'Unmanaged' as [Status]
    ,n.Caption AS [Node]
    ,'Node' AS [Object]
    ,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]
    , 'N/A' AS [_LinkFor_Object]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    , 'N/A' AS [_IconFor_Object]
    ,CASE
    WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
    ELSE ae.AccountID
    END AS [Account]
    FROM
    Orion.Nodes n
    FULL 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
    FULL 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.CustomProperties.Citrix_Servers = 'False'
    --and (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
     
    union all
    -- Muted Nodes
    (SELECT
    'Muted' as [Status]
    ,n.caption
    ,'Node' AS [Object]
    ,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]
    , 'N/A' AS [_LinkFor_Object]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    , 'N/A' AS [_IconFor_Object]
    , 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.ActionTypeID in ('66','67')
        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 
    -- Added to remove outdated period of muting
    tolocal(SuppressUntil) > GETDATE() 
    AND tolocal(SuppressFrom) < GETDATE() 
    -- AND  (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
    )
    UNION ALL
    -- Unmanaged Interfaces
    (SELECT
    'Unmanaged' as [Status]
    ,n.Caption AS [Node]
    ,i.interfacename as [Interface]
    ,tostring(tolocal(i.UnManageFrom)) AS [From]
    ,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
    else tostring(tolocal(i.UnManageUntil)) end AS [Until]
    ,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then '-'
    else tostring(daydiff(getutcdate(), i.unmanageuntil)) end as [Days Left]
    ,n.DetailsURL AS [_LinkFor_Node]
    ,i.DetailsURL AS [_LinkFor_Interface]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    ,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]
    ,CASE
    WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
    ELSE ae.AccountID
    END AS [Account]
    FROM
    Orion.npm.interfaces i
    left join orion.nodes n on n.nodeid=i.nodeid
    FULL JOIN (
        SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
        FROM Orion.AuditingEvents rec
        WHERE rec.auditingactiontype.actiontype = 'Orion.InterfaceUnmanaged'
        group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid
    FULL JOIN (
        SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
        FROM Orion.AuditingEvents ae
        WHERE ae.auditingactiontype.actiontype = 'Orion.InterfaceUnmanaged' ) ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent
     
    WHERE i.Status = 9 and n.status != 9
    --and (i.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
    )
    UNION ALL
    -- Muted Interfaces
    (SELECT
    'Muted' as [Status]
    ,n.Caption AS [Node]
    ,i.interfacename as [Interface]
    ,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]
    ,i.DetailsURL AS [_LinkFor_Interface]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    ,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]
    ,CASE
    WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
    ELSE ae.AccountID
    END AS [Account]
    FROM Orion.AlertSuppression asup
    join Orion.npm.interfaces I on asup.entityuri=I.Uri
    left join orion.nodes n on n.nodeid=i.nodeid
    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 = I.InterfaceID
    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 = I.InterfaceID and ae.timeloggedutc=mostrecent.recent
    where 
    n.status != 9
    -- Added to remove outdated period of muting
    AND tolocal(SuppressUntil) > GETDATE() 
    AND tolocal(SuppressFrom) < GETDATE() 
    -- AND (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
    )
    UNION ALL
    -- Unmanaged Application monitors
    (
    SELECT
    'Unmanaged' as [Status]
    ,n.Caption AS [Node]
    ,A.DisplayName as [Application]
    ,tostring(tolocal(A.UnManageFrom)) AS [From]
    ,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
    else tostring(tolocal(A.UnManageUntil)) end AS [Until]
    ,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then '-'
    else tostring(daydiff(getutcdate(), A.unmanageuntil)) end as [Days Left]
    ,n.DetailsURL AS [_LinkFor_Node]
    ,A.DetailsURL AS [_LinkFor_Application]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    ,'/Orion/images/StatusIcons/Small-' + A.StatusLED AS [_IconFor_Application]
    ,CASE
    WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
    ELSE ae.AccountID
    END AS [Account]
    FROM
    Orion.APM.Application A
    left join orion.nodes n on n.nodeid=A.nodeid
    FULL JOIN (
        SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
        FROM Orion.AuditingEvents rec
        WHERE rec.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged'
        group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = N.NodeID
    FULL JOIN (
        SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc, ae.DisplayName
        FROM Orion.AuditingEvents ae
        WHERE ae.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged' ) ae ON 
            ae.NetObjectID = N.NodeID 
            
            AND ae.timeloggedutc=mostrecent.recent
    -- In some cases NetObjectID from Orion.AuditingEvents does not hold the Application ID. So I tried this.
            AND ae.DisplayName LIKE  'User % unmanaged application '+A.DisplayName+' on node '+N.Caption
    WHERE A.Status = 9 and n.status != 9
    --and (A.DisplayName like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
    )
    UNION ALL
    -- Muted Application monitors
    (SELECT
    'Muted' as [Status]
    ,n.Caption AS [Node]
    ,A.DisplayName as [Application]
    ,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]
    ,A.DetailsURL AS [_LinkFor_Application]
    ,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
    ,'/Orion/images/StatusIcons/Small-' + A.StatusDescription + '.gif' AS [_IconFor_Application]
    ,CASE
    WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
    ELSE ae.AccountID
    END AS [Account]
    FROM Orion.AlertSuppression asup
    join Orion.APM.Application A on asup.entityuri=A.Uri
    left join orion.nodes n on n.nodeid=A.nodeid
    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 = A.ApplicationID
    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 = A.ApplicationID and ae.timeloggedutc=mostrecent.recent
    where 
    -- Added to remove outdated period of muting
    tolocal(SuppressUntil) > GETDATE() 
    AND tolocal(SuppressFrom) < GETDATE() 
    -- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
    )

    Can you please help here? Not sure what am I missing.

    Thanks in advance.

  • When troubleshooting large queries I try to take it part by part and see where we have issues. In this case I took only the muted nodes part to start with.

    In my installation it seems that [suppressUntil] was null on all rows as they had no enddate on the supression. That gives that the part "where -- Added to remove outdated period of muting SuppressUntil > GETDATE()" does not work. Giving you nothing in return.
    So remove or change that WHERE-part and you might find some entities