Layer 2/3 and BGP Fundamentals

HardSQL · PostgreSQL

Problem

Explain layer 2 and layer 3 packet forwarding, how BGP works, and how you would handle importing routes to BGP when there is no IGP in iBGP.

Using the supplied router, BGP route, Layer 2 neighbor, and forwarding event tables, write a query that identifies preferred valid iBGP routes usable without an IGP. Rows with NULL route-selection values or next hops are ignored.

Output

One row per router and selected prefix with router_name, prefix, next_hop, local_pref, import_status, and l3_forwarding
Include only routes with a current, up Layer 2 neighbor and a non-NULL MAC address
Order by router_name, then prefix

Schema

routers

Column	Type	Description
router_idPK	INTEGER	Router identifier
router_name	VARCHAR(40)	Router hostname

bgp_routes

Column	Type	Description
route_idPK	INTEGER	Route identifier
router_id	INTEGER	Router holding the route
prefix	VARCHAR(50)	Destination network prefix
protocol	VARCHAR(30)	Route protocol
session_type	VARCHAR(40)	BGP session classification
next_hop	VARCHAR(50)	BGP next-hop address
local_pref	NUMERIC(12,2)	Local preference
as_path_length	INTEGER	AS path length
med	NUMERIC(12,2)	Multi-exit discriminator
learned_at	TIMESTAMP	Time the route was learned
valid	BOOLEAN	Whether the route is valid

layer2_neighbors

Column	Type	Description
neighbor_idPK	INTEGER	Neighbor observation identifier
router_id	INTEGER	Local router
neighbor_ip	VARCHAR(50)	Neighbor IP address
neighbor_mac	VARCHAR(30)	Resolved neighbor MAC address
state	VARCHAR(20)	Layer 2 neighbor state
observed_at	TIMESTAMP	Observation timestamp

forwarding_events

Column	Type	Description
event_idPK	INTEGER	Forwarding event identifier
router_id	INTEGER	Router reporting the event
prefix	VARCHAR(50)	Forwarded destination prefix
action	VARCHAR(30)	Observed forwarding action
observed_at	TIMESTAMP	Event timestamp

Tablesroutersbgp_routeslayer2_neighborsforwarding_events