Hi again -
I also think in an AuthFREERADIUSSQL clause, you should be using
“AcctStartQuery”.
…..
<AuthBy FREERADIUSSQL>
…..
AcctStartQuery 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}’)
…..
You will probably also need to specify the table name directly - I’m not sure
what “${acct_table1}" is.
Radiator uses “radacct” for the table name.
Here is the standard query that Radiator runs for AcctStartQuery:
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('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', %0, '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%J', '0', '0', '%{Acct-Authentic}',
'%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')
regards
Hugh
> On 8 Sep 2019, at 09:01, Hugh Irvine <[email protected]> wrote:
>
>
> Hello Brandon -
>
> I didn’t notice the “=“ in your accounting_start_query - it shouldn’t be
> there.
>
> 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 22:17, Brandon Shiers <[email protected]> wrote:
>>
>> Thatwas the standard insert statement from what I understand in a basic
>> freeradius build. I dumped this in and it's till erroring out now:
>>
>> Sat Sep 7 06:14:50 2019: ERR: do failed for '= " 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 ('811153d4', '', '', '', '216.67.161.1', '15815639',
>> 'Ethernet', '50', NULL, '0', 'RADIUS', '', '', '0', '0', 'GPONPPPOE',
>> '50:46:5D:6C:7C:D8', '', 'Framed-User', 'PPP', '216.67.161.254')"': 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 '= " INSERT INTO
>> ${acct_table1} (acctsessionid, acctuniqueid, username, re' 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: Friday, September 06, 2019 8:41 PM
>> To: Brandon Shiers <[email protected]>
>> Cc: Brandon Shiers <[email protected]>; [email protected]
>> Subject: Re: [RADIATOR] Using FreeRadius Emulation for Accounting
>>
>>
>> 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.
>>
>
>
> --
>
> 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
--
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
