Report on Muted and Unmanaged Entities

This is the query I use for that,

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

Thanks for this report.  By the way, when I try it out, I get "Query is not valid" errors.  I tried SWQL and SQL but it still fails.  Thoughts???

Thanks again...

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:

Regards,

Ralph

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?

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

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

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.

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.

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%'