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

I have modified the query only for Windows device (incase if someone is looking for any certain Vendor related report). Added extra columns from DB & removed few unwanted one's.

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node],n.ip_address,n.machinetype

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

,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.machinetype like'%Windows%' or n.machinetype like'%VMware%'

Level 9

There are so many different variations of this above, and some work for me and some do not, but the ones that work do not give all the information I need.  What I am looking for is a report, that gives all unmanged and muted nodes and interfaces and the time range they are muted or unmanged for and also the user ID who muted or unmanged the device or interface.  Some work with the search box for me some do not.  I am pretty SQL dumb so if someone could post what would be needed to perform this in both the Custom SWQL query box and the Search SWQL query box to be able to do this it would be extremely helpful for me.  Any help would truly be appreciated.

0 Kudos

look at the one that grta​ posted above, it shows unmanaged and muted for various object types, to include the ones you are asking about. If it doesn't work its probably because you don't have SAM, if that's the case you should just be able to remove the application monitor sections from the query and get Nodes and Interfaces

0 Kudos
Level 7

Hi,

I am using below queries 😕

FIRST BOX

SELECT [N].Caption as [Element]
     , [N].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
     -- , [N].IP_Address as [IP]
     -- , [N].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute From]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.Nodes AS [N]
  ON [N].Uri = [Alerts].EntityURI

UNION

( SELECT [I].FullName as [Element]
     , [I].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
     -- , [I].IP_Address as [IP]
     -- , [I].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute from]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.NPM.Interfaces AS [I]
  ON [I].Uri = [Alerts].EntityURI

)

SECOND BOX

SELECT [N].Caption as [Element]
     , [N].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
     -- , [N].IP_Address as [IP]
     -- , [N].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute From]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.Nodes AS [N]
  ON [N].Uri = [Alerts].EntityURI

WHERE [Element] LIKE '%${SEARCH_STRING}%'

UNION

( SELECT [I].FullName as [Element]
     , [I].DetailsURL as [_LinkFor_Element]
     , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
     -- , [I].IP_Address as [IP]
     -- , [I].DetailsURL as [_LinkFor_IP]
     , [Alerts].SuppressFrom as [Mute from]
     , [Alerts].SuppressUntil as [Mute Until]
FROM Orion.AlertSuppression AS [Alerts]
JOIN Orion.NPM.Interfaces AS [I]
  ON [I].Uri = [Alerts].EntityURI

WHERE [Element] LIKE '%${SEARCH_STRING}%'
)

What modification I need to do in this query to have account name also in Dashboard.

0 Kudos
Level 8

I tried this, and the columns appear but the data is blank? I did a copy/paste and did not change anything else.

0 Kudos

Fixed, the view had a limitation set so I removed the limitation and the table populated.

0 Kudos
Level 8

My Apologies for bringing this thread back to life.  Is it possible to manipulate the query to correctly ORDER BY the 'FROM' column?  I want to view the data from newest to oldest.  If I ORDER BY [from] desc'  It does the ordering alphabetically, and not by the Date.  It makes sense as to why, but I have no clue how to go about formatting the FROM column as a datetime.

IF this is not possible is there another field I can pull in to accomplish what i am looking for?

I am using a mix of all of the above queries.

The values in questions are in bold

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 

FULL 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 

FULL 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 

ORDER BY  [from] desc, [status] asc, [node] asc

pastedImage_0.png

0 Kudos

might be fine to just change

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

to

,tolocal(n.UnManageFrom) AS [From]

- Marc Netterfield, Github
Level 17

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

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

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

0 Kudos
Level 8

Hi,

I added interfaces and application monitors in the original report.

To show all 3 types in one report I added column "Object" with shows Node for nodes or the interface or application monitor name.

Here the updated SWQL query:

-- Unmanaged Nodes
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,'Node' AS [Object]
,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]
, 'N/A' AS [_LinkFor_Object]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, 'N/A' AS [_IconFor_Object]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.Nodes n
FULL 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
FULL 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
-- Muted Nodes
(SELECT
'Muted' as [Status]
,n.caption
,'Node' AS [Object]
,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]
, 'N/A' AS [_LinkFor_Object]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, 'N/A' AS [_IconFor_Object]
, 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
-- Added to remove outdated period of muting
tolocal(SuppressUntil) > GETDATE()
AND tolocal(SuppressFrom) < GETDATE()
-- AND  (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
)
UNION ALL
-- Unmanaged Interfaces
(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
FULL 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
FULL 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
--and (i.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
)
UNION ALL
-- Muted Interfaces
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,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]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END 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.InterfaceID
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.InterfaceID and ae.timeloggedutc=mostrecent.recent
where
n.status != 9
-- Added to remove outdated period of muting
AND tolocal(SuppressUntil) > GETDATE()
AND tolocal(SuppressFrom) < GETDATE()
-- AND (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')
)
UNION ALL
-- Unmanaged Application monitors
(
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,A.DisplayName as [Application]
,tostring(tolocal(A.UnManageFrom)) AS [From]
,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(A.UnManageUntil)) end AS [Until]
,case when A.UnManageUntil is null or A.UnManageUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), A.unmanageuntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,A.DetailsURL AS [_LinkFor_Application]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + A.StatusLED AS [_IconFor_Application]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.APM.Application A
left join orion.nodes n on n.nodeid=A.nodeid
FULL JOIN (
    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
    FROM Orion.AuditingEvents rec
    WHERE rec.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged'
    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = N.NodeID
FULL JOIN (
    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc, ae.DisplayName
    FROM Orion.AuditingEvents ae
    WHERE ae.auditingactiontype.actiontype = 'Orion.APM.ApplicationUnmanaged' ) ae ON
        ae.NetObjectID = N.NodeID
       
        AND ae.timeloggedutc=mostrecent.recent
-- In some cases NetObjectID from Orion.AuditingEvents does not hold the Application ID. So I tried this.
        AND ae.DisplayName LIKE  'User % unmanaged application '+A.DisplayName+' on node '+N.Caption
WHERE A.Status = 9 and n.status != 9
--and (A.DisplayName like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')
)
UNION ALL
-- Muted Application monitors
(SELECT
'Muted' as [Status]
,n.Caption AS [Node]
,A.DisplayName as [Application]
,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]
,A.DetailsURL AS [_LinkFor_Application]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + A.StatusDescription + '.gif' AS [_IconFor_Application]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM Orion.AlertSuppression asup
join Orion.APM.Application A on asup.entityuri=A.Uri
left join orion.nodes n on n.nodeid=A.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 = A.ApplicationID
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 = A.ApplicationID and ae.timeloggedutc=mostrecent.recent
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}%')
)

Here the result in SWQL Studio:

pastedImage_4.png

Regards,

Ralph

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

2017.3.4 SP4

unmanged_nodes2.png

0 Kudos

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



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

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

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

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

Such a beautiful resource!

Can a SQL genius add a custom property call Customer for this to the query?

I worship your Epicness! Coding is an art, a talent, something many of us mere mortals lack unfortunately.

0 Kudos

This can be accomplished by adding in the appropriate custom property in the where statement, below is a modified one using your example as a node custom property called customer with a value of Test. I didn't test it for the interfaces and the applications portion, but because it's still referencing the node table you should still be able to insert it to their corresponding where statements and recreate it.

-- Unmanaged Nodes
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,'Node' AS [Object]
,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]
,n.DetailsURL AS [_LinkFor_Object]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Object]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.Nodes n
FULL 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
FULL 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.customproperties.customer = 'Test'
0 Kudos