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
  • Give this a try. Commented with --PUT YOUR CUSTOM PROPERTY HERE

    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
    --PUT YOUR CUSTOM PROPERTY HERE
    AND N.CustomProperties.AuditCategory != 'Retired'
    
    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
    )
    

Reply
  • Give this a try. Commented with --PUT YOUR CUSTOM PROPERTY HERE

    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
    --PUT YOUR CUSTOM PROPERTY HERE
    AND N.CustomProperties.AuditCategory != 'Retired'
    
    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
    )
    

Children