Publicado em: 03/05/2009
O objetivo deste artigo é apenas anotar os passos de configuração do postgres bem como a criação de uma base cdr para tarifação (billing) do asterisk, e claro, scriptar tudo isso.
- Obtendo e descompactando:
wget http://wwwmaster.postgresql.org/redir/378/f/source/v8.3.7/postgresql-8.3.7.tar.bz2 tar -xjvf postgresql-8.3.7.tar.bz2 cd postgresql-8.3.7
- Pacotes adicionais para compilação:
apt-get install gcc libc6-dev libc-dev libmudflap0-dev zlibc zlib1g-dev
- Configure e compile:
./configure --prefix=/usr/local/postgres --bindir=/usr/bin --sysconfdir=/etc/postgres --without-readline make make install
- Criando grupo e usuário:
adduser postgres
- Criando diretório para os dados:
mkdir /usr/local/postgres/data chown postgres.postgres /usr/local/postgres/data
- Inicializando o banco:
su - postgres $ initdb -D /usr/local/postgres/data The files belonging to this database system will be owned by user "postgres". *** This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". *** fixing permissions on existing directory /usr/local/postgres/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 24MB/153600 creating configuration files ... ok creating template1 database in /usr/local/postgres/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok *** WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. ***Success. You can now start the database server using: *** postgres -D /usr/local/postgres/data or pg_ctl -D /usr/local/postgres/data -l logfile start
- Rodando o postgres em segundo plano, gravando logs:
$ postmaster -D /usr/local/postgres/data > /usr/local/postgres/data/logfile &
- Criando base de dados e testando:
$ createdb asterisk $ psql asterisk
Banco instalado e funcionando!
Entendendo o header do arquivo do asterisk
1. accountcode: What account number to use (Only used when Authentication is enable) 2. src: Caller*ID number 3. dst: Destination extension 4. dcontext: Destination context 5. clid: Caller*ID with text 6. channel: Channel used 7. dstchannel: Destination channel if appropriate 8. lastapp: Last application if appropriate 9. lastdata: Last application data (arguments) 10. start: Start of call (date/time) 11. answer: Anwer of call (date/time) 12. end: End of call (date/time) 13. duration: Total time in system, in seconds (integer) 14. billsec: Total time call is up, in seconds (integer) 15. disposition: What happened to the call: ANSWERED, NO ANSWER, BUSY 16. amaflags: What flags to use: see amaflags: DOCUMENTATION, BILL, IGNORE
Uma linha de log comum:
"","4704","22221111","internal","""ftorres"" <4704>","SIP/ftorres-0824a0b8","DGV/1","Dial","Dgv/g1/11112222|60|Tt","2008-08-26 08:33:43","2008-08-26 08:34:11","2008-08-26 08:34:29",46,18,"ANSWERED","DOCUMENTATION"
Agora, vamos organizar isso tudo:
1. "", 2. "4704", 3. "22221111", 4. "internal", 5. """ftorres"" <4704>", 6. "SIP/ftorres-0824a0b8", 7. "DGV/1", 8. "Dial", 9. "Dgv/g1/22221111|60|Tt", 10. "2008-08-26 08:33:43", 11. "2008-08-26 08:34:11", 12. "2008-08-26 08:34:29", 13. 46, 14. 18, 15. "ANSWERED", 16. "DOCUMENTATION"
Em outras palavras, teriamos:
accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,start,answer,end,duration,billsec,disposition,amaflags
Agora que já conhecemos a estrutura dos logs, podemos criar nossa tabela.
debian:~# su - postgres No directory, logging in with HOME=/ postgres@debian:/$ psql asterisk Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit asterisk=# CREATE TABLE "cdr" ( "accountcode" varchar(20) NOT NULL default '', "src" varchar(80) NOT NULL default '', "dst" varchar(80) NOT NULL default '', "dcontext" varchar(80) NOT NULL default '', "clid" varchar(80) NOT NULL default '', "channel" varchar(80) NOT NULL default '', "dstchannel" varchar(80) NOT NULL default '', "lastapp" varchar(80) NOT NULL default '', "lastdata" varchar(80) NOT NULL default '', "callstartdate" timestamp NOT NULL default '1970-01-01 00:00:00', "callanswerdate" timestamp NOT NULL default '1970-01-01 00:00:00', "callenddate" timestamp NOT NULL default '1970-01-01 00:00:00', "duration" int NOT NULL default '0', "billsec" int NOT NULL default '0', "disposition" varchar(45) NOT NULL default '', "amaflags" varchar(45) NOT NULL default '' );
Este é o nosso arquivo Master.csv
"","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081de478","SIP/TMais-b6f0ac30","Hangup","","2008-11-25 10:16:08",,"2008-11-25 10:16:09",1,0,"NO ANSWER","DOCUMENTATION" "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081e62d8","SIP/TMais-081ff048","Hangup","","2008-11-25 10:16:21",,"2008-11-25 10:16:22",1,0,"NO ANSWER","DOCUMENTATION" "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-081bfe50","SIP/TMais-081d07e0","Dial","SIP/26262626@TMais|45|Tt","2008-11-25 10:16:59",,"2008-11-25 10:17:02",3,0,"NO ANSWER","DOCUMENTATION" "","101","01111112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-0816e618","SIP/TMais-081c4c58","Dial","SIP/01111112222@TMais|45|Tt","2008-11-25 10:18:02",,"2008-11-25 10:18:18",16,0,"NO ANSWER","DOCUMENTATION" "","101","11112222","internal","""flavio.torres"" <101>","SIP/flavio.torres-0816e618","SIP/TMais-081de348","Dial","SIP/11112222@TMais|45|Tt","2008-11-25 10:18:26",,"2008-11-25 10:18:29",3,0,"NO ANSWER","DOCUMENTATION" "","102","22221111","internal","""flavio.torres"" <102>","SIP/flavio.torres-0816e618","SIP/TMais-081c8408","Dial","SIP/22221111@TMais|45|Tt","2008-11-25 16:01:49","2008-11-25 16:01:57","2008-11-25 16:02:46",57,49,"ANSWERED","DOCUMENTATION"
Este é o nosso script que fará a leitura do arquivo e importará para o banco:
#!/bin/bash
FILE=$1
sed -i "s#\"#\'#g" ${FILE}
IFS=","
while read ACCOUNTCODE SRC DST DCONTEXT CLID CHANNEL DSTCHANNEL LASTAPP LASTDATA START ANSWER END DURATION BILLSEC DISPOSITION AMAFLAGS;do
${ANSWER:="'1970-01-01 00:00:00'"}
echo "INSERT INTO cdr VALUES ($ACCOUNTCODE, $SRC, $DST, $DCONTEXT, $CLID, $CHANNEL, $DSTCHANNEL, $LASTAPP, $LASTDATA, $START, $ANSWER, \
$END, $DURATION, $BILLSEC, $DISPOSITION, $AMAFLAGS)" | psql -U postgres -f- asterisk
done < $FILE
Agora, basta executá-lo assim:
bash LoadMaster.sh Master.csv
É isso aí, agora basta criar uma página ‘php’ que realize a extração dos dados e multiplique o campo BILLSEC por uma taxa da operadora.