[RADIATOR] Using FreeRadius Emulation for Accounting

Hugh Irvine hugh at open.com.au
Sun Aug 11 23:21:51 UTC 2019


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 <brandon.shiers at cerento.com> 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)
> brandon.shiers at cerento.com
> 
> -----Original Message-----
> From: Hugh Irvine 
> Sent: Wednesday, July 31, 2019 4:29 PM
> To: Brandon Shiers <brandon.shiers at cerento.com>
> Cc: Brandon Shiers <brandon.shiers at wyoming.com>; radiator at lists.open.com.au
> 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 <brandon.shiers at cerento.com> 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)
>> brandon.shiers at cerento.com
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> -----Original Message-----
>> From: Hugh Irvine
>> Sent: Tuesday, July 30, 2019 4:41 PM
>> To: Brandon Shiers <brandon.shiers at wyoming.com>
>> Cc: radiator at lists.open.com.au
>> 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 <brandon.shiers at wyoming.com> 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
>>> radiator at lists.open.com.au
>>> https://lists.open.com.au/mailman/listinfo/radiator
>> 
>> 
>> --
>> 
>> Hugh Irvine
>> hugh at open.com.au
>> 
>> 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
> hugh at open.com.au
> 
> 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
hugh at open.com.au

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.



More information about the radiator mailing list