WITH data AS (
    SELECT
      di.mac_address,
      host(ii.address)                             address,
      coalesce(d3.name, vm.name)                   hostname,
      custfields.serialized_value                  leasetime,
      coalesce(d3.name, vm.name) || '-' || di.name resname
    FROM dcim_interface di
      LEFT JOIN dcim_device d3 ON di.device_id = d3.id
      LEFT JOIN virtualization_virtualmachine vm ON di.virtual_machine_id = vm.id
      LEFT JOIN ipam_ipaddress ii ON ii.interface_id = di.id
      LEFT JOIN (
                  SELECT cfv.*
                  FROM extras_customfield cf
                    INNER JOIN extras_customfieldvalue cfv ON cf.id = cfv.field_id AND cf.name = 'default_lease_time'
                ) custfields ON custfields.obj_id = d3.id
    WHERE 1 = 1
          AND di.mac_address IS NOT NULL
          AND (ii.address <<= CIDR '192.168.22.0/24' OR ii.address IS NULL)
    ORDER BY hostname
),
config AS (
    SELECT
      'host ' || resname || '.stwalkerster.net { ' base,
      'hardware ethernet ' || mac_address || '; ' eth,
      'fixed-address ' || address || '; ' addr,
      'option host-name ' || hostname || '; ' host
    FROM data
)
SELECT base || eth  || coalesce(addr, '') || coalesce(host, '') || '}'
FROM config;