Thursday, November 15, 2007

SER Load balancing (SIP proxy load balancing)

SER provide Dispatcher Module for call load balancing. First compile dispatcher module and make changes in ser.cfg .

Dispatcher module configuration step by step---

cd ser-0.9.6
make modules modules=modules/dispatcher
cp modules/dispatcher/dispatcher.so /usr/local/lib/ser/modules/

Create Destination address (sip proxy/ gateways address) list file

vi /usr/local/etc/ser/dispatcher.list

Copy & paste following lines

--------------------------------
# proxies
#Group Protocoltype:proxy/gateway address:port

1 sip:127.0.0.1:5080
1 sip:127.0.0.1:5082

# gateways
3 sip:127.0.0.1:7070
3 sip:127.0.0.1:7072
3 sip:127.0.0.1:7074

--------------------------------

Add following configuration in ser.cfg

#load dispatcher module
loadmodule " /usr/local/lib/ser/modules/dispatcher.so"

#include dispatcher list (sip proxy address)
modparam("dispatcher", "list_file", "/usr/local/etc/ser/dispatcher.list ")
# if you wants to use focally proxy group 1 then
# modparam("dispatcher", "force_dst", 1)

# make the changes rout section where you was configure host forwarding

route{
if ( !mf_process_maxfwd_header("10") )
{
sl_send_reply("483","To Many Hops");
drop();
};

ds_select_dst("1", "0");
# ds_select_dst("3", "0");
# 1 means call load balancing on group 1 (proxy) & 3 means load balancing on group 3(gateways).

forward(uri:host, uri:port);
# t_relay();
}

After configuring ser.cfg restart ser and enjoy.

Friday, May 25, 2007

Asterisk calling card system & voip Billing syetsm IPPBX & softswitch

Openvoip's is working in open source voip project. GDcalling card system is good for small & enterprise business. GD Communication is asterisk, SER, Radius Based system. for more information http://www.openvoips.com.

Wednesday, April 25, 2007

Open Source VoIP Apps & Resources

here — 74 open source apps tucked into categories that you can use "as is" or change to fit your specific VoIP needs.

The following apps and resources are categorized by SIP, H.323, IAX, and RTP protocols and include clients, libraries, gatekeepers, and any other open source resource available for those specific protocols plus PBX and IVR platforms. You'll also find tools like faxware, voicemail apps, and middleware that applies to one or more of the previously mentioned protocols.

H.323 Clients (User Agents)

VoIP traditionally uses H.323, a rather complicated protocol that uses multiple ports and a binary code for data. But apps like FreeSWITCH make H.323 seem like a piece of cake with its all-in-one application. The following H.323 clients are broken down into Multiplatform, Linux, MacOS X, and Windows.

Multiplatform

  1. FreeSWITCH - FreeSWITCH is a telephony platform designed to facilitate the creation of voice and chat driven products scaling from a soft-phone up to a soft-switch. It can be used as a simple switching engine, a media gateway or a media server to host IVR applications using simple scripts or XML to control the callflow. FreeSWITCH runs on several operating systems including Windows, Max OS X, Linux, BSD, and Solaris on both 32- and 64- bit platforms. Note: FreeSWITCH is also multiprotocol, as it works with SIP, IAX2 and GoogleTalk to make it easy to interface with other open source PBX systems.

  2. YATE - Yate (Yet Another Telephony Engine) is a next-generation telephony engine that is the first open source telephony application capable of handling 600 H323 calls; while currently focused on Voice over Internet Protocol (VoIP) and PSTN, its power lies in its ability to be easily extended. Voice, video, data and instant messaging can all be unified under Yate's flexible routing engine, maximizing communications efficiency and minimizing infrastructure costs for businesses. YATE can be used for anything from a VoIP server to an IVR engine. The software is written in C++ and it supports scripting in various programming languages (such as those supported by the currently implemented embedded PHP, Python and Perl interpreters) and even any Unix shell. Note: YATE is multiprotocol, as it works with SIP and IAX, and H.323 protocol is stable supported just by Yate. The most used application of Yate is as a SIP-H323 translator because is the only open source stable translator.

Linux

  1. Ekiga - Ekiga (formely known as GnomeMeeting) is an open source VoIP and video conferencing application for GNOME. Note: Ekiga uses both the H.323 and SIP protocols. It supports many audio and video codecs, and is interoperable with other SIP compliant software and also with Microsoft NetMeeting.

MacOS X

  1. XMeeting - XMeeting is the first H.323 compatible video conferencing client for Mac OS X.

Windows

  1. OpenH323 Project - The OpenH323 project aims to create a full featured, interoperable implementation of the ITU-T H.323 teleconferencing protocol that can be used by personal developers and by commercial users without charge.

H.323 Gatekeeper

  1. OpenH323 Gatekeeper - The GNU Gatekeeper (GnuGk) is a full featured cross-platform H.323 gatekeeper, available freely under GPL license.

H.232 Radius Platform

  1. BSDRadius - While there are quite large number of Radius servers (including free) available in the world, little number of them (if any) are developed with VoIP specific needs in mind. BSDRadius is a RADIUS - compliant AAA (Authentication, Authorization, Accounting) server with CHAP-password authentication for H.323. Platform-independent,
    but has not been tested on Windows.

SIP Clients (User Agents)

SIP (Session Initiation Protocol) is currently described by the rfc2543SIP is a popular open standard replacement from IETF (Internet Engineering TasForce) for H.323 signaling standard for managing multimedia session initiation. SIP can be used to initiate voice, video and multimedia sessions, for both interactive applications (e.g. an IP phone call or a videoconference) and not interactive ones (e.g. a Video Streaming). It is the more promising candidate as call setup signaling for the present day and future IP based telephony services, as it has been also proposed for session initiation related uses, such as for messaging, gaming, etc.SIP needs two ports, one for the command exchange and one for the RTP stream which contains the voice. It's easier to work with firewalls than H.323, but you still need to have a proxy running. The following SIP UAs are divided into two groups for Multiplatform and Linux only:

Multi-Platform

  1. SFLphone - A nifty little default skin (Metal Gear) for SFLphone holds a multi-protocol (SIP/IAX) multi-GUI desktop VoIP phone for use in Desktop environments. The project is being developed on Linux, but should ("and must") be portable to various flavors of BSD operating systems (and maybe win32) with some involvement.

  2. Linphone - With linphone you can communicate freely with people over the internet, with voice, video, and text instant messaging. Linphone is stable under Linux, but FreeBSD and OpenBSD are reported to work.

  3. Minisip - Minisip was developed by Ph.D and Master students at the Royal Institute of Technology (KTH, Stockholm, Sweden). It can be used to make phone calls, instant message and videocalls to your buddies connected to the same SIP network. Runs on multiple Operating Systems (Linux PC, Linux familiar IPAQ PDA, Windows XP and soon Windows Mobile 2003 SE).

  4. OpenWengo - The flagship product of the OpenWengo project is a softphone which allows you to make free PC to PC video and voice calls, and to integrate all your IM contacts in one place. Through their partnership with Wengo, they also offer very cheap PC to telephone and SMS rates. Available for Linux, MacOSX, and Windows.

  5. PhoneGaim - Make phone calls to your friends and family directly from your Linspire computer with the latest software from Linspire. PhoneGaim is built right into Gaim.

  6. sipXtapi - sipXtapi is a comprehensive client library and software development kit (SDK) for SIP-based user agents. It includes SIP signaling support as well as a media framework. A complete and very feature rich softphone can be built easily by adding a graphical user interface on top of sipXtapi. Alternatively, sipXtapi was engineered to be embedded into existing applications adding real-time communications to such applications. sipXtapi is primarily developed under WIN32; however, sipXtapi can be built and used under Linux and MacOs X. WinCE support is in development.

  7. OpenZoep - OpenZoep (pronounced "open soup"), developed by Voipster, is a client-side telephony and instant messaging (IM) communications engine. It supports computer-to-computer (peer-to-peer) VoIP calls, instant messaging, and outbound PSTN and SIP calls to free and premium SIP providers.

Linux

  1. Cockatoo - Cockatoo is a project that focuses on implementing SIP/SIMPLE as an extension for Thunderbird (XPCOM component/XUL interface) that enables users to phone contacts from an address book and see their presence state. Functionalities are included into Thunderbird as an XPCOM component.

  2. YeaPhone - The goal of the YeaPhone project is to bring VoIP-Software together with the Yealink USB handset (USB-P1K) and at the same time make a PC keyboard and monitor unnecessary. This makes YeaPhone ideal for "Embedded Devices" as these do typically need extra devices for user interaction (in this case the handset) while working very energy efficient.

  3. Twinkle - Twinkle is a soft phone for your voice over IP communications using the SIP protocol. You can use it for direct IP phone to IP phone communication or in a network using a SIP proxy to route your calls.

Windows

  1. 1videoConference - 1VideoConference allows its Web, Audio/ Video phone, Skype, Msn and Yahoo users to instantly participate in live web conferences without the need for lengthy downloads or complicated installations. Simply drop a small piece of code onto your website and instantly create an online video conference room. All you need is a web cam and an internet connection and seconds later you can show presentations, share applications or users' desktops, hold live webinars, discuss new strategies face to face with business partners, and more...

SIP Proxies

  1. Open Source SIP - Open Source SIP was created in March 2006 as a project to foster the development of commercially viable SIP applications. The Open Source SIP project is sponsored by Solegy, and draws on over six years of research and development.

  2. Partysip - Partysip is a modular application where some capabilities are added and removed through GPL plugins. Depending on the list of included plugins, partysip can be used as a SIP registrar, a SIP redirect server or statefull server, or a SIP service provider (game session, answering machine, etc.).

  3. MjSip - MjSip is a complete java-based implementation of a SIP stack that provides API and implementation bound together into one package. The MjSip stack has been used in research activities by Dpt. of Information Engineering at University of Parma and by DIE - University of Roma “Tor Vergata”. MjSip includes all classes and methods for creating SIP-based applications.

  4. OpenSER - OpenSER is an open source GPL project that aims to develop a robust and scalable SIP server. Spawned from FhG FOKUS SIP Express Router (SER) by two core developers and one main contributor of SER, OpenSER promotes a development strategy open for contributions.

  5. SIP Express Router - SIP Express Router (ser) is a high-performance, configurable, free SIP server. It can act as registrar, proxy or redirect server. SER features an application-server interface, presence support, SMS gateway, SIMPLE2Jabber gateway, RADIUS/syslog accounting and authorization, server status monitoring, FCP security, etc. Web-based user provisioning, serweb, available.

  6. Siproxd - Siprox is an proxy/masquerading daemon for the SIP protocol that handles registrations of SIP clients on a private IP network and performs rewriting of the SIP message bodies to make SIP connections possible via an masquerading firewall. It allows SIP clients (like kphone, linphone) to work behind an IP masquerading firewall or router.

SIP Protocol Stacks and Libraries

  1. OpenSIPStack - The OpenSIPStack Library is an implementation of the Session Initiation Protocol as described in RFC 3261. The primary goal of the library is to provide application developers with a fully compliant interface to the SIP protocol with scalability and stability in mind. The OpenSIPStack Library has both low level interface and high level interface ideal for use in SIP Proxies, Presence Servers, Softphones and Instant Messaging clients.

  2. The GNU oSIP Library - This library aims to provide multimedia and telecom software developers an easy and powerful interface to initiate and control SIP based sessions in their applications.

  3. The eXtended osip Library - eXosip is a library that hides the complexity of using the SIP protocol for mutlimedia session establishment. This protocol is mainly to be used by VoIP telephony applications (endpoints or conference server) but might be also usefull for any application that wish to establish sessions like multiplayer games.

  4. Vovida SIP Stack - The version is not supported on Win32 platforms, although some community members have shown interest in Windows port.

  5. reSIProcate - The reSIProcate project is part of the SIPfoundry community. The project aims at building a freely available, completely standards based and complete reference implementation of a SIP stack including an easy to use application layer API. The reSIProcate stack is currently used in several commercial products and is very stable.

  6. Twisted - Twisted Matrix Laboratories is a distributed group of open-source developers working on Twisted, an event-driven networking framework written in Python and licensed under the LGPL. Twisted supports TCP, UDP, SSL/TLS, multicast, Unix sockets, a large number of protocols (including HTTP, NNTP, IMAP, SSH, IRC, FTP, and others), and much more.

  7. PJSIP - The PJSIP.ORG website is the home of PJSIP and PJMEDIA, the Open Source, high performance, small footprint SIP and media stack written in C language for building embedded/non-embedded VoIP applications. PJSIP is built on top of PJLIB, and since PJLIB is a very very portable library, basically PJSIP can run on any platforms where PJLIB are ported (including platforms where normally it would be hard to port existing programs to, such as Symbian and some custom OSes).

