Re: mysql accounting connect speeds
And the errors are?
Ivan Kalik
Kalik Informatika ISP
Dana 16/7/2007, Jeff [EMAIL PROTECTED] piše:
I need to log connect speeds from users
At any rate things working fine from our own carrier globalpops to capture
these on the start packet
but Yournetplus for some reason it doesn't work.
I see this info in the update accounting packet so i thought I would modify
the update query but It gives errors
anyone know why this is wrong.. it stops right at the AscendDataRate ='26400'
for example then nothing after
Trying to gather the Ascend-Data-Rate and USR-Connect-Speed
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscenDataRate = '%{Ascend-Xmit-Rate}' \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '24000' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:24 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:37 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:42 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
_
From: [EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:06:28 -0400
Subject: Re: mysql accounting connect speeds
And the errors are?
Ivan Kalik
Kalik Informatika ISP
Dana 16/7/2007, Jeff [EMAIL PROTECTED] piše:
I need to log connect speeds from users
At any rate things working fine from our own carrier globalpops to capture
these on the start packet
but Yournetplus for some reason it doesn't work.
I see this info in the update accounting packet so i thought I would modify
the update query but It gives errors
anyone know why this is wrong.. it stops right at the AscendDataRate ='26400'
for example then nothing after
Trying to gather the Ascend-Data-Rate and USR-Connect-Speed
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscenDataRate = '%{Ascend-Xmit-Rate}' \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql accounting connect speeds
Yes, and the AscendDataRate too.
I get the inserts fine on the start packet and the data goes right in as
suppose too.
all works fine this way for our GlobalPOPS and all data shows up and into sql
using this line in the start
---
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, AscendDataRate, USRConnectSpeed,
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '',
'0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{Acct-Delay-Time}', '%{Ascend-Xmit-Rate}', '%{USR-Connect-Speed}', '0')
---
data goes right into mysql tables
But YNP for some reason most miss the start, so I thought maybe I could grab
them on the update query cause i see one or the other on in update packet, for
ynp so then i would have what i need, but as i stated this errors out with the
error i mentioned below trying to do this
Jeff
_
From: Hugh Messenger [mailto:[EMAIL PROTECTED]
To: 'FreeRadius users mailing list' [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:40:53 -0400
Subject: RE: mysql accounting connect speeds
Jeff said:
USRConnectSpeed = '%{USR-Connect-Speed}' \
Did you actually add a USRConnectSpeed column to the radacct table? There
isn't one by default.
-- hugh
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote: Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting ALIVE record - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = '' WHERE AcctSess' at line 1 You need a comma between data items: 'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' ^^^ -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
ok heres what i have now
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscendDataRate = '%{Ascend-Data-Rate}', \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
an heres the new error
Mon Jul 16 12:49:19 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:35 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:40 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:59 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:59:34 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL
accounting ALIVE record - You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'AscendDataRate = '24000' USRConnectSpeed =
'' WHERE AcctSess' at line 1
You need a comma between data items:
'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess'
^^^
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Yes. You are missing commas before AscendDataRate and USRConnectSpeed expressions that you have added to the update query. Ivan Kalik Kalik Informatika ISP Dana 16/7/2007, Dennis Skinner [EMAIL PROTECTED] piše: Jeff wrote: Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting ALIVE record - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = '' WHERE AcctSess' at line 1 You need a comma between data items: 'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' ^^^ -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote:
AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
its not i do not understand its just these stupid bi-focals i have a hard time
seeing.
I overlooked that, sorry for being a blind idiot
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 13:54:02 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
