Unable to trace cables after import, manual data fixing

[andrewimeson]

Environment

• Python version: 3.12.11
• Nautobot version: v2.4.11
• nautobot-netbox-importer version: v2.2.0

Expected Behavior

Cables to be traceable in the UI

Observed Behavior

Only one side shows up when you click the trace button on an interface.

Steps to Reproduce

1. Import data

nautobot-server import_netbox \
    --bypass-data-validation \
    --fix-powerfeed-locations \
    --tag-issues \
    --save-text-summary=/tmp/output-$(date +"%Y-%m-%d_%H-%M-%S").txt \
    /tmp/netbox_data.json

2. Navigate to a device and interface and click "trace" button

Workaround

If I manually add some data to a few of the tables, I can trace cables again.

-- 13 = interface
UPDATE dcim_interface SET _cable_peer_type_id = 13;

UPDATE dcim_interface
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_interface.id;

UPDATE dcim_interface
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_interface.id;

UPDATE dcim_cablepath
SET destination_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_cablepath.origin_id;

UPDATE dcim_cablepath
SET destination_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_cablepath.origin_id;

[andrewimeson]

I just did a fresh import on a fresh instance and realized that Nautobot is unable to start back up because of the data validation issues, both before and after my attempt at fixing the data issues with the queries.

What's interesting is that Nautobot stays running after the import and I can browse the data both before and after the fixes (after the fixes I can see more), but it fails on next start.

[andrewimeson]

Here's the instructions I made documenting how we got it working.

I'm sure that some of it wasn't necessary or there are better ways to do it, but this got us a working Nautobot deployment with our NetBox data intact and cable tracing working.

Export

We have to create an empty table in the NetBox database the Django Redis Queue
integration or the export would error out. We couldn't just add it to --exclude for
some reason. Our particular Kubernetes deployment of NetBox used redis as a cache for NetBox.

CREATE TABLE django_rq_queue ( id integer );

From NetBox export the data.

cd /opt/netbox/netbox/
./manage.py dumpdata \
    --traceback --format=json --indent=2 \
    --natural-primary --natural-foreign \
    --output=/tmp/netbox_data.json \
    --exclude=extras.ObjectChange \
    --exclude=extras.Script \
    --exclude=extras.Report \
    --exclude=extras.imageattachment

Import

Copy the exported JSON to Nautobot

The import process would fail because when it would insert data it would violate foreign
key constraints in the Postgres database. Some of this we assume to be because of
the order data is being inserted, or that the data perhaps isn't being inserted in a
single transaction.

The solution we found is to disable the constraints (which postgres implements using
triggers under the hood), load the data, re-enable the constraints, and then re-check
the constraints. We only lost one record this way.

DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN (
        SELECT tablename
        FROM pg_tables
        WHERE tableowner = 'nautobot'
    )
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_record.tablename) || ' DISABLE TRIGGER ALL';
    END LOOP;
END $$;

Import the JSON file

nautobot-server import_netbox \
    --tag-issues \
    --bypass-data-validation \
    --save-text-summary=/tmp/output-$(date +"%Y-%m-%d_%H-%M-%S").txt \
    /tmp/netbox_data.json

Re-enable foreign key constraints

DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN (
        SELECT tablename
        FROM pg_tables
        WHERE tableowner = 'nautobot'
    )
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(table_record.tablename) || ' ENABLE TRIGGER ALL';
    END LOOP;
END $$;

Finally, we can check the database for foreign key constraint violates using the method
suggested in this Stackoverflow post

DO $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'UPDATE pg_constraint SET convalidated=false WHERE conname = %L; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.constraint_name,
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;

This results in one or more errors like the following:

insert or update on table "dcim_device" violates foreign key constraint "dcim_device_primary_ip4_id_2ccd943a_fk_ipam_ipaddress_id"
DETAIL:  Key (primary_ip4_id)=(53b5c9b3-bf15-57ba-b5e9-63a980faa732) is not present in table "ipam_ipaddress".
CONTEXT:  SQL statement "UPDATE pg_constraint SET convalidated=false WHERE conname = 'dcim_device_primary_ip4_id_2ccd943a_fk_ipam_ipaddress_id'; ALTER TABLE dcim_device VALIDATE CONSTRAINT dcim_device_primary_ip4_id_2ccd943a_fk_ipam_ipaddress_id;"
PL/pgSQL function inline_code_block line 19 at EXECUTE
(END)

Fixing Cable Connections

This section here fixed the data enough that we could restart Nautobot, and forcing the
cable re-tracing wouldn't error out.

nautobot-server shell

from nautobot.dcim.models import Interface, Cable
from django.db.models import Exists, OuterRef

