VSXi CDR to MySQL: Table definition and script

6 yrs ago

This script is provided as a reference only.

Table Definition

MySQL CDR Table Description

|CREATE TABLE `cdr` (

`recno` bigint(11) NOT NULL AUTO_INCREMENT,

`version` varchar(5) DEFAULT NULL,

`rectype` char(1) DEFAULT NULL,

`conntype` varchar(32) DEFAULT NULL,

`sessionid` varchar(36) DEFAULT NULL,

`relcause` int(11) DEFAULT NULL,

`starttime` datetime NOT NULL,

`answertime` datetime DEFAULT NULL,

`endtime` datetime DEFAULT NULL,

`gmtoffset` int(11) DEFAULT NULL,

`releasecausetext` varchar(32) DEFAULT NULL,

`releasecausecode` int(11) DEFAULT NULL,

`1streleasedialog` varchar(1) DEFAULT NULL,

`origtrunk` int(11) DEFAULT NULL,

`voiporig` varchar(6) DEFAULT NULL,

`callingnumber` varchar(41) DEFAULT NULL,

`orighost` varchar(127) DEFAULT NULL,

`callednumber` varchar(41) DEFAULT NULL,

`desthost` varchar(127) DEFAULT NULL,

`origcallid` varchar(127) DEFAULT NULL,

`origremotepayloadip` varchar(15) DEFAULT NULL,

`origremotepayloadport` varchar(6) DEFAULT NULL,

`origlocalpayloadip` varchar(15) DEFAULT NULL,

`origlocalpayloadport` varchar(6) DEFAULT NULL,

`origcodeclist` varchar(63) DEFAULT NULL,

`origingresspackets` int(11) DEFAULT NULL,

`origegresspackets` int(11) DEFAULT NULL,

`origingressoctets` int(11) DEFAULT NULL,

`origegressoctets` int(11) DEFAULT NULL,

`origingresspktloss` int(11) DEFAULT NULL,

`origingressdelay` int(11) DEFAULT NULL,

`origingressjitter` int(11) DEFAULT NULL,

`termtrunk` int(11) DEFAULT NULL,

`voipterm` varchar(6) DEFAULT NULL,

`termsourcenumber` varchar(41) DEFAULT NULL,

`termsourcehost` varchar(127) DEFAULT NULL,

`termdestnumber` varchar(41) DEFAULT NULL,

`termdesthostname` varchar(127) DEFAULT NULL,

`termcallid` varchar(127) DEFAULT NULL,

`termremotepayloadip` varchar(15) DEFAULT NULL,

`termremotepayloadport` varchar(6) DEFAULT NULL,

`termlocalpayloadip` varchar(15) DEFAULT NULL,

`termlocalpayloadport` varchar(6) DEFAULT NULL,

`termcodeclist` varchar(63) DEFAULT NULL,

`termingresspackets` int(11) DEFAULT NULL,

`termegresspackets` int(11) DEFAULT NULL,

`termingressoctets` int(11) DEFAULT NULL,

`termegressoctets` int(11) DEFAULT NULL,

`termingresspacketloss` int(11) DEFAULT NULL,

`termingressdelay` int(11) DEFAULT NULL,

`termingressjitter` int(11) DEFAULT NULL,

`finalrouteind` char(1) DEFAULT NULL,

`routingdigits` varchar(41) DEFAULT NULL,

`duration` int(11) DEFAULT NULL,

`postdialdelay` int(11) DEFAULT NULL,

`ringtime` int(11) DEFAULT NULL,

`durationms` int(11) DEFAULT NULL,

`confid` varchar(35) DEFAULT NULL,

`rpidpai` varchar(41) DEFAULT NULL,

`routeentryindex` int(11) DEFAULT NULL,

`routetableused` int(11) DEFAULT NULL,

`lnpdipped` int(11) DEFAULT NULL,

`ingresslrn` varchar(41) DEFAULT NULL,

`egresslrn` varchar(41) DEFAULT NULL,

`cnamdipped` int(11) DEFAULT NULL,

`dncdipped` int(11) DEFAULT NULL,

`origtidalias` varchar(28) DEFAULT NULL,

`termtidalias` varchar(28) DEFAULT NULL,

`extrouteserverdipped` int(11) DEFAULT NULL,

`olidigits` int(11) DEFAULT NULL,

`routingmatch` varchar(41) DEFAULT NULL,

`terrmpddms` int(11) DEFAULT NULL,

`pchargeinfo` varchar(41) DEFAULT NULL,

`jip` char(41) DEFAULT NULL,

`matcheddigitsers` varchar(41) DEFAULT NULL,

`routetableers` int(11) DEFAULT NULL,

`routsequenceers` int(11) DEFAULT NULL,

`jurisdictiontypeers` int(11) DEFAULT NULL,

`reasoncause` int(11) DEFAULT NULL,

`mappedcausecode` int(11) DEFAULT NULL,

`mappedreasoncause` int(11) DEFAULT NULL,

`reasoncausetext` varchar(32) DEFAULT NULL,

`diversionhistoryinfo` varchar(41) DEFAULT NULL,

`origburstlosscount` int(11) DEFAULT NULL,

`origmos` int(11) DEFAULT NULL,

`origrfactor` int(11) DEFAULT NULL,

`termburstlosscount` int(11) DEFAULT NULL,

`termmos` int(11) DEFAULT NULL,

`termrfactor` int(11) DEFAULT NULL,

`cdrcol` varchar(45) DEFAULT NULL,

PRIMARY KEY (`recno`),

UNIQUE KEY `recno_UNIQUE` (`recno`),

KEY `starttime` (`starttime`)

) ENGINE=InnoDB AUTO_INCREMENT=55169 DEFAULT CHARSET=latin1 |

