Hi Brandon -
You are missing a closing “)” - see below.
I’m also not sure what “SQL-User-Name” is?
You might want to use %{Quote:User-Name} instead?
…..
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}', \
'%{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}’)”
…..
regards
Hugh
> On 7 Sep 2019, at 01:47, Brandon Shiers <[email protected]> wrote:
>
> Hugh,
>
> I've been looking at this. For some reason RADIATOR appears to be dropping
> down to the accounting_update_query_alt which does have a stored procedure
> for some reason. Is it possible it's due to the first insert fails due to
> the missing AcctStartDelay (which we don't use)?
>
> So I'm thinking I just need to specify my query like this (the top one is the
> original/built-in-query):
>
> 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, \
> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \
> VALUES \
> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
> '%{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}', \
> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
>
>
> Do I just changed it to this?
>
> 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}', \
> '%{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}'"
>
> Thanks,
> Brandon Shiers, RF Engineer
> 937 West Main Street
> Riverton, WY 82501
> 307.857.6704 (o)
> 307.840.2366 (c)
> 307.856.1499 (f)
> [email protected]
>
> -----Original Message-----
> From: Hugh Irvine
> Sent: Sunday, August 11, 2019 5:22 PM
> To: Brandon Shiers <[email protected]>
> Cc: Brandon Shiers <[email protected]>; [email protected]
> Subject: Re: [RADIATOR] Using FreeRadius Emulation for Accounting
>
>
> Hello Brandon -
>
> It looks like this is what is causing the problem:
>
> DATE_SUB('2019-08-11 13:03:56', INTERVAL ( + ) SECOND),
>
> which looks to me like a call to a stored procedure.
>
> As the error says, you should check first of all that the database actually
> has this defined, and if so what the correct syntax is to call it.
>
> Otherwise you can define the query you want to use in the AuthBy FREERADIUS
> configuration.
>
> regards
>
> Hugh
>
>
>> On 12 Aug 2019, at 05:14, Brandon Shiers <[email protected]> wrote:
>>
>> Hugh,
>>
>> I had a chance to implement this finally and I'm having some issues. I've
>> not altered the handler and AuthBy statement I'd sent in earlier. The first
>> one appears to be on my end for sure as I don't have the AcctStopDelay field
>> in my database. I need to fix that. The 2nd though seems to be about the
>> SQL syntax being used by the default FreeRADIUS accounting setup in
>> Radiator. What do I need to do to correct that?
>>
>>
>> Sun Aug 11 13:03:56 2019: ERR: do failed for 'UPDATE radacct SET
>> AcctStopTime = '2019-08-11 13:03:56', AcctSessionTime = '835',
>> AcctInputOctets = '76637657', AcctOutputOctets = '58091425',
>> AcctTerminateCause = 'User-Request', AcctStopDelay = '0', ConnectInfo_stop =
>> '' WHERE AcctSessionId = '81114f86' AND UserName = 'customerservice' AND
>> NASIPAddress = '216.67.161.1'': Unknown column 'AcctStopDelay' in 'field
>> list'
>>
>> Sun Aug 11 13:03:56 2019: ERR: do failed for 'INSERT into radacct
>> (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress,
>> NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime,
>> AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
>> AcctOutputOctets, CalledStationId, CallingStationId,
>> AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress,
>> AcctStartDelay, AcctStopDelay) values('81114f86', '',
>> 'customerservice', '', '216.67.161.1', '15814535', 'Ethernet',
>> DATE_SUB('2019-08-11 13:03:56', INTERVAL ( + ) SECOND), '2019-08-11
>> 13:03:56', '835', 'RADIUS', '', '', '76637657', '58091425',
>> 'GPONPPPOE', '0C:B6:D2:E0:C4:4A', 'User-Request', 'Framed-User',
>> 'PPP', '216.67.161.228', '0', '0')': You have an error in your SQL
>> syntax; check the manual that corresponds to your MariaDB server
>> version for the right syntax to use near ') SECOND), '2019-08-11
>> 13:03:56', '835', 'RADIUS', '', '', '76637657', '58091425' at line 1
>>
>> Thanks,
>> Brandon Shiers, RF Engineer
>> 937 West Main Street
>> Riverton, WY 82501
>> 307.857.6704 (o)
>> 307.840.2366 (c)
>> 307.856.1499 (f)
>> [email protected]
>>
>> -----Original Message-----
>> From: Hugh Irvine
>> Sent: Wednesday, July 31, 2019 4:29 PM
>> To: Brandon Shiers <[email protected]>
>> Cc: Brandon Shiers <[email protected]>;
>> [email protected]
>> Subject: Re: [RADIATOR] Using FreeRadius Emulation for Accounting
>>
>>
>> Hi Brandon -
>>
>> Yes correct - if you need to you can alter the queries as you’ve listed.
>>
>> regards
>>
>> Hugh
>>
>>> On 31 Jul 2019, at 23:32, Brandon Shiers <[email protected]> wrote:
>>>
>>> If I wanted to use the AuthByFreeRadiusSQL since my authorization handler
>>> for this particular service uses it, I presume all I'd need to do is the
>>> following:
>>>
>>> <Handler Client-Identifier=Fast-Fiber> <AuthBy FREERADIUSSQL>
>>> Identifier Fast-Fiber DBSource
>>> dbi:mysql:hostname=localhost;database=foo
>>> DBUsername foo
>>> DBAuth bar
>>> FailureBackoffTime 5
>>> Timeout 30
>>> </AuthBy>
>>> </Handler>
>>>
>>>
>>> From looking at the manual it looks like specifying the AcctOnoffQuery,
>>> AccountStartQuery,AcctStartQueryAlt,AcctUpdateQuery,AcctUpdateQueryAlt,AcctStopQuery,AcctStopQueryAlt
>>> are all optional, I'm presuming if I don't specify anything there the
>>> server would use the standard queries that say come in the dialup.conf file
>>> with a standard Freeradius install, is that correct, much like setting up
>>> authentication does?
>>>
>>> Thanks,
>>> Brandon Shiers, RF Engineer
>>> 937 West Main Street
>>> Riverton, WY 82501
>>> 307.857.6704 (o)
>>> 307.840.2366 (c)
>>> 307.856.1499 (f)
>>> [email protected]
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Hugh Irvine
>>> Sent: Tuesday, July 30, 2019 4:41 PM
>>> To: Brandon Shiers <[email protected]>
>>> Cc: [email protected]
>>> Subject: Re: [RADIATOR] Using FreeRadius Emulation for Accounting
>>>
>>>
>>> Hello Brandon -
>>>
>>> If you are using a FreeRadius SQL database you can use the AuthBy
>>> FREERADIUSSQL clause.
>>>
>>> See section 3.81 in the Radiator-4.23 reference manual (“doc/ref.pdf”).
>>>
>>> There is also an example in “goodies/freeradiussql.cfg”.
>>>
>>> Typically you would do something like this:
>>>
>>> …….
>>>
>>> # Handler to process accounting requests
>>>
>>> <Handler Request-Type = Accounting-Request>
>>> <AuthBy FREERADIUSSQL>
>>> …..
>>> </AuthBy>
>>> …..
>>> </Handler>
>>>
>>> # the rest of your Handlers for authentication
>>>
>>> …...
>>>
>>> Let me know if you have any further questions.
>>>
>>> regards
>>>
>>> Hugh
>>>
>>>
>>>> On 31 Jul 2019, at 02:24, Brandon Shiers <[email protected]>
>>>> wrote:
>>>>
>>>> Looking through the documentation and the goodies directory, I don't see a
>>>> real good example of how do do the accounting setup and write to my
>>>> accounting database. Any suggestions or real-world examples? I do have
>>>> devices that will send interim updates so I will need the capability to
>>>> account for that.
>>>> _______________________________________________
>>>> radiator mailing list
>>>> [email protected]
>>>> https://lists.open.com.au/mailman/listinfo/radiator
>>>
>>>
>>> --
>>>
>>> Hugh Irvine
>>> [email protected]
>>>
>>> Radiator: the most portable, flexible and configurable RADIUS server
>>> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
>>> Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
>>> TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER,
>>> SIM, etc.
>>> Full source on Unix, Linux, Windows, macOS, Solaris, VMS, NetWare etc.
>>>
>>
>>
>> --
>>
>> Hugh Irvine
>> [email protected]
>>
>> Radiator: the most portable, flexible and configurable RADIUS server
>> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
>> Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
>> TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER,
>> SIM, etc.
>> Full source on Unix, Linux, Windows, macOS, Solaris, VMS, NetWare etc.
>>
>
>
> --
>
> Hugh Irvine
> [email protected]
>
> Radiator: the most portable, flexible and configurable RADIUS server
> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
> Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, TTLS,
> PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER, SIM, etc.
> Full source on Unix, Linux, Windows, macOS, Solaris, VMS, NetWare etc.
>
--
Hugh Irvine
[email protected]
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER, SIM, etc.
Full source on Unix, Linux, Windows, macOS, Solaris, VMS, NetWare etc.
_______________________________________________
radiator mailing list
[email protected]
https://lists.open.com.au/mailman/listinfo/radiator