SIP Test Tools

The following tools basically test SIP applications and devices, but each one is different in how it tests the protocols and in their focuses and additional applications:

  1. Callflow - Callflow is a collection of awk and shell scripts that will capture a file that can be read by ethereal and that will produce a callflow sequence diagram. The scripts have been primarily tested with SIP call flows, but should work for other network traffic as well. You can view callflow.svg with the Adobe SVG plugin, or you can view index.html with any web browser. The Callflow directive is a clean little script complete with a "to-do" list that you can play with.
  2. SipBomber 0.8 - SipBomber is an invaluable sip-protocol testing tool for Linux originally developed by Metalink in 2003 for internal use. It was later released as a GPL open source product.

  3. SIP Proxy - With SIP Proxy you will have the opportunity to eavesdrop and manipulate SIP traffic. Furthermore, predefined security test cases can be executed to find weak spots in VoIP devices.

  4. sipsak - sipsak is a small command line tool for developers and administrators of Session Initiation Protocol (SIP) applications. It can be used for some simple tests on SIP applications and devices.
  5. SIPp - SIPp is a test tool / traffic generator for the SIP protocol. It includes a few basic SipStone user agent scenarios (UAC and UAS) and establishes and releases multiple calls with the INVITE and BYE methods. It can also reads custom XML scenario files describing from very simple to complex call flows. It features the dynamic display of statistics about running tests (call rate, round trip delay, and message statistics), periodic CSV statistics dumps, TCP and UDP over multiple socket or multiplexed with retransmission management and dynamically adjustable call rates.

  6. PROTOS Test-Suite: c07-sip - The purpose of this test-suite is to evaluate implementation level security and robustness of SIP implementations. The focus was set on a specific protocol data unit (PDU), namely INVITE message (a subset of SIP).

  7. Vovida.org Load Balancer - The Load Balancer is a very simple proxy that is useful in SIP-based VoIP installations where there are multiple ingress proxy servers. The Load Balancer permits pooling these servers, thereby eliminating the need to balance user demands for connectivity through a complicated provisioning algorithm. The Load Balancer adds itself to the Via header of requests to enable responses to return before being sent to orginating endpoint. This only works with SIP messages sent over UDP (User Datagram Protocol).

IAX Clients (User Agents)

The open source project Asterisk (see below in PBX platforms) implements a software based PBX (Private Branch Exchange), or a private telephone switch that provides switching (including a full set of switching features) for an office or campus. As an internal protocol to trunk two or more PBX servers, the IAX (Inter Asterisk Exchange) protocol was created. IAX is a lightweight app based on UDP and bundles call signalling and voice into one data stream. This streaming makes it perfectly suited for connection-based simple firewalls.

  1. IAXComm - iaxComm is a cross-platform application for the Asterisk PBX. It was developed on aWindows XP system.

  2. Kiax - Kiax is an IAX client application which allows PC users to make ordinary VoIP calls to Asterisk servers. It aims to provide a simple and user-friendly graphical interface and desktop integration for calling, contact list, call register management and easy configuration.

  3. QtIAX - QtIAX is based on iaxclient (see below), but files were stripped for a bare bones environment.

  4. MozIAX - MozIAX is a Firefox VoIP extension, a cross platform software IAX2 phone (softphone) to be used with Asterisk.
  5. YakaSoftware - YakaSoftware is the open source code behind the YakaPhone, a simple, Skinnable IAX/IAX2 Softphone from YakaSoftware.

IAX/PBX Library

  1. IAXClient - IAXClient is an Open Source library to implement the IAX protocol used by The Asterisk Software PBX. Although asterisk supports other VOIP protocols (including SIP, and with patches, H.323), IAX's simple, lightweight nature gives it several advantages, particularly in that it can operate easily through NAT and packet firewalls, and it is easily extensible and simple to understand.

RTP Proxies

RTP, or Real-time transport protocol, is the Internet-standard protocol for the transport of real-time data, including audio and video. RTP is used in virtually all voice-over-IP architectures, for videoconferencing, media-on-demand, and other applications. A thin protocol, it supports content identification, timing reconstruction, and detection of lost packets.

  1. Maxim Sobolev's RTPproxy - RTPproxy is a proxy for RTP streams that can help SER (SIP Express Router) handle NAT (Network Address Translation, defined in RFC 1631) situations, as well as proxy IP telephony between IPv4 and IPv6 networks. The code has been extensively tested on FreeBSD, Linux, MacOS and Solaris. It should be relatively easy to port it to any system which has a POSIX layer.

RTP Protocol Stacks

  1. JRTPLIB - JRTPLIB is an object-oriented RTP library written in C++. The library offers support for the Real-time Transport Protocol (RTP), defined in RFC 3550. It makes it very easy to send and receive RTP packets and the RTCP (RTP Control Protocol) functions are handled entirely internally. The latest version of the library is 3.7.0 (January 2007).

  2. oRTP - oRTP is a Real-time Transport Protocol (RFC3550) stack under LGPL. Written in C, works under Linux (and probably any Unix) and Windows.

  3. GNU ccRTP - ccRTP is a C++ library based on GNU Common C++ which provides a high performance, flexible and extensible standards-compliant RTP stack with full RTCP support. The design and implementation of ccRTP make it suitable for high capacity servers and gateways as well as personal client applications.

  4. Vovida RTP Stack - Vovida RTP is augmented by a control protocal (RTCP) to monitor data delivery and network statistics. Together they resolve of many of the problems a UDP network enviroment may experience, such as lost packets, jitter, and out of sequence packets.

  5. RTPlib - This library, offered by Bell Labs, is based on the most recent version of the specification, incorporating some of the newest features, including RTCP scalability algorithms.

PBX Platforms

  1. Asterisk - Asterisk is a popular and extensible open source telephone that offers flexibility, functionality and features not available in advanced, high-end (high-cost) proprietary business systems. Asterisk is a complete IP PBX (private branch exchange) for businesses that runs on Linux, BSD, Windows and OS X and provides all of the features you would expect from a PBX and more. It has support for three-way calling, caller ID services, ADSI, IAX, SIP, H.323 (as both client and gateway), MGCP (call manager only) and SCCP/Skinny.

  2. OpenPBX.org 1.2 RC3 - This release includes the highly anticipated and robust new conference bridge application called NConference. OpenPBX.org RC2 is now generally available as a tarball that includes the ability to run on several BSDs as well as MacOS X. Both are forks of Asterisk with T.38 termination.

  3. Open Source Software PBX - Open Source PBX developed using Perl. OpenPBX.org will be stable, featureful, easy to use, and easy to deploy on a range of operating systems.

  4. PBX4Linux - PBX4Linux is an ISDN PBX which interconnects ISDN telephones, ISDN lines, and a H.323 gateway. This is a pure software solution except for the ISDN cards and telephones, as it connects to a Linux box. The great benefit is the NT-mode that allows to connect telephones to an ISDN card.

  5. SIPxchange - An enterprise-grade SIP PBX, SIP call manager and router, and SIP Softphone based on 100% SIP and 100% open source software. Produced by Pingtel, SIPxchange product suite runs on commodity server hardware using the Linux operating system, supports a large variety of IP phones and gateways, and seamlessly interoperates with legacy components.

  6. sipX - sipX is a modular server based solution that runs on standard Linux complete with voice mail and auto-attendant. Alternatively, sipX can be used as a high performance Enterprise toll-bypass SIP router. It combines all common calling features, XML-based SIP call routing, voice mail and auto-attendant, Web-based configuration, as well as integrated management and configuration of the PBX and attached phones and gateways. sipX does not require any additional hardware as it interoperates with any SIP compliant gateway, phone or application.

IVR Platforms

  1. GNU Bayonne - GNU Bayonne 2 was developed starting in 2005, with a special focus on SIP. GNU Bayonne is an integral part of GNU Telephony that offers free, scalable, media independent software environment for development and deployment of telephony solutions for use with current and next generation telephone networks.

  2. CT Server - A client/server library for rapid Computer Telephony (CT) application development in Perl. It uses Voicetronix hardware, and runs under Linux. Supports OpenSwitch cards for building PC PBXes.

Voicemail Apps

  1. lintad - Linux Telephone Answering Device (lintad) is a fax and voicemail application. Lintad uses a softmodem as a soundcard attached to the phoneline to play greetings and record messages. Messages and faxes are made available to browersers via Apache and PHP.
  2. Linux Voicemail/OpenUMS - The purpose of this project is to create an open source voicemail/unified messaging system that runs on Linux that has the ability to integrate with business telephone systems.
  3. VOCP System - VOCP is a complete messaging solution for voice modems, with voicemail, fax, email pager, DTMF command shell and Text-to-Speech support, 3 GUIs and a web interface. Send and receive faxes and voicemail, listen to emails and execute programs on the host.
  4. OpenVXI - The Open VXI VoiceXML interpreter is a portable open source library that interprets the VoiceXML dialog markup language. It is designed to serve as a reference for parties interested in understanding how VoiceXML markup might be executed.

Speech Software


  1. The Festival Speech Synthesis System - Festival offers a general framework for building speech synthesis systems as well as including examples of various modules. As a whole it offers full text to speech through a number APIs: from shell level, though a Scheme command interpreter, as a C++ library, from Java, and an Emacs interface. Festival is multi-lingual (currently English (British and American), and Spanish) though English is the most advanced. The system is written in C++ and uses the Edinburgh Speech Tools Library for low level architecture and has a Scheme (SIOD) based command interpreter for control. Documentation is given in the FSF texinfo format which can generate, a printed manual, info files and HTML.

  2. OpenSALT - SALT (Speech Application Language Tags) is a lighweight markup language that integrates speech services into standard markup languages such as HTML. SALT supports the authoring of multi-modal dialogs as well as voice-only dialogs and is suitable for the development of applications across desktop and telephony platforms. SALT is defined through the efforts of the SALT Forum, of which Carnegie Mellon is a contributor. The OpenSALT project makes available a SALT 1.0 compliant open-source browser based on the open source Mozilla web browser and make use of open source Sphinx recognition and Festival synthesis software. Their first Windows release is available for download. A Linux version will follow when a fully featured Windows version is complete. They will subsequently focus on developing a version suitable for mobile devices and a version for telephony-based systems.

  3. CMU Sphinx Projects - The packages that the CMU Sphinx Group is releasing are a set of reasonably mature, world-class speech components that provide a basic level of technology to anyone interested in creating speech-using applications without the once-prohibitive initial investment cost in research and development; the same components are open to peer review by all researchers in the field, and are used for linguistic research as well.

Fax Servers

  1. HylaFAX - HylaFAX is an enterprise-class system for sending and receiving facsimiles as well as for sending alpha-numeric pages. The software is designed around a client-server architecture. Fax modems may reside on a single machine on a network and clients can submit an outbound job from any other machine on the network. Client software is designed to be lightweight and easy to port.

  2. AstFax - AstFax provides an outgoing email to fax gateway for the Asterisk PBX package. Incoming fax to email can also be configured so your Asterisk server can act as both an outgoing and incoming fax server.

Development Stacks

  1. OpenSS7 - OpenSS7 provides a robust and GPL'ed SS7, SIGTRAN, ISDN and VoIP stack for Linux and other UN*X operating systems.

  2. ooh323c - Objective Systems Open H.323 for C (ooh323c) is a simple H.323 protocol stack developed in C. The ASN.1 PER messaging code was developed using the ASN1C compiler using a modified version of our core run-time libraries. Additional open source components as well as code developed in-house were added to produce a functioning stack. The goal is to produce a reusable framework that contains the signaling logic to allow channels to be created and terminated for different H.323 applications. ooH323c is now included as an add-on to the Asterisk open source PBX.

  3. ++Skype Library - ++Skype library is a new, modern way to develop platform independent Skype add-on software. The ++Skype is a C++ library of thoroughly designed classes that can help you to build platform-independent add-on software. Be sure to read the documentation, as this software requires several tools and libraries not included in this article.

  4. OpenBloX™ - The OpenBloX™ framework is an Open Source set of directories and files, implementing in a whole or part of the 3GPP and 3GPP2 Diameter specifications. The package contain at minimum the Diameter base protocol as described by IETF RFC 3588 and any extensions provided to support upper layers as specified by the 3GPP specifications, such as Rx, Gx, Ro, Cx, Sh and other 3GPP defined interfaces.

Middleware

  1. MobiCent - Mobicents is the first and only open source VoIP Platform certified for JSLEE 1.0 compliance. Mobicents brings to telecom applications a robust component model and execution environment. It compliments J2EE to enable convergence of voice, video and data in next generation intelligent applications.

  2. Ernie - Software application that integrates Web 2.0 design principals with next generation communications technologies, including VoIP, presence and web languages such as Python. LAMP developers are Ernie's primary users.

  3. SIP Thor - SIP Thor is based on P2PSIP technology that enables scalability with no single point of failure. SIP Thor is based on P2PSIP, a set of technologies that combines exiting IETF standards like SIP, DNS and ENUM with Peer-To-Peer techniques like distributed hash tables (DHT).

