cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
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

Accepted Solutions
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to 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
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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 
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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?

0 Kudos
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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   

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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.

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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 

0 Kudos