2 Replies Latest reply on Mar 2, 2018 1:02 PM by deverts

    Report on Unmanaged & Muted Entities (nodes and interfaces)

    alex777

      Hi all,

       

      Does anyone has an SQL Query/Report writer (need to be exportable in Excel) that list all nodes and interfaces that are unmanaged or muted in the following format? This is exactly what I want but with nodes only.

       

       

      LINK : https://thwack.solarwinds.com/threads/114752

       

      Thank you!

       

      A

        • Re: Report on Unmanaged & Muted Entities (nodes and interfaces)
          deverts

          CAVEAT: I didn't write this, and I'm not an SWQL expert! One of my peers installed the SWQL jdk and wrote this custom SWQL that reports on "unmanaged" nodes. I'm sure you (or some SWQL Thwacker) can use it as an example to take it further for all of us.

           

          D

           

           

          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 [Account] asc, [status] desc