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  

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    

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  

I'm getting data in the query resource now, but any idea why the last three columns are empty?

"SuppressUntil" is empty because no end time was specified when the node was muted. The node will stay muted until it's explicitly un-muted. That's the default behavior if a user just clicks Maintenance Mode - Mute Alerts Now, like this:

The other columns could be empty if the alert had been muted a long time ago and the events had since been purged from Orion.AuditEvents. However, I see the SuppressFrom dates are from today, so I'm assuming you just did these, correct?

Run this query in SWQL Studio and see if the mute events are being logged in there. That's where my query gets that data; if the events are not being logged, the TimeLoggedUtc and AccountID columns will be empty. Do you see a row with a recent timestamp for each node you muted?

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

FROM Orion.AuditingEvents  

where ActionTypeID in (102,103)  

group by NetObjectID  

If you mean to run the query in Database Manager, I did that after removing the "Orion." in front of AuditingEvents in line 2. However, there were no returned results. Not sure what that means though.

And yes, I muted some alerts and unmanaged some nodes just today for testing. I would have expected to see my user account and the timestamp for when I executed the action.

Strange.

No, I mean run the query in the SWQL Studio application. That's the SWQL equivalent to Database Manager. It should have been installed when you installed Solarwinds.

In this case, running that query in Database Manager (after removing "Orion.") should produce the same result as running it in SWQL Studio. Does the AuditingEvents table contain any rows at all? What do you get with these two queries in Database Manager?

select count('x') from AuditingEvents

select count('x') from AuditingEvents where ActionTypeID in (102,103)

What version of Solarwinds are you running?

Where is the SWQL Studio? On the Start menu on the Primary polling server?

The first query gives a result of 980. The second one a result of 0. Hmmm.

It should be on the Start menu on your main Orion poller, in the Solarwinds Orion SDK group.

OK, that suggests that Solarwinds is not logging auditing events for Alert Suppression (muting). That's why those three columns are empty in the query results - there is no matching data in the table that logs the auditing events.

When you run the query below, what do you get? I wonder if your installation has a Action Type ID for these events.

SELECT ActionTypeID, ActionType, ActionTypeDisplayName
FROM Orion.AuditingActionTypes
where ActionType like '%Suppression%'