Reporting for Asterisk Call Center

Home » Asterisk Users » Reporting for Asterisk Call Center

10 thoughts on - Reporting for Asterisk Call Center

  • Dear;

    Are the below are used any more in asterisk 1.8 version, and for what we use them:

    ;dial trunk
    exten => _X.,1,Dial(SIP/trunk/${EXTEN})

    ;exten h must be in same context!
    exten => h,1,noop(“extended CDR”)
    exten => h,n,set(CDR(hangupcause)=${HANGUPCAUSE}) ; hangupcause
    exten => h,n,set(CDR(peerip)=${CHANNEL(peerip)}) ; like 10.0.0.5 if behind nat
    exten => h,n,set(CDR(recvip)=${CHANNEL(recvip)}) ; like 194.79.52.192 – public ip
    exten => h,n,set(CDR(from)=${CHANNEL(from)}) ; like sip:1111@sip.proxy.cz
    exten => h,n,set(CDR(uri)=${CHANNEL(uri)}) ; like sip:1111@10.0.0.5
    exten => h,n,set(CDR(useragent)=${CHANNEL(useragent)}) ; useragent like Aastra_57i
    exten => h,n,set(CDR(codec1)=${CHANNEL(audioreadformat)}) ; codec *
    exten => h,n,set(CDR(codec2)=${CHANNEL(audiowriteformat)}) ;
    exten => h,n,set(CDR(llp)=${CHANNEL(rtpqos,audio,local_lostpackets)}) ; lost packets by local end **
    exten => h,n,set(CDR(rlp)=${CHANNEL(rtpqos,audio,remote_lostpackets)}) ; lost packets by remote end
    exten => h,n,set(CDR(ljitt)=${CHANNEL(rtpqos,audio,local_jitter)}) ; the same for jitter
    exten => h,n,set(CDR(rjitt)=${CHANNEL(rtpqos,audio,remote_jitter)})

    Regards
    Bilal

  • Dear Tark;

    The asterisk version I am running is 1.8 and I can select mysql from menuselect when I am compiling.

    But when I googled for cdr-mysql, I discovered that I have to login for mysql and create the database and run a script to create this and give the grants. All what I found in google is related to other asterisk versions, while mine is 1.8, so the problem is how to know the required script to create the database and give the right grants to be used for CDR that suite the version I am running? From where I can get this?

    One more thing, I found there is asterisk-stat and cdr-stats, can I use those with asterisk 1.8? And I will be able to use them to generate basic call center reports (reports related to queuing and number of logged in agents)?

    Appreciate the kindly help.
    Regards
    Bilal

  • The following script will generate an “asterisk” database with a table named
    “CDR” that will work with asterisk 1.8. Be sure to change ‘PASSWORD’ with
    whatever password you want to use.

    SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;
    CREATE DATABASE `asterisk` DEFAULT CHARACTER SET latin1 COLLATE
    latin1_swedish_ci;
    USE `asterisk`;

    CREATE TABLE IF NOT EXISTS `cdr` (
    `recid` mediumint(8) unsigned NOT NULL auto_increment COMMENT ‘Record ID’,
    `calldate` datetime NOT NULL default ‘0000-00-00 00:00:00’,
    `clid` varchar(80) NOT NULL default ”,
    `src` varchar(80) NOT NULL default ”,
    `dst` varchar(80) NOT NULL default ”,
    `dcontext` 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 ”,
    `duration` int(11) NOT NULL default ‘0’,
    `billsec` int(11) NOT NULL default ‘0’,
    `disposition` varchar(45) NOT NULL default ”,
    `amaflags` int(11) NOT NULL default ‘0’,
    `accountcode` varchar(20) NOT NULL default ”,
    `uniqueid` varchar(32) NOT NULL default ”,
    `userfield` varchar(255) NOT NULL default ”,
    PRIMARY KEY (`recid`),
    KEY `calldate` (`calldate`),
    KEY `dst` (`dst`),
    KEY `accountcode` (`accountcode`),
    KEY `src` (`src`),
    KEY `disposition` (`disposition`),
    KEY `uniqueid` (`uniqueid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    CREATE USER ‘asterisk’@’localhost’ IDENTIFIED BY ‘PASSWORD’;
    GRANT FILE ON * . * TO ‘asterisk’@’localhost’ IDENTIFIED BY ‘PASSWORD’
    WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
    MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT INSERT ON `asterisk`.`cdr` TO ‘asterisk’@’localhost’;

    If you’re going to be running the mysql database on the same server as the
    asterisk box, the following cdr_mysql.conf should also work for 1.8:

    [global]
    hostname=localhost
    dbname=asterisk
    table=cdr
    password=PASSWORD
    user=asterisk
    port=3306
    sock=/var/lib/mysql/mysql.sock
    userfield=1
    loguniqueid=yes

  • Dear wcselby;

    Thanks a lot for your reply.

    For below script, I have some questions if you can help me:

    1) I am looking to have reports for the call center, so I need to determine how many calls in the queue, and how many agents logged and when the agent logged in and when logged out … etc. But I do not see this in the fields created for the CDR, only what I see those fields related to the call it self. So how I can get this?

    2) What is required from me to be done to have CDR for the call center? From where to be enabled? In that case, from where I can determine the fields that I have to add it for the cdr table in the database to be able to have cdr for the call center events?

    3) Generally speaking, any events will be added in the cdr (those related for calls or call center or any other thing), how can I know the fields that I have to add it to be able to store in the database? Is it the only solution is to look for the Master file to see what the cdr is logging, and based on it I have to create the database?

    Appreciate your kindly help.
    Regards
    Bilal

  • Thanks a lot.

    Really I am trying to know how to do AGI to get the information from asterisk (for example, how to talk with asterisk to know the concurrent calls, or the number of agents in the queue, … etc)? Where I can find this?

    Regards
    Bilal

  • Un-top-posting…

    An AGI is a program that is executed in the context of a channel —
    meaning, during a call.

    Are you planning on calling into your Asterisk server to trigger the
    collection of statistics?

    I guess you could set up a ‘cron job’ to periodically write a call file
    to create a channel and execute an AGI, but I suspect you should be
    looking at AMI.

  • Dear;

    It look like AGI is not suiting this, maybe I need AMI?

    Because no need to do a call to collect the data, I need to collect data without doing a call … so what do u suggest?

    What I need, is to know how many concurrent calls in the queue, and how many waiting and how many agents in the queue and so on. So what I have to use? AMI or AGI?

    Regards
    Bilal

  • Maybe next time you can search the Asterisk wiki before asking because
    you would have found:

    https://wiki.asterisk.org/wiki/display/AST/Asterisk+Manager+Interface+%28AMI%29

    From these and your questions on other mailing lists it seems VoIP
    (Asterisk) is new to you. Perhaps you could start with reading the
    Asterisk 1.8 book, go through the Asterisk wiki and Google a lot before
    asking about every little detail.

    In a posting on (iirc) the yate mailing list you mentioned that you use
    Fedora 10. If you still use F10, you do know that Fedora 10 is End of
    Line for a long time and has not received security updates in a long
    time and by now has more security holes than Swiss cheese?

    Regards,
    Patrick