69 Replies Latest reply on Oct 31, 2019 12:58 PM by Al Ma

    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

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

                    1 of 1 people found this helpful
                      • 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
                            mesverrum

                            yep you got it

                            1 of 1 people found this helpful
                              • 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

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

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

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

                                                                2 of 2 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
                                                                        cathsheh1

                                                                        this information was fantastic .... is there any way to add groups to this...to be able to monitor groups that are muted also?

                                                                        • Re: Report on Muted and Unmanaged Entities
                                                                          johnlad

                                                                          This is great and works, is there a way to add interfaces as well to this query?

                                                              • Re: Report on Muted and Unmanaged Entities
                                                                justin.benner

                                                                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

                                                                  2 of 2 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?

                                                                    • Re: Report on Muted and Unmanaged Entities
                                                                      mfortner

                                                                      My Apologies for bringing this thread back to life.  Is it possible to manipulate the query to correctly ORDER BY the 'FROM' column?  I want to view the data from newest to oldest.  If I ORDER BY [from] desc'  It does the ordering alphabetically, and not by the Date.  It makes sense as to why, but I have no clue how to go about formatting the FROM column as a datetime.

                                                                       

                                                                      IF this is not possible is there another field I can pull in to accomplish what i am looking for?

                                                                       

                                                                      I am using a mix of all of the above queries.

                                                                      The values in questions are in bold

                                                                       

                                                                      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 

                                                                      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 

                                                                      ORDER BY  [from] desc, [status] asc, [node] asc

                                                                       

                                                                      • Re: Report on Muted and Unmanaged Entities
                                                                        tsanchez11

                                                                        I tried this, and the columns appear but the data is blank? I did a copy/paste and did not change anything else.

                                                                        • Re: Report on Muted and Unmanaged Entities
                                                                          ankitaja

                                                                          Hi,

                                                                           

                                                                          I am using below queries :-\

                                                                           

                                                                          FIRST BOX

                                                                           

                                                                           

                                                                          SELECT [N].Caption as [Element]
                                                                               , [N].DetailsURL as [_LinkFor_Element]
                                                                               , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
                                                                               -- , [N].IP_Address as [IP]
                                                                               -- , [N].DetailsURL as [_LinkFor_IP]
                                                                               , [Alerts].SuppressFrom as [Mute From]
                                                                               , [Alerts].SuppressUntil as [Mute Until]
                                                                          FROM Orion.AlertSuppression AS [Alerts]
                                                                          JOIN Orion.Nodes AS [N]
                                                                            ON [N].Uri = [Alerts].EntityURI

                                                                          UNION

                                                                          ( SELECT [I].FullName as [Element]
                                                                               , [I].DetailsURL as [_LinkFor_Element]
                                                                               , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
                                                                               -- , [I].IP_Address as [IP]
                                                                               -- , [I].DetailsURL as [_LinkFor_IP]
                                                                               , [Alerts].SuppressFrom as [Mute from]
                                                                               , [Alerts].SuppressUntil as [Mute Until]
                                                                          FROM Orion.AlertSuppression AS [Alerts]
                                                                          JOIN Orion.NPM.Interfaces AS [I]
                                                                            ON [I].Uri = [Alerts].EntityURI

                                                                          )

                                                                           

                                                                           

                                                                          SECOND BOX

                                                                          SELECT [N].Caption as [Element]
                                                                               , [N].DetailsURL as [_LinkFor_Element]
                                                                               , '/Orion/images/StatusIcons/Small-' + [N].StatusIcon AS [_IconFor_Element]
                                                                               -- , [N].IP_Address as [IP]
                                                                               -- , [N].DetailsURL as [_LinkFor_IP]
                                                                               , [Alerts].SuppressFrom as [Mute From]
                                                                               , [Alerts].SuppressUntil as [Mute Until]
                                                                          FROM Orion.AlertSuppression AS [Alerts]
                                                                          JOIN Orion.Nodes AS [N]
                                                                            ON [N].Uri = [Alerts].EntityURI

                                                                          WHERE [Element] LIKE '%${SEARCH_STRING}%'

                                                                          UNION

                                                                          ( SELECT [I].FullName as [Element]
                                                                               , [I].DetailsURL as [_LinkFor_Element]
                                                                               , '/Orion/images/StatusIcons/Small-' + [I].StatusIcon AS [_IconFor_Element]
                                                                               -- , [I].IP_Address as [IP]
                                                                               -- , [I].DetailsURL as [_LinkFor_IP]
                                                                               , [Alerts].SuppressFrom as [Mute from]
                                                                               , [Alerts].SuppressUntil as [Mute Until]
                                                                          FROM Orion.AlertSuppression AS [Alerts]
                                                                          JOIN Orion.NPM.Interfaces AS [I]
                                                                            ON [I].Uri = [Alerts].EntityURI

                                                                          WHERE [Element] LIKE '%${SEARCH_STRING}%'
                                                                          )

                                                                           

                                                                          What modification I need to do in this query to have account name also in Dashboard.

                                                                          • Re: Report on Muted and Unmanaged Entities
                                                                            johnlad

                                                                            There are so many different variations of this above, and some work for me and some do not, but the ones that work do not give all the information I need.  What I am looking for is a report, that gives all unmanged and muted nodes and interfaces and the time range they are muted or unmanged for and also the user ID who muted or unmanged the device or interface.  Some work with the search box for me some do not.  I am pretty SQL dumb so if someone could post what would be needed to perform this in both the Custom SWQL query box and the Search SWQL query box to be able to do this it would be extremely helpful for me.  Any help would truly be appreciated.