I'm currently looking at writing my own report using a custom SQL query. I came up with the following so far:
SELECT AccountName, UDT_IPAddressCurrent.IPAddress, Nodes.Caption, UDT_Port.Name FROM UDT_User JOIN UDT_UserToIPAddressCurrent ON UDT_User.UserID = UDT_UserToIPAddressCurrent.UserID JOIN UDT_IPAddressCurrent ON UDT_UserToIPAddressCurrent.IPAddress = UDT_IPAddressCurrent.IPAddress JOIN Nodes ON Nodes.NodeID = UDT_IPAddressCurrent.RouterPortID
This is far from ideal and I'm still missing information (MAC Addres and correct port number) but I'm guessing it's a start.
1 of 1 people found this helpful
I now have the following SQL query:
SELECT TOP 1000
FROM UDT_Endpoint AS ep
JOIN UDT_PortToEndpointCurrent AS pep ON ep.EndpointID = pep.EndpointID
LEFT JOIN UDT_Port AS p ON p.PortID = pep.PortID
LEFT JOIN Nodes AS n ON p.NodeID = n.NodeID
JOIN NodeL3Entries AS arp ON arp.MACAddress = ep.MACAddress
LEFT JOIN UDT_DNSNameCurrent AS dns ON dns.IPAddress = arp.IPAddress
LEFT JOIN UDT_UserToIPAddressCurrent AS u ON arp.IPAddress = u.IPAddress
LEFT JOIN UDT_User AS usr ON usr.UserID = u.UserID
p.Name NOT LIKE 'Po%'
ORDER BY n.Caption, p.Name
But I do not trust this information to be correct. For example, on 1 switch port, I see two different IP address with the same MAC address and the same user/port/... information. Thus the current SQL query does not provide the correct information that I need.
Surely there must be a more easy way to track down this information! Who can help me?