Hi all,
Greetings..!
When I search in Thwack to create a custom report for mute and unmanaged devices , i got below script and it is working fine but is there a way that we can add device notes into this script so that it makes our life easy to understand why device has been muted and also from below query, suppressed by (Account ID ) data is not coming for all fields, what could be the reason?. Thanks in advance.
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
)
