Report showing user who unmanaged elements and muted alerts

This SWQL query* will get you a list of all nodes that are currently unmanaged, the unmanage start time, when the node will be re-managed, the most recent occasion when someone unmanaged the node, and the account ID of the user who did it:

Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID  
from Orion.Nodes N   
LEFT OUTER JOIN (  
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]  
FROM Orion.AuditingEvents   
where ActionTypeID=
(
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where ActionType = 'Orion.NodeUnmanaged'
)   
group by NetObjectID   
) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID  
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID  
Where Unmanaged = TRUE   
ORDER BY N.Caption  

Edited 2018-05-03 to account for variation in Orion.AuditingEvents.ActionTypeID across installations.

Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID  
from Orion.Nodes N   
LEFT OUTER JOIN (  
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]  
FROM Orion.AuditingEvents   
where ActionTypeID=
(
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where ActionType = 'Orion.NodeUnmanaged'
)   
group by NetObjectID   
) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID  
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID  
Where Unmanaged = TRUE   
ORDER BY N.Caption  

Dear team,

I need one query to see the future scheduled unmange activity along with the present unmanage schedule activity

This is excellent stuff, especially since I'm a n00b with SQL queries. I've been trying to add line to exclude specific N.Caption names to exclude nodes we don't want reported (items to manage but not alert, 100% of the time muted)

This query gets me exactly what I need except excluding caption name that contains 'TST' Any help appreciated.

Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID    

from Orion.Nodes N

INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri    

LEFT OUTER JOIN (    

SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]    

FROM Orion.AuditingEvents  

where ActionTypeID in (   

SELECT ActionTypeID   

FROM Orion.AuditingActionTypes   

where ActionType = 'Orion.AlertSuppressionAdded'   

or ActionType = 'Orion.AlertSuppressionChanged'   

)

group by NetObjectID    

) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID    

LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID    

ORDER BY N.Caption

Results:

want to exclude anything with 'TST' in the name.

Thanks,

Paul

NICE!

Thanks for sharing your queries.

However, I noticed that the user account ID showing is incorrect if the scenario is:

1. User1 muted node1.

2. User2 Unmute node1.

3. User2 muted node1 again - this time, the account ID column is still showing User1 not User2.

Even if User2 unmute and mute the node multiple times, the User1 is still showing instead of the current user who muted the alert (User2).

is this another bug in mute alert feature?

Thanks in advance!

Building on this original post and the great work by @m-milligan I added some. I used it to add a custom query resource to a customer summary page. This shows devices in maintenance mode, suppressed from/to, the latest note, and the timestamp that note was added.

It took some work to get only the latest note to show up (the MAX timestamp area).

SELECT

N.Caption as [Node],

SuppressFrom,

SuppressUntil,

NN.Note as [Note],

NN.TimeStamp,

n.DetailsURL AS [_LinkFor_Node],

'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

FROM

(select Nodeid, MAX(Timestamp) as "Timestamp"

from Orion.NodeNotes

group by Nodeid) X

JOIN Orion.NodeNotes NN on X.Nodeid=NN.NodeID

and NN.TimeStamp=X.Timestamp

JOIN Orion.Nodes N

on N.Nodeid=NN.NodeID

JOIN Orion.AlertSuppression oa

on oa.EntityUri=N.Uri

where EntityUri not like '%/Interfaces%'

order by Timestamp desc

Hope this helps somebody. It was tough to be sure.

I may make this an original post afterwards due to how much has changed from OPs post.

Thanks all for the inspiration! Built some more on this code:

SELECT

    N.Caption AS [Node],

    ToString(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('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],

    N.DetailsUrl AS [_LinkFor_Node],

    --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,

    CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],

    AE.AccountID AS [Supressed By],

    'Unmanaged' AS [Type]

FROM Orion.Nodes N

LEFT OUTER JOIN (

        SELECT

            AE.NetObjectID,

            Max(AE.AuditEventID) as [AuditEventID],

            Max(AE.TimeLoggedUtc) as [TimeLoggedUtc] 

        FROM Orion.AuditingEvents AE

        where ActionTypeID in

            ( 

            SELECT ActionTypeID 

            FROM Orion.AuditingActionTypes 

            where

                ActionType = 'Orion.AlertSuppressionAdded' 

                or ActionType = 'Orion.AlertSuppressionChanged' 

            ) 

        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

(

SELECT

    N.Caption AS [Node],

    ToString(Supp.SuppressFrom) AS [Suppressed From],

    ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],

    CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],

    N.DetailsUrl AS [_LinkFor_Node],

    --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,

    CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],

    AE.AccountID AS [Supressed By],

    'Muted' AS [Type]

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

            Max(AE.TimeLoggedUtc) as [TimeLoggedUtc] 

        FROM Orion.AuditingEvents AE

        where ActionTypeID in

            ( 

            SELECT ActionTypeID 

            FROM Orion.AuditingActionTypes 

            where

                ActionType = 'Orion.AlertSuppressionAdded' 

                or ActionType = 'Orion.AlertSuppressionChanged' 

            ) 

        group by AE.NetObjectID

) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID

LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid

)