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
