#
<br /># Configuration for the Freeradius SQL module, when using MySQL stored procedures
<br /># that rotate automatically tables in the format radacctYYYYMM
<br />#
<br /># It requires:
<br />#
<br /># 1. mediaproxy >=1.8.0 available from http://mediaproxy.ag-projects.com
<br />#
<br /># 2. patch to freeradius to support stored procedures available in
<br /># setup/radius/freeradius/freeradius.patch
<br />#
<br /># 3. MySQL server >= 5.0.13
<br />#
<br /># 4. MySQL stored procedures created by Andrei Magureanu available in
<br /># setup/radius/OpenSIPS/radius_accounting.proc
<br />#
<br />#
<br />sql {
<br /> driver = "rlm_sql_mysql"
<br /> server = "12.*.*.218"
<br /> login = "radius"
<br /> password = "************"
<br /> radius_db = "radius"
<br /> sqltrace = no
<br /> sqltracefile = ${logdir}/sqltrace-%Y%m%d.log
<br /> num_sql_socks = 25
<br /> connect_failure_retry_delay = 60
<br />
<br /> accounting_start_query = "\
<br /> CALL insert_radacct_record( \
<br /> 'radius', \
<br /> '%{Acct-Session-Id}', \
<br /> '%{Acct-Unique-Session-Id}', \
<br /> '%{Billing-Party}', \
<br /> '%{Billing-Party}', \
<br /> '%{SIP-Proxy-IP}', \
<br /> '%{NAS-Port}', \
<br /> '%S', \
<br /> '0', \
<br /> '0', \
<br /> '0', \
<br /> '0', \
<br /> '%{Called-Station-Id}', \
<br /> '%{Calling-Station-Id}', \
<br /> '%{Sip-Response-Code}', \
<br /> '%{Service-Type}', \
<br /> '%{ENUM-TLD}', \
<br /> '%{Framed-IP-Address}', \
<br /> '%{Acct-Delay-Time}', \
<br /> '0', \
<br /> '%{Sip-Response-Code}', \
<br /> '%{Sip-Method}', \
<br /> '%{Sip-Translated-Request-URI}', \
<br /> '%{Sip-To-Tag}', \
<br /> '%{Sip-From-Tag}', \
<br /> '%{Sip-RPId}', \
<br /> '%{Source-IP}', \
<br /> '%{Source-Port}', \
<br /> '%{Canonical-URI}', \
<br /> '', \
<br /> '', \
<br /> '%{Sip-Application-Type}', \
<br /> '%{User-Agent}', \
<br /> '%{From-Header}' \
<br /> )"
<br />
<br /> accounting_stop_query = "\
<br /> CALL update_radacct_record( \
<br /> 'radius', \
<br /> '%S', \
<br /> '%{Acct-Delay-Time}', \
<br /> '%{Connect-Info}', \
<br /> '%{X-RTP-Stat}', \
<br /> '%{Acct-Session-Id}', \
<br /> '%{Sip-To-Tag}', \
<br /> '%{Sip-From-Tag}' \
<br /> )"
<br />
<br /> accounting_update_query = "\
<br /> CALL update_raddact_record_mediaproxy( \
<br /> 'radius', \
<br /> '%{Acct-Session-Time}', \
<br /> '%{Acct-Input-Octets}', \
<br /> '%{Acct-Output-Octets}', \
<br /> '%{Sip-User-Agents}', \
<br /> '%{Media-Codecs}', \
<br /> '%{Sip-Application-Type}', \
<br /> '%{Media-Info}', \
<br /> '0', \
<br /> '%{Acct-Session-Id}', \
<br /> '%{Sip-To-Tag}', \
<br /> '%{Sip-From-Tag}' \
<br /> )"
<br />
<br /> accounting_failed_query = "\
<br /> CALL insert_radacct_record( \
<br /> 'radius', \
<br /> '%{Acct-Session-Id}', \
<br /> '%{Acct-Unique-Session-Id}', \
<br /> '%{Billing-Party}', \
<br /> '%{Billing-Party}', \
<br /> '%{SIP-Proxy-IP}', \
<br /> '%{NAS-Port}', \
<br /> '%S', \
<br /> '%S', \
<br /> '0', \
<br /> '0', \
<br /> '0', \
<br /> '%{Called-Station-Id}', \
<br /> '%{Calling-Station-Id}', \
<br /> '%{Sip-Response-Code}', \
<br /> '%{Service-Type}', \
<br /> '%{ENUM-TLD}', \
<br /> '%{Framed-IP-Address}', \
<br /> '%{Acct-Delay-Time}', \
<br /> '0', \
<br /> '%{Sip-Response-Code}', \
<br /> '%{Sip-Method}', \
<br /> '%{Sip-Translated-Request-URI}', \
<br /> MD5(RAND()), \
<br /> '%{Sip-From-Tag}', \
<br /> '%{Sip-RPId}', \
<br /> '%{Source-IP}', \
<br /> '%{Source-Port}', \
<br /> '%{Canonical-URI}', \
<br /> '', \
<br /> '', \
<br /> '%{Sip-Application-Type}', \
<br /> '%{User-Agent}', \
<br /> '%{From-Header}' \
<br /> )"
<br />}
<br />
<br />
<br />
<br />I am wondering if my OpenSIPS config is missing something. When you set up the following
<br />modparam("acc", "radius_extra", User-Name=$Au; \
<br /> Calling-Station-Id=$from; \
<br /> Called-Station-Id=$to; \
<br /> Sip-Translated-Request-URI=$ru; \
<br /> Sip-RPid=$avp(s:rpid); \
<br /> Source-IP=$avp(s:source_ip); \
<br /> Source-Port=$avp(s:source_port); \
<br /> SIP-Proxy-IP=$avp(s:sip_proxy_ip); \
<br /> Canonical-URI=$avp(s:can_uri); \
<br /> Billing-Party=$avp(billing_party); \
<br /> Divert-Reason=$avp(s:divert_reason); \
<br /> User-Agent=$hdr(user-agent); \
<br /> Contact=$hdr(contact); \
<br /> Event=$hdr(event); \
<br /> ENUM-TLD=$avp(s:enum_tld)")
<br />
<br />
<br />
<br />
<br />
<br />modparam("siptrace", "traced_user_avp", "$avp(s:traced_user)")
<br />
<br />do I need to fill in what all the $avp variables will be or does the Radius dictionary files take are of this? I noticed this today and am thinking that the radacct month database is not getting created because these values are never passed. I also need to figure out what to do with siptrace since I haven't used it yet.
<br />
<br />
<br />
<br />
<br />
<br />
<br />On Jan 12, 2010 4:35pm, "Adrian Georgescu [via OpenSIPS (Open SIP Server)]" <<a href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=4295106&i=0" target="_top" rel="nofollow">[hidden email]</a>> wrote:
<div class='shrinkable-quote'><br />>
<br />>
<br />>
<br />>
<br />> Can you paste your sql.conf file?
<br />>
<br />>
<br />>
<br />> Adrian
<br />>
<br />>
<br />>
<br />> On Jan 12, 2010, at 8:28 PM, osiris123d wrote:
<br />>
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > I have read and re-installed CDRTool about 4 times and I don't think
<br />>
<br />>
<br />> > I am
<br />>
<br />>
<br />> > missing anything in the INSTALL.txt file. I apologize if you think
<br />>
<br />>
<br />> > I am
<br />>
<br />>
<br />> > just brushing through the install and not actually trying to figure
<br />>
<br />>
<br />> > out the
<br />>
<br />>
<br />> > issue before asking, but I have gone through the instructions many
<br />>
<br />>
<br />> > times and
<br />>
<br />>
<br />> > the issue is not sticking out. This is what I do to get Freeradius
<br />>
<br />>
<br />> > installed and set up
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Execute the following
<br />>
<br />>
<br />> >         apt-get install freeradius-xs freeradius-xs-mysql
<br />>
<br />>
<br />> >         mysqladmin -u root -p create radius
<br />>
<br />>
<br />> >         mysql -u root -p radius
<br />>
<br />> > /var/www/CDRTool/setup/radius/OpenSIPS/radacct.mysql
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         ***********
<br />>
<br />>
<br />> >         At this point I do Option B instead of option A since I want "To
<br />>
<br />>
<br />> > automatically create a table for each calendar month radacctYYYYMM"
<br />>
<br />>
<br />> >         instead of "To write data into one radacct table"
<br />>
<br />>
<br />> >         ***********
<br />>
<br />>
<br />> >         cp /var/www/CDRTool/setup/radius/OpenSIPS/radius_accounting.conf
<br />>
<br />>
<br />> > /etc/freeradius/sql.conf
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Grant access to radius user to the radius database (I put in a bunch
<br />>
<br />>
<br />> > just to
<br />>
<br />>
<br />> > troubleshoot that its not a permission issue)
<br />>
<br />>
<br />> >         GRANT ALL ON radius.* TO radius@12.*.*.218 IDENTIFIED by
<br />>
<br />>
<br />> > '**********';
<br />>
<br />>
<br />> >         GRANT ALL ON radius.* TO radius@localhost IDENTIFIED by
<br />>
<br />>
<br />> > '**********';
<br />>
<br />>
<br />> >         GRANT ALL ON radius.* TO radius@66.*.*.14 IDENTIFIED by
<br />>
<br />>
<br />> > '**********';
<br />>
<br />>
<br />> >         GRANT ALL ON radius.* TO [hidden email] IDENTIFIED by
<br />>
<br />>
<br />> > '**********';
<br />>
<br />>
<br />> >         GRANT ALL ON cdrtool.* TO locker@localhost IDENTIFIED by 'lockadm18';
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Execute the following
<br />>
<br />>
<br />> >         mysql -u root -p -h localhost radius
<br />>
<br />> > /var/www/CDRTool/setup/radius/OpenSIPS/radius_accounting.proc
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Edit the following
<br />>
<br />>
<br />> >         nano /etc/freeradius/client.conf
<br />>
<br />>
<br />> >         client 66.*.*.14 {
<br />>
<br />> >         secret=YourSecret
<br />>
<br />>
<br />> >         nastype=other
<br />>
<br />>
<br />> >         shortname=sip
<br />>
<br />>
<br />> >         }
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         client 66.*.*.13 {
<br />>
<br />> >         secret=YourSecret
<br />>
<br />>
<br />> >         nastype=other
<br />>
<br />>
<br />> >         shortname=mediaproxy
<br />>
<br />>
<br />> >         }
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         client 12.*.*.218 {
<br />>
<br />> >         secret=YourSecret
<br />>
<br />>
<br />> >         nastype=other
<br />>
<br />>
<br />> >         shortname=radius
<br />>
<br />>
<br />> >         }
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         
<br />>
<br />>
<br />> > Edit the following
<br />>
<br />>
<br />> >         nano /etc/freeradius/sql.conf
<br />>
<br />>
<br />> >         driver = "rlm_sql_mysql"
<br />>
<br />>
<br />> >         server = "12.*.*.218"
<br />>
<br />>
<br />> >
<br />>
<br />> >         login = "radius"
<br />>
<br />>
<br />> >         password = "**********"
<br />>
<br />>
<br />> >         radius_db = "radius"
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Edit the following
<br />>
<br />>
<br />> >         nano /etc/freeradius/radiusd.conf
<br />>
<br />>
<br />> >         accounting {
<br />>
<br />>
<br />> >         detail
<br />>
<br />>
<br />> >         sql
<br />>
<br />>
<br />> >         ok
<br />>
<br />> > anything about
<br />>
<br />>
<br />> > the ok option, but your freeradius-xs package puts it here
<br />>
<br />>
<br />> >         }
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Edit the following
<br />>
<br />>
<br />> >         nano /etc/freeradius/proxy.conf
<br />>
<br />>
<br />> >         realm DEFAULT {
<br />>
<br />>
<br />> >         type = radius
<br />>
<br />>
<br />> >         authhost = 12.*.*.218:1812
<br />>
<br />> > Server
<br />>
<br />>
<br />> >         accthost = 12.*.*.218:1813
<br />>
<br />> > Server
<br />>
<br />>
<br />> >         secret = YourSecret
<br />>
<br />>
<br />> >         ldflag = fail_over
<br />>
<br />>
<br />> >         nostrip
<br />>
<br />>
<br />> >         }
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Verify that /etc/freeradius/dictionary looks like this
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         # standard radius dictionary
<br />>
<br />>
<br />> >         $INCLUDE /usr/share/freeradius/dictionary
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         # OpenSER dictionary
<br />>
<br />>
<br />> >         $INCLUDE /etc/freeradius/dictionary.openser
<br />>
<br />> > INSTALL.txt file
<br />>
<br />>
<br />> > says opensips, but your freeradius-xs package installs this
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >         # MediaProxy dictionary
<br />>
<br />>
<br />> >         $INCLUDE /etc/freeradius/dictionary.mediaproxy
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > This is all the INSTALL.txt file says to do from a Freeradius
<br />>
<br />>
<br />> > standpoint. I
<br />>
<br />>
<br />> > don't see how I can mess up the sql.conf file since you basically
<br />>
<br />>
<br />> > just have
<br />>
<br />>
<br />> > to copy over radius_accounting.conf to /etc/freeradius/sql.conf and
<br />>
<br />>
<br />> > edit the
<br />>
<br />>
<br />> > "server" line and "password" line.
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > The MySQL version is 5.1.41-3. So thats not an issue
<br />>
<br />>
<br />> > Mediaproxy version is >=1.8.0
<br />>
<br />>
<br />> > I installed Freeradius by doing the apt-get of AG Projects
<br />>
<br />>
<br />> > Freeradius-xs
<br />>
<br />>
<br />> > packages
<br />>
<br />>
<br />> > I inserted the radius_accounting.proc stored procedures
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > So not sure what I could have missed.
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > Adrian Georgescu wrote:
<br />>
<br />>
<br />> >>
<br />>
<br />>
<br />> >> This has nothing to do with the software but with its configuration
<br />>
<br />>
<br />> >> namely the Freeradius sql.conf file. Read the installation
<br />>
<br />>
<br />> >> instructions and the sample files in CDRTool/setup/ directory.
<br />>
<br />>
<br />> >> Adrian
<br />>
<br />>
<br />> >>> I am still not able to get CDRTool to work because it never creates
<br />>
<br />>
<br />> >> the
<br />>
<br />>
<br />> >> months radacct table in the radius database. I have installed
<br />>
<br />>
<br />> >> everything on
<br />>
<br />>
<br />> >> Debian 5.0.3 by just using the following repositories
<br />>
<br />>
<br />> >>
<br />>
<br />>
<br />> >>
<br />>
<br />>
<br />> >> _______________________________________________
<br />>
<br />>
<br />> >> Users mailing list
<br />>
<br />>
<br />> >> [hidden email]
<br />>
<br />>
<br />> >> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
<br />> >>
<br />>
<br />>
<br />> >>
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > --
<br />>
<br />>
<br />> > View this message in context: http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4293798.html
<br />> > Sent from the OpenSIPS - Users mailing list archive at Nabble.com.
<br />>
<br />>
<br />> >
<br />>
<br />>
<br />> > _______________________________________________
<br />>
<br />>
<br />> > Users mailing list
<br />>
<br />>
<br />> > [hidden email]
<br />>
<br />>
<br />> > http://lists.opensips.org/cgi-bin/mailman/listinfo/users
<br />>
<br />>
<br />> _______________________________________________
<br />>
<br />>
<br />> Users mailing list
<br />>
<br />>
<br />> [hidden email]
<br />>
<br />>
<br />> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
<br />>
<br />>
<br />>
<br />>
<br />>
<br />>
<br />>
<br />>
<br />>
<br />> View message @ http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4294985.html
<br />>
<br />>
<br />>
<br />>
<br />> To unsubscribe from Re: CDRtool freeradius mysql error, click here.
<br />>
<br />>
<br />>
<br />>
<br />>
<br />>
<br />></div>
<br><hr align="left" width="300">
View this message in context: <a href="http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4295106.html">Re: Re: [OpenSIPS-Users] CDRtool freeradius mysql error</a><br>
Sent from the <a href="http://n2.nabble.com/OpenSIPS-Users-f1449235.html">OpenSIPS - Users mailing list archive</a> at Nabble.com.<br>