5 Replies Latest reply on Sep 29, 2017 9:17 AM by bartley

    UDT report to include: MAC | hostname | IPaddress | Switchname | Port | Vlan | LastSeen

    a-phrounds

      Good afternoon.

       

      Would someone please point me in the right direction for creating a UDT report that includes:

      MAC | hostname | IPaddress | Switchname | Port | Vlan | LastSeen

      Will share result with Thwack community.

       

      Thank you very much!

      Respectfully,

      Peter Rounds

      Senior Network Engineer

      Syracuse University ITS

      phrounds@syr.edu

      (315) 443-3128

        • Re: UDT report to include: MAC | hostname | IPaddress | Switchname | Port | Vlan | LastSeen
          eky

          Hi,

          I think we are looking for same information:

          Please let me know if you succeed.

          If i Do first I will let you know.

          Newbie: Combining UDT and Interface data in an repport

              • Re: UDT report to include: MAC | hostname | IPaddress | Switchname | Port | Vlan | LastSeen
                intvlan1shut

                Oh dear god please YES!

                I would LOVE a report that does this. As it stands, the current (legacy? Report Writer) UDT reports only include a couple bits of info like the OUI Report which only lists MAC address and company. Ok but where are they? I know the information is there, it's just not in a default report and I can't for the life of me figure out how to combine (into a new user created report) things like MAC Addess, Vendor (based on OUI), IP address, Location, Switchport, Switch name, Switch IP etc

                 

                Basically, I'd like to be able to (at least to begin with, with the hope of additional functionality in the future) run a report that lists all devices on the network by

                MAC Address

                Vendor (based on MAC OUI)

                Location (perhaps based off of either the location listed in the config of the switch etc that the device is seen to be hanging off of or perhaps from a user defined list of IP ranges with a corresponding location....user defined database of IP spaces and the physical locations where they are used)

                Switchport #

                Switch or Device name that the device is hanging off of (perhaps even making this a link which can be used to go to the NPM page for said device or a link to fire up (again, user defined console app? CMD prompt or putty etc) to log right into the device.

                Ability to setup export the report in different formats, pdf, xml etc

                Ability to send the report/Log to an external server/database for further parsing etc.

                 

                Please Please Please

            • Re: UDT report to include: MAC | hostname | IPaddress | Switchname | Port | Vlan | LastSeen
              eky

              Hi,

               

              The beginning. Somebody please try to perfect this..

               

              USE [SolarWindsOrion]

              SELECT

                    dbo.udt_FormatMACAddressForUI(E.MACAddress) as MACAddress,

                    ip.IPAddress,

                    P2e.LastSeen as LastSeen,

                    P2E.VlanID,

                    N.caption as Nodename,

                    P.Name as PortName,

                    P.PortDescription

                    FROM UDT_Endpoint E with(nolock)

                    INNER JOIN

                          (select PGE.EndpointID, PGE.LastSeen, PGE.PortID, PGE.VlanID

                                from dbo.UDT_PortToEndpointHistory PGE

                                inner join (select EndpointID, max(LastSeen) as LastSeen

                                                 from dbo.UDT_PortToEndpointHistory  where ConnectionType=1

                                                 group by EndpointID

                                                 ) tm on PGE.EndpointID = tm.EndpointID and PGE.LastSeen = tm.LastSeen ) P2E

                    on (E.EndpointID =P2E.EndpointID )

                    INNER JOIN UDT_Port P with(nolock) ON P2E.PortID=P.PortID

                    INNER JOIN Nodes N with(nolock) ON P.NodeID =N.NodeID

                    LEFT JOIN MacPrefixes M on SUBSTRING(E.MACAddress,1,6) = M.MacPrefix

                  LEFT JOIN Vendors V on M.Vendor =V.Name

                  left join ( select distinct  IPAddress ,EndpointID from dbo.UDT_IPAddress ) IP on e.EndpointID=ip.EndpointID