Introducing the Nautobot Source Integration

SELECT
    m.name AS manufacturer,
    dt.model,
    dt.u_height,
    dt.device_count
FROM nautobot_device_types dt, nautobot_manufacturers m
WHERE dt.manufacturer->>'id' = m.id
ORDER BY dt.device_count DESC;

SELECT
    l.name AS location_name,
    l.description,
    l.device_count,
    l.rack_count
FROM nautobot_locations l
ORDER BY l.device_count DESC;

SELECT
    i.device->>'id' AS device_id,
    COUNT(*) AS interface_count,
    COUNT(*) FILTER (WHERE i.enabled IS TRUE) AS enabled_count,
    COUNT(*) FILTER (WHERE i.mgmt_only IS TRUE) AS mgmt_count
FROM nautobot_interfaces i
GROUP BY i.device->>'id'
ORDER BY interface_count DESC
LIMIT 20;

SELECT
    l.name AS nautobot_location,
    l.physical_address,
    d.name AS device_name,
    d.serial AS device_serial,
    i.instance_id AS aws_instance_id,
    i.instance_type AS aws_instance_type,
    i.region AS aws_region
FROM nautobot_locations l
JOIN nautobot_devices d ON d.location->>'id' = l.id
CROSS JOIN aws_ec2_instances i
WHERE l.name ILIKE '%' || i.region || '%'
   OR l.description ILIKE '%' || i.region || '%'
ORDER BY l.name, d.name;