WebSVN – ALCASAR – Diff – //conf/radius/queries.conf



          SET \
             acctstoptime       =  '%S', \
             acctsessiontime    =  unix_timestamp('%S') - \
                                   unix_timestamp(acctstarttime), \
             acctterminatecause =  '%{Acct-Terminate-Cause}', \
 
          WHERE acctstoptime IS NULL \
          AND nasipaddress      =  '%{NAS-IP-Address}' \
          AND acctstarttime     <= '%S'"
 
	accounting_update_query = " \

            (acctsessionid,    acctuniqueid,      username, \
             realm,            nasipaddress,      nasportid, \
             nasporttype,      acctstarttime,     acctsessiontime, \
             acctauthentic,    connectinfo_start, acctinputoctets, \
             acctoutputoctets, calledstationid,   callingstationid, \
             servicetype,      framedprotocol,    framedipaddress) \
 
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \

             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Service-Type}', '%{Framed-Protocol}', \
             '%{Framed-IP-Address}')"
 
 
	accounting_start_query = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,     username, \
             realm,            nasipaddress,     nasportid, \
             nasporttype,      acctstarttime,    acctstoptime, \
             acctsessiontime,  acctauthentic,    connectinfo_start, \
             connectinfo_stop, acctinputoctets,  acctoutputoctets, \
             calledstationid,  callingstationid, acctterminatecause, \
             servicetype,      framedprotocol,   framedipaddress) \
 
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', '%S', NULL, \
             '0', '%{Acct-Authentic}', '%{Connect-Info}', \
             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"
 
 
	accounting_start_query_alt  = " \
          UPDATE ${acct_table1} SET \
             acctstarttime     = '%S', \
 
             connectinfo_start = '%{Connect-Info}' \
          WHERE acctsessionid  = '%{Acct-Session-Id}' \
          AND username         = BINARY '%{SQL-User-Name}' \
          AND nasipaddress     = '%{NAS-IP-Address}'"
 

             acctinputoctets    = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
                                  '%{%{Acct-Input-Octets}:-0}', \
             acctoutputoctets   = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
                                  '%{%{Acct-Output-Octets}:-0}', \
             acctterminatecause = '%{Acct-Terminate-Cause}', \
 
             connectinfo_stop   = '%{Connect-Info}' \
          WHERE acctsessionid   = '%{Acct-Session-Id}' \
          AND username          = BINARY '%{SQL-User-Name}' \
          AND nasipaddress      = '%{NAS-IP-Address}'"
 

             realm, nasipaddress, nasportid, \
             nasporttype, acctstarttime, acctstoptime, \
             acctsessiontime, acctauthentic, connectinfo_start, \
             connectinfo_stop, acctinputoctets, acctoutputoctets, \
             calledstationid, callingstationid, acctterminatecause, \
             servicetype, framedprotocol, framedipaddress) \
 
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \

             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Acct-Terminate-Cause}', \
             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"
 
