List all IP addresses and the interfaces that implement them

This query retrieves all interfaces on all main node devices. For each interface, the query lists the IP addresses that the interface implements. An interface can implement multiple IP addresses.

In addition to using information from the entityData table to list the interfaces on each device, this query lists the IP addresses implemented by each interface identified. If an interface does not implement an IP address, that interface is not returned by this query.

Example

1]  SELECT     eInterface.entityId Interface_Entity_ID,
2]             eMainNode.entityName Main_Node_Name,
3]             eInterface.entityName Interface_Entity_Name,
4]             ip.address IP_Address
5]  FROM       entityData eInterface
6]  INNER JOIN entityData eMainNode ON eMainNode.entityId =
7]                   eInterface.mainNodeEntityId
8]  INNER JOIN protocolEndPoint p ON p.implementingEntityId = eInterface.entityId
9]  INNER JOIN ipEndPoint ip ON ip.entityId = p.endPointEntityId
10] WHERE      eInterface.entityType = 2
11] ORDER BY   eInterface.entityId

Description

The table below describes this query.

Table 1. Description of the query 

Line numbers

Description

1-4

Specify the data to show in the results, as follows:

  • The unique entity ID of the interface within the topology database, represented by eInterface.entityId
  • The name of a main node device, represented by eMainNode.entityName
  • The name of the interface, represented by eInterface.entityName
  • An IP address implemented by this interface, represented by ip.address

5

Use the entityData table as the driving table for this query. Use the alias eInterface for the entityData table to indicate that the data extracted using this alias is interface data.

6-7

Identify the containing main node device for each of the entities retrieved in the preceding line.

Do this by joining the entityData table to itself using the mainNodeEntityId field.

8-9

Identify the IP addresses implemented by each of the entities identified in line 5 of the query.

Do this by performing an INNER JOIN statement on the protocolEndPoint table to extract the entity ID for any protocol-specific information associated with the entities identified in line 5.

Then perform a second INNER JOIN statement on the ipEndPoint table to limit the protocol-specific information returned by the query to IP information.

10

Limit the components of the device to interfaces only.

Do this by filtering the components to retrieve only components with an entity type of 2, which corresponds to an interface.

11

To facilitate readability of the results, order first by the unique entity ID of the interface.

Results

The table below shows the results of this query.

Table 2. Results of the query

Interface Entity ID

Main Node Name

Interface Entity Name

IP Address

270

172.20.4.11

172.20.4.11[0[5]]

172.50.0.2

338

172.18.1.196

172.18.1.196[0[2]]

172.50.0.3

366

172.18.1.54

172.18.1.54[0[2]]

172.50.0.4

370

172.18.1.54

172.18.1.54[0[1]]

172.50.0.5

373

172.20.4.13

172.20.4.13[0[1]]

172.50.0.6

377

172.20.4.20

172.20.4.20[0[1]]

172.50.0.7

417

192.168.139.7

192.168.139.7[ 0 [ 5 ] ]

172.20.11.1

417

192.168.139.7

192.168.139.7[ 0 [ 5 ] ]

172.20.1.2