Shell Script

#!/bin/bash

MYSQL_HOST="REPLACE"

MYSQL_USER="REPLACE"

MYSQL_PASSWORD="REPLACE"

MYSQL_DB="REPLACE"

MYSQL_TABLE="cdr"

# First Check DB connectivity. If connection is down

db_status=`/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e exit 2>/dev/null; echo $?`

echo "DB Status:"$db_status

if [ $db_status -eq 0 ]; then

# Process all pending *.cdr files in /CDR/

for file in /CDR/*.cdr; do

# Print CDR being processed

echo $file

# Parse fields

IFS=";"

while read cdrcol version rectype conntype sessionid relcause starttime answertime endtime gmtoffset releasecausetext releasecausecode firstreleasedialog origtrunk voiporig callingnumber orighost callednumber desthost origcallid origremotepayloadip origremotepayloadport origlocalpayloadip origlocalpayloadport origcodeclist origingresspackets origegresspackets origingressoctets origegressoctets origingresspktloss origingressdelay origingressjitter termtrunk voipterm termsourcenumber termsourcehost termdestnumber termdesthostname termcallid termremotepayloadip termremotepayloadport termlocalpayloadip termlocalpayloadport termcodeclist termingresspackets termegresspackets termingressoctets termegressoctets termingresspacketloss termingressdelay termingressjitter finalrouteind routingdigits duration postdialdelay ringtime durationms confid rpidpai routeentryindex routetableused lnpdipped ingresslrn egresslrn cnamdipped dncdipped origtidalias termtidalias extrouteserverdipped olidigits routingmatch terrmpddms pchargeinfo jip matcheddigitsers routetableers routsequenceers jurisdictiontypeers reasoncause mappedcausecode mappedreasoncause reasoncausetext diversionhistoryinfo origburstlosscount origmos origrfactor termburstlosscount termmos termrfactor

echo "INSERT INTO cdr (version,rectype,conntype,sessionid,relcause,starttime,answertime,endtime,gmtoffset,releasecausetext,releasecausecode,1streleasedialog,origtrunk,voiporig,callingnumber,orighost,callednumber,desthost,origcallid,origremotepayloadip,origremotepayloadport,origlocalpayloadip,origlocalpayloadport,origcodeclist,origingresspackets,origegresspackets,origingressoctets,origegressoctets,origingresspktloss,origingressdelay,origingressjitter,termtrunk,voipterm,termsourcenumber,termsourcehost,termdestnumber,termdesthostname,termcallid,termremotepayloadip,termremotepayloadport,termlocalpayloadip,termlocalpayloadport,termcodeclist,termingresspackets,termegresspackets,termingressoctets,termegressoctets,termingresspacketloss,termingressdelay,termingressjitter,finalrouteind,routingdigits,duration,postdialdelay,ringtime,durationms,confid,rpidpai,routeentryindex,routetableused,lnpdipped,ingresslrn,egresslrn,cnamdipped,dncdipped,origtidalias,termtidalias,extrouteserverdipped,olidigits,routingmatch,terrmpddms,pchargeinfo,jip,matcheddigitsers,routetableers,routsequenceers,jurisdictiontypeers,reasoncause,mappedcausecode,mappedreasoncause,reasoncausetext,diversionhistoryinfo,origburstlosscount,origmos,termburstlosscount,termmos,cdrcol) VALUES ('$version','$rectype','$conntype','$sessionid','$relcause','$starttime','$answertime','$endtime','$gmtoffset','$releasecausetext','$releasecausecode','$firstreleasedialog','$origtrunk','$voiporig','$callingnumber','$orighost','$callednumber','$desthost','$origcallid','$origremotepayloadip','$origremotepayloadport','$origlocalpayloadip','$origlocalpayloadport','$origcodeclist','$origingresspackets','$origegresspackets','$origingressoctets','$origegressoctets','$origingresspktloss','$origingressdelay','$origingressjitter','$termtrunk','$voipterm','$termsourcenumber','$termsourcehost','$termdestnumber','$termdesthostname','$termcallid','$termremotepayloadip','$termremotepayloadport','$termlocalpayloadip','$termlocalpayloadport','$termcodeclist','$termingresspackets','$termegresspackets','$termingressoctets','$termegressoctets','$termingresspacketloss','$termingressdelay','$termingressjitter','$finalrouteind','$routingdigits','$duration','$postdialdelay','$ringtime','$durationms','$confid','$rpidpai','$routeentryindex','$routetableused','$lnpdipped','$ingresslrn','$egresslrn','$cnamdipped','$dncdipped','$origtidalias','$termtidalias','$extrouteserverdipped','$olidigits','$routingmatch','$terrmpddms','$pchargeinfo','$jip','$matcheddigitsers','$routetableers','$routsequenceers','$jurisdictiontypeers','$reasoncause','$mappedcausecode','$mappedreasoncause','$reasoncausetext','$diversionhistoryinfo','$origburstlosscount','$origrfactor','$termburstlosscount','$termmos','$cdrcol');" | /usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DB

done < $file

# Move file to /CDR/migrated/

mv $file /CDR/migrated/

done

# Don't do anything if DB connection failed

else

echo "DB Connecitivy Error";