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

Re: Report on Muted and Unmanaged Entities

Jump to solution

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

Re: Report on Muted and Unmanaged Entities

Jump to solution

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

Re: Report on Muted and Unmanaged Entities

Jump to solution

That was it, thanks!

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

Yep that's how I do them as well

- Marc Netterfield, Github
0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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

0 Kudos
Highlighted
Level 12

Re: Report on Muted and Unmanaged Entities

Jump to solution

How i will exclude only one node example its name abcd in the below script

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

0 Kudos
Highlighted
Level 12

Re: Report on Muted and Unmanaged Entities

Jump to solution

Can you share the script that need to show the details what are the node scheduled to unmange in future.

0 Kudos
Highlighted
Level 13

Re: Report on Muted and Unmanaged Entities

Jump to solution

Whenever I try this widget, I get rows returned of objects that I did not personally put into maintenance mode, but yet my user name is attached to them.

I'm not sure why the system thinks that my user name put multiple objects into maintenance mode when it was another engineer on another team.

I wish this widget worked for me. So useful and necessary.

Any ideas why the table has wrong user names attached to these vents?

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

You'd have to open up the tables I reference in the db and see what the raw data looks like for one of the incidents with the wrong user.  The logic it uses its first to get all the nodes that are currently unmanaged,  then it checks the audit history for the most recent event where a user unmanaged that node and it pulls up the user name from that event.  Thinking that through the only way I can imagine there would be wrong info in there is if you guys are unmanaging nodes in a way that doesn't generate an audit event.

- Marc Netterfield, Github
0 Kudos