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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Result:

query 2.jpg

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

Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

That fixed it. Thanks much friend.

Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Excellent! Glad that did it!

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Absolutely.

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
Highlighted

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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

0 Kudos