2 Replies Latest reply on Mar 13, 2013 9:17 AM by t0mmetje

    Displaying a list with User, IP and Switch port number


      Hey all,


      I finally got the User Device Tracker to show me usernames when I click on an IP address. Now when I click on a switch to watch the node details, I see a section displaying the port details. It contains the following information:


      • Port Number
      • Host Name
      • IP Address
      • MAC Address
      • VLAN ID


      I would like to have a sixth column displaying the user who logged in on that port using the given IP address.


      Also, once I have this information, I would like to export it to an excel sheet or a csv file. Any thoughts on how to do this most easily?


      Thanks id advance.


      Kind regards,


        • Re: Displaying a list with User, IP and Switch port number

          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.

            • Re: Displaying a list with User, IP and Switch port number

              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?

              1 of 1 people found this helpful