Radacct update at 3 hours
Hi, We want to have accounting records created every 3 hours in case the users are not disconnected to create daily/weekly/monthly report. I have gone through all the steps involved till modifying the accounting queries in sql.conf, i realized that my sql.conf is different from one listed at http://freeradius.org/radiusd/raddb/sql.conf Now i stalled that whether to modify. I am using FR 2.1.10. LNS is setup to send periodic updates every 3 hours and FR is updating the records but the acctstarttime is unchanged and acctstoptime is set to "null" but i can see that the acctinoctets and output octets are updated. We need to have separate record created in MYSQL radacct table once received update from the LNS. Any help on this would be appreciated. Thanks in advance Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't."
Maybe my way of doing things is not the right one, but I had to solve the same issue a while ago. What I did is I changed the simple update in sql.conf to a stored procedure call, and in my stored proc, I update the radacct entry, but I also insert another record into a "log" table. That way I can do my stats easily. I am sure there is an easiest way of doing it. On 11-12-02 10:22 AM, Hitesh Vinzoda wrote:
Hi,
We want to have accounting records created every 3 hours in case the users are not disconnected to create daily/weekly/monthly report.
I have gone through all the steps involved till modifying the accounting queries in sql.conf, i realized that my sql.conf is different from one listed at http://freeradius.org/radiusd/raddb/sql.conf
Now i stalled that whether to modify. I am using FR 2.1.10.
LNS is setup to send periodic updates every 3 hours and FR is updating the records but the acctstarttime is unchanged and acctstoptime is set to "null" but i can see that the acctinoctets and output octets are updated. We need to have separate record created in MYSQL radacct table once received update from the LNS.
Any help on this would be appreciated.
Thanks in advance Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in
"There are 10 types of people in this world. One who can understand binary and other's can't."
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Francois Gaudreault, ing. jr fgaudreault@inverse.ca :: +1.514.447.4918 (x130) :: www.inverse.ca Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence (www.packetfence.org)
Hi, Thanks for your prompt response. Could you please let me know where to include in sql.conf file to call the procedures. P.S. : we are using freeradius 2.1.10 Thanks Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't." ________________________________ From: Francois Gaudreault <fgaudreault@inverse.ca> To: freeradius-users@lists.freeradius.org Sent: Friday, 2 December 2011 9:32 PM Subject: Re: Radacct update at 3 hours Maybe my way of doing things is not the right one, but I had to solve the same issue a while ago. What I did is I changed the simple update in sql.conf to a stored procedure call, and in my stored proc, I update the radacct entry, but I also insert another record into a "log" table. That way I can do my stats easily. I am sure there is an easiest way of doing it. On 11-12-02 10:22 AM, Hitesh Vinzoda wrote: Hi,
We want to have accounting records created every 3 hours in case the users are not disconnected to create daily/weekly/monthly report.
I have gone through all the steps involved till modifying the accounting queries in sql.conf, i realized that my sql.conf is different from one listed at http://freeradius.org/radiusd/raddb/sql.conf
Now i stalled that whether to modify. I am using FR 2.1.10.
LNS is setup to send periodic updates every 3 hours and FR is updating the records but the acctstarttime is unchanged and acctstoptime is set to "null" but i can see that the acctinoctets and output octets are updated. We need to have separate record created in MYSQL radacct table once received update from the LNS.
Any help on this would be appreciated.
Thanks in advance Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in
"There are 10 types of people in this world. One who can understand binary and other's can't."
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Francois Gaudreault, ing. jr fgaudreault@inverse.ca :: +1.514.447.4918 (x130) :: www.inverse.ca Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence (www.packetfence.org) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
My current sql.conf looks like below and it does not have accounting sections at all. ========================================================= [root@br-radius01 raddb]# cat sql.conf # -*- text -*- ## ## sql.conf -- SQL modules ## ## $Id$ ###################################################################### # # Configuration for the SQL module # # The database schemas and queries are located in subdirectories: # # sql/DB/schema.sql Schema # sql/DB/dialup.conf Basic dialup (including policy) queries # sql/DB/counter.conf counter # sql/DB/ippool.conf IP Pools in SQL # sql/DB/ippool.sql schema for IP pools. # # Where "DB" is mysql, mssql, oracle, or postgresql. # sql { # # Set the database to one of: # # mysql, mssql, oracle, postgresql # database = "mysql" # # Which FreeRADIUS driver to use. # driver = "rlm_sql_${database}" # Connection info: server = "localhost" #port = 3306 login = "radius" password = "radpass" # Database table configuration for everything except Oracle radius_db = "radius" # If you are using Oracle then use this instead # radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))" # If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct" # Allow for storing data after authentication postauth_table = "radpostauth" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" # Table to keep group info usergroup_table = "radusergroup" # If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table # read_groups = yes # Remove stale session if checkrad does not see a double login deletestalesessions = yes # Print all SQL statements when in debug mode (-x) sqltrace = no sqltracefile = ${logdir}/sqltrace.sql # number of sql connections to make to server num_sql_socks = 5 # number of seconds to dely retrying on a failed database # connection (per_socket) connect_failure_retry_delay = 60 # lifetime of an SQL socket. If you are having network issues # such as TCP sessions expiring, you may need to set the socket # lifetime. If set to non-zero, any open connections will be # closed "lifetime" seconds after they were first opened. lifetime = 0 # Maximum number of queries used by an SQL socket. If you are # having issues with SQL sockets lasting "too long", you can # limit the number of queries performed over one socket. After # "max_qeuries", the socket will be closed. Use 0 for "no limit". max_queries = 0 # Set to 'yes' to read radius clients from the database ('nas' table) # Clients will ONLY be read on server startup. For performance # and security reasons, finding clients via SQL queries CANNOT # be done "live" while the server is running. # #readclients = yes # Table to keep radius client info nas_table = "nas" # Read driver-specific configuration $INCLUDE sql/${database}/dialup.conf } ================================================ Thanks Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't." ________________________________ From: Hitesh Vinzoda <hiteshvinzoda@yahoo.com> To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Sent: Friday, 2 December 2011 9:57 PM Subject: Re: Radacct update at 3 hours Hi, Thanks for your prompt response. Could you please let me know where to include in sql.conf file to call the procedures. P.S. : we are using freeradius 2.1.10 Thanks Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't." ________________________________ From: Francois Gaudreault <fgaudreault@inverse.ca> To: freeradius-users@lists.freeradius.org Sent: Friday, 2 December 2011 9:32 PM Subject: Re: Radacct update at 3 hours Maybe my way of doing things is not the right one, but I had to solve the same issue a while ago. What I did is I changed the simple update in sql.conf to a stored procedure call, and in my stored proc, I update the radacct entry, but I also insert another record into a "log" table. That way I can do my stats easily. I am sure there is an easiest way of doing it. On 11-12-02 10:22 AM, Hitesh Vinzoda wrote: Hi,
We want to have accounting records created every 3 hours in case the users are not disconnected to create daily/weekly/monthly report.
I have gone through all the steps involved till modifying the accounting queries in sql.conf, i realized that my sql.conf is different from one listed at http://freeradius.org/radiusd/raddb/sql.conf
Now i stalled that whether to modify. I am using FR 2.1.10.
LNS is setup to send periodic updates every 3 hours and FR is updating the records but the acctstarttime is unchanged and acctstoptime is set to "null" but i can see that the acctinoctets and output octets are updated. We need to have separate record created in MYSQL radacct table once received update from the LNS.
Any help on this would be appreciated.
Thanks in advance Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in
"There are 10 types of people in this world. One who can understand binary and other's can't."
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Francois Gaudreault, ing. jr fgaudreault@inverse.ca :: +1.514.447.4918 (x130) :: www.inverse.ca Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence (www.packetfence.org) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi all, I get this working now. Thanks a lot to you guys. Now i want to test one more feature, i.e. Is there a way where we can set the accounting updates to be triggered at a specific time of a day e.g. everynight at 23:59. My gut feeling is this has to be configured on the NAS. Thanks Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't." ________________________________ From: Alan DeKok <aland@deployingradius.com> To: Hitesh Vinzoda <hiteshvinzoda@yahoo.com>; FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Sent: Friday, 2 December 2011 10:09 PM Subject: Re: Radacct update at 3 hours Hitesh Vinzoda wrote:
My current sql.conf looks like below and it does not have accounting sections at all.
That's nice. We know. We're familiar with the default configuration files. Now read it. It *tells you* where the rest of the configuration is located. Alan DeKok.
On Sat, Dec 3, 2011 at 6:23 PM, Hitesh Vinzoda <hiteshvinzoda@yahoo.com> wrote:
Hi all,
I get this working now. Thanks a lot to you guys. Now i want to test one more feature, i.e. Is there a way where we can set the accounting updates to be triggered at a specific time of a day e.g. everynight at 23:59. My gut feeling is this has to be configured on the NAS.
Short version: You can't. Don't bother. Really. Long version: There's two attributes that radius can send that MAY be able to do what you want, in a rundabout fashion. First one is Session-Timout, which basically tells the NAS the maximum time the session can last before the NAS have to cut it off forcefully. In some types of connection (e.g. 3G, or ADSL with the modem dialing) the client will then try to reconnect immediately, so this might effectively have the same effect with what you want. You can use unlang with sql expansion (mysql has some nice function for date/time calculation) or exec to calculate the value of Session-Timeout which would make the NAS drops the connection at 23:59. The second one is Acct-Interim-Interval. Similar to above, you can calculate Acct-Interim-Interval in a way that you'd get an Acct-Interim packet at 23:59. For example: - the client is connected on 06:00 - you want interim updates to be sent (roughly) every 3 hours - If you set Acct-Interim-Interval to be 2 hours, 59 minutes and 10 seconds (i.e. 10750 seconds) the NAS should (hopefuly) send an Acct-Interim packet somewhere around 23:59. - you can use unlang with sql or exec (same as above) to perform the necessary Acct-Interim-Interval calculation, but it will be complicated. I suugest you don't bother with either. Really. Just set Acct-Interim-Interval into something managable (e.g. 1 hour), and for billing purposes just use whatever information available at that time (e.g. use last Acct-Interim sent before 23:59). -- Fajar
Hi Fajar, Thanks for your prompt response. This is really very helpful. I will try this at the first available opportunity. Thanks Hitesh Vinzoda Network Administrator +91-9924117399 www.vinzoda.in "There are 10 types of people in this world. One who can understand binary and other's can't." ________________________________ From: Fajar A. Nugraha <list@fajar.net> To: Hitesh Vinzoda <hiteshvinzoda@yahoo.com>; FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Sent: Saturday, 3 December 2011 6:38 PM Subject: Re: Radacct update at 3 hours On Sat, Dec 3, 2011 at 6:23 PM, Hitesh Vinzoda <hiteshvinzoda@yahoo.com> wrote:
Hi all,
I get this working now. Thanks a lot to you guys. Now i want to test one more feature, i.e. Is there a way where we can set the accounting updates to be triggered at a specific time of a day e.g. everynight at 23:59. My gut feeling is this has to be configured on the NAS.
Short version: You can't. Don't bother. Really. Long version: There's two attributes that radius can send that MAY be able to do what you want, in a rundabout fashion. First one is Session-Timout, which basically tells the NAS the maximum time the session can last before the NAS have to cut it off forcefully. In some types of connection (e.g. 3G, or ADSL with the modem dialing) the client will then try to reconnect immediately, so this might effectively have the same effect with what you want. You can use unlang with sql expansion (mysql has some nice function for date/time calculation) or exec to calculate the value of Session-Timeout which would make the NAS drops the connection at 23:59. The second one is Acct-Interim-Interval. Similar to above, you can calculate Acct-Interim-Interval in a way that you'd get an Acct-Interim packet at 23:59. For example: - the client is connected on 06:00 - you want interim updates to be sent (roughly) every 3 hours - If you set Acct-Interim-Interval to be 2 hours, 59 minutes and 10 seconds (i.e. 10750 seconds) the NAS should (hopefuly) send an Acct-Interim packet somewhere around 23:59. - you can use unlang with sql or exec (same as above) to perform the necessary Acct-Interim-Interval calculation, but it will be complicated. I suugest you don't bother with either. Really. Just set Acct-Interim-Interval into something managable (e.g. 1 hour), and for billing purposes just use whatever information available at that time (e.g. use last Acct-Interim sent before 23:59). -- Fajar
participants (4)
-
Alan DeKok -
Fajar A. Nugraha -
Francois Gaudreault -
Hitesh Vinzoda