[OpenSIPS-Users] CDRtool freeradius mysql error

Tue Jan 12 23:57:27 CET 2010

#
# Configuration for the Freeradius SQL module, when using MySQL stored  
procedures
# that rotate automatically tables in the format radacctYYYYMM
#
# It requires:
#
# 1. mediaproxy >=1.8.0 available from http://mediaproxy.ag-projects.com
#
# 2. patch to freeradius to support stored procedures available in
# setup/radius/freeradius/freeradius.patch
#
# 3. MySQL server >= 5.0.13
#
# 4. MySQL stored procedures created by Andrei Magureanu available in
# setup/radius/OpenSIPS/radius_accounting.proc
#
#
sql {
driver = "rlm_sql_mysql"
server = "12.*.*.218"
login = "radius"
password = "************"
radius_db = "radius"
sqltrace = no
sqltracefile = ${logdir}/sqltrace-%Y%m%d.log
num_sql_socks = 25
connect_failure_retry_delay = 60

accounting_start_query = "\
CALL insert_radacct_record( \
'radius', \
'%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{Billing-Party}', \
'%{Billing-Party}', \
'%{SIP-Proxy-IP}', \
'%{NAS-Port}', \
'%S', \
'0', \
'0', \
'0', \
'0', \
'%{Called-Station-Id}', \
'%{Calling-Station-Id}', \
'%{Sip-Response-Code}', \
'%{Service-Type}', \
'%{ENUM-TLD}', \
'%{Framed-IP-Address}', \
'%{Acct-Delay-Time}', \
'0', \
'%{Sip-Response-Code}', \
'%{Sip-Method}', \
'%{Sip-Translated-Request-URI}', \
'%{Sip-To-Tag}', \
'%{Sip-From-Tag}', \
'%{Sip-RPId}', \
'%{Source-IP}', \
'%{Source-Port}', \
'%{Canonical-URI}', \
'', \
'', \
'%{Sip-Application-Type}', \
'%{User-Agent}', \
'%{From-Header}' \
)"

accounting_stop_query = "\
CALL update_radacct_record( \
'radius', \
'%S', \
'%{Acct-Delay-Time}', \
'%{Connect-Info}', \
'%{X-RTP-Stat}', \
'%{Acct-Session-Id}', \
'%{Sip-To-Tag}', \
'%{Sip-From-Tag}' \
)"

accounting_update_query = "\
CALL update_raddact_record_mediaproxy( \
'radius', \
'%{Acct-Session-Time}', \
'%{Acct-Input-Octets}', \
'%{Acct-Output-Octets}', \
'%{Sip-User-Agents}', \
'%{Media-Codecs}', \
'%{Sip-Application-Type}', \
'%{Media-Info}', \
'0', \
'%{Acct-Session-Id}', \
'%{Sip-To-Tag}', \
'%{Sip-From-Tag}' \
)"

accounting_failed_query = "\
CALL insert_radacct_record( \
'radius', \
'%{Acct-Session-Id}', \
'%{Acct-Unique-Session-Id}', \
'%{Billing-Party}', \
'%{Billing-Party}', \
'%{SIP-Proxy-IP}', \
'%{NAS-Port}', \
'%S', \
'%S', \
'0', \
'0', \
'0', \
'%{Called-Station-Id}', \
'%{Calling-Station-Id}', \
'%{Sip-Response-Code}', \
'%{Service-Type}', \
'%{ENUM-TLD}', \
'%{Framed-IP-Address}', \
'%{Acct-Delay-Time}', \
'0', \
'%{Sip-Response-Code}', \
'%{Sip-Method}', \
'%{Sip-Translated-Request-URI}', \
MD5(RAND()), \
'%{Sip-From-Tag}', \
'%{Sip-RPId}', \
'%{Source-IP}', \
'%{Source-Port}', \
'%{Canonical-URI}', \
'', \
'', \
'%{Sip-Application-Type}', \
'%{User-Agent}', \
'%{From-Header}' \
)"
}

