Report on Muted and Unmanaged Entities

Question

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.

mesverrum · Accepted Answer

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

Report on Muted and Unmanaged Entities

Top Replies