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

Re: Report on Muted and Unmanaged Entities

Jump to solution

Excellent resource! Thank you

0 Kudos
Highlighted
Level 11

Re: Report on Muted and Unmanaged Entities

Jump to solution

Hi All,

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

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

Hi,

Is there any way to get the Node Notes included in a column as well?

0 Kudos

Re: Report on Muted and Unmanaged Entities

Jump to solution

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]

,nn.Note

FROM

Orion.Nodes n

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

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]

,nn.Note

FROM Orion.AlertSuppression asup

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

join orion.nodenotes nn on nn.nodeid=n.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 = 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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

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
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

That did it. Thanks!

Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

This is excellent!

I was able to reverse engineer your query and make it work for interfaces. The below query will return interfaces that are unmanaged, when the node is NOT UNmanaged.

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

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

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

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

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

--uncomment above line for search function

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

left 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

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

--uncomment above line for search function

)

ORDER BY [node] asc, [status] desc