Unmanaged Muted

I have a SWQL query that I have been using for a couple of years now, which I copied for THWACK.  It lists the devices that have been muted or unmanaged.  It does just what I need.  We have placed this on our home Summary page as widget.

Now comes my issue -
I has been asked to flag device as retired and unmanage them.  I need to filter on the "retired" flag.  Only show the device are Not Retired
NOTE This has been set up -->     NodesCustomProperties.AuditCategory Value  'Retired'

Where/how in the code would I add this statement? NodesCustomProperties.AuditCategory != 'Retired'

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

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
)

Thank You for any help!

Steve

Parents Reply Children
  • SELECT [N].Caption AS [Node]
          , [N].CustomProperties.AuditCategory
          , 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 AS [N]
    LEFT OUTER JOIN (
          SELECT AE.NetObjectID
                , Max(AE.AuditEventID) AS [AuditEventID]
                , Max(AE.TimeLoggedUtc) AS [TimeLoggedUtc]
          FROM Orion.AuditingEvents AS [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 AS [AE]
          ON AE.AuditEventID = LastMuteEvents.AuditEventid
    WHERE [N].UnManaged = 1
          AND IsNull([N].CustomProperties.AuditCategory, '') NOT LIKE '%Retired%'
    
    UNION
    
    (
          SELECT [N].Caption AS [Node]
                , [N].CustomProperties.AuditCategory
                , 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 AS [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 AS [AE]
                WHERE ActionTypeID IN (
                            SELECT ActionTypeID
                            FROM Orion.AuditingActionTypes
                            WHERE ActionType IN (
                                        'Orion.AlertSuppressionAdded'
                                        , 'Orion.AlertSuppressionChanged'
                                        )
                            )
                GROUP BY AE.NetObjectID
                ) AS [LastMuteEvents]
                ON LastMuteEvents.NetObjectID = [N].NodeID
          LEFT OUTER JOIN Orion.AuditingEvents AS [AE]
                ON AE.AuditEventID = LastMuteEvents.AuditEventid
          WHERE IsNull([N].CustomProperties.AuditCategory, '') NOT LIKE '%Retired%'
          )
    

    This snippet works, but has a definite limitation: It doesn't look at Unmanaged/Muted Interfaces, Applications, Groups, or Volumes.  As long as you know this is for Nodes only, then you are good.

    The issue (previous) is that we are hitting the NULL isn't a [string] issue.  This was gotten around by forcibly casting the Custom Property value to an empty string.

  • I feel like this would be a nice "sidebar" element on a Modern Dashboard, but que sera sera. Smirk

  • It worked Thank You -
    This will help So much going forward with "retirement" of device from the environment!
    Thanks You Again

  • I tweaked it a little bit (to make the management of the Custom Properties easier to understand) and added it to the Custom Queries Content Exchange as Unmanaged & Muted Nodes (with a Node Custom Property Filter] - Custom Queries - The Orion Platform