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

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

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

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.

As an example, this is my query for unmanaged or muted nodes

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node]

,tostring(tolocal(n.UnManageFrom)) AS [From]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(n.UnManageUntil)) end AS [Until]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

,CASE

WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'

ELSE ae.AccountID

END AS [Account]

FROM

Orion.Nodes n

JOIN (

    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

    FROM Orion.AuditingEvents rec

    WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'

    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID

JOIN (

    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

WHERE n.Status = 9

union all

(SELECT

'Muted' as [Status]

,n.caption

,tostring(tolocal(SuppressFrom)) as [From]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(SuppressUntil )) end AS [Until]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup

join orion.nodes n on asup.entityuri=n.uri

join (

    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID

join (

    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

)

ORDER BY [node] asc, [status] desc

- Marc Netterfield, Github
Highlighted
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
Highlighted
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
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Are you looking at the tables (and the query) with SWQL Studio or with a traditional SQL tool (SQL Query Analyzer, Solarwinds Database Manager)? You'll only see the Uri column if you're using SWQL Studio, because Orion.Nodes is an entity in SWIS (it's based on the Nodes view in the SolarwindsOrion database, but it's not the same as the view).

0 Kudos
Highlighted
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

OK, this query will get you all of the unmanaged nodes. The original query didn't account for nodes that have been unmanaged for a very long time - long enough that the original unmanage event has been purged from Orion.AuditEvents.

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

Dear team,

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

0 Kudos