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

Report on Muted and Unmanaged Entities

Jump to solution

I'm looking for an SQL/SWQL query or Report that will  show all muted and/or unmanaged entities in Orion with from and to dates and the user name that has made this configuration.  Currently I have two separated reports. One provides all the audit events for unmanaged and muted entities for the last year.  The other report identifies all unmanaged nodes, interfaces and applications from Orion.  What is really needed is a way to create a single view / join to take the current unmanaged / muted inventory an add the user detail from audit to output.  If this report exists already somewhere in Orion, or if someone has solved this already please point me in the right direction.  It would seem that a report showing all muted entities and by whom. would be something all Orion Admins would find beneficial.  Thanks!  As a side note, my current unmanaged device and application report has my entities grouped by the custom field Department.  

1 Solution

This is the query I use for that,

pastedImage_0.png

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

and (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

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

where (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

)

ORDER BY [node] asc, [status] desc

- Marc Netterfield, Github

View solution in original post

78 Replies

First off, THANK YOU for doing this - You're awesome! Second....I can't seem to get it to return any results, even though I know I have individual interfaces in unmanaged mode

0 Kudos

I noticed his edit of my query is keying into the actiontype, but those id's are not always the same in all environments, it depends which modules you have installed.

You may want to check your orion.auditingevents table to confirm what the correct actiontypeid is for your environment.

This is the section of code I'm referring to

JOIN (

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

    FROM Orion.AuditingEvents rec

    WHERE rec.actiontypeid = 33

    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid

JOIN (

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

    FROM Orion.AuditingEvents ae

    WHERE ae.actiontypeid = 33 ) ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent

- Marc Netterfield, Github

I changed mine to:

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

    FROM Orion.AuditingEvents rec

    WHERE rec.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged'

    GROUP BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid

JOIN (SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

    FROM Orion.AuditingEvents ae

    WHERE ae.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged') ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent

Hopefully, this will mean it can work, regardless of the modules installed as it's using the name, not the unique ID.

- David Smith
0 Kudos

Thanks! That works beaut.

Is there a way to show Muted Interfaces as well? This is the query I'm using but it only shows unmanaged interfaces.

Thanks!

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node]

,i.interfacename as [Interface]

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

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

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

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

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

,n.DetailsURL AS [_LinkFor_Node]

,i.DetailsURL AS [_LinkFor_Interface]

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

,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]

,CASE

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

ELSE ae.AccountID

END AS [Account]

FROM

Orion.npm.interfaces i

join orion.nodes n on n.nodeid=i.nodeid

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

    FROM Orion.AuditingEvents rec 

    WHERE rec.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged' 

    GROUP BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid 

JOIN (SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc 

    FROM Orion.AuditingEvents ae 

    WHERE ae.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged') ae ON

ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent 

WHERE i.Status = 9 and n.status != 9

union all

(SELECT

'Muted' as [Status]

,n.caption

,i.interfacename as [Interface]

,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]

,i.DetailsURL AS [_LinkFor_Interface]

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

,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]

, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup

join orion.npm.interfaces i on asup.entityuri=i.uri

join orion.nodes n on n.nodeid=i.nodeid

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 = i.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 = i.NodeID and ae.timeloggedutc=mostrecent.recent

)

ORDER BY [node] asc, [status] desc

I have this same issue as well.  I can see unmanaged interfaces, but not muted interfaces.

John Handberg
0 Kudos

Yep that's how I do them as well

- Marc Netterfield, Github
0 Kudos

That was it, thanks!

0 Kudos

For some reason I'm getting an invalid query error. FYI I am not using a search box.

Is there anything that needs editing before this can be used?

0 Kudos

I just tried it and it worked in my environment as is, what version of orion are you using?  Did you put this into a custom query resource or somewhere else?

Shouldnt need to make changes as I had commented out the search box parts, but in hindsight you might want to use a left join for the nodenotes table that way it shows the ones that didn't get notes as well.

- Marc Netterfield, Github

Sorry my bad. I placed it in a Custom Table resource instead of a Custom Query.

It is working now. Thanks!

EDIT: Does it only show UnManaged or Muted nodes if they have a Note added?

I've noticed that this query doesn't display the same results as the previous one in the thread.

Nodes that are muted but don't have a Note aren't being displayed.

0 Kudos

Try changing the JOIN Statement to a LEFT Join:

left join orion.nodenotes nn on nn.nodeid=n.nodeid

That should include entries where there is a NULL Value in the Notes field.

- David Smith

That did it. Thanks!

Hi All,

Can we get the SQL query for Network devices (EffectiveCategory =1) muted status only.

0 Kudos

Excellent resource! Thank you

0 Kudos

This is fantastic! Much better than the report writer version I've been using I left out the search function as there's not many nodes in the list.

Thanks for sharing!

0 Kudos

Super, thanks a lot! I wll definietly give this a shot!

Typo disclaimer: Sent from my Samsung tablet

Greg Forsthoefel

Gforsthoefel@roomstogo.com

Home / Office: 941-493-1300

Mobile: 941-705-1387

0 Kudos