I wanted a small simple widget that anyone in the organization and use to see if a node is muted/unmanaged along with the start & end times. Natively, users can see that a node is muted/unmanaged but they had no way of knowing for how long. This query was my first step in bridging that gap.
The widget shows who suppressed the node, the suppression type and the start/end times.
Here's the SWQL query
SELECT
CASE
when s.ScheduleTask.AccountID is NULL then ''
ELSE s.ScheduleTask.AccountID
END as [Supressed By]
, CASE
WHEN n.UnManaged != 'False' then 'Unmanaged'
ELSE ''
END as [Unmanaged]
, CASE
WHEN n.UnManaged <> 'False' then TOSTRING(TOLOCAL(n.UnManageFrom))
ELSE ''
END as [Start Unmanage]
, CASE
WHEN n.UnManaged <> 'False' then TOSTRING(TOLOCAL(n.UnManageUntil))
ELSE ''
END as [End Unmanage]
, CASE
when z.ID like '%' then 'Muted'
ELSE ''
END as [Muted]
, CASE
when z.SuppressFrom is NULL then ''
ELSE TOSTRING(TOLOCAL(z.SuppressFrom))
End AS [Start Mute]
, CASE
WHEN z.SuppressUntil is NULL then ''
ELSE TOSTRING(TOLOCAL(z.SuppressUntil))
End AS [End Mute]
FROM Orion.Nodes AS n
left join Orion.ScheduleEntityAssignment s on n.uri = s.EntityUri
left JOIN orion.AlertSuppression z on N.Uri = Z.EntityUri
WHERE NodeID=${nodeid}
and (s.ScheduleTask.Enabled = 'TRUE'
and (s.ScheduleTask.NextRun is NOT NULL)
or (n.UnManaged != 'False')
or (z.ID like '%'))