This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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
    )
    

  • Thanks but did not work
    Orion.Nodes does not contain Custom Properties

    They are located in this table -
    Orion.NodesCustomProperties.AuditCategory
    I will keep digging
    Again thank you for the effort.
  • Have you tried this? Do you get any error? I think this will work. You are correct that custom properties are not in orion.nodes BUT many tables are "pre-joined", meaning that you can actually write like suggested above. Jumping from one table to another on the same line.

Reply
  • Have you tried this? Do you get any error? I think this will work. You are correct that custom properties are not in orion.nodes BUT many tables are "pre-joined", meaning that you can actually write like suggested above. Jumping from one table to another on the same line.

Children
No Data