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

    Displaying a list with User, IP and Switch port number

    t0mmetje

      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,

      Tom

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

          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
              t0mmetje

              I now have the following SQL query:

              SELECT TOP 1000

                  n.Caption,

                  p.Name,

                  usr.AccountName,

                  dns.DNSName,

                  arp.IPAddress,

                  ep.MACAddress,

                  pep.VlanID,

                  p.PortDescription

              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

              WHERE

                  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