36 Replies Latest reply on Jan 31, 2019 9:02 AM by mharvey

    Report on Muted and Unmanaged Entities

    forstgre

      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.  

        • Re: Report on Muted and Unmanaged Entities
          mesverrum

          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

          4 of 4 people found this helpful
            • Re: Report on Muted and Unmanaged Entities
              forstgre

              Super, thanks a lot!  I wll definietly give this a shot!

               

              Typo disclaimer: Sent from my Samsung tablet

               

              Greg Forsthoefel

              Gforsthoefel@roomstogo.com

              Home / Office: 941-493-1300

              Mobile: 941-705-1387

              • Re: Report on Muted and Unmanaged Entities
                forstgre

                Thank you for your assistance with this effort!  This loks like exactly the kind if report we need.  However, I seem to be struggling a bit on the implementation of this.  In SWQL studio, your query returns a header line with an empty result set. Yet I know there are several nodes in a Unmanaged state.

                 

                My old Audit report demonstrates this fact.

                 

                If I try creating a datasource for the report writer using this query, I get a "Query is not Valid" error...

                 

                Am I doing something wrong?  Any additional direction you can share on this is greatly appreciated!

                  • Re: Report on Muted and Unmanaged Entities
                    mesverrum

                    This is meant to be used inside the custom query resource, all the linkfor and iconfor stuff won't work in the reportwriter.  In custom query there is a check box to make the query work with a search input, paste then query into that second box.  In the first box (this would be the version without the search input) just comment out the lines that have

                    (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

                    There are two of them as this query is a union of two separate searches.

                      • Re: Report on Muted and Unmanaged Entities
                        forstgre

                        Thanks,  I'll look for that!  Is this done by adding a widget to the Portal page and assigning this "custom query" as the content?

                         

                        Sorry for the newbie questions...

                            • Re: Report on Muted and Unmanaged Entities
                              tezdoll

                              Are you able to show a screen shot. I got the muted alerts piece to work but not unmanaged as well. I'm not a SQL guru.

                               

                              Top windows:

                              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

                              )

                              ORDER BY [node] asc, [status] desc

                               

                              Bottom Window (Search box clicked)

                              where (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')

                                • Re: Report on Muted and Unmanaged Entities
                                  mesverrum

                                   

                                  First box

                                  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

                                  )

                                  ORDER BY [node] asc, [status] desc

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                  Second box

                                   

                                  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

                                  4 of 4 people found this helpful
                                    • Re: Report on Muted and Unmanaged Entities
                                      superfly99

                                      This is fantastic! Much better than the report writer version I've been using I left out the search function as there's not many nodes in the list.

                                       

                                      Thanks for sharing!

                                      • Re: Report on Muted and Unmanaged Entities
                                        stevenstadel

                                        Excellent resource! Thank you

                                        • Re: Report on Muted and Unmanaged Entities
                                          er.vansh17091

                                          Hi All,

                                           

                                          Can we get the SQL query for Network devices (EffectiveCategory =1) muted status only.

                                          • Re: Report on Muted and Unmanaged Entities
                                            Deltona

                                            Hi,

                                             

                                            Is there any way to get the Node Notes included in a column as well?

                                              • Re: Report on Muted and Unmanaged Entities
                                                mesverrum

                                                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]

                                                ,nn.Note

                                                FROM

                                                Orion.Nodes n

                                                join orion.nodenotes nn on nn.nodeid=n.nodeid

                                                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]

                                                ,nn.Note

                                                FROM Orion.AlertSuppression asup

                                                join orion.nodes n on asup.entityuri=n.uri

                                                join orion.nodenotes nn on nn.nodeid=n.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 = 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

                                                3 of 3 people found this helpful
                                                  • Re: Report on Muted and Unmanaged Entities
                                                    Deltona

                                                    For some reason I'm getting an invalid query error. FYI I am not using a search box.

                                                    Is there anything that needs editing before this can be used?

                                                    • Re: Report on Muted and Unmanaged Entities
                                                      whomademesme

                                                      This is excellent!

                                                       

                                                      I was able to reverse engineer your query and make it work for interfaces. The below query will return interfaces that are unmanaged, when the node is NOT UNmanaged.

                                                       

                                                       

                                                       

                                                       

                                                       

                                                      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

                                                      JOIN (

                                                          SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

                                                          FROM Orion.AuditingEvents rec

                                                          WHERE rec.actiontypeid = 33

                                                          group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid

                                                      JOIN (

                                                          SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

                                                          FROM Orion.AuditingEvents ae

                                                          WHERE ae.actiontypeid = 33 ) 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}%')

                                                      --uncomment above line for search function

                                                       

                                                       

                                                       

                                                       

                                                      union all

                                                      (SELECT

                                                      'Muted' as [Status]

                                                      ,n.caption

                                                      ,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]

                                                      , ae.AccountID 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.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 = i.NodeID and ae.timeloggedutc=mostrecent.recent

                                                      --and (i.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%')

                                                      --uncomment above line for search function

                                                       

                                                      )

                                                      ORDER BY [node] asc, [status] desc

                                                      1 of 1 people found this helpful
                                                        • Re: Report on Muted and Unmanaged Entities
                                                          bhollowell

                                                          First off, THANK YOU for doing this - You're awesome! Second....I can't seem to get it to return any results, even though I know I have individual interfaces in unmanaged mode

                                                            • Re: Report on Muted and Unmanaged Entities
                                                              mesverrum

                                                              I noticed his edit of my query is keying into the actiontype, but those id's are not always the same in all environments, it depends which modules you have installed.

                                                              You may want to check your orion.auditingevents table to confirm what the correct actiontypeid is for your environment.

                                                              This is the section of code I'm referring to

                                                               

                                                              JOIN (

                                                                  SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

                                                                  FROM Orion.AuditingEvents rec

                                                                  WHERE rec.actiontypeid = 33

                                                                  group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid

                                                              JOIN (

                                                                  SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

                                                                  FROM Orion.AuditingEvents ae

                                                                  WHERE ae.actiontypeid = 33 ) ae ON ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent

                                                              1 of 1 people found this helpful
                                                                • Re: Report on Muted and Unmanaged Entities
                                                                  David Smith

                                                                  I changed mine to:

                                                                   

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

                                                                   

                                                                  Hopefully, this will mean it can work, regardless of the modules installed as it's using the name, not the unique ID.

                                                                    • Re: Report on Muted and Unmanaged Entities
                                                                      mesverrum

                                                                      Yep that's how I do them as well

                                                                      • Re: Report on Muted and Unmanaged Entities
                                                                        superfly99

                                                                        Thanks! That works beaut.

                                                                         

                                                                        Is there a way to show Muted Interfaces as well? This is the query I'm using but it only shows unmanaged interfaces.

                                                                        Thanks!

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                        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

                                                                         

                                                                         

                                                                        join orion.nodes n on n.nodeid=i.nodeid

                                                                         

                                                                         

                                                                        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 

                                                                         

                                                                         

                                                                        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

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                        union all

                                                                         

                                                                         

                                                                        (SELECT

                                                                         

                                                                         

                                                                        'Muted' as [Status]

                                                                         

                                                                         

                                                                        ,n.caption

                                                                         

                                                                         

                                                                        ,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]

                                                                         

                                                                         

                                                                        , ae.AccountID AS [Account]

                                                                         

                                                                         

                                                                         

                                                                         

                                                                         

                                                                        FROM Orion.AlertSuppression asup

                                                                         

                                                                         

                                                                        join orion.npm.interfaces i on asup.entityuri=i.uri

                                                                         

                                                                         

                                                                        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.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 = i.NodeID and ae.timeloggedutc=mostrecent.recent

                                                                         

                                                                         

                                                                         

                                                                        )

                                                                         

                                                                         

                                                                        ORDER BY [node] asc, [status] desc

                                                            • Re: Report on Muted and Unmanaged Entities
                                                              rajasekar

                                                              How i will exclude only one node example its name abcd in the below script

                                                               

                                                               

                                                              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

                                                               

                                                               

                                                              )

                                                               

                                                               

                                                              ORDER BY [node] asc, [status] desc

                                                              • Re: Report on Muted and Unmanaged Entities
                                                                rajasekar

                                                                Can you share the script that need to show the details what are the node scheduled to unmange in future.

                                                                • Re: Report on Muted and Unmanaged Entities
                                                                  hpstech

                                                                  Whenever I try this widget, I get rows returned of objects that I did not personally put into maintenance mode, but yet my user name is attached to them.

                                                                   

                                                                  I'm not sure why the system thinks that my user name put multiple objects into maintenance mode when it was another engineer on another team.

                                                                   

                                                                  I wish this widget worked for me. So useful and necessary.

                                                                   

                                                                  Any ideas why the table has wrong user names attached to these vents?

                                                                    • Re: Report on Muted and Unmanaged Entities
                                                                      mesverrum

                                                                      You'd have to open up the tables I reference in the db and see what the raw data looks like for one of the incidents with the wrong user.  The logic it uses its first to get all the nodes that are currently unmanaged,  then it checks the audit history for the most recent event where a user unmanaged that node and it pulls up the user name from that event.  Thinking that through the only way I can imagine there would be wrong info in there is if you guys are unmanaging nodes in a way that doesn't generate an audit event.

                                                        • Re: Report on Muted and Unmanaged Entities
                                                          justinb@city.ketchikan.ak.us

                                                          Thanks for sharing this!  Exactly what I needed.

                                                        • Re: Report on Muted and Unmanaged Entities
                                                          mr.e

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

                                                          • Re: Report on Muted and Unmanaged Entities
                                                            grta

                                                            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

                                                            1 of 1 people found this helpful
                                                            • Re: Report on Muted and Unmanaged Entities
                                                              mharvey

                                                              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?