Find devices connected to a named device

This query identifies all main node devices connected to a single specified main node device.

Example

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

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 a specified main node device, represented by `locm.entityId`. 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 The name of the local device, represented by `locm.entityName` The unique entity ID of a device that is next to the specified device, represented by `nbrm.entityId` The name of the neighboring device, represented by `nbrm.entityName`
5	Use the `entityData` table as the driving table for this query. Use the alias `loc` for the `entityData` table to indicate that the data extracted using this alias is for local entities.
6	Identify all the connections for the entities associated with the local device. Do this by joining the `connects` table using the `aEndEntityId` value.
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 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 17) and the neighboring devices are all considered to be at the `aend` (see line 18).

Results

The table below shows the results of this query. This data includes examples of devices connected to themselves. These are connections within the same device between local VLANs and VLAN trunk ports.

Table 2. Results of the query
Local main node entity ID	Local main node entity name	Neighbor main node entity ID	Neighbor main mode entity name
`5`	`VE001.example.net`	`83`	`192.168.35.225`
`5`	`VE001.example.net`	`2698`	`192.168.34.86`
`77`	`VE002.example.net`	`77`	`VE002.example.net`
`77`	`VE002.example.net`	`77`	`VE002.example.net`
`531`	`192.168.39.175`	`5`	`VE001.example.net`