Reporting device moves

jfanter-stlcc

I am currently trialing UDT 2.5.1 and am looking to develop a report that shows all the devices (or be filtered by OUI) that have been moved, disconnected, or added in the last 24 hours.  We are looking to track our VoIP phones with this.

Is this possible with the current release and how would I do this?

cvachovecj

Hi,

Here's what our engineering says:

Attached is a sample query how to get all l2/l3 endpoint moves in the last 24 hours, this should give the idea how to make the report.

However the question is whether to show just first or last move, or all of them (which may generate quite a big report), whether to show l2 or l3 endpoints, or both.

-- layer2select E.MACAddress, P.Name, N.Caption as Node, P2E.FirstSeen, P2e.LastSeen from UDT_Endpoint Einner join UDT_PortToEndpoint P2E on P2E.EndpointID = E.EndpointIDinner join UDT_Port P on P.PortID = P2E.PortIDinner join Nodes N on N.NodeID = P.NodeIDwhere DATEDIFF(hour, isnull(P2E.LastSeen, GETUTCDATE()), GETUTCDATE()) < 24order by N.Caption, P.Name, E.MACAddress-- layer3select IP.IPAddress, ISNULL(P.Name, '') as Port, N.Caption as Node, IP.FirstSeen, IP.LastSeenfrom UDT_IPAddress IPinner join UDT_Endpoint E on E.EndpointID = IP.EndpointIDinner join Nodes N on N.NodeID = IP.RouterNodeIDleft outer join UDT_Port P on P.PortID = IP.RouterPortIDwhere IP.EndpointID not in (select EndpointID from UDT_PortToEndpoint)and DATEDIFF(hour, isnull(IP.LastSeen, GETUTCDATE()), GETUTCDATE()) < 24order by N.Caption, P.Name, IP.IPAddress

Regards,

Jiri