2 Replies Latest reply on Jan 19, 2016 8:48 AM by netjcp

    SWSQL Assistance report writer

    netjcp

      Hi All,

       

      I'm looking for some assistance with multiple joins,

      I've created an inventory report for circuits. and I am able to get all the information I need from nodes and interfaces, however, I am trying to also get the IP address of the interface in my report and that is where I am having problems. I believe I need to join Interfaces.InterfaceID and IPAddresses.InterfaceID...

       

      Can anyone help.. what I have below works fine w/out the IP address of the interface....

       

      SELECT

      Nodes.City AS City, Nodes.Address_all, Nodes.Caption AS Node_Name, IfName AS IfName,  Nodes.MachineType AS Machine_Type, Interfaces.InterfaceSpeed AS Interface_Speed, Interfaces.Carrier AS Carrier, Interfaces.XX_Corp_Circuit_id AS XX_Corp_Circuit_id, Interfaces.Carrier_Web_Contact AS Carrier_Web_Contact, Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID

       

      FROM

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

      WHERE 

      (

        (Interfaces.Carrier IS NOT NULL) AND

        (Nodes.XX_Org_Unit <> 'Location')

      )

        • Re: SWSQL Assistance report writer
          antonis.athanasiou

          Hi,

           

          You will need to lookup IP Addresses values from the NodeIPAddresses table, and match them with the NodeID and InterfaceIndex from the Interfaces table.

           

          Try this:

           

          SELECT
          Nodes.City AS City, Nodes.Address_all, Nodes.Caption AS Node_Name, IfName AS IfName,  Nodes.MachineType AS Machine_Type, Interfaces.InterfaceSpeed AS Interface_Speed, Interfaces.Carrier AS Carrier, 
          Interfaces.XX_Corp_Circuit_id AS XX_Corp_Circuit_id, Interfaces.Carrier_Web_Contact AS Carrier_Web_Contact, Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID,
          NodeIPAddresses.IPAddress
          
          
          FROM
          Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
          INNER JOIN NodeIPAddresses ON NodeIPAddresses.NodeID=Nodes.NodeID AND NodeIPAddresses.InterfaceIndex=interfaces.InterfaceIndex
          WHERE 
          (
            (Interfaces.Carrier IS NOT NULL) AND
            (Nodes.XX_Org_Unit <> 'Location')
          )
          

           

          let me know if that helps!