cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Node report with interfaces and IP Addresses

Jump to solution

Hi,

I'm trying to come up with a way to generate a report showing all nodes, the interfaces on those nodes, and then the ip address assigned to that interface.  All the information is in the database (the tables I'm looking at are Nodes, Interfaces and NodeIPAddresses) but I cant work out the relationship between NodeIPAddresses.IPAddessN and the interface.

Example

Node NameInterfaceIP Address
Node1Interface11.1.1.1
Node1Interface22.2.2.2
Node1Interface3NULL
Node2Interface11.1.1.2
Node2Interface22.2.2.3
Node3etcetc

I'd probably add a few other fields from the nodes and interfaces tables (status for example) and add some groupings, however the above is the base building blocks I'm looking for.

Appreciate any advice.

Stuart

0 Kudos
1 Solution

Ok, I think I might have it.  Just working through the list to see if anything is missing.

select

Interfaces.nodeID AS NodeID,

Nodes.Caption AS NodeName,

interfaces.caption AS InterfaceName,

interfaces.interfaceIndex,

NodeIPAddresses.IPAddress,

NodeIPAddresses.InterfaceIndex

from

Interfaces

Right join nodes on (nodes.nodeID = interfaces.NodeID)

Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

View solution in original post

4 Replies
Level 17

Here's a quick-and-dirty that should get you where you are going:

select interfaces.caption, interfaces.interfaceIndex,

NodeIPAddresses.IPAddress, NodeIPAddresses.InterfaceIndex

from NodeIPAddresses

inner join interfaces on

     (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

...etc...

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

Ok, I think I might have it.  Just working through the list to see if anything is missing.

select

Interfaces.nodeID AS NodeID,

Nodes.Caption AS NodeName,

interfaces.caption AS InterfaceName,

interfaces.interfaceIndex,

NodeIPAddresses.IPAddress,

NodeIPAddresses.InterfaceIndex

from

Interfaces

Right join nodes on (nodes.nodeID = interfaces.NodeID)

Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

View solution in original post

Is your problem solved?

0 Kudos

That's looking good, now I need to get Nodes.Caption added too.

I've added Interfaces.NodeID - how would I join this to Nodes.nodeID?

Also I need to make sure that the report has ALL nodes (even if they have no interfaces) and from the nodes that DO have interfaces, ALL interfaces (even if they have no ip address)

Thanks for the pointers so far

0 Kudos