3 ## ippool-dhcp.conf -- MySQL queries for sqlippool-dhcp instance
4 ## Only post-auth method is used
8 ## This series of queries allocates an IP address
9 ## First, clear expired entries
10 allocate-clear = "UPDATE ${ippool_table} \
11 SET nasipaddress = '', pool_key = 0, \
12 callingstationid = '', username = '', \
14 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
17 ## Then find an available IP address
18 ## The ORDER BY clause of this query tries to allocate the same IP address
19 ## which user had last session regardless of expiry time
20 ## to handle DHCP request and duplicates from the same client
21 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
22 WHERE pool_name = '%{control:Pool-Name}' AND \
24 (callingstationid = '%{Calling-Station-Id}') OR \
25 (expiry_time < NOW() OR expiry_time IS NULL) \
27 ORDER BY (callingstationid = '%{Calling-Station-Id}') DESC, \
32 ## If an IP could not be allocated, check to see if the pool exists or not
33 ## This allows the module to differentiate between a full pool and no pool
34 ## Note: If you are not running redundant pool modules this query may be
35 ## commented out to save running this query every time an ip is not allocated.
36 pool-check = "SELECT id FROM ${ippool_table} \
37 WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
40 ## This is the final IP Allocation query, which saves the allocated ip details
41 allocate-update = "UPDATE ${ippool_table} \
42 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
43 callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
44 calledstationid = 'Freeradius-DHCP', \
45 expiry_time = GREATEST( \
46 IF(ISNULL(expiry_time),'0000-00-00 00:00:00',expiry_time), \
47 NOW() + INTERVAL ${lease-duration} SECOND) \
48 WHERE framedipaddress = '%I' AND \
50 (callingstationid = '%{Calling-Station-Id}') OR \
51 (expiry_time < NOW() OR expiry_time IS NULL) \
55 ## This series of queries frees an IP number when an accounting
56 ## START record arrives. Unused, but must be set to non-empty query
57 start-update = "SELECT NOW()"
59 ## This series of queries frees an IP number when an accounting
60 ## STOP record arrives. Unused, but must be set to non-empty query
61 stop-clear = "SELECT NOW()"
63 ## This series of queries frees an IP number when an accounting
64 ## ALIVE record arrives. Unused, but must be set to non-empty query
65 alive-update = "SELECT NOW()"
67 ## This series of queries frees the IP numbers allocate to a
68 ## NAS when an accounting ON record arrives. Unused, but must be set
70 on-clear = "SELECT NOW()"
72 ## This series of queries frees the IP numbers allocate to a
73 ## NAS when an accounting OFF record arrives. Unused, but must be set
75 off-clear = "SELECT NOW()"