Find all devices in a given VPN

This query identifies all of the VPNs listed in the database. For each VPN the query provides the name of that VPN and the list of IP addresses collected within that subnet. The IP address collected within a VPN might refer to main nodes or interfaces; typically they refer to interfaces.

Example

1]  SELECT     v.VPNName VPN_Name,
2]             v.VPNType VPN_Type,
3]             e.entityName Entity_Name
4]  FROM networkVpn v
5]  INNER JOIN collects c ON c.collectingEntityId = v.entityId
6]  INNER JOIN entityData e ON e.entityId = c.collectedEntityId
7]  ORDER BY   v.VPNName

Description

The table below describes this query.

Table 1. Description of the query 
Line numbers Description
1-3 Specify the data to show in the results, as follows:
  • The IP address of the VPN, represented by v.VPNName
  • The VPN type, represented by v.VPNType
  • The name – usually an IP address – of an interface or main node within this VPN, represented by e.entityName
4 Use the networkVpn table as the driving table for this query. This enables the query to extract all the VPNs in the database.
5 Retrieve a listing of all the collected entities within each VPN. At this point the collected entities are identified by their entity identifier only. The corresponding IP address is retrieved in the next line. Do this by joining the collects table.
6 Extract the entity data for each interface or main node collected within each VPN. Do this by joining the entityData table to the query. This enables the query to retrieve the IP address for each of the collected entities
7 For readability purposes, order the results by the name of the collecting VPN.

Results

The table below shows the results of this query.

Table 2. Results of the query
VPN name VPN type Entity name
VPN-BLUE MPLS L2 PseudoWire 172.18.1.31
VPN-BLUE MPLS L2 PseudoWire 172.20.11.54
VPN-BLUE MPLS L2 PseudoWire 172.20.11.75
VPN-GREEN MPLS L2 BGP VPN 10.1.1.26
VPN-GREEN MPLS L2 BGP VPN 10.1.1.27
VPN-GREEN MPLS L2 BGP VPN 172.18.1.30
VPN-PURPLE MPLS IP VPN RT PAIR 10.1.1.59
VPN-PURPLE MPLS IP VPN RT PAIR 10.1.1.75
VPN-RED MPLS IP VPN 172.20.11.103
VPN-RED MPLS IP VPN 172.20.11.111
VPN-WHITE MPLS IP VPN MESH 172.18.1.233
VPN-WHITE MPLS IP VPN MESH 172.18.1.240
VPN-YELLOW MPLS IP VPN 10.1.1.6
VPN-YELLOW MPLS IP VPN 10.1.1.8
VPN-YELLOW MPLS IP VPN 10.1.1.9
VPN-YELLOW MPLS IP VPN 10.1.1.25