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