I am wondering if my OpenSIPS config is missing something. When you set up  
the following
modparam("acc", "radius_extra", User-Name=$Au; \
Calling-Station-Id=$from; \
Called-Station-Id=$to; \
Sip-Translated-Request-URI=$ru; \
Sip-RPid=$avp(s:rpid); \
Source-IP=$avp(s:source_ip); \
Source-Port=$avp(s:source_port); \
SIP-Proxy-IP=$avp(s:sip_proxy_ip); \
Canonical-URI=$avp(s:can_uri); \
Billing-Party=$avp(billing_party); \
Divert-Reason=$avp(s:divert_reason); \
User-Agent=$hdr(user-agent); \
Contact=$hdr(contact); \
Event=$hdr(event); \
ENUM-TLD=$avp(s:enum_tld)")

modparam("siptrace", "traced_user_avp", "$avp(s:traced_user)")

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.

On Jan 12, 2010 4:35pm, "Adrian Georgescu [via OpenSIPS (Open SIP Server)]"  
<ml-node+4294985-1189821650 at n2.nabble.com> wrote:

> Can you paste your sql.conf file?

> Adrian

> On Jan 12, 2010, at 8:28 PM, osiris123d wrote:

> >

> > I have read and re-installed CDRTool about 4 times and I don't think

> > I am

> > missing anything in the INSTALL.txt file. I apologize if you think

> > I am

> > just brushing through the install and not actually trying to figure

> > out the

> > issue before asking, but I have gone through the instructions many

> > times and

> > the issue is not sticking out. This is what I do to get Freeradius

> > installed and set up

> >

> >

> > Execute the following

> > apt-get install freeradius-xs freeradius-xs-mysql

> > mysqladmin -u root -p create radius

> > mysql -u root -p radius

> > /var/www/CDRTool/setup/radius/OpenSIPS/radacct.mysql

> >

> > ***********

> > At this point I do Option B instead of option A since I want "To

> > automatically create a table for each calendar month radacctYYYYMM"

> > instead of "To write data into one radacct table"

> > ***********

> > cp /var/www/CDRTool/setup/radius/OpenSIPS/radius_accounting.conf

> > /etc/freeradius/sql.conf

> >

> >

> > Grant access to radius user to the radius database (I put in a bunch

> > just to

> > troubleshoot that its not a permission issue)

> > GRANT ALL ON radius.* TO radius at 12.*.*.218 IDENTIFIED by

> > '**********';

> > GRANT ALL ON radius.* TO radius at localhost IDENTIFIED by

> > '**********';

> > GRANT ALL ON radius.* TO radius at 66.*.*.14 IDENTIFIED by

> > '**********';

> > GRANT ALL ON radius.* TO [hidden email] IDENTIFIED by

> > '**********';

> > GRANT ALL ON cdrtool.* TO locker at localhost IDENTIFIED by 'lockadm18';

> >

> > Execute the following

> > mysql -u root -p -h localhost radius

> > /var/www/CDRTool/setup/radius/OpenSIPS/radius_accounting.proc

> >

> >

> >

> > Edit the following

> > nano /etc/freeradius/client.conf

> > client 66.*.*.14 {

> > secret=YourSecret

> > nastype=other

> > shortname=sip

> > }

> >

> > client 66.*.*.13 {

> > secret=YourSecret

> > nastype=other

> > shortname=mediaproxy

> > }

> >

> > client 12.*.*.218 {

> > secret=YourSecret

> > nastype=other

> > shortname=radius

> > }

> >

> >

> > Edit the following

> > nano /etc/freeradius/sql.conf

> > driver = "rlm_sql_mysql"

> > server = "12.*.*.218"

> >

> > login = "radius"

> > password = "**********"

> > radius_db = "radius"

> >

> >

> > Edit the following

> > nano /etc/freeradius/radiusd.conf

> > accounting {

> > detail

> > sql

> > ok

> > anything about

> > the ok option, but your freeradius-xs package puts it here

> > }

> >

> > Edit the following

> > nano /etc/freeradius/proxy.conf

> > realm DEFAULT {

> > type = radius

> > authhost = 12.*.*.218:1812

> > Server

> > accthost = 12.*.*.218:1813

> > Server

> > secret = YourSecret

> > ldflag = fail_over

> > nostrip

> > }

> >

> >

> >

> > Verify that /etc/freeradius/dictionary looks like this

> >

> > # standard radius dictionary

> > $INCLUDE /usr/share/freeradius/dictionary

> >

> > # OpenSER dictionary

> > $INCLUDE /etc/freeradius/dictionary.openser

> > INSTALL.txt file

> > says opensips, but your freeradius-xs package installs this

> >

> > # MediaProxy dictionary

> > $INCLUDE /etc/freeradius/dictionary.mediaproxy

> >

> >

> >

> >

> > This is all the INSTALL.txt file says to do from a Freeradius

> > standpoint. I

> > don't see how I can mess up the sql.conf file since you basically

> > just have

> > to copy over radius_accounting.conf to /etc/freeradius/sql.conf and

> > edit the

> > "server" line and "password" line.

> >

> > The MySQL version is 5.1.41-3. So thats not an issue

> > Mediaproxy version is >=1.8.0

> > I installed Freeradius by doing the apt-get of AG Projects

> > Freeradius-xs

> > packages

> > I inserted the radius_accounting.proc stored procedures

> >

> > So not sure what I could have missed.

> >

> >

> >

> > Adrian Georgescu wrote:

> >>

> >> This has nothing to do with the software but with its configuration

> >> namely the Freeradius sql.conf file. Read the installation

> >> instructions and the sample files in CDRTool/setup/ directory.

> >> Adrian

> >>> I am still not able to get CDRTool to work because it never creates

> >> the

> >> months radacct table in the radius database. I have installed

> >> everything on

> >> Debian 5.0.3 by just using the following repositories

> >>

> >>

> >> _______________________________________________

> >> Users mailing list

> >> [hidden email]

> >> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
> >>

> >>

> >

> > --

> > View this message in context:  
> http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4293798.html
> > Sent from the OpenSIPS - Users mailing list archive at Nabble.com.

> >

> > _______________________________________________

> > Users mailing list

> > [hidden email]

> > http://lists.opensips.org/cgi-bin/mailman/listinfo/users

> _______________________________________________

> Users mailing list

> [hidden email]

> http://lists.opensips.org/cgi-bin/mailman/listinfo/users

> View message @  
> http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4294985.html

> To unsubscribe from Re: CDRtool freeradius mysql error, click here.

-- 
View this message in context: http://n2.nabble.com/CDRtool-freeradius-mysql-error-tp4293459p4295106.html
Sent from the OpenSIPS - Users mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.opensips.org/pipermail/users/attachments/20100112/9d04bcd0/attachment-0001.htm 