24 Replies Latest reply on Nov 12, 2018 9:48 AM by mesverrum

    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

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

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