invalid_interfaces = Interface.objects.filter(cable_id__isnull=False).exclude(Exists(Cable.objects.filter(id=OuterRef('cable_id'))))
print(f"Found {invalid_interfaces.count()} interfaces with invalid cable references.")
for interface in invalid_interfaces:
    print(f"Device: {interface.device.name}, Interface: {interface.name}, Invalid cable_id: {interface.cable_id}")

for interface in invalid_interfaces:
    interface.cable = None
    interface.validated_save()  # Or interface.save() if validated_save is not available in your version

from nautobot.dcim.models import PowerPort, PowerOutlet, Cable
from django.db.models import Exists, OuterRef

invalid_powerports = PowerPort.objects.filter(cable_id__isnull=False).exclude(Exists(Cable.objects.filter(id=OuterRef('cable_id'))))
invalid_poweroutlets = PowerOutlet.objects.filter(cable_id__isnull=False).exclude(Exists(Cable.objects.filter(id=OuterRef('cable_id'))))

print(f"Found {invalid_powerports.count()} power ports with invalid cable references.")
for pp in invalid_powerports:
    print(f"Device: {pp.device.name}, PowerPort: {pp.name}, Invalid cable_id: {pp.cable_id}")

print(f"Found {invalid_poweroutlets.count()} power outlets with invalid cable references.")
for po in invalid_poweroutlets:
    print(f"Device: {po.device.name}, PowerOutlet: {po.name}, Invalid cable_id: {po.cable_id}")

for pp in invalid_powerports:
    pp.cable = None
    pp.validated_save()  # Or pp.save() if validated_save isn't available

for po in invalid_poweroutlets:
    po.cable = None
    po.validated_save()  # Or po.save() if validated_save isn't available

from nautobot.dcim.models import PowerFeed, Cable
from django.db.models import Exists, OuterRef

invalid_powerfeeds = PowerFeed.objects.filter(cable_id__isnull=False).exclude(
    Exists(Cable.objects.filter(id=OuterRef("cable_id")))
)
print(f"Found {invalid_powerfeeds.count()} powerfeeds with invalid cable references.")
for powerfeed in invalid_powerfeeds:
    print(
        f"PowerPanel: {powerfeed.power_panel.name if powerfeed.power_panel else 'None'}, "
        f"PowerFeed: {powerfeed.name}, Invalid cable_id: {powerfeed.cable_id}"
    )

for powerfeed in invalid_powerfeeds:
    powerfeed.cable = None
    powerfeed.validated_save()

Force cable retracing

We're not sure if this step is needed. If you run it after the SQL queries (detailed in
the next section) it will break the cable tracing function.

nautobot-server trace_paths --no-input --force

Database

This is the step that finally derives and inserts the data needed for the cable tracing
to work in the UI.

-- Fix types and destinations on cable paths
UPDATE dcim_cablepath
SET destination_id = c.termination_a_id,
    origin_type_id = c.termination_a_type_id,
    destination_type_id = c.termination_b_type_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_cablepath.origin_id;

UPDATE dcim_cablepath
SET destination_id = c.termination_b_id,
    origin_type_id = c.termination_a_type_id,
    destination_type_id = c.termination_b_type_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_cablepath.origin_id;

-- Fix dcim_interface

UPDATE dcim_interface
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_interface.id;

UPDATE dcim_interface
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_interface.id;

-- Fix dcim_consoleport

UPDATE dcim_consoleport
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_consoleport.id;

UPDATE dcim_consoleport
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_consoleport.id;

-- Fix dcim_consoleserverport

UPDATE dcim_consoleserverport
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_consoleserverport.id;

UPDATE dcim_consoleserverport
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_consoleserverport.id;

-- Fix dcim_powerfeed

UPDATE dcim_powerfeed
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_powerfeed.id;

UPDATE dcim_powerfeed
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_powerfeed.id;

-- Fix dcim_poweroutlet

UPDATE dcim_poweroutlet
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_poweroutlet.id;

UPDATE dcim_poweroutlet
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_poweroutlet.id;

-- Fix dcim_powerport

UPDATE dcim_powerport
SET _cable_peer_id = c.termination_a_id
FROM dcim_cable c
WHERE c.termination_b_id = dcim_powerport.id;

UPDATE dcim_powerport
SET _cable_peer_id = c.termination_b_id
FROM dcim_cable c
WHERE c.termination_a_id = dcim_powerport.id;

Then restart Nautobot.

Sometimes the init process will get hung up because it's trying to create a key that
already exists (you'll see it in the init container logs). In that case, delete the
offending key from the database.

DELETE FROM users_token WHERE key = 'REDACTED';