Tuesday, April 3, 2007

Asterisk callme back

Fro CallMe Back Just call your asterisk box with your mobile, let it ring and hang up. Asterisk will call you back at once and provide you with a normal dial tone. after that can Make call.......


simply copy and past on extensions.conf file.......

[capiin]
exten => 1234/016066666,1,Wait,1
exten => 1234/016066666,2,AGI,callback.agi
exten => 1234/016066666,3,Hangup
[capidialtone]
exten => s,1,Dial,CAPI/@1234:b
exten => s,2,Hangup


And make agi file...

< ? p h p
ob_implicit_flush(true);
set_time_limit(0);
$err=fopen("php://stderr","w");
$in = fopen("php://stdin","r");
while (!feof($in)) { $temp = str_replace("\n","",fgets($in,4096));
$s = split(":",$temp);
$agi[str_replace("agi_","",$s[0])] = trim($s[1]);
if (($temp == "") || ($temp == "\n")) { break;
}
}
$cf = fopen("/home/var/spool/asterisk/outgoing/cb".$agi["callerid"],"w+"); fputs($cf,"Channel: CAPI/".$agi["extension"].":".$agi["callerid"]."\n");
fputs($cf,"Context: capidialtone\n");
fputs($cf,"Extension: s\n");
fputs($cf,"SetVar: CALLERIDNUM=".$agi["extension"]."\n");
fputs($cf,"MaxRetries: 2\n");
fputs($cf,"RetryTime: 10\n");
fclose($cf); fclose($in);
fclose($err);
? >

In this example 1234 is the incoming/outgoing MSN and 016066666 the callerid of a mobile phone.

Thursday, March 29, 2007

Soekris Net4801 how-to install or configure

Soekris Net4801 how-to install

* 1 Soekris Net4801 lspci data
* 2 Bootstrapping Gentoo on a Soekris Net4801 system
o 2.1 Introduction
o 2.2 General information
o 2.3 The first step
o 2.4 Readying the flash card
o 2.5 Building the system
o 2.6 Entering the flash environment
o 2.7 Building and optimizing your kernel
o 2.8 Mounting partitions during boot time
o 2.9 Serial console setup
o 2.10 Networking setup
o 2.11 Misc settings and has-to-do's
o 2.12 Installing and optimizing Grub
o 2.13 Exiting the environment
o 2.14 Troubleshooting
+ 2.14.1 Serial console software
+ 2.14.2 Grub issues
o 2.15 Optimizations
+ 2.15.1 Optimizing the portage tree


Soekris Net4801 lspci data

00:00.0 Host bridge: Cyrix Corporation PCI Master
00:06.0 Ethernet controller: National Semiconductor Corporation DP83815 (MacPhyter) Ethernet Controller
00:07.0 Ethernet controller: National Semiconductor Corporation DP83815 (MacPhyter) Ethernet Controller
00:08.0 Ethernet controller: National Semiconductor Corporation DP83815 (MacPhyter) Ethernet Controller
00:12.0 ISA bridge: National Semiconductor Corporation SC1100 Bridge
00:12.1 Bridge: National Semiconductor Corporation SC1100 SMI
00:12.2 IDE interface: National Semiconductor Corporation SCx200 IDE (rev 01)
00:12.5 Bridge: National Semiconductor Corporation SC1100 XBus
00:13.0 USB Controller: Compaq Computer Corporation ZFMicro Chipset USB (rev 08)


Bootstrapping Gentoo on a Soekris Net4801 system

Introduction

This how-to describes how to install a complete Gentoo Linux system on a Soekris Net4801 system. Let's begin by looking at the capabilities of the system itself, and after that, i will explain how to make Gentoo run on it using a 2 GB flash card and the on-board IDE flash card interface.

General information

The machine consists of an AMD Geode 266 mhz cpu, 2 serial ports(one of them is pin-based) and a compatch flash based IDE interface. There are several features on the board besides these, but at this point we'll stick with what we need to get the system up and running with Gentoo. The general idea here, is to built a system on a flash card using an ordinary PC, which can then be booted on the Soekris box upon built completion. This procedure pretty much follows a standard Gentoo installation as described in the handbook, but I'll take you through the steps to avoid confusion.

The first step

Start out by booting a system with a functioning network connection using either the Gentoo Minimal CD, or any other system you have at your disposal. It's totally up to you what system you want to use, as long as it is Linux and has a working internet network connection. Fire up your favorite console and grant yourself root priviledges. Insert the Compact Flash card into a flash reader and connect it to your machine. When you're ready, follow the instructions below.

Readying the flash card

First we need to identify the flash card. Type:

# fdisk -l

When you've figured out what device node your flash card uses, we need to create the necessary partitions on the card. In this how-to we assume that the card is located at sdb. Type:

# fdisk /dev/sdb

Start out by erasing all current partitions on the card. "p" lists all partitions, and "d" allows you to delete them one by one.
Warning: You have to assure yourself that you are in fact working on the flash card. If you are not, you might end up deleting your entire system! If you accidentally erase something you shouldn't have, quit the program using ctrl+c. Then the changes will be undone.

Create a swap partition(sdb1), and a root partition(sdb2). "n" creates a new partition, and "t" changes its type. 82 is swap. 128 MB should be fine for the swap partition, and the root partition should have at least 1.5 MB. Remember to set the bootflag on your root partition using "a". Finally, save your spanking new partition layout using "w".

Now we need to create the filesystems in the new partitions. Remember to use your own device node. This example again uses sdb. Type:

# mkswap /dev/sdb1
# mke2fs -j /dev/sdb2

The flash card is now ready for the installation.

Building the system

First, activate the swap partition:

# swapon /dev/sdb1

Now the root partition needs to be mounted. Type:

# mkdir /mnt/gentoo
# mount /dev/sdb2 /mnt/gentoo

Verify the date:

# date

If it's incorrectly set, change it using(this example is february 5th 2007 15:39):

# date 020515392007

Now we need to download and apply the basic system onto the flash card. It is very important that you use a stage3 i586 release, since the Soekris cpu doesn't support i686. Type:

# cd /mnt/gentoo
# wget http://mirror.uni-c.dk/pub/gentoo/releases/x86/2006.1/stages/stage3-i586-2006.1.tar.bz2
# tar xvjpf stage3-*.tar.bz2

You may, of course, use some other mirror for this file if you like, and also keep in mind top grab the latest release. At the time of this writing 2006.1 is the newest release. More mirrors can be found at http://www.gentoo.org/main/en/mirrors.xml

Now grab a portage snapshot, and unpack it on the flash card. Again, you may use any mirror you like. Type:

# wget http://mirror.uni-c.dk/pub/gentoo/snapshots/portage-latest.tar.bz2
# tar xvjf /mnt/gentoo/portage-latest.tar.bz2 -C /mnt/gentoo/usr

Add the following lines to the file /mnt/gentoo/etc/make.conf:
File: /etc/make.conf

MAKEOPTS="-j2"

