Output of this query is showing 1 hour delay in unmanageFrom and UnmanageUntil and some time showing wrong accountID.
any one help me?????
Select N.Caption, N.UnManageFrom, N.UnManageUntil, AE.AccountID, N.StatusDescription
from Orion.Nodes N
LEFT OUTER JOIN (
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents
where ActionTypeID=
(
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where ActionType = 'Orion.NodeUnmanaged'
)
group by NetObjectID
) AS [LastUnmanageEvent] on LastUnmanageEvent.NetObjectID = N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastUnmanageEvent.AuditEventID
Where (N.Unmanaged = TRUE OR N.UnManageFrom > GETUTCDATE())
UNION
(
Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.AccountID, N.StatusDescription
from Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri
LEFT OUTER JOIN (
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID], Max(TimeLoggedUtc) as [TimeLoggedUtc]
FROM Orion.AuditingEvents
where ActionTypeID in (
SELECT ActionTypeID
FROM Orion.AuditingActionTypes
where ActionType = 'Orion.AlertSuppressionAdded'
or ActionType = 'Orion.AlertSuppressionChanged'
)
group by NetObjectID
) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID
WHERE (
ToUTC(Supp.SuppressFrom) >= TOLOCAL(GETDATE())
OR
ToUTC(Supp.SuppressFrom) <= TOLOCAL(GETDATE()) AND ToUTC(Supp.SuppressUntil) >= TOLOCAL(GETDATE())
)
)
ORDER BY N.Caption
