Displaying muted devices

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

Parents
  • This is excellent.  I am looking to do exactly the same thing, but for a limited subset of devices.  How would i leverage a custom property to display only devices that have this property, instead of all devices in the environment?

  • Not exactly the same query as above but one I use in a widget. Probably stole it from someone here on THWACK.... :-)
    Added a where clause in two places to check a custom property. Change it to whatever you want to filter on.

    SELECT
        N.Caption AS [Node],
        ToString(N.UnManageFrom) AS [Suppressed From],
        CASE
            WHEN ToString(N.UnManageUntil)='Jan  1 9999 12:00AM' THEN ' - Forever - '
            ELSE ToString(N.UnManageUntil)
        END AS [Suppressed TO],
        CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
        N.DetailsUrl AS [_LinkFor_Node],
        --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
        CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
        AE.AccountID AS [Supressed By],
        'Unmanaged' AS [Type]
    
    FROM Orion.Nodes N
    LEFT OUTER JOIN (
            SELECT 
                AE.NetObjectID, 
                Max(AE.AuditEventID) as [AuditEventID], 
                Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]   
            FROM Orion.AuditingEvents AE
            where ActionTypeID in 
                (   
                SELECT ActionTypeID   
                FROM Orion.AuditingActionTypes   
                where 
                    ActionType = 'Orion.AlertSuppressionAdded'   
                    or ActionType = 'Orion.AlertSuppressionChanged'   
                )   
            group by AE.NetObjectID 
    ) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
    LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
    
    WHERE
        N.UnManaged=1
        AND N.CustomProperties.YourCP='thevalue'
    
    UNION
    (
    SELECT
        N.Caption AS [Node],
        ToString(Supp.SuppressFrom) AS [Suppressed From],
        ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
        CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
        N.DetailsUrl AS [_LinkFor_Node],
        --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
        CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
        AE.AccountID AS [Supressed By],
        'Muted' AS [Type]
    
    FROM Orion.Nodes N
    INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri=N.Uri
    LEFT OUTER JOIN (
            SELECT 
                AE.NetObjectID, 
                Max(AE.AuditEventID) as [AuditEventID], 
                Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]   
            FROM Orion.AuditingEvents AE
            where ActionTypeID in 
                (   
                SELECT ActionTypeID   
                FROM Orion.AuditingActionTypes   
                where 
                    ActionType = 'Orion.AlertSuppressionAdded'   
                    or ActionType = 'Orion.AlertSuppressionChanged'   
                )   
            group by AE.NetObjectID 
    ) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
    LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
    WHERE N.CustomProperties.YourCP='thevalue'
    )

Reply
  • Not exactly the same query as above but one I use in a widget. Probably stole it from someone here on THWACK.... :-)
    Added a where clause in two places to check a custom property. Change it to whatever you want to filter on.

    SELECT
        N.Caption AS [Node],
        ToString(N.UnManageFrom) AS [Suppressed From],
        CASE
            WHEN ToString(N.UnManageUntil)='Jan  1 9999 12:00AM' THEN ' - Forever - '
            ELSE ToString(N.UnManageUntil)
        END AS [Suppressed TO],
        CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
        N.DetailsUrl AS [_LinkFor_Node],
        --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
        CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
        AE.AccountID AS [Supressed By],
        'Unmanaged' AS [Type]
    
    FROM Orion.Nodes N
    LEFT OUTER JOIN (
            SELECT 
                AE.NetObjectID, 
                Max(AE.AuditEventID) as [AuditEventID], 
                Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]   
            FROM Orion.AuditingEvents AE
            where ActionTypeID in 
                (   
                SELECT ActionTypeID   
                FROM Orion.AuditingActionTypes   
                where 
                    ActionType = 'Orion.AlertSuppressionAdded'   
                    or ActionType = 'Orion.AlertSuppressionChanged'   
                )   
            group by AE.NetObjectID 
    ) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
    LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
    
    WHERE
        N.UnManaged=1
        AND N.CustomProperties.YourCP='thevalue'
    
    UNION
    (
    SELECT
        N.Caption AS [Node],
        ToString(Supp.SuppressFrom) AS [Suppressed From],
        ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
        CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
        N.DetailsUrl AS [_LinkFor_Node],
        --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
        CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
        AE.AccountID AS [Supressed By],
        'Muted' AS [Type]
    
    FROM Orion.Nodes N
    INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri=N.Uri
    LEFT OUTER JOIN (
            SELECT 
                AE.NetObjectID, 
                Max(AE.AuditEventID) as [AuditEventID], 
                Max(AE.TimeLoggedUtc) as [TimeLoggedUtc]   
            FROM Orion.AuditingEvents AE
            where ActionTypeID in 
                (   
                SELECT ActionTypeID   
                FROM Orion.AuditingActionTypes   
                where 
                    ActionType = 'Orion.AlertSuppressionAdded'   
                    or ActionType = 'Orion.AlertSuppressionChanged'   
                )   
            group by AE.NetObjectID 
    ) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
    LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
    WHERE N.CustomProperties.YourCP='thevalue'
    )

Children
No Data