# We add the following patch (freeradius V2 to V3) in order ALCASAR works
# be carefull : 'tolower' need now the 'expr' module
accounting {
	reference = "%{tolower:type.%{Acct-Status-Type}.query}"
	type {

Subversion Repositories ALCASAR

(root)//conf/radius/queries.conf – Rev 2706 → 3278

Rev 2706		Rev 3278
Line 152...		Line 152...
152	`SET \`	152	`SET \`
153	`acctstoptime = '%S', \`	153	`acctstoptime = '%S', \`
154	`acctsessiontime = unix_timestamp('%S') - \`	154	`acctsessiontime = unix_timestamp('%S') - \`
155	`unix_timestamp(acctstarttime), \`	155	`unix_timestamp(acctstarttime), \`
156	`acctterminatecause = '%{Acct-Terminate-Cause}', \`	156	`acctterminatecause = '%{Acct-Terminate-Cause}', \`
157	`acctstopdelay = %{%{Acct-Delay-Time}:-0} \`	-
158	`WHERE acctstoptime IS NULL \`	157	`WHERE acctstoptime IS NULL \`
159	`AND nasipaddress = '%{NAS-IP-Address}' \`	158	`AND nasipaddress = '%{NAS-IP-Address}' \`
160	`AND acctstarttime <= '%S'"`	159	`AND acctstarttime <= '%S'"`
161		160
162	`accounting_update_query = " \`	161	`accounting_update_query = " \`
Line 177...		Line 176...
177	`(acctsessionid, acctuniqueid, username, \`	176	`(acctsessionid, acctuniqueid, username, \`
178	`realm, nasipaddress, nasportid, \`	177	`realm, nasipaddress, nasportid, \`
179	`nasporttype, acctstarttime, acctsessiontime, \`	178	`nasporttype, acctstarttime, acctsessiontime, \`
180	`acctauthentic, connectinfo_start, acctinputoctets, \`	179	`acctauthentic, connectinfo_start, acctinputoctets, \`
181	`acctoutputoctets, calledstationid, callingstationid, \`	180	`acctoutputoctets, calledstationid, callingstationid, \`
182	`servicetype, framedprotocol, framedipaddress, \`	181	`servicetype, framedprotocol, framedipaddress) \`
183	`acctstartdelay, xascendsessionsvrkey) \`	-
184	`VALUES \`	182	`VALUES \`
185	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	183	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
186	`BINARY '%{SQL-User-Name}', \`	184	`BINARY '%{SQL-User-Name}', \`
187	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	185	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
188	`'%{NAS-Port-Type}', \`	186	`'%{NAS-Port-Type}', \`
Line 195...		Line 193...
195	`'%{%{Acct-Input-Octets}:-0}', \`	193	`'%{%{Acct-Input-Octets}:-0}', \`
196	`'%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`	194	`'%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`
197	`'%{%{Acct-Output-Octets}:-0}', \`	195	`'%{%{Acct-Output-Octets}:-0}', \`
198	`'%{Called-Station-Id}', '%{Calling-Station-Id}', \`	196	`'%{Called-Station-Id}', '%{Calling-Station-Id}', \`
199	`'%{Service-Type}', '%{Framed-Protocol}', \`	197	`'%{Service-Type}', '%{Framed-Protocol}', \`
200	`'%{Framed-IP-Address}', \`	198	`'%{Framed-IP-Address}')"`
201	`'0', '%{X-Ascend-Session-Svr-Key}')"`	-
202		199
203	`accounting_start_query = " \`	200	`accounting_start_query = " \`
204	`INSERT INTO ${acct_table1} \`	201	`INSERT INTO ${acct_table1} \`
205	`(acctsessionid, acctuniqueid, username, \`	202	`(acctsessionid, acctuniqueid, username, \`
206	`realm, nasipaddress, nasportid, \`	203	`realm, nasipaddress, nasportid, \`
207	`nasporttype, acctstarttime, acctstoptime, \`	204	`nasporttype, acctstarttime, acctstoptime, \`
208	`acctsessiontime, acctauthentic, connectinfo_start, \`	205	`acctsessiontime, acctauthentic, connectinfo_start, \`
209	`connectinfo_stop, acctinputoctets, acctoutputoctets, \`	206	`connectinfo_stop, acctinputoctets, acctoutputoctets, \`
210	`calledstationid, callingstationid, acctterminatecause, \`	207	`calledstationid, callingstationid, acctterminatecause, \`
211	`servicetype, framedprotocol, framedipaddress, \`	208	`servicetype, framedprotocol, framedipaddress) \`
212	`acctstartdelay, acctstopdelay, xascendsessionsvrkey) \`	-
213	`VALUES \`	209	`VALUES \`
214	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	210	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
215	`BINARY '%{SQL-User-Name}', \`	211	`BINARY '%{SQL-User-Name}', \`
216	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	212	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
217	`'%{NAS-Port-Type}', '%S', NULL, \`	213	`'%{NAS-Port-Type}', '%S', NULL, \`
218	`'0', '%{Acct-Authentic}', '%{Connect-Info}', \`	214	`'0', '%{Acct-Authentic}', '%{Connect-Info}', \`
219	`'', '0', '0', \`	215	`'', '0', '0', \`
220	`'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \`	216	`'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \`
221	`'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \`	217	`'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"`
222	`'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"`	-
223		218
224	`accounting_start_query_alt = " \`	219	`accounting_start_query_alt = " \`
225	`UPDATE ${acct_table1} SET \`	220	`UPDATE ${acct_table1} SET \`
226	`acctstarttime = '%S', \`	221	`acctstarttime = '%S', \`
227	`acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \`	-
228	`connectinfo_start = '%{Connect-Info}' \`	222	`connectinfo_start = '%{Connect-Info}' \`
229	`WHERE acctsessionid = '%{Acct-Session-Id}' \`	223	`WHERE acctsessionid = '%{Acct-Session-Id}' \`
230	`AND username = BINARY '%{SQL-User-Name}' \`	224	`AND username = BINARY '%{SQL-User-Name}' \`
231	`AND nasipaddress = '%{NAS-IP-Address}'"`	225	`AND nasipaddress = '%{NAS-IP-Address}'"`
232		226
Line 237...		Line 231...
237	`acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 \| \`	231	`acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 \| \`
238	`'%{%{Acct-Input-Octets}:-0}', \`	232	`'%{%{Acct-Input-Octets}:-0}', \`
239	`acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`	233	`acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`
240	`'%{%{Acct-Output-Octets}:-0}', \`	234	`'%{%{Acct-Output-Octets}:-0}', \`
241	`acctterminatecause = '%{Acct-Terminate-Cause}', \`	235	`acctterminatecause = '%{Acct-Terminate-Cause}', \`
242	`acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \`	-
243	`connectinfo_stop = '%{Connect-Info}' \`	236	`connectinfo_stop = '%{Connect-Info}' \`
244	`WHERE acctsessionid = '%{Acct-Session-Id}' \`	237	`WHERE acctsessionid = '%{Acct-Session-Id}' \`
245	`AND username = BINARY '%{SQL-User-Name}' \`	238	`AND username = BINARY '%{SQL-User-Name}' \`
246	`AND nasipaddress = '%{NAS-IP-Address}'"`	239	`AND nasipaddress = '%{NAS-IP-Address}'"`
247		240
Line 251...		Line 244...
251	`realm, nasipaddress, nasportid, \`	244	`realm, nasipaddress, nasportid, \`
252	`nasporttype, acctstarttime, acctstoptime, \`	245	`nasporttype, acctstarttime, acctstoptime, \`
253	`acctsessiontime, acctauthentic, connectinfo_start, \`	246	`acctsessiontime, acctauthentic, connectinfo_start, \`
254	`connectinfo_stop, acctinputoctets, acctoutputoctets, \`	247	`connectinfo_stop, acctinputoctets, acctoutputoctets, \`
255	`calledstationid, callingstationid, acctterminatecause, \`	248	`calledstationid, callingstationid, acctterminatecause, \`
256	`servicetype, framedprotocol, framedipaddress, \`	249	`servicetype, framedprotocol, framedipaddress) \`
257	`acctstartdelay, acctstopdelay) \`	-
258	`VALUES \`	250	`VALUES \`
259	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	251	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
260	`BINARY '%{SQL-User-Name}', \`	252	`BINARY '%{SQL-User-Name}', \`
261	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	253	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
262	`'%{NAS-Port-Type}', \`	254	`'%{NAS-Port-Type}', \`
Line 269...		Line 261...
269	`'%{%{Acct-Input-Octets}:-0}', \`	261	`'%{%{Acct-Input-Octets}:-0}', \`
270	`'%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`	262	`'%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`
271	`'%{%{Acct-Output-Octets}:-0}', \`	263	`'%{%{Acct-Output-Octets}:-0}', \`
272	`'%{Called-Station-Id}', '%{Calling-Station-Id}', \`	264	`'%{Called-Station-Id}', '%{Calling-Station-Id}', \`
273	`'%{Acct-Terminate-Cause}', \`	265	`'%{Acct-Terminate-Cause}', \`
274	`'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \`	266	`'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"`
275	`'0', '%{%{Acct-Delay-Time}:-0}')"`	-
276	`# We add the following patch (freeradius V2 to V3) in order ALCASAR works`	267	`# We add the following patch (freeradius V2 to V3) in order ALCASAR works`
277	`# be carefull : 'tolower' need now the 'expr' module`	268	`# be carefull : 'tolower' need now the 'expr' module`
278	`accounting {`	269	`accounting {`
279	`reference = "%{tolower:type.%{Acct-Status-Type}.query}"`	270	`reference = "%{tolower:type.%{Acct-Status-Type}.query}"`
280	`type {`	271	`type {`