cancel
Showing results for 
Search instead for 
Did you mean: 
m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
m-milligan
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
rajasekar
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Dear team,

I need one query to see the future scheduled unmange activity along with the present unmanage schedule activity

0 Kudos
m-milligan
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
rajasekar
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
rajasekar
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
rajasekar
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
m-milligan
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
m-milligan
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
rajasekar
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