4 Replies Latest reply on Jan 23, 2020 12:19 PM by ebradford

    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



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

                    Hi wes.reneau, I have a need to create reports involving Unmanage and Mute too. I reviewed the code you posted, and made my own changes. I have some questions for you on your solution.

                     

                    First, what version of Orion are you using? We are using 2019.2 HF3 (NPM 12.5). IN our deployment, the table AlertSuppression is empty. We do have an AlertSuppression2 table, which has the "muting" information in it. What's more, our AlertSuppression table has different column headers than does AlertSuppression2 table. The Alert Suppression 2 table has columns which align with your posted solution.

                     

                    Secondly, the SQL functions tostring, tolocal, daydiff are not available in MS SQL. Are you  using Oracle perhaps?

                     

                    Lastly, in the where clauses, I noted this basic setup:

                    WHERE [asup2].[SuppressUntil] > GETUTCDATE()   
                      AND [asup2].[SuppressFrom] < GETUTCDATE() 

                     

                    I get the idea. If there was a suppression that SuppressUntil before the current date/time, then that means that the suppression would have expired. And, it seems to me that there is an assumption being made. In the Nodes.UnManageUntil field, for nodes that are unmanaged and have no end date set, the end date is set as "9999-01-01 00:00:00.000" but it seems that this is not the case with the SuppressUntil field. We have several nodes that are muted indefinitely, and one test-node I set to mute until later today. When I run following query, only the result that does not have NULL in the SuppressUntil field is the one that I set an end date for muting. All others that are muted indefinitely have NULL in the field, not 9999-01-01...

                     

                    So, why are I bringing this up? Because you also posted a revised 'where' clause. I wanted to give you some feedback on it. I appreciate that you posted at the beginning of the thread that you are a Noob. I was in the same place 18 months ago. Some may say I am an advanced Noob, LOL-- not yet a professional. I hope you receive this discouse constructively.

                     

                    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'

                     

                    Line 9, "SuppressUntil = '9999-01-01 00:00:00'", Is included in Line 3 "tolocal(SuppressUntil) > GETDATE()   " with one exception, if there were a scheduled mute which begins in the future, and has not end date, then it could be included by Line 9 but not Lines 3-5. It seems like that is not the reason why you have those lines. Further, since it appears that the default behavior for indefinite mutes is to use NULL instead of "9999/01..." Then the whole where clause could be condensed down to one of these:

                     

                    If you want only current mutes:

                    WHERE SuppressFrom < GETUTCDATE()

                    This is equivalent to Lines 1-9.

                     

                    Or, if you want to include mutes scheduled in the future, then simply use:

                    Nothing would give you all current and future mutes.

                     

                    Also, I noted that you are converting to local time at many points. If you work with UTC times, then you won't have the convert -- except in your final output. The logic which the table viewer won't see doesn't matter if it is in UTC or local time, but operating in UTC, your query will run faster and be simpler to read. When it comes to the final table others will read, then convert to UTC (or just present in UTC and let the viewer convert in his/her own mind if needed).

                     

                    Good job. You have done an impressive amount of work. Keep at it, and keep learning -- I can tell you have the mindset for scripting SQL.