3 ## ippool.conf -- MySQL queries for rlm_sqlippool
7 # ## This series of queries allocates an IP address
8 # allocate-clear = "UPDATE ${ippool_table} \
9 # SET nasipaddress = '', pool_key = 0, \
10 # callingstationid = '', username = '', \
11 # expiry_time = NULL \
12 # WHERE pool_key = '${pool-key}'"
14 ## This series of queries allocates an IP address
15 ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
16 ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
17 ## from the WHERE clause)
19 allocate-clear = "UPDATE ${ippool_table} \
20 SET nasipaddress = '', pool_key = 0, \
21 callingstationid = '', username = '', \
23 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
24 AND nasipaddress = '%{Nas-IP-Address}'"
28 ## The ORDER BY clause of this query tries to allocate the same IP-address
29 ## which user had last session...
30 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
31 WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL) \
32 ORDER BY (username <> '%{User-Name}'), \
33 (callingstationid <> '%{Calling-Station-Id}'), \
38 # ## If you prefer to allocate a random IP address every time, i
39 # ## use this query instead
40 # allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
41 # WHERE pool_name = '%{control:Pool-Name}' \
42 # AND expiry_time = NULL \
49 ## If an IP could not be allocated, check to see if the pool exists or not
50 ## This allows the module to differentiate between a full pool and no pool
51 ## Note: If you are not running redundant pool modules this query may be
52 ## commented out to save running this query every time an ip is not allocated.
53 pool-check = "SELECT id FROM ${ippool_table} \
54 WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
57 ## This is the final IP Allocation query, which saves the allocated ip details
58 allocate-update = "UPDATE ${ippool_table} \
59 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
60 callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
61 expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
62 WHERE framedipaddress = '%I' AND expiry_time IS NULL"
66 ## This series of queries frees an IP number when an accounting
67 ## START record arrives
68 start-update = "UPDATE ${ippool_table} \
69 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
70 WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}' \
71 AND username = '%{User-Name}' \
72 AND callingstationid = '%{Calling-Station-Id}' \
73 AND framedipaddress = '%{Framed-IP-Address}'"
75 ## This series of queries frees an IP number when an accounting
76 ## STOP record arrives
77 stop-clear = "UPDATE ${ippool_table} \
78 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
80 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
81 AND username = '%{User-Name}' \
82 AND callingstationid = '%{Calling-Station-Id}' \
83 AND framedipaddress = '%{Framed-IP-Address}'"
87 ## This series of queries frees an IP number when an accounting
88 ## ALIVE record arrives
89 alive-update = "UPDATE ${ippool_table} \
90 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
91 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
92 AND username = '%{User-Name}' \
93 AND callingstationid = '%{Calling-Station-Id}' \
94 AND framedipaddress = '%{Framed-IP-Address}'"
98 ## This series of queries frees the IP numbers allocate to a
99 ## NAS when an accounting ON record arrives
100 on-clear = "UPDATE ${ippool_table} \
101 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
103 WHERE nasipaddress = '%{Nas-IP-Address}'"
105 ## This series of queries frees the IP numbers allocate to a
106 ## NAS when an accounting OFF record arrives
107 off-clear = "UPDATE ${ippool_table} \
108 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
110 WHERE nasipaddress = '%{Nas-IP-Address}'"