This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

UDT hostname query via SQL


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]


dbo.udt_FormatMACAddressForUI(E.MACAddress) as MACAddress,


P2e.LastSeen as LastSeen,


N.caption as Nodename,

P.Name as PortName,


FROM UDT_Endpoint E with(nolock)


(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