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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi rajasekar​,

This query includes future scheduled unmanagement:

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 (N.Unmanaged = TRUE OR N.UnManageFrom > GETUTCDATE())
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 Current status column and  future scheduled mute also in this query.

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Sure. This query gets only current and future mute events:

Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription
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
WHERE (
ToUTC(Supp.SuppressFrom) >= TOLOCAL(GETDATE())
OR
ToUTC(Supp.SuppressFrom) <= TOLOCAL(GETDATE()) AND ToUTC(Supp.SuppressUntil) >= TOLOCAL(GETDATE())
)
ORDER BY N.Caption

This query puts the future unmanage and future mute events into a single table:

Select N.Caption, N.UnManageFrom, N.UnManageUntil, LastUnmanageEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription
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 (N.Unmanaged = TRUE OR N.UnManageFrom > GETUTCDATE()) 
UNION
(
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, LastMuteEvent.TimeLoggedUtc, AE.AccountID, N.StatusDescription
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
WHERE (
ToUTC(Supp.SuppressFrom) >= TOLOCAL(GETDATE())
OR
ToUTC(Supp.SuppressFrom) <= TOLOCAL(GETDATE()) AND ToUTC(Supp.SuppressUntil) >= TOLOCAL(GETDATE())
)
)
ORDER BY N.Caption
0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi m-milligan

Am using your query but in that some utc and account ID is empty can you suggest for this.

pastedImage_1.png

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Those are nodes that have never been unmanaged before. TimeLoggedUTC and AccountID come from Orion.AuditingEvents. If the node hasn't ever been unmanaged, it won't have a record in Orion.AuditingEvents.

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

The nodes which is not unmanaged before got unmanaged now means who done unmanage now that account ID need to come need to come.

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi m-milligan​ in the above query am getting the account ID as previously done the scheduled activity but i need the account ID that done the scheduled activity now at the last.

0 Kudos
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Do you mean you need to know the account ID that did it the previous time, not the current one?

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

I need the current account ID only but as per your script it showing the previous account ID so please do the modification and share that well help us.

0 Kudos
Highlighted
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi m-milligan

Most of the event account ID is empty and some are not showing the correct account ID who is doing unmanage.

pastedImage_0.png

0 Kudos