Find all devices connected to a named device together with connecting interfaces

This query identifies all main node devices that are connected to a main device, and also retrieves the interface data that is associated with each of those connections.

Example

1]  SELECT     locm.entityid Local_Main_Node_Entity_ID,
2]             locm.entityName Local_Main_Node_Entity_Name,
3]             loc.entityName Local_Interface_Name,
4]            nbrm.entityid Neighbor_Main_Node_Entity_ID,
5]             nbrm.entityName Neighbor_Main_Node_Entity_Name,
6]             nbr.entityName Neighbor_Interface_Name
7]  FROM       entityData loc
8]  INNER JOIN connects c ON c.aEndEntityId = loc.entityId
9]  INNER JOIN entityData nbr ON nbr.entityId = c.zEndEntityId
10] INNER JOIN entityData nbrm ON nbrm.entityid = nbr.mainnodeentityid
11] INNER JOIN entityData locm ON locm.entityid = loc.mainnodeentityid
12] WHERE      loc.mainNodeEntityId = 5
13] UNION
14] SELECT     locm.entityid as locMainNodeEntityId,
15]            locm.entityName as locMainNodeEntityName,
16]            loc.entityName as locEntityName,
17]            nbrm.entityid as nbrMainNodeEntityId,
18]            nbrm.entityName as nbrMainNodeEntityName,
19]            nbr.entityName as nbrEntityName
20] FROM       entityData loc
21] INNER JOIN connects c ON c.zEndEntityId = loc.entityId
22] INNER JOIN entityData nbr ON nbr.entityId = c.aEndEntityId
23] INNER JOIN entityData nbrm ON nbrm.entityid = nbr.mainnodeentityid
24] INNER JOIN entityData locm ON locm.entityid = loc.mainnodeentityid
25] WHERE      loc.mainNodeEntityId = 5

Description

The table below describes this query.

Table 1. Description of the query 
Line number(s) Description
1-6 Specify the data to show in the results, as follows:
  • The unique entity ID of a specified main node device. This is the named device whose neighbors you want to extract from the database. The rest of this description refers to this device as the local device, represented by locm.entityId
  • The name of the local device, represented by locm.entityName
  • The name of the interface on the local device, represented by loc.entityName
  • The unique entity ID of a device that is adjacent to the specified device, represented by nbrm.entityId
  • The name of the neighboring device, represented by nbrm.entityName
  • The name of the interface on the neighboring device, represented by nbr.entityName
7

Extract the entity data for each neighboring entity.

Do this by joining the entityData table a second time using the zEndEntityId value. Use the alias nbr for the entityData table to indicate that the data extracted using this alias is for neighboring entities.
8

Limit the results to neighboring main node devices only.

Do this by joining the entityData table a second time using the mainNodeEntityId value.

Use the alias nbrm for the entityData table to indicate that the data extracted using this alias is entity data for a neighboring main node device.
9

Limit the results to local main node devices only.

Do this by joining the entityData table a second time using the mainNodeEntityId.

Use the alias locm for the entityData table to indicate that the data extracted using this alias is entity data for a local main node device.
10 Specify the identity of the local device.
11 Use the UNION statement to to ensure that all connections are retrieved.
12-21 This is the same code as line 1-10 with the difference that here the specified device is considered to be the zend (see line 21) and the neighboring devices are all considered to be at the aend (see line 22).

Results

The following table shows an example of the results of the query.the results of the query.

Table 2. Results of the query 
Local main node entity ID Local main node entity name Local interface name Neighbor main node entity ID

Neighbor main node entity name

 Neighbor interface name
5 VE001.example.net VE001.example.net[0[3]] 83 192.168.35.225 192.168.35.225
5 VE001.example.net VE001.example.net[0[4]] 2698 192.168.34.86 192.168.34.86
77 VE002.example.net VLAN_OBJECT_VE002.example.net_VLAN_400 77 VE002.example.net VLAN_trunk_400_VE002.example.
net[ 0 [ 2 ] ]
77 VE002.examplenet VLAN_OBJECT_VE002.example.net_VLAN_1 77 VE002.example.net VLAN_trunk_1_VE002.example.
net[ 0 [ 2 ] ]
531 192.168.39.175 192.168.39.175 5 VE001.example.net VE001.example.net[0[5]]