2 Replies Latest reply on Feb 23, 2017 6:57 AM by rharland2012

    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!

        • Re: VLAN Devices Report - Looking to add port name or description, but...
          Steven Klassen

          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
              ) AS
          SELECT 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 PortName
          FROM
              [DBO].[Nodes] n
          INNER JOIN
              [DBO].[UDT_Port] p
          ON
              p.NodeID = n.NodeID
          INNER JOIN
              [DBO].[UDT_PortToEndpointCurrent] p2e
          ON
              p2e.PortID = p.PortID
          INNER JOIN
              [DBO].[UDT_Endpoint] e
          ON
              e.EndpointID = p2e.EndpointID
          LEFT OUTER JOIN
              [DBO].[UDT_IPAddressCurrent] ip
          ON
              ip.EndpointID = e.EndpointID
          LEFT OUTER JOIN
              [DBO].[UDT_DNSNameCurrent] dns
          ON
              dns.IPAddress = ip.IPAddress
          LEFT JOIN
              MacPrefixes m
          ON
              SUBSTRING(e.MACAddress,1,6) = m.MacPrefix
          LEFT JOIN
              Vendors v
          ON
              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 NodeID
          FROM
          UDT_VLANDevicesWithPortName INNER JOIN Nodes ON (UDT_VLANDevicesWithPortName.NodeID = Nodes.NodeID)
          WHERE 
          (
            (UDT_VLANDevicesWithPortName.VLAN = 1)
          )
          

           

          This puts the port name back in the mix:

           

          1 of 1 people found this helpful