This report is for filtered (exclude Network Node) based on Device category
SELECT
N.Caption AS [Node],
N.Category As [Device Category],
N.CustomProperties.ProductionState AS [ProductionState],
N.IP_Address AS [IP Address],
N.Vendor AS [Vendor],
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],
case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left],
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.NodeUnmanaged'
)
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 AND N.Category != 1
--
UNION
(
SELECT
N.Caption AS [Node],
N.Category As [Device Category],
N.CustomProperties.ProductionState AS [ProductionState],
N.IP_Address AS [IP Address],
N.Vendor AS [Vendor],
ToString(Supp.SuppressFrom) AS [Suppressed From],
ISNULL(ToString(Supp.SuppressUntil),' - Forever - ') AS [Suppressed TO],
case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'NA'
else tostring(daydiff(getutcdate(), Supp.SuppressUntil)) end as [Days Left],
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
where N.Category != 1
--
)
