Report on Muted and Unmanaged Entities

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.

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.

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

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

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?

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()

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 NodesSELECT'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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROMOrion.Nodes nFULL 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.NodeIDFULL 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 asupjoin orion.nodes n on asup.entityuri=n.urijoin ( 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.NodeIDjoin ( 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.recentwhere -- Added to remove outdated period of mutingtolocal(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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROMOrion.npm.interfaces ileft join orion.nodes n on n.nodeid=i.nodeidFULL 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.interfaceidFULL 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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROM Orion.AlertSuppression asupjoin Orion.npm.interfaces I on asup.entityuri=I.Urileft join orion.nodes n on n.nodeid=i.nodeidjoin ( 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.InterfaceIDjoin ( 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.recentwhere n.status != 9-- Added to remove outdated period of mutingAND 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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROMOrion.APM.Application Aleft join orion.nodes n on n.nodeid=A.nodeidFULL 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.NodeIDFULL 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.CaptionWHERE 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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROM Orion.AlertSuppression asupjoin Orion.APM.Application A on asup.entityuri=A.Urileft join orion.nodes n on n.nodeid=A.nodeidjoin ( 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.ApplicationIDjoin ( 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.recentwhere -- Added to remove outdated period of mutingtolocal(SuppressUntil) > GETDATE() AND tolocal(SuppressFrom) < GETDATE() -- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%'))

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 NodesSELECT'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],CASEWHEN ae.accountID IS NULL THEN 'Audit Log Not Found'ELSE ae.AccountIDEND AS [Account]FROMOrion.Nodes nFULL 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.NodeIDFULL 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

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.
Learn more today by joining now.