Hello,
We are on 2020.2.6 and I noticed that SWQL query which was working before for non administrative users stopped. Query to show devices which are in Unmanaged or Muted state with information about person who added them. It is strange as I could pull data from those tables - Orion.AuditingEvents, Orion.Nodes, Orion.AlertSuppression separately and they displayed info for non admin users but situation changes where there are joins..
SELECT
'Unmanaged' as [Status]
,n.Caption AS [Node]
,tostring(tolocal(n.UnManageFrom)) AS [From]
,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'
else tostring(tolocal(n.UnManageUntil)) end AS [Until]
,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'
else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]
,n.DetailsURL AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
,CASE
WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'
ELSE ae.AccountID
END AS [Account]
FROM
Orion.Nodes n
JOIN (
SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent
FROM Orion.AuditingEvents rec
WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'
group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID
JOIN (
SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc
FROM Orion.AuditingEvents ae
WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent
WHERE n.Status = 9
union all
(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 [node] asc, [status] desc
Same goes with History of Unmanaged device on Node page
SELECT Audit.TimeLoggedUtc as "Time_Logged"
,ActionType.ActionTypeDisplayName as "Action"
,Audit.AccountID as "User"
,node.Caption as "Node"
,Node.IP_Address as "IP"
,Node.UnManageFrom as "FromTime"
,Node.UnManageUntil as "UntilTime"
FROM Orion.AuditingEvents as Audit
LEFT JOIN Orion.AuditingActionTypes As ActionType
ON Audit.ActionTypeID = ActionType.ActionTypeID
RIGHT JOIN Orion.Nodes AS Node
ON Audit.NetworkNode = Node.NodeID
WHERE
(ActionTypeID =27
or ActionTypeID = 28
or ActionTypeID = 78
or ActionTypeID = 80)
AND
Node.NodeID = ${NodeID}
ORDER BY Audit.TimeLoggedUtc desc
