Unmanaged & Muted Nodes (with a Node Custom Property Filter]

Inspired by a comment thread from Unmanaged Muted - Report Lab - The Orion Platform - THWACK (solarwinds.com)

This is a custom query that can be put on a global summary page to show all devices which have been unmanaged or muted based AND match a specific filter.

Raw Results:

As rendered on a Summary Page:

Custom Properties Required:

Orion Nodes / AuditCategory [string]

KMSigma.SWI

swf.nfs 1 month ago

I guess count is also important how many are unmanaged or muted and for that purpose we also need to create donut widget with link of counted nodes
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
KMSigma.SWI over 1 year ago in reply to manoj_hande

I'm glad it works for you. I'm also not a SQL guy, I just play one on SolarWinds videos.
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel

manoj_hande over 1 year ago in reply to KMSigma.SWI

Thanks for reply , but this query gives me all list of nodes which are unmanaged or mute , if unmanaged node is more than 365 days then it not gives the name of user but still shows in list , in that way i found useful.

I am not SQL guy .

your posts helps a lot.

SELECT

            N.Caption AS [Node],
	        [C].Description,
            N.CustomProperties.ProductionState AS [ProductionState],
            N.IP_Address AS [IP Address],
            N.Vendor AS [Vendor],
	        N.DetailsURL AS [_LinkFor_Node],
            '/Orion/images/StatusIcons/Small-' + N.StatusLED AS [_IconFor_Node],
            N.Status,

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

case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left],

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

            )  

        group by AE.NetObjectID

) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID

LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
INNER JOIN Orion.NodeCategories AS [C]
  ON [N].Category = [C].CategoryID

WHERE

    N.UnManaged=1 

--    
  

UNION

(

SELECT

    N.Caption AS [Node],
	    [C].Description,
            N.CustomProperties.ProductionState AS [ProductionState],
            N.IP_Address AS [IP Address],
            N.Vendor AS [Vendor],
            N.DetailsURL AS [_LinkFor_Node],
            '/Orion/images/StatusIcons/Small-' + N.StatusLED AS [_IconFor_Node],
            N.Status,
	    
    ToString(Supp.SuppressFrom) AS [Suppressed From],

    ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],

case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), Supp.SuppressUntil)) end as [Days Left],

    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
INNER JOIN Orion.NodeCategories AS [C]
  ON [N].Category = [C].CategoryID



--

)

KMSigma.SWI over 1 year ago in reply to manoj_hande

Thanks for that. The more I look at this query, the less I like it. I think it's because it's based around the AuditingEvents entity and that entity has an enable/disable flag and an expiration (default 365 days). I'd almost start over and being with the AlertSuppression Table (for muted elements) and then figure out a way to bring in "unmanaged" elements.
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel

manoj_hande over 1 year ago

SELECT

    N.Caption AS [Node],
	    N.Category As [Device Category],
            N.CustomProperties.ProductionState AS [ProductionState],
            N.IP_Address AS [IP Address],
            N.Vendor AS [Vendor],
	    

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

case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left],

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

            )  

        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 

--    
  

UNION

(

SELECT

    N.Caption AS [Node],
	    N.Category As [Device Category],
            N.CustomProperties.ProductionState AS [ProductionState],
            N.IP_Address AS [IP Address],
            N.Vendor AS [Vendor],
	    
    ToString(Supp.SuppressFrom) AS [Suppressed From],

    ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],

case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), Supp.SuppressUntil)) end as [Days Left],

    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



--

)

KMSigma.SWI