We wanted a report that contains unmanaged devices and another report containing unmanaged interfaces along with the unmanaged from and to fields and the time it was unmanaged AND the last node note. Somewhere along the lines here it has duplicate rows and I can't figure out why. I have tried MAX function on the timestamp fields but that doesn't work, it just does't seem to do anything.
Problem: I get results that would basically be considered duplicates or out of date events. For report I only need the latest row that was unmanaged, and the latest note for the node. For interfaces i only wanted one row per interface and latest node note.
Code I am using below currently. Any ideas? Surely it's due to the joins I'm trying to do as I don't know swql very good yet but I've been running into a wall. Something I may want to do is combine unmanaged + muted devices / interfaces into one report instead of like 4 different ones too.
Interface Unmanaged
Select N.Caption, N.UnManageFrom, N.UnManageUntil, AE.TimeLoggedUtc, AE.AccountID, N.Node.DisplayName, note.Note, note.NodeID, AddHour(-7,note.TimeStamp) as [LastNoteTime]
from orion.npm.interfaces N
LEFT JOIN Orion.AuditingEvents AE ON N.NodeID = AE.NetworkNode
LEFT JOIN orion.NodeNotes note ON N.NodeID = note.NodeID
Where UnManaged = 1
Device Unmanaged
Select N.Caption, N.UnManageFrom, N.UnManageUntil, AE.TimeLoggedUtc, AE.AccountID, note.Note, note.NodeID, AddHour(-7,note.TimeStamp) as [LastNoteTime]
from orion.nodes N
LEFT JOIN Orion.AuditingEvents AE ON N.NodeID = AE.NetObjectID
LEFT JOIN orion.NodeNotes note ON N.NodeID = note.NodeID
Where UnManaged = 1 AND AE.ActionTypeID = 27
