cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

1 Solution

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 

View solution in original post

65 Replies

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.

0 Kudos

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?

0 Kudos

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

0 Kudos

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%'
0 Kudos

I get the following:

query.jpg

0 Kudos

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

0 Kudos

So as an FYI, depending how old someone's instance is, and how many modules they have installed the audit events and regular events tables will have all kinds of different numbers for eventids.  To make a query work in other people's environments I find it is 100% required to write queries with a join to the relevant types tables tables and filter based on the name strings.

As an example, this is my query for unmanaged or muted nodes

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

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

)

ORDER BY [node] asc, [status] desc

- Marc Netterfield, Github

So as an FYI, depending how old someone's instance is, and how many modules they have installed the audit events and regular events tables will have all kinds of different numbers for eventids.  To make a query work in other people's environments I find it is 100% required to write queries with a join to the relevant types tables tables and filter based on the name strings.

Who thought that was a good idea?

(Breathe)

This query will get the muted nodes regardless of what values your particular installation has for those audit event IDs:

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

This query will get the unmanaged nodes regardless of what values your particular installation has for those audit event IDs:

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 
0 Kudos

Result:

query 2.jpg

0 Kudos

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 

View solution in original post

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?

0 Kudos

I should think so. Say the name of your node custom property is "Department" and you want to filter for nodes where the Department is "Financials". You'd need to add a line like this to the WHERE clause at the end:

AND N.CustomProperties.Department = 'Financials'

So the final query would look like:

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
AND N.CustomProperties.Department = 'Financials'  
ORDER BY N.Caption

Thank you so much!  That did the trick.

0 Kudos

Also worth pointing out that if you have a lot of dashboards it is immensely easier to just apply a view limitation to the entire view instead of editing every resource on the page with the same filters.

Go to custom view and find what you need here

pastedImage_0.png

If your custom property isn't in the list then you need to run a tool on your Orion server called:

pastedImage_1.png

- Marc Netterfield, Github

That fixed it. Thanks much friend.

Excellent! Glad that did it!

0 Kudos

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?

0 Kudos

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 

0 Kudos

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

0 Kudos

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

- Marc Netterfield, Github

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?

0 Kudos