Page MenuHomePhabricator
Paste P187

netbox to isc-dhcpd
ActivePublic

Authored by stwalkerster on Jan 27 2018, 2:33 AM.
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;

Event Timeline

stwalkerster created this object with visibility "Public (No Login Required)".