diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool/mssql/queries.conf')
| -rw-r--r-- | raddb/mods-config/sql/ippool/mssql/queries.conf | 175 |
1 files changed, 175 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool/mssql/queries.conf b/raddb/mods-config/sql/ippool/mssql/queries.conf new file mode 100644 index 0000000..8105dcc --- /dev/null +++ b/raddb/mods-config/sql/ippool/mssql/queries.conf @@ -0,0 +1,175 @@ +# -*- text -*- +# +# ippool/mssql/queries.conf -- MSSQL queries for rlm_sqlippool +# +# $Id$ + +# +# MSSQL-specific syntax - required if finding the address and updating +# it are separate queries +# +#allocate_begin = "BEGIN TRAN" +#allocate_commit = "COMMIT TRAN" + +allocate_begin = "" +allocate_commit = "" + +# +# This series of queries allocates an IP address +# + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + WITH cte AS ( \ + SELECT TOP(1) FramedIPAddress, CallingStationId, UserName, expiry_time \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND NASIPAddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + ) \ + UPDATE cte \ + SET \ + CallingStationId = '%{Calling-Station-Id}', \ + UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + OUTPUT INSERTED.FramedIPAddress" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + WITH cte AS ( \ + SELECT TOP(1) FramedIPAddress, NASIPAddress, pool_key, \ + CallingStationId, UserName, expiry_time \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < CURRENT_TIMESTAMP \ + ORDER BY expiry_time \ + ) \ + UPDATE cte \ + SET \ + NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ + CallingStationId = '%{Calling-Station-Id}', \ + UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + OUTPUT INSERTED.FramedIPAddress" + +# +# If you prefer to allocate a random IP address every time, use this query instead. +# Note: This is very slow if you have a lot of free IPs. +# +#allocate_find = "\ +# WITH cte AS ( \ +# SELECT TOP(1) FramedIPAddress, NASIPAddress, pool_key, \ +# CallingStationId, UserName, expiry_time \ +# FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < CURRENT_TIMESTAMP \ +# ORDER BY newid() \ +# ) \ +# UPDATE cte WITH (rowlock, readpast) \ +# SET \ +# NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ +# CallingStationId = '%{Calling-Station-Id}', \ +# UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ +# OUTPUT INSERTED.FramedIPAddress" + +# +# If an IP could not be allocated, check to see if the pool exists or not +# This allows the module to differentiate between a full pool and no pool +# Note: If you are not running redundant pool modules this query may be +# commented out to save running this query every time an ip is not allocated. +# +pool_check = "\ + SELECT TOP(1) id \ + FROM ${ippool_table} \ + WHERE pool_name='%{control:${pool_name}}'" + +# +# This is the final IP Allocation query, which saves the allocated ip details. +# Only needed if allocate_existing / allocate_find do not also update the pool. +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ +# CallingStationId = '%{Calling-Station-Id}', \ +# UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ +# WHERE FramedIPAddress = '%I'" + +# +# Use a stored procedure to find AND allocate the address. Read and customise +# `procedure.sql` in this directory to determine the optimal configuration. +# +#allocate_begin = "" +#allocate_find = "\ +# EXEC fr_allocate_previous_or_new_framedipaddress \ +# @v_pool_name = '%{control:${pool_name}}', \ +# @v_username = '%{User-Name}', \ +# @v_callingstationid = '%{Calling-Station-Id}', \ +# @v_nasipaddress = '%{NAS-IP-Address}', \ +# @v_pool_key = '${pool_key}', \ +# @v_lease_duration = ${lease_duration} \ +# " +#allocate_update = "" +#allocate_commit = "" + +# +# This series of queries frees an IP number when an accounting START record arrives. +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + WHERE NASIPAddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Expire an IP when an accounting STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Update the expiry time for an IP when an accounting ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Expires all IPs allocated to a NAS when an accounting ON record arrives +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# Expires all IPs allocated to a NAS when an accounting OFF record arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" |