Also, add the needed mirrors and rsync servers using(this only works if you use a Gentoo system, or the Gentoo minimal CD. Otherwise you'll have to find the mirrors manually at http://www.gentoo.org/main/en/mirrors.xml):

# mirrorselect -i -o >> /mnt/gentoo/etc/make.conf
# mirrorselect -i -r -o >> /mnt/gentoo/etc/make.conf

Choose the ones appropriate for your location.

Copy your network information into the new system. Type:

# cp -L /etc/resolv.conf /mnt/gentoo/etc/resolv.conf

Now it's time to chroot into the new environment. Type:

# mount -t proc none /mnt/gentoo/proc
# mount -o bind /dev /mnt/gentoo/dev
# chroot /mnt/gentoo /bin/bash
# env-update
# source /etc/profile
# export PS1="(chroot) $PS1"


Entering the flash environment

You are now working inside your flash card. Cool, eh? I think it is. Anyways, start out by syncing your Portage tree:

# emerge --sync

If it tells you that a newer version of Portage is available, emerge it using:

# emerge portage

You have to define your timezone. In this example I'm using Europe, Copenhagen. You, of course, have to adjust this to fit with your own timezone. Tab completion is your friend:

# cp /usr/share/zoneinfo/Europe/Copenhagen /etc/localtime


Building and optimizing your kernel

Now we need to install the kernel source. Type:

# USE="-doc symlink" emerge -va gentoo-sources

At this point we need to built a kernel for the Soekris system. Start the configuration like this:

# cd /usr/src/linux
# make menuconfig

The following settings are essential:

--> Processor type and features
--> [ ]Symmetric multi-processing support
--> Processor family
-->(x) GeodeGX1

--> Device Drivers
--> ATA/ATAPI/MFM/RLL support
--> National SCx200 chipset support

--> Serial ATA (prod) and Parallel ATA (experimental) drivers
--> SC1200 PATA support
--> Network device support
--> [x] Network device support
--> Ethernet (10 or 100Mbit)
--> [x] National Semiconductor DP8381x series PCI Ethernet support
--> Sound
--> < > Sound card support

You may turn off lots of other unneeded stuff, but the above should give you a bootable kernel, so we'll leave it at that for the time being. After all, this is a guide to get Gentoo running on the Soekris, not a kernel setup guide.

End the configuration and answer YES when asked if you want to save the configuration. Now, compile the kernel:

# make && make modules_install

Copy your brand new kernel into /boot:

# cp arch/i386/boot/bzImage /boot/kernel-2.6.19-gentoo-r5


Mounting partitions during boot time

Now we have to make sure that our partitions gets mounted correctly during boot time. This is done through /etc/fstab. We have a swap partition and a root partition and our fstab therefore have to look like this:
File: /etc/fstab

/dev/hda2 / ext3 noatime 0 1
/dev/hda1 none swap sw 0 0
proc /proc proc defaults 0 0
shm /dev/shm tmpfs nodev,nosuid,noexec 0 0

Be aware that you might need to change hda to hdb in the first two lines. It depends on how your Soekris box detects your flash card. Normally though, the card will be named hda, so the above settings should be ok. If you experience boot problems when you're ready to boot the machine after following this how-to, you should try changing hda to hdb to see if it solves the problem.

Serial console setup

We now have to make sure that we can log onto the Soekris system using a null modem serial cable. To do this we need to make a change in our /etc/inittab file. Type:

# nano -w /etc/inittab

Find the section ressembling the following lines, and change it to look like this:
File: /etc/inittab

...
# SERIAL CONSOLES
s1:12345:respawn:/sbin/agetty 19200 ttyS0 vt100
...


Networking setup

Now set your desired hostname:

# nano -w /etc/conf.d/hostname

Set up the machine's IP address in /etc/conf.d/net. Below is an example of a class C network using static IP and a gateway. If you want to use dhcp the file must be blank:
File: /etc/conf.d/net

config_eth0=( "x.x.x.x netmask 255.255.255.0" )
routes_eth0=( "default gw x.x.x.x" )

Add your NIC(network card) to the default runlevel:

# rc-update add net.eth0 default


Misc settings and has-to-do's

Change the root password to whatever you want to use:

# passwd

If you want to localize your keyboard settings, change "us" to your desired language in /etc/conf.d/keymaps:

# nano -w /etc/conf.d/keymaps

Remember to change the machine's time to local time if you're hardware clock isn't running UTC(change "UTC" to "local"):

# nano -w /etc/conf.d/clock

Now, install syslog-ng and vixie-cron and add them to the default runlevel:

# emerge syslog-ng
# emerge vixie-cron
# rc-update add syslog-ng default
# rc-update add vixie-cron default

If you are using dhcp, remember to install a dhcp client:e dhcp(jeg mener servicen hedder dhcpcd, men jeg er ikke helt sikker.):

# emerge -va dhcpcd

Also, install openssh, which enables you to connect to the Soekris box using the network interface:

# emerge -va openssh
# rc-update add sshd default


Installing and optimizing Grub

Now, install the Grub boot loader:

# emerge -va grub

What we need to do now, is to optimize the /boot/grub/grub.conf file to work with our Soekris box. This enables us to get a read-out from the null model cable. First, remove any existing grub.conf file(there probably aren't any, but just to make sure):

# rm /boot/grub/grub.conf

Now, create a new one:

# nano -w /boot/grub/grub.conf

The first section has to look like this. You can change the timeout valure if you like, but 5 seconds seems quite suitable:
File: /boot/grub/grub.conf

default 0
timeout 5
serial --unit=0 --speed=19200 --word=8 --parity=no --stop=1
terminal serial

Now, add your Gentoo kernel and the belonging options. Insert the following section and adjust it to fit your own kernel image:
File: /boot/grub/grub.conf

title=Gentoo Linux 2.6.19-r5
root (hd0,1)
kernel /boot/kernel-2.6.19-gentoo-r5 root=/dev/hda2 console=ttyS0,19200n8

We now need to install Grub onto the master boot record of the flash card. It is very important that you use the correct hd values here. Grub counts from 0 instead of 1 as Linux device nodes do. If sda is your primary harddrive it would be called hd0 using Grub terms and the first partition on that drive would be named hd0,0 and so on and so forth. If you only have one harddrive connected, your flash card is probably called hd1 and your root partition on the flash card is probably called hd1,1, but remember to use tab completion just to make sure. First start the Grub command line tool:

# grub

Now, define the root partition on the flash card. If your flash card is called sdb, is disc number 2 in your system, and has its root partition as partition 2, type as follows:

> root (hd1,1)

Now install Grub into the flash MBR using the command. Again, we assume that the flash card is drive number 2 in your system:

> setup (hd1)
> quit


Exiting the environment

The system is now finished, and you may exit the chroot and unmount all flash partitions:

# exit
# umount /mnt/gentoo/proc
# umount /mnt/gentoo/dev
# umount /mnt/gentoo

If everything went well, the system is now ready for booting in the Soekris Net4801 box itself. If you encounter any problems, I've added a few notes below, which might help you solve some of those issues.

Troubleshooting

Serial console software

You can use the software gtkterm(emerge gtkterm) through X to communicate with the Soekris Net4801 using a null model cable. Connect the cable to your PC and set the speed to the Soekris default value of 19200, bits=8, stopbits=1, flowcontrol=none, parity=none. This will allow you to follow the boot process and maintain the system. An SSH connection is recommended for this during normal circumstances, but since it doesn't allow you to get read-out during boot, the null model connections is very handy if you have to diagnose boot problems.

Grub issues

If Grub doesn't load as expected, you might have installed Grub on a wrong disc. Re-insert the flash card into a card reader, and reenter the environment to check if you made any errors.

If Grub can't find your root directory during boot time, you might need to change hda to hdb in /etc/fstab.

Optimizations

Optimizing the portage tree

To optimize the portage tree for an embedded system, we can exclude a lot of the software we won't ever need. It is safe to assume that the system won't ever run X or KDE/Gnome, so there is no reason for those components to take up space in the tree. This optimization is also conveyed with syncronization in mind. Syncronizing the full portage tree on a slow system like the Soekris Net4801 is a major annoyance, since it takes forever to complete. The exclusion of packages can be done using the following method:

Create a file that contains your excludes(/etc/portage/rsync_excludes). The following excludes are appropriate. A lot more can be excluded if you want to, but these are deemed suitable for exclusion. Insert the following into /etc/portage/rsync_excludes:
File: /etc/portage/rsync_excludes

- app-accessibility/
- metadata/cache/app-accessibility/
- app-emulation/
- metadata/cache/app-emulation/
- app-laptop/
- metadata/cache/app-laptop/
- app-office/
- metadata/cache/app-office/
- app-pda/
- metadata/cache/app-pda/
- dev-games/
- metadata/cache/dev-games/
- games-*/
- metadata/cache/games-*/
- gnome-*/
- metadata/cache/gnome-*/
- kde-*/
- metadata/cache/kde-*/
- rox-*/
- metadata/cache/rox-*/
- media-gfx/
- metadata/cache/media-gfx/
- media-tv/
- metadata/cache/media-tv/
- media-video/
- metadata/cache/media-video/
- sci-*/
- metadata/cache/sci-*/
- x11-*/
- metadata/cache/x11-*/
- xfce-*/
- metadata/cache/xfce-*/

Add these excludes permantently to your /etc/make.conf file:
File: /etc/make.conf

...
PORTAGE_RSYNC_EXTRA_OPTS="--exclude-from=/etc/portage/rsync_excludes"
...

Finally, remove the excludes from the portage tree::

# cd /usr/portage
# rm -r `gawk '{ printf("%s ",$2) }' /etc/portage/rsync_excludes`

The optimization is now complete, and you can run a "emerge --sync" to test it.

Monday, March 26, 2007

Voice Mail configuration AGI

here voice mail agi in perl & configuration infornation..
http://www.voip-info.org/wiki/view/Magic+VoiceMail
and TDM400P configuration
http://www.voip-info.org/wiki/view/TDM400P

Friday, February 23, 2007

These are step by step very simple instructions to help most people to build

Tutorial on SIP and SIP SER server Installation of SER.

I would like to present three installation options:

· Follow the steps below using Red Hat Fedora Core 1 and rpm files. This is possibly getting a little old now (2006)

· FreeBSD comes with SER as an installable option, just tick the box.

· I have done some testing using Fedora Core 4 using the same steps as below
and compiled SER from www.openser.org and it seams to be the same as the
following.

These are step by step very simple instructions to help most people to build
a SER Server starting with a PC.

Linux Kernal using RedHat's Open Source "Fedora

Core 1"

The following instructions are based on building a Unix server using typical PC Intel i386 based hardware.

IMPORTANT NOTE: Fedora Core 1 does not support Intel PCs using Serial ATA Harddisks, and recent testing shows that SER does not run on Fedora Core 2.

  1. Follow the instructions at http://fedora.redhat.com/download/
    1. Download the three ISO images for Fedora Core 1 (not 2), also called
      "yarrow" from Red Hat.
      http://download.fedora.redhat.com/pub/fedora/linux/core/1/
    2. Create three CDROMs using the three ISO images.
  2. Insert Fedora Core 1 CD 1 and boot from CD
    1. Press Enter at boot screen to start setup
    2. When prompted to do the Media Test you can select Skip if you are confident your media is OK
    3. On the Fedora Core welcome screen, select Next
    4. Select English (English) as your language and press Next
    5. Select your keyboard type and press Next
    6. Select your mouse type and press Next
    7. If you have an existing Redhat Linux installation on the computer
      the Fedora installer will find this installation and ask you if you
      want to Upgrade the existing installation or perform a refresh installation
      of Fedora Core. If this is the case, select Install Fedora Core and
      press Next
  3. We want to do a Server installation of Fedora Core so select Server and press Next

  1. Select Manual partition with Disk Druid and press Next
    1. Delete any existing disk partitions so that your whole disk is Free Space
    2. For simplicity we will make 3 partitions, one swap partition, one boot partition and one big root partition
    3. Create a "Swap" partition to be 2 or 4 times the size of your available memory
      1. Click New
      2. Select swap as the File System Type
      3. In the Size (MB) text field enter the size of your swap partition.
        As a guide, set the swap partition to be 2 or 4 times the size of your available memory. As an example, my computer has 256Meg RAM, so I will set a 1Gig swap
      4. Under Additional Size Options select Fixed Size
      5. Ensure Force to be a primary partition is not selected and press OK
    4. Create a "/boot" partition with a File System Type of etx3 with 100Mbytes
      1. Click New
      2. In the Mount Point text field enter /boot to be an ext3 type of 100Meg.
      3. Select ext3 as the File System Type
      4. In the Size (MB) text field enter the size of your boot partition. This partition should be at least 50Meg, and a good size is usually
        100Meg.
      5. Under Additional Size Options select Fixed Size
      6. Select Force to be a primary partition and press OK
    5. Create a "/"
      root partition with the remained of the disk
      (Optional would be to create a /var partition for the logs to go)
      1. Click New
      2. In the Mount Point text field enter /
      3. Select ext3 as the File System Type
      4. Under Additional Size Options select Fill to maximum allowable size to use the remainder of the disk
      5. Ensure Force to be a primary partition is not selected and press OK
      6. Now you have setup your partitions press Next
  2. Select GRUB as the boot loader and press Next
  3. Next you will need to setup the network device of your machine. The information you enter here will vary depending on your environment
    1. Select eth0 device and press Edit
    2. Deselect Configure using DHCP - you want to configure a fixed ip address
    3. Select Activate on boot
    4. Enter your IP address
    5. Enter your Netmask
    6. Press OK
    7. Set the hostname manually
    8. Enter Gateway IP address.
    9. Enter Primary DNS IP address - For the Tutorial you may need to change it to simulate the SRV records, probably 169.222.239.2
    10. Enter Secondary DNS IP address if available
    11. Enter Tertiary DNS IP address if available
    12. Press Next
  4. Select No firewall and press Next
    1. Press Proceed on the warning screen about a firewall, you can setup a proper firewall later on
  5. Select the Default language for your system and press Next
  6. Select your timezone and press Next
  7. Enter your desired Root Password and Confirm and press Next
  8. This document assumes that this server will be dedicated to be your SIP server and so we will remove some packages that are not needed by your SIP server. For the purposes of the APAN SIP tutorial we will install Xwindows so you have a graphical interface with a browser to access the serweb tool. (Xwindows is not normally needed for your SIP server)
  9. Under Desktops select X Window System and click Details (far right side of X Window System
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      XFree86-twm
      Xfree86-xdm
      firstboot
      gdm
      rhgb
      xterm
  1. To install a decent Window Manager, under Desktops select your Window Manager of choise, either GNOME or KDE and install your required packages.
    For the purposes of the APAN SIP tutorial we will install we will install KDE.
  2. Under Desktops select KDE Desktop Environment and click Details
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      kdeadmin
      kdenetwork
      kdeutils
  3. Under Applications select Editors and click Details
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      vim-enhanced

      emacs
  4. Under Applications select Graphic Internet and click Details
    • Ensure only the following packages are installled (for a small installation although you can add more):
      mozilla

  1. Under Applications deselect Text-based Internet
  2. Under Servers click Details for Server Configuration Tools
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      Xfree86-xauth

  3. Under Web Servers click Details for
    Web S
    • Ensure only the following packages are installed (for a small installation although you can add more):
      httpd-manual
      php
      php-mysql
  4. Under Servers deselect Windows File Server
  5. Under Servers select SQL Database Server and click Details
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      mysql-server

  1. Under Development select Development Tools and click Details
    • Ensure only the following packages are installed (for a small installation although you can add more):
      rpm-build
  2. Under Development select Network Servers and click Details
    • Ensure only the following packages are installed (for a small installation
      although you can add more):
      freeradius
      tftp-server
  3. Under System deselect Administration Tools
  4. Under System select System Tools and click Details
    • Ensure only the following packages are installed (for a small installation although you can add more):
      ethereal
      nmap
      screen
  5. Under System deselect Printing Support
  6. We are now ready to continue with the installation.
    • Click Next
  7. The installation will now check for dependencies, but there shouldn't be any for our installation as above
    • Click Next again
  8. The required media will be listed in a popup dialog box, click Continue
  9. The installation will now proceed by formatting the required filesystems, transferring the install image to the hard drive, and then installing the
    selected packages.
  10. You will be prompted to insert the required media at different times throughout
    the installation. When prompted, insert the required media and press OK.
  11. When prompted to reboot, remove the installation media and press Reboot
  12. You have now installed a system that will run as a SER SIP Server for the APAN SIP Tutorial

#
# ENABLE ALL THE FOLLOWING SERVICES TO START AUTOMATICLY ON RELOAD OF SERVER
Use the programme "ntsysv"
# enable these:
# http
# mysql
# radiusd
# tftp

Step by Step Installation of SER on RedHad Fedora Core 1

The following has been tested on a RedHat Fedora Core 1 install as per instructions and is based on SER version 0.8.12

Key to the colours used:

Pink indicates a command or a filename
Red indicates something specific to your SER Server,
do not just put in the example

rpm -i ser-0.8.12-0.i386.rpm 

Start

/etc/rc.d/init.d/ser start

Do some environmental changes

export SIP_DOMAIN="aarnet.edu.au"

    • Edit /etc/profile and add the following line (same as above) onto the end of the file:

export SIP_DOMAIN="aarnet.edu.au"
Note I used an IP address here to make it work.

  • Get the SER mysql module, configure the install, install it, configure
    SER and restart SER

from SER or from this server

    • Install mysql
      rpm -i ser-mysql-0.8.12-0.i386.rpm
    • Start
      /etc/rc.d/init.d/mysqld start
    • If you wanted to you could make some changes, not recommended for first build, in the following file:
      • Edit /usr/sbin/ser_mysql.sh, the following may want to be changed, I just leave them as is
        DBHOST=localhost
        USERNAME=ser
        DEFAULT_PW=heslo
        ROUSER=serro
        RO_PW=47serro11
        SQL_USER="root"
        DEFAULT_PW=heslo
        # DEFAULT_PW is the password used by serctl to add users etc!
        Note:
        If you change the DEFAULT_PW password then you must add something to ser.cfg add

modparam("usrloc|other_modules_using_db", "db_url",
"mysql://ser:otherpassword@localhost/ser")


    • Run the SER script to create the SER database in mysql. IMPORTANT NOTE : You need to change your path statement in your shell environmentals to find the HA1 programme, personally I just ssh on as root and it runs fine.
      /usr/sbin/ser_mysql.sh create
      Notes: MySql password for root: BLANK
      Domain (Realm) is the domain name for the SIP users: aarnet.edu.au Second password: BLANK
    • Enter mysql and check that the tables have been created, follow these commands:
      mysql
      connect ser;
      show tables;
      select * from subscriber;
      exit
    • Copy the original SER configuration file and edit ser.cfg.
      • cp /etc/ser/ser.cfg /etc/ser/ser.cfg-original
      • Now edit the /etc/ser/ser.cfg /etc/ser/ser.cfg to make it do what you want it to. Here is a good guide: http://mit.edu/sip/sip.edu/ser.shtml.
    • Restart SER so that the changes take effect
      /etc/rc.d/init.d/ser restart
  • Create two users using serctl. IMPORTANT NOTE : YOU MUST BE LOGGED IN AS ROOT (NOT JUST sudo) or fix up the path statement in your shell. Format of the command is: serctl add <username> <password> <email> and you need to use the DEFAULT_PW=heslo as the password set in /usr/sbin/ser_mysql.sh
    /usr/sbin/serctl add srk password Stephen.Kingham@aarnet.edu.au
    /usr/sbin/serctl add
    caf password Clayton.Forbes@aarnet.edu.au

change password to the password that will be used by the user, do not use password as a password!

  • Test the connection using two SIP UAs.
    • See the examples of how to configure UAs.
    • Use the this command to see who is logged in:
      serctl ul show
  • Configure mysql so that it only listens to local requests and increase the maximum number of sessions:
    • edit /etc/my.cnf
      Change the start of the file so that it has these extra lines:
      [mysqld]
      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      set-variable = max_connections=500
      bind-address=127.0.0.1
    • Restart mysql
      /etc/rc.d/init.d/mysql restart
  • Create a special user for SER to run as, rather than running it as root.
    • Use the following command to create the new user in Unix:
      /usr/sbin/useradd -c "SER" -u 494 -s
      /sbin/nologin -r -d /usr/lib/ser ser
    • Now tell SER to run as the user ser by adding these lines into /etc/ser/ser.cfg:
      uid=ser
      gid=ser
    • Delete the old fifo file otherwise the new user "ser" may not be able to use it
      rm /tmp/ser_fifo

Step by Step Installation of WEBSER on RedHad Fedora Core 1

When the original distribution is installed the directory locations do not match up with the typical apache file locations. These notes document all the changes needed. SUGGESTION: For the Tutorial we should develop a new install TAR so delegates do not spend 30 minutes editing all the files.

  • Get the TAR, un-tar and place the files in the right directory
    • from SER or from this server
    • untar
      tar -xvzf serweb_2004-01-04.tar.gz
    • change directory to where the files were untared to
      cd serweb_2004-01-04
    • Create the directory and move the files there
      mkdir /var/www/phplib
      chown root /var/www/phplib
      chgrp root /var/www/phplib
      chmod 755 /var/www/phplib
      cp ./phplib/* /var/www/phplib/ -R

# Move the html files to the DocumentRoot directory
mkdir /var/www/html/serweb/
chown apache /var/www/html/serweb/
chgrp root /var/www/html/serweb/
chmod 755 /var/www/html/serweb/
cp ./html/* /var/www/html/serweb -R

  • edit these lines in /etc/php.ini
    and change short_open_tag = Off" to "On"

and change register_globals = Off" to "On"

· Create these files as part of the serweb module.

vi /var/www/html/serweb/prolog.html
AARNet SER SIP Server
vi /var/www/html/serweb/separator.html
vi /var/www/html/serweb/epilog.html
End of page text

  • ALL the files that follw are with respect to /var/www/html/serweb
    cd /var/www/html/serweb
  • edit these lines in ./config.php
    In general replace all 192.168.2.16 with 192.94.63.28
    #If you have changed the mysql username, dbname or password you need to change these:
    db_name="ser"; //database name
    db_user="ser"; //database conection user
    db_pass="heslo"; //database conection password
    # NOTE: The above relate to the mysql database, see /usr/sbin/ser_mysql.sh.
    $this->root_path="/serweb/";
    #The above must match where the serweb html directory is put
    this user_pages_path = this root_path."user_interface/";


    $this->prolog="/serweb/prolog.html";
    $this->separator="/serweb/separator.html";
    $this->epilog="/serweb/epilog.html";
    # /* email address for questions concerning registration */
    $this->root_uri.

$this->root_path."user_interface/my_account.php?#session#\n\n".
$this->root_uri.

$this->root_path."user_interface/reg/confirmation.php?nr=#confirm#\n\n".
# There is heaps more to customise in this file which we will leave to the user. These include more email references
# and text used in emails to users who register. Here are some example which do not need to be changed for this tutorial:
$this->mail_header_from="Stephen.Kingham@aarnet.edu.au";
$this->web_contact="sip:Stephen.Kingham@aarnet.edu.au";
$this->title="The AARNet IP Telephony Site";
$this->infomail = "Stephen.Kingham@aarnet.edu.au";
$this->regmail = "Stephen.Kingham@aarnet.edu.au";

  • Edit /var/www/phplib/db_mysql.inc
    # You need to add the following to /* public: connection parameters */
    var $Host = "192.94.63.28";
    var $Database = "ser";
    var $User = "ser";
    var $Password = "heslo";

The follwoing changes can be applied two ways:

1. Get and extract this tar file

get the file fixupserwebphp.tar from this server put it in the directory /usr/local/src/ser issue these commands

cd /var/www/html/serweb

tar -xvf fixupserwebphp.tar

OR


2. Manualy make all these changes

Generaly you have to add "../" to all these files so that they point correctly to where the phplib is.


Edit ./admin/prepend.php
# change $_PHPLIB["libdir"] = "../../../phplib/";

  • Edit ./user_interface/prepend.php
    # change $_PHPLIB["libdir"] = "../../../phplib/";
  • Edit ./user_interface/reg/prepend.php
    # change $_PHPLIB["libdir"] = "../../../../phplib/";
  • Edit ./admin/acl.php
    # change require "../../../phplib/oohforms.inc";
  • Edit ./admin/index.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./admin/users.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./user_interface/accounting.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./user_interface/find_user.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./user_interface/index.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/missed_calls.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/my_account.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/phonebook.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/send_im.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/notification_subscription.php
    # require "../../../phplib/oohforms.inc";
  • Edit ./user_interface/reg/finish.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./user_interface/reg/get_pass.php
    # require "../../../../phplib/oohforms.inc";
  • Edit ./user_interface/reg/index.php
    # require "../../../../phplib/oohforms.inc";

LAST STEP: START APACHE
/etc/rc.d/init.d/httpd start

Initial configuration of SER

Some key SER configuration commands

  • Create users:You must be root.
    sudo su -
    Format of the command is: serctl add username password email
    and you need to use the DEFAULT_PW=heslo as the password set in /usr/sbin/ser_mysql.sh
    /usr/sbin/serctl add srk password Stephen.Kingham@aarnet.edu.au
    /usr/sbin/serctl add
    caf password Clayton.Forbes@aarnet.edu.au
    change password to the password that will be used by the user, do not use password as a password!
  • Give the User several aliases, such as a telephone number:
    /usr/sbin/serctl
  • Set up some permanent forking, so calls to the user also go through to telephone numbers:
    /usr/sbin/serctl
  • Test the connection using two SIP UAs.
    • See the examples of how to configure UAs.
    • Use the this command to see who is logged in:
      serctl ul show
  • What places users can call are set by a flag for the user in mysql, the "acl" table. Use the serctl command to enable various flags for each user. You can create the flags by configuring the serctl programme it's self, ie vi /usr/sbin/serctl.
    /usr/sbin/serctl acl grant srk
    free-pstn

    /usr/sbin/serctl acl grant srk
    ld
    /usr/sbin/serctl acl grant srk
    ld

    What the various flags do is done within the routing in ser.cfg.

Thursday, February 22, 2007

CDRTool installation (MYSQL)

Obtain the software from http://cdrtool.ag-projects.com. The software is
available as a tar archive or as a debian package.

Installing from the tar archive:

Change directory to your web root (/var/www on Debian systems)

tar zxvf cdrtool*.tgz

Installing from the debian package:

dpkg -i cdrtool*.deb

The files will be installed under /var/www/CDRTool directory.


Create CDRTool database
-----------------------

You need MySQL root access with GRANT priviledges from the machine where
CDRTool runs, otherwise copy the files required by setup/setup_mysql.sh to
the MySQL machine and run the script locally.

Change the MySQL password of the cdrtool user from file
setup/create_users.mysql

Then run the database setup script:

/var/www/CDRtool/setup/mysql/setup_mysql.sh

The script performs the following:

1. It adds to the MySQL server a user for cdrtool software
2. It creates a new MySQL database "cdrtool"
3. It creates a default web user admin/admin, you may use
this initial account to login in the web interface
4. It populates the cdrtool database with initial values


PHP setup and Apache setup
--------------------------

Install PHP and dependencies. If you have chosen to install the debian package, the dependencies will be resolved during installation.

Example for Debian systems and php4:

Enable php-mysql module in /etc/php/*/php.ini:

extension=mysql.so

Enable php in apache web server. Configuration example for apache on Debian systems:

/etc/apache/httpd.conf

DirectoryIndex index.phtml index.php index.html index.htm index.shtml index.cgi
AddType application/x-httpd-php .php
AddType application/x-httpd-php .phtml

Make sure php module is insbale in apache configuration. For apache this is done in /etc/apache/modules.conf:

LoadModule php4_module /usr/lib/apache/1.3/libphp4.so

Change AllowOverride None to AllowOverride All

For apache2 us a2enmode php command.

Create a virtual host for cdrtool. Example for apache2:

cp setup/apache2/sites-available/cdrtool.example.com /etc/apache2/sites-enabled
cp setup/apache2/conf.d/cdrtool /etc/apache2/conf.d/



ServerName cdrtool.example.com
DocumentRoot /var/www/
CustomLog /var/log/apache2/cdrtool-access.log combined
ErrorLog /var/log/apache2/cdrtool-errors.log
SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown

# To enable SSL:
# a2enmode ssl
# add Listen 443 to ports.conf file
# generate site certificates

# SSLEngine On
# SSLCertificateFile /etc/apache2/ssl/snakeoil-rsa.crt
# SSLCertificateKeyFile /etc/apache2/ssl/snakeoil-rsa.key



Enable the virtual site using:

a2ensite cdrtool.example.com

You may enable ssl using "a2enmod ssl" command. If you use ssl you must also generate a site certificate using the instructions provided by apache2 software.


Create a configuration file
---------------------------

cd /var/www/CDRTool
cp setup/global.inc.new.installation global.inc

Edit global.inc and setup your variables to match your system.

For each different datasource you must instantiate a new class extending one of the existing classes (ser, asterisk or cisco).

global.inc.in contains a more elaborate example for setting up datasources


Enable rating engine
--------------------

The rating engine is used for prepaid applications and as price calculator for postpaid traffic. It provides access to the rating tables and stays in memory (running as a daemon) to avoid reading the rating tables at every rating request. The rating tables can be reloaded without restarting the rating engine.

See the information in the head of the scripts/ratingEngine.php daemon for how to enable prepaid or other rating functionality. For prepaid, an external B2BUA is required, CDRTool provides only the rating and update balance functions.

Set the IP and port where the rating engine listens to and the OpenSER datasource in global.inc:

$RatingEngine=array("socketIP" => IP.address.of.the.private.interface.of.cdrtool.example.com",
"socketPort" => "9024",
"CDRS_class" => "ser_radius");

The MSQL schema for prepaid accounts: setup/mysql/create_prepaid.mysql The network engine has no security features, it must run over a protected network.

Edit /etc/default/cdrtool and set RUN_ratingEngine=yes

Then restart the rating engine:

/etc/init.d/cdrtool restart


High availability
-----------------

Setup heartbeat and MySQL replication.


CRON setup
----------

Some operations must be scheduled to run periodically by cron. Such operations are the scripts that block fraudulous accounts in OpenSER, normalize the call detail records, purge the old SIP traces.

The sample cron file is in /var/www/CDRTool/setup/crontabs/

This is done automatically if you install the debian package.


RADIUS setup
------------

CDRTool works with FreeRadius server from http://www.freeradius.org

apt-get install freeradius freeradius-mysql Create the database for radius tables on MySQL server

mysqladmin -u root create radius

Create RADIUS tables

The paths might differ depending on freeradius installation

cp /usr/share/doc/freeradius/examples/db_mysql.sql.gz .
gunzip db_mysql.sql.gz
mysql -u root radius < /usr/share/doc/freeradius/examples/db_mysql.sql

Patch radacct table for storage of CISCO VSA and rating. Apply a patch to the default radacct table. This will add fields necessary to store Vendor Specific Atributes from Cisco or OpenSER.

It is not possible to mix call details records from CISCO and OpenSER in the same radius table. You need to create multiple radius server profiles and save the CDRs into dedicated tables for each datasource.

Modify the default radacct table for OpenSER by running:

/var/www/CDRTool/setup/radius/OpenSER/radacct-patch.sh

and for CISCO by running:

/var/www/CDRTool/setup/radius/CISCO/radacct-patch.sh

Configure Freeradius to write data into the radacct table:

cp /var/www/CDRTool/setup/radius/OpenSER/sql.conf /etc/freeradius/sql.conf

For large volume of CDRs it is usefull automatically rotate the tables in which the data is written.

/var/www/CDRTool/setup/radius/OpenSER/radius_accounting.*, contain mysql procedures that automatically create monthly tables in the format radacctYYYYMM. To use mysql procedures, your must use MySQL server version 5 or greater and Freeradius server mysql module must be compiled to support CLIENT_MULTI_RESULTS mysql connection flag. This must be done in
src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c:

if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
config->sql_server,
config->sql_login,
config->sql_password,
config->sql_db,
atoi(config->sql_port),
NULL,
CLIENT_FOUND_ROWS|CLIENT_MULTI_RESULTS ))) {

Also, if you use MediaProxy for accounting in combination with OpenSER you must use the MediaProxy version that supports the same mysql procedures.

Edit the database connection details in sql.conf and restart Freeradius server.

Add RADIUS clients in clients.conf. Each device sending RADIUS accounting requests must be added to /etc/radddb/clients.conf. Examples:

client 10.0.0.0/8 {
secret=DssUET01
nastype=other
shortname=localnet
}

Enable MySQL accounting in FreeRadius server. Edit radius.conf "sql" must be uncommented or added if missing.

# accounting {
detail
sql
}

Copy /var/www/CDRTool/setup/radius/OpenSER/dictionary.ser to /etc/freeradius


OpenSER setup
-------------

CDRTool version >4.3 have been tested only against OpenSER 1.0 and above.

Enable MySQL storage for subscribers and accounting and Radius accounting. For SIP Tracing you need OpenSER siptrace module. To enable quota you must add an extra column in the subscriber table:

alter table openser.subscriber add column quota int not null;

Entries required in openser.cfg:

loadmodule "acc.so"

modparam("acc", "failed_transaction_flag", 1)
modparam("acc", "report_cancels", 0)
modparam("acc", "report_ack", 0)
modparam("acc", "early_media", 0)

modparam("acc", "log_level", 1)
modparam("acc", "log_flag", 1)
modparam("acc", "log_missed_flag", 1)

modparam("acc", "radius_config", "/etc/openser/radius/client.conf")
modparam("acc", "radius_flag", 1)
modparam("acc", "radius_missed_flag", 1)
modparam("acc", "radius_extra", "Sip-RPid=$avp(s:rpid); \
Source-IP=$si; \
Source-Port=$sp; \
Canonical-URI=$avp(can_uri); \
Billing-Party=$avp(billing_party); \
Divert-Reason=$avp(s:divert_reason); \
X-RTP-Stat=$avp(s:rtp_statistics); \
From-Header=$hdr(from); \
User-Agent=$hdr(user-agent); \
Contact=$hdr(contact); \
Event=$hdr(event); \
SIP-Proxy-IP=$avp(s:sip_proxy_ip)")

Further, you must enable accounting in various parts of the routing script by setting the accounting flag and saving the AVP containing the proxy IP address. It is important to save $avp(can_uri) after the Proxy has performed all possible lookups except DNS. The Canonical-URI will be used for rating the session. Example:

route {
...
setflag(ACCOUNTING_FLAG);
avp_write("SER_IP","$avp(s:sip-proxy)");
avp_write("$ru", "$avp(can_uri)");
}

Copy /var/www/CDRTool/setup/radius/OpenSER/dictionary.ser to /etc/openser/radius/ and uncomment the line:

$INCLUDE /etc/radiusclient-ng/dictionary


SOAP engine
-----------

Warning!

Support for the SOAP engine has been discontinued from version >=5.0.

Note: It depends on the SOAP library from http://pear.php.net/package/SOAP. On Debian systems, this dependency is automatically installed but if CDRTool is installed from the tar archive you must download and install manually this SOAP library.

The SOAP engine can be used by an external provisioning system to access information available in CDRTool (like prepaid accounts).

The rating and other CDRTool functions (like provisioning of prepaid accounts or checking the balance) are available by using SOAP calls. The SOAP calls are translated into requests for the rating engine (which must be up and running, see previous section).

The SOAP functionality including a WSDL file for creating a SOAP client is available in SOAP/WSDL/ directory.

The SOAP server supports authorization, the SOAP clients must connect using an account that has soap_client right assigned into the CDRTool accounts page. The SOAP server uses the Apache web server for transport, it can work with both HTTP and HTTPS. The servers logs all network requests to syslog.

Define in global.inc the SOAP server URL where the client should connect to:

$SOAPServer=array("cdr_source"=>"ser_radius",
"location" =>"https://example.com/CDRTool/SOAP/server.php",
"wsdl" =>"https://example.com/CDRTool/SOAP/WSDL/"
);

A test SOAP client is available in SOAP/client.php. It connects to the server and displays the CDRTool server version.

To prevent a bug in PEAR SOAP server add the following last line in function SOAP_ServerErrorHandler from SOAP/Server.php

function SOAP_ServerErrorHandler($errno, $errmsg, $filename,$linenum, $vars) {
....

unset($soap_server_fault->backtrace);
}

Interaction between rating engine and SOAP:

Function Provisioning B2BUA RatingEngine SOAP/XML
----------------------------------------------------------------------------------------
MaxSessionTime <----------------->

DeductBalance <----------------->

AddBalance ----------------------------------------------------------->
<------------------

CheckBalance ----------------------------------------------------------->
<------------------

ShowAccounts ----------------------------------------------------------->
<------------------

LastMissedCalls ----------------------------------------------------------->
LastPlacedCalls ----------------------------------------------------------->
LastReceivedCalls ----------------------------------------------------------->
OpenSERLocations ----------------------------------------------------------->



MediaProxy setup
----------------

MediaProy can log into CDRTool the IP bandwidth utilization, the codecs and the User Agents. MediaProxy logs this information into the database used by the Radiusserver, the radacct table.

MediaProxy can also correct the session duration of CDRs for which no BYE messages have been received.

The following information is logged:

- InputOctets and OutputOctets
- CodecType (like GSM or G711)
- ApplicationType (text/audio/video)
- User Agent(s)
- Media IP source and IP destination

See MediaProxy documentation for how to enable Radius or database accounting.


Asterisk setup
--------------

Download from cvs the asterisk-addons and patch asterisk to store CDRs in MySQL.

See /var/www/CDRTool/setup/asterisk for more information.


Cisco setup
-----------

Use the CISCO CLI, enter enable mode and configuration from terminal:

>enable
config terminal
aaa new-model
aaa group server radius aaa.router
server RADIUS_SERVER_IP auth-port 1812 acct-port 1813
aaa accounting connection h323 start-stop group radius
gw-accounting h323
gw-accounting h323 vsa
gw-accounting voip


Manual CDR uploads
------------------

If MySQL is down you will miss CDRs written by RADIUS. These CDRs can be reload manually from radius accounting files using the supplied upload scripts in scripts directory (IPNX, OpenSER and CISCO import scripts available).


Q931 Release codes for Cisco
----------------------------

In CDRTool CISCO hexadecimal release codes are automatically mapped to Q931 release codes.


Rating engine
-------------

See RATING file.


Monitoring
----------

Monit can check if the rating engine stopped working and restart it. A sample configuration file for monit is available in setup/monit/monitrc. Edit the IP address and hostname where the rating engine runs and copy /var/www/CDRTool/setup/monit/monitrc /etc/monit/monit.d/cdrtool

Usage statistics
----------------

CDRTool can build usage statistics of the number of online SIP devices from OpenSER, simultaneous number of media streams and relayed IP traffic by MediaProxy.

Define DB_siponline as a new database class in global.inc. Example:

class DB_siponline extends DB_Sql {
var $Host = "db";
var $Database = "openser";
var $User = "openser";
var $Password = "password";
var $Halt_On_Error ="no";
}

Define all media proxy servers in:

/var/www/CDRTool/status/config/media_servers.php

The script that gathers the usage information and builds the graphical statistics is /var/www/CDRTool/scripts/buildStatistics.php

This script runs as a cron job every 5 minutes.

The usage information is cumulated in /tmp/CDRTool-sip-statistics.txt file, which is a plain text file with one line per domain and values separated by tabs. This file can be easily parsed by external scripts to build statistics using other tools like SNMP.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

MYSQL SP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
/*
LAST UPDATED: 2007-01-04 03:14 CET

CHANGELOG:
- Added database name as parameter to stored procedures
- Fixed the multiple query result in "update_raddact_record_mediaproxy"
- Modus operandi update
- Fixed determination of previous year in "update_raddact_record_mediaproxy"
- Consolidate SET statements to minimize binary log entries
- Fixed bug in update_raddact_record_mediaproxy, the record values were reset
after execution of update on the first table causing failure to update
the previous table

Starting with version CDRTool version 4.9 it is possible to store call
details records in automatically rotated radacct tables by using MySQL
stored procedures.

This implementation has been contributed by
Andrei Magureanu

How to setup auto-rotation
--------------------------

1. Load radius_accounting.proc into the mysql server. MySQL >=5.0
version and SUPER priviledges are required.

2. Patch the Freeradius server mysql client to allow stored procedures.
Instructions can be found in ../freeradius directory. Re-compile and
re-install Freeradius server.

3. Copy radius_accounting.conf to sql.conf to the Freeradius server. Edit
the new sql.conf with the database connection information.

4. Update MediaProxy configuration to use radius accounting, instead of
database accounting (MediaProxy >= 1.8.0 is required)

5. In CDRTool, change global.inc table name to:
"table" => "radacct".date("Ym"),


The modus operandi of the stored procedure
------------------------------------------

Currently, call detail records generated by the OpenSER SIP Proxy are stored into a central radius.radacct table. The radacct table grows and must be manually purged, the purge operation locks the table for writing, which is blocking the application that performs the queries, and this situation is unacceptable.

The radacct tables are populated with data as follows: When a SIP session starts, an INSERT query generated by the Freeradius server inserts data into MySQL a radacctYYYYMM table (e.g. radacct200611). Later, when the SIP session ends, an UPDATE query sets various fields of the previously inserted record. The server that performs the INSERT matches the inserted data based on indexes present in the table. We want to store the data in monthly tables that have a name based on the following syntax:

radacct%Y%m

Examples:

radacct200611 (November table)
radacct200612 (December table)

The stored procedure achieves the following: On INSERT the monthly table is created automatically when necessary. If an INSERT query fails with the mysql code table not available, the right table is created and the INSERT query is performed in the new table. The table name is derived from the current month, which is determined from the AcctStartTime radius attribute. On UPDATE the procedure detects if the UPDATE query has failed because the record does not exist in the current table and updates the record in the previous table by substracting one calendar month, redefining the table name
and retry the query. The table name is derived from the AcctStopTime radius attribute. A example of queries on how the procedures can be used is available at the end of this script.

*/

-- OPTIONAL (COMMENT OUT): DATABASE TO USE
-- USE radius;

-- MUST FOR UNIX BASED SYSTEMS -- (SEE: http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html)
-- SET GLOBAL log_bin_trust_function_creators = 1;


DELIMITER ;

-- CREATE "BEGINNING OF DEFAULT TABLE NAME" FUNCTION

DROP FUNCTION IF EXISTS `set_radacct_table_name`;

DELIMITER $$

CREATE FUNCTION `set_radacct_table_name`() RETURNS varchar(50)
DETERMINISTIC
BEGIN
DECLARE tblname VARCHAR(50);
SET tblname = 'radacct';
RETURN tblname;
END $$

DELIMITER ;

-- CREATE SUPPORT "CREATE TABLE" PROCEDURE

DROP PROCEDURE IF EXISTS `create_radacct_table`;

DELIMITER $$

CREATE PROCEDURE `create_radacct_table`(IN var_tbl VARCHAR(50))

BEGIN

SET @stbl = CONCAT("CREATE TABLE IF NOT EXISTS ", var_tbl, " (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(255) NOT NULL default '',
`AcctUniqueId` varchar(255) NOT NULL default '',
`UserName` varchar(64) NOT NULL default '',
`Realm` varchar(64) default '',
`NASIPAddress` varchar(15) NOT NULL default '',
`NASPortId` varchar(50) NOT NULL default '',
`NASPortType` varchar(255) NOT NULL default '',
`AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(32) default NULL,
`ConnectInfo_stop` varchar(32) default NULL,
`AcctInputOctets` bigint(12) default NULL,
`AcctOutputOctets` bigint(12) default NULL,
`CalledStationId` varchar(50) NOT NULL default '',
`CallingStationId` varchar(50) NOT NULL default '',
`AcctTerminateCause` varchar(32) NOT NULL default '',
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default '',
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL,
`SipMethod` varchar(50) NOT NULL default '',
`SipResponseCode` smallint(5) unsigned NOT NULL default '0',
`SipToTag` varchar(128) NOT NULL default '',
`SipFromTag` varchar(128) NOT NULL default '',
`SipTranslatedRequestURI` varchar(255) NOT NULL default '',
`SipUserAgents` varchar(255) NOT NULL default '',
`SipApplicationType` varchar(255) NOT NULL default '',
`SipCodecs` varchar(255) NOT NULL default '',
`SipRPID` varchar(255) NOT NULL default '',
`SipRPIDHeader` varchar(255) NOT NULL default '',
`SourceIP` varchar(255) NOT NULL default '',
`SourcePort` varchar(255) NOT NULL default '',
`CanonicalURI` varchar(255) NOT NULL default '',
`DelayTime` varchar(5) NOT NULL default '',
`Timestamp` bigint(20) NOT NULL default '0',
`DestinationId` varchar(15) NOT NULL default '',
`Rate` text NOT NULL,
`Price` double(20,4) default NULL,
`Normalized` enum('0','1') default '0',
`BillingId` varchar(255) NOT NULL default '',
`MediaInfo` varchar(32) default NULL,
`RTPStatistics` text NOT NULL,
`FromHeader` varchar(128) NOT NULL default '',
`UserAgent` varchar(128) NOT NULL default '',
`Contact` varchar(128) NOT NULL default '',
PRIMARY KEY (`RadAcctId`),
UNIQUE KEY `sess_id` (`AcctSessionId`(128),`SipFromTag`,`SipToTag`),
KEY `UserName` (`UserName`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStartTime` (`AcctStartTime`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`),
KEY `caller_idx` (`CallingStationId`),
KEY `called_idx` (`CalledStationId`),
KEY `canon_idx` (`CanonicalURI`),
KEY `source_ip_idx` (`SourceIP`),
KEY `billing_id_idx` (`BillingId`),
KEY `dest_id_idx` (`DestinationId`),
KEY `sip_req_uri_idx` (`SipTranslatedRequestURI`),
KEY `normalize_idx` (`Normalized`),
KEY `MediaInfo_idx` (`MediaInfo`),
KEY `Realm_idx` (`Realm`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1");
PREPARE ctbl_stmt FROM @stbl;
EXECUTE ctbl_stmt;
DEALLOCATE PREPARE ctbl_stmt;

END $$

DELIMITER ;

-- CREATE "INSERT RECORD" PROCEDURE
DROP PROCEDURE IF EXISTS `insert_radacct_record`;

DELIMITER $$

CREATE PROCEDURE `insert_radacct_record`(
IN var_db_name VARCHAR(20),
IN var_AcctSessionId VARCHAR(255),
IN var_AcctUniqueId VARCHAR(255),
IN var_UserName VARCHAR(64),
IN var_Realm VARCHAR(64),
IN var_NASIPAddress VARCHAR(15),
IN var_NASPortId VARCHAR(50),
IN var_AcctStartTime VARCHAR(20),
IN var_AcctStopTime VARCHAR(20),
IN var_AcctSessionTime INT(12),
IN var_AcctInputOctets BIGINT(12),
IN var_AcctOutputOctets BIGINT(12),
IN var_CalledStationId VARCHAR(50),
IN var_CallingStationId VARCHAR(50),
IN var_AcctTerminateCause VARCHAR(32),
IN var_ServiceType VARCHAR(32),
IN var_FramedProtocol VARCHAR(32),
IN var_FramedIPAddress VARCHAR(15),
IN var_AcctStartDelay INT(12),
IN var_AcctStopDelay INT(12),
IN var_SipResponseCode SMALLINT(5),
IN var_SipMethod VARCHAR(50),
IN var_SipTranslatedRequestURI VARCHAR(255),
IN var_SipToTag VARCHAR(128),
IN var_SipFromTag VARCHAR(128),
IN var_SipRPID VARCHAR(255),
IN var_SourceIP VARCHAR(255),
IN var_SourcePort VARCHAR(255),
IN var_CanonicalURI VARCHAR(255),
IN var_Rate TEXT,
IN var_RTPStatistics TEXT
)
BEGIN

-- SET VARIABLES
SET @v_AcctSessionId = var_AcctSessionId,
@v_AcctUniqueId = var_AcctUniqueId,
@v_UserName = var_UserName,
@v_Realm = var_Realm,
@v_NASIPAddress = var_NASIPAddress,
@v_NASPortId = var_NASPortId;

IF var_AcctStartTime = '0' OR var_AcctStartTime = '' THEN
SET @v_AcctStartTime = NOW();
ELSE
SET @v_AcctStartTime = CAST(var_AcctStartTime AS DATETIME);
END IF;

IF var_AcctStopTime = '0' OR var_AcctStopTime = '' THEN
SET @v_AcctStopTime = CAST('0000-00-00 00:00:00' AS DATETIME);
ELSE
SET @v_AcctStopTime = CAST(var_AcctStopTime AS DATETIME);
END IF;

SET @v_db_name = var_db_name,
@v_AcctSessionTime = var_AcctSessionTime,
@v_AcctInputOctets = var_AcctInputOctets,
@v_AcctOutputOctets = var_AcctOutputOctets,
@v_CalledStationId = var_CalledStationId,
@v_CallingStationId = var_CallingStationId,
@v_AcctTerminateCause = var_AcctTerminateCause,
@v_ServiceType = var_ServiceType,
@v_FramedProtocol = var_FramedProtocol,
@v_FramedIPAddress = var_FramedIPAddress,
@v_AcctStartDelay = var_AcctStartDelay,
@v_AcctStopDelay = var_AcctStopDelay,
@v_SipResponseCode = var_SipResponseCode,
@v_SipMethod = var_SipMethod,
@v_SipTranslatedRequestURI = var_SipTranslatedRequestURI,
@v_SipToTag = var_SipToTag,
@v_SipFromTag = var_SipFromTag,
@v_SipRPID = var_SipRPID,
@v_SourceIP = var_SourceIP,
@v_SourcePort = var_SourcePort,
@v_CanonicalURI = var_CanonicalURI,
@v_Rate = var_Rate,
@v_RTPStatistics = var_RTPStatistics,
@var_y = (SELECT YEAR(@v_AcctStartTime)),
@var_m = (SELECT DATE_FORMAT(@v_AcctStartTime, '%m')),
@var_tbl_begin = (SELECT set_radacct_table_name()),
@var_tbl = CONCAT(@var_tbl_begin, @var_y, @var_m),
@var_tbl_cnt = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @v_db_name
AND TABLE_NAME = @var_tbl);

-- TABLE DOES NOT EXIST
IF @var_tbl_cnt = 0 THEN

-- CREATE RAD TABLE
SET @ctblproc = CONCAT("CALL create_radacct_table(", "'", @var_tbl, "'", ")");
PREPARE ctbl_proc_stmt FROM @ctblproc;
EXECUTE ctbl_proc_stmt;
DEALLOCATE PREPARE ctbl_proc_stmt;

END IF;

-- INSERT RECORD IN NEW TABLE
SET @instmt = CONCAT("INSERT INTO ", @var_tbl, " (
RadAcctId, AcctSessionId, AcctUniqueId,
UserName, Realm, NASIPAddress, NASPortId,
AcctStartTime, AcctStopTime, AcctSessionTime,
AcctInputOctets, AcctOutputOctets, CalledStationId,
CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay,
AcctStopDelay, SipResponseCode,
SipMethod, SipTranslatedRequestURI, SipToTag,
SipFromTag, SipRPID, SourceIP, SourcePort, CanonicalURI,
Rate, RTPStatistics)
VALUES (NULL, ?, ?, ?, SUBSTRING_INDEX(?, '@',-1),
?, ?, ?, ?, ?, ?, ?, trim(leading 'sip:' from trim(leading 'sips:' from ?)), trim(leading 'sip:' from trim(leading 'sips:' from ?)), ?, ?, ?, ?, ?, ?, ?, ?,trim(leading 'sip:' from trim(leading 'sips:' from ?)), ?, ?, ?, ?, ?, trim(leading 'sip:' from trim(leading 'sips:' from ?)), ?, ? )");

PREPARE add_rad FROM @instmt;
EXECUTE add_rad USING @v_AcctSessionId, @v_AcctUniqueId,
@v_UserName, @v_Realm, @v_NASIPAddress, @v_NASPortId,
@v_AcctStartTime, @v_AcctStopTime, @v_AcctSessionTime,
@v_AcctInputOctets, @v_AcctOutputOctets, @v_CalledStationId,
@v_CallingStationId, @v_AcctTerminateCause, @v_ServiceType,
@v_FramedProtocol, @v_FramedIPAddress, @v_AcctStartDelay,
@v_AcctStopDelay, @v_SipResponseCode, @v_SipMethod,
@v_SipTranslatedRequestURI, @v_SipToTag, @v_SipFromTag,
@v_SipRPID, @v_SourceIP, @v_SourcePort, @v_CanonicalURI,
@v_Rate, @v_RTPStatistics;
DEALLOCATE PREPARE add_rad;

-- CLEAN UP
SET @v_AcctSessionId = null,
@v_AcctUniqueId = null,
@v_UserName = null,
@v_Realm = null,
@v_NASIPAddress = null,
@v_NASPortId = null,
@v_AcctStartTime = null,
@v_AcctStopTime = null,
@v_AcctSessionTime = null,
@v_AcctInputOctets = null,
@v_AcctOutputOctets = null,
@v_CalledStationId = null,
@v_CallingStationId = null,
@v_AcctTerminateCause = null,
@v_ServiceType = null,
@v_FramedProtocol = null,
@v_FramedIPAddress = null,
@v_AcctStartDelay = null,
@v_AcctStopDelay = null,
@v_SipResponseCode = null,
@v_SipMethod = null,
@v_SipTranslatedRequestURI = null,
@v_SipToTag = null,
@v_SipFromTag = null,
@v_SipRPID = null,
@v_SourceIP = null,
@v_SourcePort = null,
@v_CanonicalURI = null,
@v_Rate = null,
@v_RTPStatistics = null,
@var_y = null,
@var_m = null,
@v_db_name = null,
@var_tbl_begin = null,
@var_tbl = null;

END $$

DELIMITER ;

-- CREATE "UPDATE RECORD" PROCEDURE

DROP PROCEDURE IF EXISTS `update_radacct_record`;

DELIMITER $$

CREATE PROCEDURE `update_radacct_record`(
IN var_db_name VARCHAR(20),
IN var_AcctStopTime VARCHAR(20),
IN var_AcctStopDelay INT(12),
IN var_ConnectInfo_stop VARCHAR(32),
IN var_RTPStatistics VARCHAR(255),
IN var_AcctSessionId VARCHAR(255),
IN var_SipToTag VARCHAR(128),
IN var_SipFromTag VARCHAR(128)
)

BEGIN

-- SET VARIABLES
SET @v_db_name = var_db_name,
@var_tbl_begin = (SELECT set_radacct_table_name()),
@v_stoptime = var_AcctStopTime,
@var_month_formatted = (SELECT DATE_FORMAT(@v_stoptime, '%m')),
@var_yst = (SELECT DATE_FORMAT(@v_stoptime, '%Y')),
@var_tbl_month = CONCAT(@var_tbl_begin, @var_yst, @var_month_formatted),
@v_AcctStopTime = CAST(var_AcctStopTime AS DATETIME),
@v_AcctStopDelay = var_AcctStopDelay,
@v_ConnectInfo_stop = var_ConnectInfo_stop,
@v_RTPStatistics = var_RTPStatistics,
@v_AcctSessionId = var_AcctSessionId,
@v_SipToTag = var_SipToTag,
@v_SipFromTag = var_SipFromTag,
@var_tbl_cnt1 = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @v_db_name
AND TABLE_NAME = @var_tbl_month);

IF @var_tbl_cnt1 = 1 THEN
-- WE HAVE A TABLE BASED ON StopTime
-- SO LET'S TRY TO UPDATE
SET @updstmt1 = CONCAT("UPDATE ", @var_tbl_month, " SET
AcctStopTime = ?,
AcctSessionTime = UNIX_TIMESTAMP(?) - UNIX_TIMESTAMP(AcctStartTime),
AcctStopDelay = ?,
ConnectInfo_stop = ?,
RTPStatistics = ?,
Normalized = '0'
WHERE
AcctSessionId = ?
AND ((SipToTag = ? AND SipFromTag = ?) OR (SipToTag = ? AND SipFromTag = ?))
AND ConnectInfo_stop IS NULL;");
PREPARE update_rad1 FROM @updstmt1;
EXECUTE update_rad1 USING @v_AcctStopTime, @v_AcctStopTime,
@v_AcctStopDelay, @v_ConnectInfo_stop, @v_RTPStatistics,
@v_AcctSessionId, @v_SipToTag, @v_SipFromTag,
@v_SipFromTag, @v_SipToTag;

SET @updatedrows = (SELECT ROW_COUNT()),
@updstmt1 = null;

DEALLOCATE PREPARE update_rad1;

ELSE
SET @updatedrows = 0;
END IF;


IF @updatedrows = 0 THEN
-- UPDATE LAST MONTH'S TABLE BASED ON var_AcctStopTime

SET @var_prev_month = (SELECT DATE_FORMAT(DATE_ADD(@v_stoptime, INTERVAL -1 MONTH), '%Y%m')),
@var_tbl_pmonth = CONCAT(@var_tbl_begin, @var_prev_month),
@var_tbl_cnt2 = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @v_db_name
AND TABLE_NAME = @var_tbl_pmonth);

IF @var_tbl_cnt2 = 1 THEN
-- UPDATE PREVIOUS MONTH TABLE

SET @updstmt2 = CONCAT("UPDATE ", @var_tbl_pmonth, " SET
AcctStopTime = ?,
AcctSessionTime = UNIX_TIMESTAMP(?) - UNIX_TIMESTAMP(AcctStartTime),
AcctStopDelay = ?,
ConnectInfo_stop = ?,
RTPStatistics = ?,
Normalized = '0'
WHERE
AcctSessionId = ?
AND ((SipToTag = ? AND SipFromTag = ?) OR
(SipToTag = ? AND SipFromTag = ?));");
PREPARE update_rad2 FROM @updstmt2;
EXECUTE update_rad2 USING @v_AcctStopTime, @v_AcctStopTime,
@v_AcctStopDelay, @v_ConnectInfo_stop, @v_RTPStatistics,
@v_AcctSessionId, @v_SipToTag, @v_SipFromTag,
@v_SipFromTag, @v_SipToTag;

SET @updatedrows = (SELECT ROW_COUNT()),
@updstmt = null;

DEALLOCATE PREPARE update_rad2;
ELSE
SET @updatedrows = 0;
END IF;

END IF;

-- CLEAN UP
SET @v_AcctStopTime = null,
@v_AcctStopDelay = null,
@v_ConnectInfo_stop = null,
@v_RTPStatistics = null,
@v_AcctSessionId = null,
@v_SipToTag = null,
@v_SipFromTag = null,
@v_db_name = null;

END $$

DELIMITER ;

-- CREATE "MEDIAPROXY RECORD UPDATE" PROCEDURE

DROP PROCEDURE IF EXISTS `update_raddact_record_mediaproxy`;

DELIMITER $$

CREATE PROCEDURE `update_raddact_record_mediaproxy`(
IN var_db_name VARCHAR(20),
IN var_AcctSessionTime int(12),
IN var_AcctInputOctets bigint(12),
IN var_AcctOutputOctets bigint(12),
IN var_SipUserAgents VARCHAR(255),
IN var_SipCodecs VARCHAR(255),
IN var_SipApplicationType VARCHAR(255),
IN var_MediaInfo VARCHAR(32),
IN var_Normalized ENUM('0','1'),
IN var_AcctSessionId VARCHAR(255),
IN var_SipToTag VARCHAR(128),
IN var_SipFromTag VARCHAR(128)
)

BEGIN

-- SET VARIABLES
SET @var_tbl_begin = (SELECT set_radacct_table_name()),
@var_prev_month = (SELECT DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -1 MONTH), '%Y%m')),
@var_current_month = (SELECT DATE_FORMAT(CURRENT_DATE, '%Y%m')),
@var_tbl = CONCAT(@var_tbl_begin, @var_current_month),
@var_tbl_prev_month = CONCAT(@var_tbl_begin, @var_prev_month),
@v_AcctSessionTime = var_AcctSessionTime,
@v_AcctInputOctets = var_AcctInputOctets,
@v_AcctOutputOctets = var_AcctOutputOctets,
@v_SipUserAgents = var_SipUserAgents,
@v_SipCodecs = var_SipCodecs,
@v_SipApplicationType = var_SipApplicationType,
@v_MediaInfo = var_MediaInfo,
@v_Normalized = var_Normalized,
@v_AcctSessionId = var_AcctSessionId,
@v_SipToTag = var_SipToTag,
@v_SipFromTag = var_SipFromTag,
@v_na = 'n/a',
@v_dbname = var_db_name,
@updatedrows = 0,
@var_tbl_cnt1 = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @v_dbname
AND TABLE_NAME = @var_tbl),
@var_tbl_cnt2 = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @v_dbname
AND TABLE_NAME = @var_tbl_prev_month),
@proc_query = CONCAT("CALL proc_update_raddact_record_mediaproxy (?,?,?,?,?,?,?,?,?,?,?,?,?)");

PREPARE proc_rad_stmt FROM @proc_query;

IF @var_tbl_cnt1 = 1 THEN
-- FOR THE CURRENT MONTH
EXECUTE proc_rad_stmt USING @var_tbl, @v_AcctSessionTime,
@v_AcctInputOctets, @v_AcctOutputOctets,
@v_SipUserAgents, @v_SipCodecs, @v_SipApplicationType,
@v_MediaInfo, @v_Normalized, @v_AcctSessionId,
@v_SipToTag, @v_SipFromTag, @v_dbname;
END IF;

IF @updatedrows = 0 AND @var_tbl_cnt2 = 1 THEN
-- FOR THE PREVIOUS MONTH
EXECUTE proc_rad_stmt USING @var_tbl_prev_month, @v_AcctSessionTime,
@v_AcctInputOctets, @v_AcctOutputOctets,
@v_SipUserAgents, @v_SipCodecs, @v_SipApplicationType,
@v_MediaInfo, @v_Normalized, @v_AcctSessionId,
@v_SipToTag, @v_SipFromTag, @v_dbname;

IF @var_tbl_cnt1 = 1 AND @updatedrows = 0 THEN
-- FOR THE CURRENT MONTH WITH SipToTag = 'n/a'
EXECUTE proc_rad_stmt USING @var_tbl, @v_AcctSessionTime,
@v_AcctInputOctets, @v_AcctOutputOctets,
@v_SipUserAgents, @v_SipCodecs, @v_SipApplicationType,
@v_MediaInfo, @v_Normalized, @v_AcctSessionId,
@v_na, @v_SipFromTag, @v_dbname;

IF @var_tbl_cnt2 = 1 AND @updatedrows = 0 THEN
-- FOR THE PREVIOUS MONTH WITH SipToTag = 'n/a'
EXECUTE proc_rad_stmt USING @var_tbl_prev_month, @v_AcctSessionTime,
@v_AcctInputOctets, @v_AcctOutputOctets,
@v_SipUserAgents, @v_SipCodecs,
@v_SipApplicationType, @v_MediaInfo, @v_Normalized, @v_AcctSessionId,
@v_na, @v_SipFromTag, @v_dbname;
END IF;
END IF;
END IF;

DEALLOCATE PREPARE proc_rad_stmt;

-- CLEAN UP

SET @var_current_month = null,
@var_db_name = null,
@var_tbl_begin = null,
@var_tbl = null,
@var_tbl_cnt = null,
@var_prev_month = null,
@var_tbl_prev_month = null,
@v_AcctSessionTime = null,
@v_AcctInputOctets = null,
@v_AcctOutputOctets = null,
@v_SipUserAgents = null,
@v_SipCodecs = null,
@v_SipApplicationType = null,
@v_MediaInfo = null,
@v_Normalized = null,
@v_AcctSessionId = null,
@v_SipFromTag = null,
@v_SipToTag = null,
@v_na = null,
@v_dbname = null;

END $$

DELIMITER ;

-- CREATE SUPPORT PROCEDURE FOR "update_raddact_record_mediaproxy"

DROP PROCEDURE IF EXISTS `proc_update_raddact_record_mediaproxy`;

DELIMITER $$

CREATE PROCEDURE `proc_update_raddact_record_mediaproxy`(
IN var_tblname VARCHAR(50),
IN var_AcctSessionTime int(12),
IN var_AcctInputOctets bigint(12),
IN var_AcctOutputOctets bigint(12),
IN var_SipUserAgents VARCHAR(255),
IN var_SipCodecs VARCHAR(255),
IN var_SipApplicationType VARCHAR(255),
IN var_MediaInfo VARCHAR(32),
IN var_Normalized ENUM('0','1'),
IN var_AcctSessionId VARCHAR(255),
IN var_SipToTag VARCHAR(128),
IN var_SipFromTag VARCHAR(128),
IN var_dbname VARCHAR(20)
)
BEGIN

-- TABLENAME VARIABLE
SET @var_tbl = var_tblname,
@var_db_name = var_dbname,
@var_tbl_cnt = (SELECT COUNT(TABLE_NAME) AS TBLCNT
FROM information_schema.tables
WHERE TABLE_SCHEMA = @var_db_name
AND TABLE_NAME = @var_tbl);

IF @var_tbl_cnt = 0 THEN
-- TABLE DOESN'T EVEN EXISTS, SO RETURN 0
SET @rcnt = (SELECT 0);
ELSE

SET @radupdstmt = CONCAT("UPDATE ", @var_tbl, " SET
AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, ?, 0),
AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND),
AcctInputOctets = AcctInputOctets + ?,
AcctOutputOctets = AcctOutputOctets + ?,
SipUserAgents = ?,
SipCodecs = ?,
SipApplicationType = ?,
MediaInfo = ?,
Normalized = ?
WHERE
AcctSessionId = ?
AND SipToTag = ? AND SipFromTag = ?;");
PREPARE update_rad FROM @radupdstmt;

SET @v_AcctSessionTime = var_AcctSessionTime,
@v_AcctInputOctets = var_AcctInputOctets,
@v_AcctOutputOctets = var_AcctOutputOctets,
@v_SipUserAgents = var_SipUserAgents,
@v_SipCodecs = var_SipCodecs,
@v_SipApplicationType = var_SipApplicationType,
@v_MediaInfo = var_MediaInfo,
@v_Normalized = var_Normalized,
@v_AcctSessionId = var_AcctSessionId,
@v_SipToTag = var_SipToTag,
@v_SipFromTag = var_SipFromTag;

EXECUTE update_rad USING @v_AcctSessionTime, @v_AcctInputOctets, @v_AcctOutputOctets,
@v_SipUserAgents, @v_SipCodecs, @v_SipApplicationType,
@v_MediaInfo, @v_Normalized, @v_AcctSessionId, @v_SipToTag, @v_SipFromTag;
SET @rcnt = (SELECT ROW_COUNT());
DEALLOCATE PREPARE update_rad;

END IF;

END $$

DELIMITER ;


/*

-- TEST PROCEDURE THAT INSERTS A RECORD
CALL insert_radacct_record (
'radius','3c3b5ff12bf2-m5udeydrjsuw@snom320-000413241247', '5af53194787eccf1',
'adi@umts.ro', 'adi@umts.ro', '83.149.75.105', '5060', '2006-12-10 12:09:19',
'0', '0', '0', '0', 'sip:3333@umts.ro=3Buser=3Dphone',
'sip:adi@umts.ro', '200', 'Sip-Session', '', '', '0', '0', '200', 'Invite',
'sip:3333@vm01.dns-hosting.info=3Buser=3Dphone', 'as5664a60b', '27qems1o2j',
'208005169', '81.23.228.147', '5060',
'sip:3333@vm01.dns-hosting.info=3Buser=3Dphone', '', ''
);

-- TEST PROCEDURE THAT UPATES A RECORD WITH MEDIAPROXY INFORMATION
CALL update_raddact_record_mediaproxy (
'radius','3','5896','7140','snom320/5.2 + Asterisk', 'GSM', 'Audio', '', '0',
'3c3b5ff12bf2-m5udeydrjsuw@snom320-000413241247', 'as5664a60b','27qems1o2j'
);

-- TEST PROCEDURE THAT UPATES A RECORD
CALL update_radacct_record (
'radius','2006-12-10 12:09:21', '0', '', 'n/a',
'3c3b5ff12bf2-m5udeydrjsuw@snom320-000413241247', 'as5664a60b', '27qems1o2j'
);

*/