cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
rajasekar
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
m-milligan
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
rajasekar
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
trilobite_rex
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
mesverrum
Level 20

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

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
rajasekar
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
trilobite_rex
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Thank you so much!  That did the trick.

0 Kudos
mfanderson01
Level 7

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

M,

      first thing thank you for sharing your query. I am having an issue with the join statement on the alert suppression table. where are you getting the N.Uri part? I do not see anything with Uri in my nodes table. In fact not finding anything to link the two tables at all

thanks

0 Kudos
paul326
Level 7

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

This is excellent stuff, especially since I'm a n00b with SQL queries. I've been trying to add line to exclude specific N.Caption names to exclude nodes we don't want reported (items to manage but not alert, 100% of the time muted)

This query gets me exactly what I need except excluding caption name that contains 'TST' Any help appreciated.

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

Results:

NAP-DMZSW-022018-08-22 16:52:46.58NULL2018-08-22 16:52:46.987
NAP-VLNX-FAZ-012018-08-12 01:28:20.327NULL2018-08-12 01:28:20.78
pla-esx6-vmw-02.eby-brown.com2018-06-21 16:25:09.177NULL2018-06-21 16:25:09.55
ROC-FS-C3560CX-SW12018-07-15 00:52:08.097NULL2018-07-15 00:52:08.55
TST-V2K3-VPK-012018-09-10 20:54:46.093NULL2018-09-10 20:54:46.39
TST-V2K3-VPK-022018-04-07 21:59:17.117NULL2018-04-07 21:59:17.553
TST-V2K3-VPK-032018-04-07 21:59:17.553NULL2018-04-07 21:59:18.007
TST-V2K3-VPK-042018-04-07 21:59:18.007NULL2018-04-07 21:59:18.443

want to exclude anything with 'TST' in the name.

Thanks,

Paul

0 Kudos