UTM5+freeradius

добавляем в sql.conf
запросы в базу UTM5 для авторизации

authorize_check_query="SELECT ip_groups.ip_group_id, ip_groups.uname,'User-Password',ip_groups.upass,':=' FROM UTM5.ip_groups, UTM5.iptraffic_service_links, UTM5.service_links, UTM5.accounts WHERE ip_groups.uname = '%{SQL-User-Name}' AND ip_groups.is_deleted='0' AND iptraffic_service_links.is_deleted='0' AND service_links.is_deleted='0' AND accounts.is_deleted='0' AND accounts.is_blocked='0' AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id AND iptraffic_service_links.id=service_links.id AND service_links.account_id=accounts.id \
UNION SELECT ip_groups.ip_group_id, ip_groups.uname,'Simultaneous-Use','1',':=' FROM UTM5.ip_groups, UTM5.iptraffic_service_links, UTM5.service_links, UTM5.accounts WHERE ip_groups.uname = '%{SQL-User-Name}' AND ip_groups.is_deleted='0' AND iptraffic_service_links.is_deleted='0' AND service_links.is_deleted='0' AND accounts.is_deleted='0' AND accounts.is_blocked='0' AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id AND iptraffic_service_links.id=service_links.id AND service_links.account_id=accounts.id"

authorize_reply_query="SELECT ip_group_id, uname, 'Framed-IP-Address', \
inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS a, ':=' FROM UTM5.ip_groups \
WHERE uname='%{SQL-User-Name}' AND is_deleted='0' \
UNION SELECT ip_group_id, uname, 'Auth-Type', 'Reject' as a, ':=' \
FROM ip_groups WHERE uname='%{SQL-User-Name}' AND is_deleted='0'"

запросы в базу UTM5 для аккаунтинга

accounting_start_query = "INSERT INTO dhs_sessions_log (account_id, slink_id, recv_date,last_update_date,Framed_IP_Address, NAS_Port, Acct_Delay_Time, Acct_Session_Id, NAS_Port_Type, User_Name, Service_Type, Framed_Protocol, NAS_IP_Address, NAS_Id, Acct_Status_Type, Acct_Input_Packets, Acct_Input_Octets, Acct_Input_Gigawords, Acct_Output_Packets, Acct_Output_Octets, Acct_Output_Gigawords, Acct_Session_Time, Acct_Terminate_Cause, Called_Station_Id, Calling_Station_Id ) \
VALUES ( (select account_id from service_links where id in ( select id from iptraffic_service_links where is_deleted <> '1' and ip_group_id in \
( select ip_group_id from ip_groups where uname = '%{SQL-User-Name}' and is_deleted <> '1' ))), \
( select id from iptraffic_service_links where is_deleted <> '1' and ip_group_id in ( select ip_group_id from ip_groups where uname = '%{SQL-User-Name}' and is_deleted <> '1' )), '%l','0', \
(INET_ATON(CONCAT_WS('.',SUBSTRING_INDEX('%{Framed-IP-Address}','.',-1),SUBSTRING_INDEX(SUBSTRING_INDEX('%{Framed-IP-Address}','.',-2),'.',1),SUBSTRING_INDEX(SUBSTRING_INDEX('%{Framed-IP-Address}','.',2),'.',-1), SUBSTRING_INDEX('%{Framed-IP-Address}','.',1)))), '%{NAS-Port}', '%{Acct-Delay-Time}', \
'%{Acct-Session-Id}', '%{NAS-Port-Type}', '%{SQL-User-name}', '%{Service-Type}', '%{Framed-Protocol}', (INET_ATON(CONCAT_WS('.',SUBSTRING_INDEX('%{NAS-IP-Address}','.',-1), SUBSTRING_INDEX(SUBSTRING_INDEX('%{NAS-IP-Address}','.',-2),'.',1),SUBSTRING_INDEX(SUBSTRING_INDEX('%{NAS-IP-Address}','.',2),'.',-1),SUBSTRING_INDEX('%{NAS-IP-Address}','.#',1)))), '%{NAS-IP-Address}', '1', '%{Acct-Input-Packets:-0}', '%{Acct-Input-Octets:-0}', '%{Acct-Input-Gigawords:-0}', '%{Acct-Output-Packets:-0}', '%{Acct-Output-Octets:-0}', '%{Acct-Output-Gigawords:-0}', '%{Acct-Session-Time:-0}', '%{Acct-Terminate-Cause:-0}', '%{Called-Station-Id}','%i' );"

accounting_update_query = "UPDATE dhs_sessions_log SET last_update_date=UNIX_TIMESTAMP('%S'), Acct_Status_Type='3', Acct_Input_Packets ='%{Acct-Input-Packets}', Acct_Input_Octets ='%{Acct-Input-Octets}', Acct_Input_Gigawords ='%{Acct-Input-Gigawords}', Acct_Output_Packets ='%{Acct-Output-Packets}', Acct_Output_Octets ='%{Acct-Output-Octets}', Acct_Output_Gigawords ='%{Acct-Output-Gigawords}', Acct_Terminate_Cause='%{Acct-Terminate-Cause}', Acct_Session_Time ='%{Acct-Session-Time}' WHERE Acct_Session_Id = '%{Acct-Session-Id}'"

accounting_stop_query = "UPDATE dhs_sessions_log SET last_update_date=UNIX_TIMESTAMP('%S'), Acct_Status_Type='2', Acct_Input_Packets ='%{Acct-Input-Packets}', Acct_Input_Octets ='%{Acct-Input-Octets}', Acct_Input_Gigawords ='%{Acct-Input-Gigawords}', Acct_Output_Packets ='%{Acct-Output-Packets}', Acct_Output_Octets ='%{Acct-Output-Octets}', Acct_Output_Gigawords ='%{Acct-Output-Gigawords}', Acct_Terminate_Cause='%{Acct-Terminate-Cause}', Acct_Session_Time ='%{Acct-Session-Time}' WHERE Acct_Session_Id = '%{Acct-Session-Id}'"

запросы в базу UTM5 для проверки двойных подключений

simul_count_query = "SELECT COUNT(*) FROM dhs_sessions_log WHERE User_Name='%{SQL-User-Name}' AND (Acct_Status_Type = '3' OR Acct_Status_Type = '1') "
simul_verify_query = "SELECT COUNT(*) FROM dhs_sessions_log WHERE User_Name='%{SQL-User-Name}' AND (Acct_Status_Type = '3' OR Acct_Status_Type = '1') "