Unmanaged Muted

If I am understanding your question you just need the 'retired' nodes for custom property name AuditCategoryValue? This may work. 

Select IP_Address, Caption, NodeID, AuditCategoryValue FROM Orion.NodesCustomProperties cp INNER JOIN Orion.nodes nd on cp.NodeID = nd.NodeID

If I am understanding your question you just need the 'retired' nodes for custom property name AuditCategoryValue? This may work. 

Select IP_Address, Caption, NodeID, AuditCategoryValue FROM Orion.NodesCustomProperties cp INNER JOIN Orion.nodes nd on cp.NodeID = nd.NodeID

Yes that is correct but I would like to put this filter on the current report see above
I do not want the "Retired" nodes to be listed in the report

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.

Thanks I try now

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

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