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

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

Will do.

Thanks eky!

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

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

I can use the script above, but i need to filter this out on a nodes customproperty.

How do i do this? example

dbo.nodes.city='Goodcity'