Below query is the who kept the alerts muted will show the account name before version upgrade, but After version upgrade to 2025.4.1 not able to see Account name only showing system kept the alerts muted.. I have tried second query after upgrade but getting error. Could you please help with the query to display Account name who kept the alerts muted. Giving screenshot also for reference.
First Query - Before Upgrade (shows Account name)
SELECT
'Muted' as [Status]
,n.caption
,tostring(tolocal(SuppressFrom)) as [From]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(SuppressUntil )) end AS [Until]
,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, ae.AccountID AS [Account]
FROM Orion.AlertSuppression asup
join orion.nodes n on asup.entityuri=n.uri
join (
SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
FROM Orion.AuditingEvents ae
WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID
join (
SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
FROM Orion.AuditingEvents ae
WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent
order by n.caption
Second Query - After upgrade
SELECT 'Muted' as [Status] ,
n.caption ,
tostring(tolocal(SuppressFrom)) as [From] ,
case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00'
then 'Not set' else tostring(tolocal(SuppressUntil )) end AS [Until] ,
case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00'
then '-' else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left] ,
n.DetailsURL AS [_LinkFor_Node] ,
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node] ,
isnull(
case
when ae.AccountID = 'system' then
(
SELECT top 1
substring(ae2.AuditEventMessage,
indexof(ae2.AuditEventMessage,'User ') + 5,
indexof(ae2.AuditEventMessage,' assigned')
- (indexof(ae2.AuditEventMessage,'User ') + 5)
)
FROM Orion.AuditingEvents ae2
WHERE ae2.NetObjectID = n.NodeID
AND ae2.AuditEventMessage like '%assigned new entity%'
ORDER BY ae2.TimeLoggedUtc desc
)
else ae.AccountID
end,
ae.AccountID
) as [Account]
FROM Orion.AlertSuppression asup
join orion.nodes n on asup.entityuri=n.uri
join (
SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent
FROM Orion.AuditingEvents ae
WHERE ae.auditingactiontype.actiontype in
('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
group BY ae.netobjectid
) mostrecent ON mostrecent.NetObjectID = n.NodeID
join (
SELECT ae.NetObjectID,
ae.AccountID,
ae.AuditEventMessage,
ae.timeloggedutc
FROM Orion.AuditingEvents ae
WHERE ae.auditingactiontype.actiontype in
('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')
Order BY ae.TimeLoggedUtc desc
) ae ON ae.NetObjectID = n.NodeID
and ae.timeloggedutc=mostrecent.recent
order by n.caption
