Report showing user who unmanaged elements and muted alerts

I have found a few threads on a similar subject, but nothing that is exactly what I want. I need a report that shows the following in a single query, or in 2 separate queries if necessary:

1. Nodes CURRENTLY Unmanaged showing when they were unmanaged, when they will be re-managed if available, and WHO did it.

2. List nodes that currently have their alerts muted, also along with who muted the alerts and when.

In each case, I need to be able to filter the report by using a particular node custom property.

Will this be a custom SWQL or SQL report? I cannot see how to include all the needed fields using Manage Reports.

Thanks for any help.

  • 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 
  • Thanks for the help m-milligan. I used the SWQL in a custom query resource - one e3ach for unmanaged nodes and muted alerts and am not getting the expected results. I unmanaged three nodes, but only two of them show in the query results. I muted alerts on three nodes and the query is empty. Any ideas how to troubleshoot? Thanks again.

  • I can't reproduce the discrepancy in muted nodes. Can you check the number of currently muted nodes with this query? It will exclude any muted items that are not nodes.

    SELECT ID, EntityUri, SuppressFrom, SuppressUntil
    FROM Orion.AlertSuppression Supp
    where Supp.EntityUri like '%NodeID%' and Supp.EntityUri not like '%NodeID%/%'

    I'll continue looking at the unmanaged nodes query.

  • That query produces an error in the resource. Are there any characters out of place or mis-typed by any chance?

  • OK, this query will get you all of the unmanaged nodes. The original query didn't account for nodes that have been unmanaged for a very long time - long enough that the original unmanage event has been purged from Orion.AuditEvents.

    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 

  • I had a clause in the query that apparently doesn't play nicely with custom query resources. I've corrected my query. Use this:

    SELECT ID, EntityUri, SuppressFrom, SuppressUntil 

    FROM Orion.AlertSuppression Supp 

    where Supp.EntityUri like '%NodeID%' and Supp.EntityUri not like '%NodeID%/%'

  • Thanks for the update. I removed the 'not like' part at the end of the query and find that it shows the expected nodes (which is all I am interested in for now). Now, how do I include the node name in the muted alerts query?

  • This includes the node name (N.Caption):

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

    Edited 2019-05-31 to ensure that only mute events on ​nodes ​are considered.

    Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.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]

    FROM Orion.AuditingEvents    

    where ActionTypeID in (   

    SELECT ActionTypeID   

    FROM Orion.AuditingActionTypes   

    where ActionType = 'Orion.AlertSuppressionAdded'   

    or ActionType = 'Orion.AlertSuppressionChanged'

    )

    and NetObjectType='N'    

    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   

  • Well... something changed again because going from the brief 3 line query:

    SELECT ID, EntityUri, SuppressFrom, SuppressUntil

    FROM Orion.AlertSuppression Supp

    where Supp.EntityUri like '%NodeID%'

    which works to what you have above... yields a blank result again.

  • How about this query? This will also get you the currently muted nodes, etc.

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

    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