0 Replies Latest reply on Sep 24, 2019 6:41 AM by fraser-sky

    Displaying muted devices

    fraser-sky

      Hi All

       

      I have created a Maintenance Tab and it displays all nodes and interfaces that have been Muted and it works when They are manually set to mute.

      We now have an automatic process that mutes devices and it looks like my sql query is missing these.

       

      I got the sql query from a post on Thwack forums : Re: Report on Muted and Unmanaged Entities

      I have changed it a little to suite what I was after:

      SELECT

      'Muted' as [Status]

      ,n.Caption AS [Node]

      ,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]

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

       

       

      Looking at the Orion.AlertSuppression table and the  column for entityuri, The devices that have been manually set are being displayed from this query are created by account name of the Main server :

      swis://wpappta0.bskyb.com/Orion/Orion

       

      The automatic process which does mute devices but does not display is being set from a different account but is still in the entityuri column :

      swis://api.solarwinds.bskyb.com/Orion/Orion

       

      If anybody can assist with this

       

      Thanks

       

      Fraser