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

OK, your installation is using different IDs for those actions. Replace (102,103) in the query I provided with (55,56).

0 Kudos
patriot
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Result:

query 2.jpg

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

patriot
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

That fixed it. Thanks much friend.

m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Excellent! Glad that did it!

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

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Absolutely.

0 Kudos
mesverrum
Level 20

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
https://github.com/Mesverrum/MyPublicWork
https://www.linkedin.com/in/marcnetterfield/

If you aren't already familiar you may as well go ahead and get familiar with the API and SDK
https://github.com/solarwinds/OrionSDK
patriot
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
mesverrum
Level 20

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
https://github.com/Mesverrum/MyPublicWork
https://www.linkedin.com/in/marcnetterfield/

If you aren't already familiar you may as well go ahead and get familiar with the API and SDK
https://github.com/solarwinds/OrionSDK