netbox to isc-dhcpd
No One
Actions

Authored By

	stwalkerster
	Jan 27 2018, 2:33 AM

Size

1 KB

Referenced Files

None

Subscribers

None

netbox to isc-dhcpd
View Options

	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;