3 Replies Latest reply on Dec 3, 2019 8:18 AM by wes.reneau

    Custom Query to list muted and unmanaged nodes with details

    wes.reneau

      I'm expounding on an original request I made here.

       

      I want to list the status, node name, from - until and who muted/unmanaged (not in screen shot). 

      I've come dangerously close by using the query written by grta on this post.  The problem I'm having is that the Muted nodes are still showing up in the query despite their "until" times expiring.  I used the snipped of code that @grta mentioned in their original reply but when doing so I only get Unmanaged nodes.  I do have muted nodes to test with, I set one indefinitely prior to running this query. 

       

      The code in question is, you'll see that I added it to the 2nd select.

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

       

       

       

      The query I'm using is as follows:

       

      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

       

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

       

      )

      ORDER BY [node] asc, [status] desc

       

       

       

      In closing, I'm a SWQL noob, cobbling together bits and pieces of items to fashion what I want...

       

      Thanks

      Wes

        • Re: Custom Query to list muted and unmanaged nodes with details
          foonly

          Unmanaged show up fine, but I'm not seeing muted nodes.

            • Re: Custom Query to list muted and unmanaged nodes with details
              wes.reneau

              I dialed back my query to focus on muted nodes, specifically those w/o a ending date/time and I've got this.  This does not address the unmanaged nodes, those can be captured w/ a SQL query using the following " Status = '9'"  It doesn't get you the details you'd probably want but it works in a pinch until the custom query is fixed up.

               

              The key was adding the "or" in the where clause.  Forgive my formatting but it makes it easier for me to read.

               

               

               

              -- This version suppresses nodes that "suppress untill" time has past.  I'm not sure why they show up

              -- despite the time having already expired.  But the WHERE clause prevents them from showing up.

              -- Now able to see the nodes that have no expiration date for mutes.  Accomplished this by adding

              -- OR SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

              -- to the WHERE clause.

               

               

              SELECT

                      -- ONODES is the alias is for Orion.Nodes table

                      -- OALERTSUPPRESS is the alias for Orion.AlertSuppression table

               

                      ONODES.caption AS [Device], ONODES.DetailsURL AS [_LinkFor_Device],

                      '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Device],

                      tostring(tolocal(SuppressFrom)) AS [Supress From],

                    

                      CASE

                          WHEN SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

                          THEN 'Not set'

                      ELSE

                          tostring(tolocal(SuppressUntil )) END AS [Supress Until],

                      CASE

                          WHEN SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

                          THEN '-'

                      ELSE

                          tostring(daydiff(getutcdate(), OALERTSUPPRESS.SuppressUntil)) END AS [Days Left]

                   

               

               

              FROM

                      Orion.AlertSuppression OALERTSUPPRESS

               

              JOIN

                      Orion.nodes ONODES ON ONODES.uri = OALERTSUPPRESS.EntityURI

               

              WHERE

                      -- The end date (SuppressUntil) is GREATER than todays date (GETDATE)

                      -- This hides the nodes that have until dates that occur in the past

                      -- apprently this is an issue as these dates dont always purge

                      tolocal(SuppressUntil) > GETDATE()

              AND

                      tolocal(SuppressFrom) < GETDATE()

              OR

                      SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

               

              ORDER BY [SuppressUntil] ASC

              • Re: Custom Query to list muted and unmanaged nodes with details
                wes.reneau

                I went back and edited the query that does unmanaged and unmuted and added the same OR clause and it appears to work.  SWQL follows.  Your mileage may vary...

                 

                 

                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


                --added by WR

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

                OR

                    --Added to capture those nodes where the suppress until time is not set

                    SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

                )



                ORDER BY [node] asc, [status] desc





                -- The following is whats used to search the results, it'll go in the Search SWQL Query pane on the custom query widget


                '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