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

Thank you so much Chris.

Code is Poetry.

Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

I only get unamanged nodes when I use your query.  Not muted items.

2017.3.4 SP4

unmanged_nodes2.png

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

Same here! used the last query here, removed 2 Application sections, added Order by at the end and used without Search option. I only see Unmanaged nodes in Custom query as well as SWQL studios.

I am on 12.4 2018.4 HF2

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

Finally got back to this and I now have muted and unmanaged.  I added an OR to capture those nodes w/ suppressuntil unset.  This is a tweak on the previous version supplied.  Your mileage may vary...

OR

    --Added to capture those nodes where the suppress until time is not set

    SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

Full SWQL follows

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


--added by WR

where  

    -- Added to remove outdated period of muting 

    tolocal(SuppressUntil) > GETDATE()  

AND

    tolocal(SuppressFrom) < GETDATE()  

    -- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%') 

OR

    --Added to capture those nodes where the suppress until time is not set

    SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

)



ORDER BY [node] asc, [status] desc





-- The following is what is used to search the results, it'll go in the Search SWQL Query pane on the custom query widget


'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



0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

Thanks Wes!

The query works in SWQL studio but not in Web report writer. it says query is invalid. What could be causing that?

Also is there a way to also add a column which shows the entity type whether it is a node or interface that is unmanaged/muted? And a condition to use custom properties table?

This will really be appreciated!

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

This is SWQL only.  If I'm not mistaken Report Writer is SQL only.

Also, the rumor is that Report Writer is being depreciated, at least I think I read that someplace. 


Thanks

Wes

0 Kudos
Highlighted
Level 9

Re: Report on Muted and Unmanaged Entities

Jump to solution

Hi Wes,

I might have confused you by saying report writer but I meant that I used the web report writer front end with the choice of SWQL and it failed. When I pasted in SWQL studio it worked...

Hope you get some time to review this.

Not sure if others are able to see all 4 categories:

Unmanaged Nodes

Unmanaged Interfaces

Muted Nodes

Muted Interfaces

Ability to see the date/time stamp

Ability to use custom property condition for a particular suport group, etc

This will be an amazing report if working successfully.

Thanks in advance Wes!

0 Kudos
Highlighted
Level 17

Re: Report on Muted and Unmanaged Entities

Jump to solution

I've been using this resource, but noticed recently that devices that are no longer muted still show up in the report even after the the time has gone by and the nodes are no longer muted.  Was there a change that would have caused this?  Is there a way to update it to only show items where Days Left is greater than 0?

maintenance.PNG

0 Kudos
Highlighted

Re: Report on Muted and Unmanaged Entities

Jump to solution

I noticed that too, there are scenarios where previous unmanage events just sit in the DB forever, and other cases where they get deleted.  The example GRTA posted has logic to avoid showing the stale ones, where suppressuntil > getdate()

- Marc Netterfield, Github
0 Kudos
Highlighted
Level 17

Re: Report on Muted and Unmanaged Entities

Jump to solution

Thanks for pointing that out.  Didn't see that.  Adding that was just what I needed.

0 Kudos