Hi
I found this in one of the questions but I would like to be able to run the query with hostnames as well to report on. Sorry I am a newby to SQL queries.
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