VLAN Devices Report - Looking to add port name or description, but...

rharland2012

Here's the error message I get:

I see a similar thread from Deltona​ from some time back with a similar error - unfortunately the result he got is not what I'm after.

SQL is as follows (I moved the portname line up a bit while trying different things, but still errors):

SELECT  TOP 10000 UDT_VLANDevices.IPAddress AS IPAddress,

UDT_VLANDevices.HostName AS HostName,

UDT_VLANDevices.VendorIcon AS VendorIcon,

UDT_VLANDevices.MACVendor AS MACVendor,

UDT_VLANDevices.MACAddress AS MACAddress,

UDT_VLANDevices.ConnectedTo AS ConnectedTo,

UDT_VLANDevices.PortName AS PortName,

UDT_VLANDevices.VLAN AS VLAN,

Nodes.NodeID AS NodeID

FROM

UDT_VLANDevices INNER JOIN Nodes ON (UDT_VLANDevices.NodeID = Nodes.NodeID)

WHERE 

(

  (UDT_VLANDevices.VLAN = 1)

)

The report's basically perfect - I just want to get the port name or number in on the action.

This almost seems broken, but I don't dig into SQL often enough to be efficient - so I thought I would call on my fellow UDT users.

As a carrot, there's an unused Thwackpack for the kind soul who might supply a way to get this report with the port name, number, or description into the report without error.

Thanks, all!

mrxinu

You're using a view called UDT_VLANDevices that doesn't have a PortName column. I made a copy of the view (below) that I changed slightly that makes your query work again:

CREATE VIEW    UDT_VLANDevicesWithPortName    (        IPAddress,        HostName,        MACAddress,        VLAN,        ConnectedTo,        NodeID,        MACVendor,        VendorIcon,        PortName    ) ASSELECT DISTINCT    ip.IPAddress,    dns.DNSName                                     AS HostName,    [DBO].[udt_FormatMACAddressForUI](e.MACAddress) AS MACAddress,    CASE p2e.VlanID        WHEN 0        THEN NULL        ELSE p2e.VlanID    END       AS VLAN,    n.Caption AS ConnectedTo,    n.NodeID,    ISNULL(m.Vendor,m.Organization) AS MACVendor ,    ISNULL(v.Icon,'Unknown.gif')    AS VendorIcon,    p.Name AS PortNameFROM    [DBO].[Nodes] nINNER JOIN    [DBO].[UDT_Port] pON    p.NodeID = n.NodeIDINNER JOIN    [DBO].[UDT_PortToEndpointCurrent] p2eON    p2e.PortID = p.PortIDINNER JOIN    [DBO].[UDT_Endpoint] eON    e.EndpointID = p2e.EndpointIDLEFT OUTER JOIN    [DBO].[UDT_IPAddressCurrent] ipON    ip.EndpointID = e.EndpointIDLEFT OUTER JOIN    [DBO].[UDT_DNSNameCurrent] dnsON    dns.IPAddress = ip.IPAddressLEFT JOIN    MacPrefixes mON    SUBSTRING(e.MACAddress,1,6) = m.MacPrefixLEFT JOIN    Vendors vON    m.Vendor = v.Name ;

Your query updated to use this new view:

SELECT  TOP 10000 UDT_VLANDevicesWithPortName.IPAddress AS IPAddress,UDT_VLANDevicesWithPortName.HostName AS HostName,UDT_VLANDevicesWithPortName.VendorIcon AS VendorIcon,UDT_VLANDevicesWithPortName.MACVendor AS MACVendor,UDT_VLANDevicesWithPortName.MACAddress AS MACAddress,UDT_VLANDevicesWithPortName.ConnectedTo AS ConnectedTo,UDT_VLANDevicesWithPortName.PortName AS PortName,UDT_VLANDevicesWithPortName.VLAN AS VLAN,Nodes.NodeID AS NodeIDFROMUDT_VLANDevicesWithPortName INNER JOIN Nodes ON (UDT_VLANDevicesWithPortName.NodeID = Nodes.NodeID)WHERE (  (UDT_VLANDevicesWithPortName.VLAN = 1))

This puts the port name back in the mix: