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.

"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%'

I get the following:

OK, your installation is using different IDs for those actions. Replace (102,103) in the query I provided with (55,56).

Result:

Result:

Great. So if you run the original query to show muted nodes, but you refer to EventTypes 55 and 56, does that produce the results you expected? Here's the revised query using your EventTypeIDs:

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  

That fixed it. Thanks much friend.

Excellent! Glad that did it!

So, I assume that I could use these SWQL queries in a report as well as in the "custom query" resource on a view, correct?

Absolutely.

Oooops. Now that I look at the "unmanage" query, I see that it too does not populate the AccountID column even though it looks to me like the Join statement is there:

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=27  

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

Ideas?

The hard coded action type is not correct for your environment, the numbers change based on the order you installed modules and what version you started with as they have added additional actions in over the years.  Look at my query above to see how to look for an action by name instead of specific ID

See my reply to mesverrum​. Here's the revised query for unmanaged nodes (the revised query for muted nodes is in my reply):

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  

Thank you very much for this. Can you include the future scheduled mute also in this query.

I have a bunch of different dashboards I would like to apply this to.  Is there any way to add a filter based on a custom property?