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')

)

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:

SELECTNodes.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
FROMNodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)INNER JOIN NodeIPAddresses ON NodeIPAddresses.NodeID=Nodes.NodeID AND NodeIPAddresses.InterfaceIndex=interfaces.InterfaceIndexWHERE (  (Interfaces.Carrier IS NOT NULL) AND  (Nodes.XX_Org_Unit <> 'Location'))

let me know if that helps!