This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report on Muted and Unmanaged Entities

FormerMember
FormerMember

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.  

Parents
  • This is the query I use for that,

    pastedImage_0.png

    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
    

  • FormerMember
    0 FormerMember in reply to mesverrum

    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.

    pastedImage_0.png

    My old Audit report demonstrates this fact.

    pastedImage_3.png

    If I try creating a datasource for the report writer using this query, I get a "Query is not Valid" error...

    pastedImage_1.png

    Am I doing something wrong?  Any additional direction you can share on this is greatly appreciated!

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

  • FormerMember
    0 FormerMember in reply to mesverrum

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

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

  • pastedImage_0.png

    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

  • This is fantastic! Much better than the report writer version I've been using emoticons_grin.png I left out the search function as there's not many nodes in the list.

    Thanks for sharing!

  • Excellent resource! Thank you

  • Hi All,

    Can we get the SQL query for Network devices (EffectiveCategory =1) muted status only.

  • Hi,

    Is there any way to get the Node Notes included in a column as well?

Reply Children
  • 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

  • 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?

  • I just tried it and it worked in my environment as is, what version of orion are you using?  Did you put this into a custom query resource or somewhere else?

    Shouldnt need to make changes as I had commented out the search box parts, but in hindsight you might want to use a left join for the nodenotes table that way it shows the ones that didn't get notes as well.

  • Sorry my bad. I placed it in a Custom Table resource instead of a Custom Query.

    It is working now. Thanks!

    EDIT: Does it only show UnManaged or Muted nodes if they have a Note added?

    I've noticed that this query doesn't display the same results as the previous one in the thread.

    Nodes that are muted but don't have a Note aren't being displayed.

  • Try changing the JOIN Statement to a LEFT Join:

    left join orion.nodenotes nn on nn.nodeid=n.nodeid

    That should include entries where there is a NULL Value in the Notes field.

  • 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

  • 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 emoticons_sad.png

  • 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