Current Call.. from MYSQL


I’m linking via ODBC to the CDR database, from MS Access, and trying to get the current call to populate to my Access Form. On this form i want the caller ID, unique ID, and also to for the user to add some notes. However, it seems that the only records in the CDR table are for ‘completed calls’ i.e. the record seems to only add to the table once the call is ‘hung up’…

Can I get the current call?
AND can is the current call available via querying/linking to MYSQL so that I can populate it to MS Access form?

Thanks for reading…


That is true, cdr’s are about completed calls and you won’t be able to use mysql queries into asteriskcdrdb for your needs, look into the AMI (Asterisk manager Interface) generally available on TCP port 5038 (if well configured), for dynamic state data.

also our old friend

might be good starting points for you.

From your linux box itself which has default access, try


and follow the above thingy

look for the Newstate events . . .

also . .


with an appropriate username/password from /etc/asterisk/manager*.conf heirarchy to see what is possibly available.

make your own authority in that same /etc/asterisk/manager*.conf heirarchy to suit.

Thanks for your reply and pointing me in the right direction…

After searching and reading a little i wonder if it is doable to build a solution to my request as follows:

Use an AMI Event to write, via PHP, to a new DB “CallStatus” and then query the DB from the MS Access form for the call detail. Which AMI event would write a row to a DB for an incoming call at any extension?

Additional questions: the uniqueID that is stored in the CDR --how is this ID generated? (Does it come from the AMI, or is it derived from another source… if it comes from the AMI then great i guess i can use that to pull the information for the call recording/duration, etc…, otherwise, i’ve more of an issue to tackle to finding the UniqueID if it is not passed in the AMI event)

In all… where do i start? i’m clueless to Linux/Asterisk/FreePBX, but I’m willing to learn.

What are/is my next step/s?

Has anyone done this before…? (I googled for “FreePBX AMI Current Call” but nothing came up on the first 8-10 search pages).


If you want a good “hands-on” example look at the vtiger asterisk integration code (php/mysql) for call popups.

Be prepared to get your hands dirty, no free ride here and you will DEFINITELY need to be “clued” :slight_smile:

1 Like

The UniqueID is based on the epoch time, it is there in the newstate event and will be there later in the CDR’s but it won’t be there until the call is done, neither in the csv nor the mysql record.

Event: Newstate
Privilege: call,all
Channel: SIP/
State: Up
CallerID: 3235551212
CallerIDName: Batman
Uniqueid: 1343129387.24193

and later the source of the cdr record

Event: Cdr
Privilege: call,all
Source: 3235551212
Destination: 3456
DestinationContext: ext-local
CallerID: “Batman” <3235551212>
Channel: SIP/
LastApplication: VoiceMail
LastData: [email protected]|su
StartTime: 2012-07-24 04:29:47
AnswerTime: 2012-07-24 04:29:48
EndTime: 2012-07-24 04:29:51
Duration: 4
BillableSeconds: 3
Disposition: ANSWERED
UniqueID: 1343129387.24193
UserField: heh, this is the tricky bit . . . .(learn to write to it)

You will have to be snappy to catch that usefully in Microslowwwww Access though

Thanks for replying.

This definitely is a lot of work for what ideally would be a ‘small project’. I’m reading up on what other call popup applications can write to a separate DB. it maybe that i just “get the call info” at the end of the call as a temporary solution. Although, when I have time to really get my teeth sunk into this I can play with it to get the current call in real time.

MS Access can be slow, although i plan to get the data upon opening a new “call taking form” and technically speaking if the call popup has written the data it should populate as the form opens…

The things that make me the most nervous is making modifications to the pbx and then risking messing it up and having no phone system. Likely, i get out and old box, set up, and play with it on there first.

Again, thanks for replying… very much appreciated…

I think perhaps you can use Caller ID Superfecta to do this, provided you are able to use MSSQL to write the info from the PBX to your Access database. This functionality does not exist out of the box with Superfecta, you will have to do some PHP and MSSQL coding, but there are code examples to guide you. Since superfecta works to gather and distribute the Caller ID name of a caller, it all executes before the call begins. There is already code for querying a MSSQL database for a name, it shouldn’t be too hard to alter the code to send the call details back to a MSSQL database.

If you are interested in going this route, start a new issue on the Superfecta Dev site:

If you want timely info for inbound calls, as I inferred by reading your original post. forget superfecta or mysql it just won’t work, there is rarely any useful state in that available data and that data is just not reliable enough except perhaps for CNAM, but why pay a penny for what you have already?. Nor will it provide you information downline about your current UniqueID, that would haveto be done later with a lot of work and CPU cycles

Using AMI will have absolutely no effect on your PBX, (unless you grow a pair and write to that wonderful userfield, if you do then it just gets better :slight_smile: )

If you are talking about outbound calls then I apologize.

Setting up MSSQL with Asterisk is both a pain and unnecessary, and slow use the freely available mysql connector for Acceszzzzzzzz.

Just to correct some misinformation, in addition to looking up a CNAM, Superfecta can easily read the Asterisk channel data, and a user could parse all of this information out and do whatever they want with it, including sending it to various destinations. I will yield to your expertise re: MSSQL (I have never used it), but there are other ways of harnessing the Superfecta “send-to” modules which may be useful to the OP.

pbx*CLI> core show channel SIP/103-00001c1b
 -- General --
           Name: SIP/103-00001c1b
           Type: SIP
       UniqueID: 1343134297.10798
       LinkedID: 1343134297.10798
      Caller ID: <redacted>
 Caller ID Name: <redacted>
    DNID Digits: <redacted>
       Language: en
          State: Up (6)
          Rings: 0
  NativeFormats: 0x4 (ulaw)
    WriteFormat: 0x4 (ulaw)
     ReadFormat: 0x4 (ulaw)
 WriteTranscode: No
  ReadTranscode: No
1st File Descriptor: 37
      Frames in: 213
     Frames out: 266
 Time to Hangup: 0
   Elapsed Time: 0h0m10s
  Direct Bridge: DAHDI/4-1
Indirect Bridge: DAHDI/4-1
 --   PBX   --
        Context: macro-dialout-trunk
      Extension: s
       Priority: 19
     Call Group: 0
   Pickup Group: 0
    Application: Dial
           Data: DAHDI/G0/<redacted>,300,tTwW
    Blocking in: ast_waitfor_nandfds

  CDR Variables:
level 1: dnid=<redacted>
level 1: clid="<redacted>" <103>
level 1: src=103
level 1: dst=<redacted>
level 1: dcontext=from-internal
level 1: channel=SIP/103-00001c1b
level 1: dstchannel=DAHDI/4-1
level 1: lastapp=Dial
level 1: lastdata=DAHDI/G0/<redacted>,300,tTwW
level 1: start=2012-07-24 09:51:37
level 1: answer=2012-07-24 09:51:41
level 1: duration=9
level 1: billsec=5
level 1: disposition=ANSWERED
level 1: amaflags=DOCUMENTATION
level 1: uniqueid=1343134297.10798
level 1: linkedid=1343134297.10798
level 1: sequence=15111


Perhaps not my misinformation, perhaps exactly the same information indeed, I am not aware of a more efficient way of getting channel information from outside Asterisk than the well documented AMI interface. Please elucidate as to how your methoid differs. .

thanks for you comments.

the freepbx is already set up with MySQL and i don’t plan on changing that.

I thought I was making headway, but I’m completely lost in the AMI…

question: with regards to the caller ID lookup source, instead of a select query, what about if i was to write an “INSERT” query to send the current caller ID to write a row in the MYSQL/Table?? The only issue I see by doing that is that it wont know what extension the call went to as the caller id event, from what I understand, is processed way before actually sending the call to a station/extension. Another issue is that I wont have the feature of doing the caller ID lookup to our customer DB (unless i can have more then one query that executes).

This is very interesting stuff… especially as i am just learning…

That’s the rub, you can build your own table as the data becomes apparent ( A la the vtiger method I pointed you to) inserting into asteriskcdrdb would be a mistake, it is far from synchronous

Vtiger makes it’s own table, use that opensource code as a template maybe.

A real call, not the one I posted that went to voicemail ( I did that on the fly)
DestinationChannel: (your channel)

will show that extension instead of the voicemail in my example, just try it.

Please don’t be lost in AMI , it is well documented. just google it.

Send me 5000 dollars and I will do it for you, the baby needs new shoes :slight_smile:

but really, just your feet wet .



thanks… those must be some shoes for $5k! LOL

I’m going around in circles with this little project. I googled for help on AMI like you previouly stated there is heaps of help. basically I wanteto understand what it does and how it is accessed etc… I found many sources that are all aimed at various developer experience levels (but not a simple guide for the complete and utter newby, like myself). The source that i found the most informative is:

I’m reading through the documentation and not finding anything that directly sticks out of how I can actually learn what I want to do. For Example, I do understand that I will need:

  1. use the AMI to provide an action on an event.
  2. the event will need to be something like… “CALL ANSWERED”… however, nothing on the list of events is quite a obvious as to this event… or at least it is not names that.
  3. once I figure out the event, i will then need to know what language i am going to need to write a line of code (or 2, or pages of it); just write the current call, extension that answered it and date time, caller id, etc to a my own table in the MySQL DB. (This table will provide me with the data that need for the current call and also future statistics/call tracking for a client.

I at least know what it is that I wish to accomplish and have a plan, but I don’t have the tools/knowledge to get it to work. For example:
How do I get into the AMI?
What language does it operate on, etc…

In addition, I was looking around at vTiger and it also seems confusing. it seems that it is available as a subscription… and there also seems to be some sources that say it is free/shareware… regardless, I’m wondering if the scope of vTiger does what i’m looking for and i can just simply query my DB. I tried installing the free version, but I am not even clear on how to configure/open/view the vTiger application, let alone get into its workings… Meaning where is the open source page/template that i should sink my teeth into?

Among our post you had referred to a well written AMI is the likely approach that I should take–and I appreciate that if something that is worth doing is worth doing correctly. Also, with that said the amount of time to learn a new language/system is starting to hinder on getting this completed. So basically, the post is a cry for some additional direction. (I don’t want the solution handed to me on a silver place, as there is no satisfaction in not doing it myself).

please help; forgive my long post, i’m trying to avoid learning something the wrong way just to spend hours, days, weeks, or event months to under a faulty learning foundation.


Ruby Slippers are my dream, then I can click my heels three times :wink:

vTiger is free, and there is some good code examples in it. Perhaps

and from an earlier life

will be a good beginning, old but gold, IWFM, that’s why I posted there.

so the the top end examples from vTiger for asterik integration are


not by any means complete but is all just LAAMP (linux,apache(httpd),asterisk,mysql and php) just the same tools that FreePBX is built on.

You will as suggested get your slippers of any color dirty to do it though if you go that route, but if you do those slippers will be slippery as shitake off a chrome shovel.

Note to self , I hope I left not mushroom for Scott to chide me again here ! :wink:

Awesome! Thanks… LMAO!

I’m sure I’ll have questions… trying to install vtiger… not getting beyond the install screen saying that some things are recommended but are not installed.

copied and pasted…

PHP version >= 5.2 5.3.10
IMAP Support No
Zlib Support Yes
GD graphics library Yes
Database Extension Yes

Recommended PHP Settings:
Directive Recommended PHP.ini value
display_errors On Off
max_execution_time 600 30
allow_call_time_pass_reference On Off
log_errors Off On
short_open_tag On Off

Although vTiger works, I find it’s datastructures to be as scary as a Bengal Tiger sneaking up on you in the dark :wink:

I amagine most of those are not too important but you will need a php version to suit, that was the main reason I moved from the same old Centos based FreePBX distro’s to Debian a year ago, they have hopefully caught up by now though.

A long time ago, I did exactly as you did, and although disappointed with it’s data obfuscation, those modules I mentioned saved me a lot of time reading all the php documentation past the basics, I am not, and don’t want to be, a particulary good php script writer, but isolating those few files was worth the time I did spend and worked a lot quicker than my old fashioned perl/bash methods. I managed to trade up my old man’s slippers to at least a pair of sneakers that occasionally flash when I walk on them, and haven’t used vTiger since then . . . .

Hmmm, oh well back to my pipe and upgraded slippers.

AMI is just TCP socket based. It also is in ASCII so very easy to parse.

Rather than try to understand it in the abstract just login to the manager interface and watch the output.

telnet localhost 5038
Asterisk Call Manager/1.0
Action: Login
UserName: test
Secret: 1234

Response: Success
Message: Authentication accepted

Do this from a command shell on the system, substitute a valid username and secret from manager.conf or it’s constituent includes.

Almost every language lets you open a socket connection so you write in the environment you are most comfortable.

Ok… so after reading, installing, configuring, re installing and configuring I’m getting a little handle on this whole process…

I eventually figured that flow the vTiger incoming call and watched the events occur after typing the php AsteriskClient.php … it was intriguing–everything was showing up there!!

but, i got stuck… and below is the copied and pasted asteriskclient.php script that parts don’t make sense…

The SQL statements have ?'s in them that are representing data, or so i would think… the odd think, and this to me would be a basic coding tenant is that there should be a separate variable for each element of data to be stuffed in to the SQL strings… (not a question mark); this was evidenced by data only appearing in the vtiger_asteriskincomingevents table and not the incoming calls table. So I guess my question is:::

Did the developer forget to put the actual ‘data variables’ in, or is this just how it is done in PHP? If they did forget, then what are the variable for each of the following fields (as represented in the table–i noted an array, but at almost 3am it stopped seeming logical): from_number, from_name, to_number, callertype, flag, timer, refuid. The last 4 I believe are specific to the php script itself and likely not in the events… but i’m clueless so i could be wrong…

copied and pasted the whole document for clarity…


<?php /*+********************************************************************************** * The contents of this file are subject to the vtiger CRM Public License Version 1.0 * ("License"); You may not use this file except in compliance with the License * The Original Code is: vtiger CRM Open Source * The Initial Developer of the Original Code is vtiger. * Portions created by vtiger are Copyright (C) vtiger. * All Rights Reserved. ************************************************************************************/ @ini_set('error_reporting', E_WARNING & ~E_NOTICE); chdir('../../../'); # In case chdir is not permitted # ini_set('include_path','../../../'); require_once ('config.php'); require_once ('include/utils/utils.php'); require_once ('include/language/en_us.lang.php'); require_once ('modules/PBXManager/utils/AsteriskClass.php'); require_once ('modules/PBXManager/AsteriskUtils.php'); main__asteriskClient(); function main__asteriskClient() { global $app_strings, $current_user; global $adb, $log; $data = getAsteriskInfo($adb); $errno = $errstr = null; $sock = @fsockopen($data['server'], $data['port'], $errno, $errstr, 1); stream_set_blocking($sock, false); if($sock === false) { echo "Socket cannot be created due to errno [$errno] - $errstr"; $log->debug("Socket cannot be created due to errno [$errno] - $errstr"); exit(0); } echo "Connecting to asterisk server @ " . date("Y-m-d H:i:s") . "\n"; $log->debug("Connecting to asterisk server @ " . date("Y-m-d H:i:s")); echo "Connected successfully\n\n"; $asterisk = new Asterisk($sock, $data['server'], $data['port']); # authorize user first authorizeUser($data['username'], $data['password'], $asterisk); // Keep looping to poll the asterisk events while(true) { // Give some break to avoid server hanging sleep(1); try { $incoming = asterisk_handleEvents($asterisk, $adb, $data['version']); asterisk_IncomingEventCleanup($adb); } catch(Exception $ex) { echo "EXCEPTION: " . $ex->getMessage() . "\n"; } } fclose($sock); unset($sock); } /* * Delete the stale incoming events information recorded to avoid * overgrowth of the database. */ function asterisk_IncomingEventCleanup($adb) { $HOURRANGE = 60 * 60; $TIMELIMIT = $HOURRANGE * 12; // Delete events older than 'n' hours $adb->pquery("DELETE FROM vtiger_asteriskincomingevents WHERE timer < ? ", array(time() - $TIMELIMIT) ); } /** * Grab the events from server, parse it and process it. */ function asterisk_handleEvents($asterisk, $adb, $version="1.4") { $fnEntryTime = time(); //values of flag for asteriskincomingevents(-1 for stray calls, 0 for incoming calls, 1 for outgoing call) do { $mainresponse = $asterisk->getAsteriskResponse(); if(!empty($mainresponse)) { $state = ($version == "1.6")? "ChannelStateDesc" : "State"; if(asterisk_handleResponse1($mainresponse, $state, $adb)) { if(asterisk_handleResponse2($mainresponse, $adb, $asterisk, $state)) { if(asterisk_handleResponse3($mainresponse, $adb, $asterisk)){ // Proceed if previous event could not be handled. } } } } else { // No more response to consume break; } } while(true); return false; } function asterisk_handleResponse1($mainresponse, $state, $adb) { if( (($mainresponse['Event'] == 'Newstate' || $mainresponse['Event'] == 'Newchannel') && ($mainresponse[$state] == 'Ring') || ($mainresponse['Event'] == 'Newstate' && $mainresponse[$state] == 'Ringing')) ) { $uniqueid = $mainresponse['Uniqueid']; if(!empty($mainresponse['CallerID'])) { $callerNumber = $mainresponse['CallerID']; }elseif(!empty($mainresponse['CallerIDNum'])) { $callerNumber = $mainresponse['CallerIDNum']; } if(!empty($mainresponse['CallerIDName'])) { $callerName = $mainresponse['CallerIDName']; } $channel = $mainresponse['Channel']; $sql = "INSERT INTO vtiger_asteriskincomingevents (uid, channel, from_number, from_name, timer, flag) VALUES(?,?,?,?,?,?)"; $adb->pquery($sql, array($uniqueid, $channel, $callerNumber, $callerName, time(), -1)); return false; } return true; } function asterisk_handleResponse2($mainresponse, $adb, $asterisk, $state) { $appdata = $mainresponse['AppData']; $uniqueid = $channel = $callerType = $extension = null; $parseSuccess = false; if( $mainresponse['Event'] == 'Newexten' && (strstr($appdata, "__DIALED_NUMBER") || strstr($appdata, "EXTTOCALL")) ) { $uniqueid = $mainresponse['Uniqueid']; $channel = $mainresponse['Channel']; $splits = explode('/', $channel); $callerType = $splits[0]; $splits = explode('=', $appdata); $extension = $splits[1]; $parseSuccess = true; } else if($mainresponse['Event'] == 'OriginateResponse'){ //if the event is OriginateResponse then its an outgoing call and set the flag to 1, so that AsteriskClient does not pick up as incoming call $uniqueid = $mainresponse['Uniqueid']; $adb->pquery("UPDATE vtiger_asteriskincomingevents set flag = 1 WHERE uid = ?", array($uniqueid)); } if($parseSuccess) { if(checkExtension($extension, $adb)) { $sql = "UPDATE vtiger_asteriskincomingevents SET to_number=?, callertype=?, timer=?, flag=? WHERE uid=?"; $adb->pquery($sql, array($extension, $callerType, time(), 0, $uniqueid)); $callerinfo = $adb->pquery("SELECT from_number,from_name FROM vtiger_asteriskincomingevents WHERE uid = ?",array($uniqueid)); if($adb->num_rows($callerinfo) > 0){ $callerNumber = $adb->query_result($callerinfo, 0, "from_number"); $callerName = $adb->query_result($callerinfo, 0, "from_name"); if(empty($callerNumber) || $callerNumber == '0') { // We don't have the information who is calling, could happen in Asterisk 1.4 (when call is made to Queue) // Let us defer the popup show for next Event: Link $sql = "UPDATE vtiger_asteriskincomingevents SET flag=? WHERE uid=?"; $adb->pquery($sql, array(-1, $uniqueid)); } else { $query = "INSERT INTO vtiger_asteriskincomingcalls (refuid, from_number, from_name, to_number, callertype, flag, timer) VALUES(?,?,?,?,?,?,?)"; $adb->pquery($query,array($uniqueid, $callerNumber, $callerName, $extension, $callerType, 0, time())); } } } return false; } return true; } function asterisk_handleResponse3($mainresponse, $adb, $asterisk){ $uid = false; $receiver_callerinfo = false; // Asterisk 1.4 (Event: Link), Asterisk 1.6 (Event: Bride, Bridgestate: Link) if($mainresponse['Event'] == 'Link' || ($mainresponse['Event'] == 'Bridge' && $mainresponse['Bridgestate'] == 'Link')){ $uid = $mainresponse['Uniqueid1']; $uid2 = $mainresponse['Uniqueid2']; $callerNumber = $mainresponse['CallerID1']; $extensionCalled = $mainresponse['CallerID2']; // Ignore the case wheren CallerIDs are same! if($callerNumber == $extensionCalled) { // case handled but we ignored. return false; } $callerType = ''; $status = "received"; $sourceChannel = $mainresponse['Channel1']; // Check if Popup has already been shown to user? // Due to (asterisk 1.4 bug: // Popup display for Call made to queue is defered and will be handled below // So we need to pick up events with (flag = 0, asterisk 1.6) or (flag = -1, asterisk 1.4) // asterisk 1.4 - from_number is NULL, // TODO check the state of from_number in asterisk 1.6 $checkres = $adb->pquery("SELECT * FROM vtiger_asteriskincomingevents WHERE uid=? and (flag = 0 or flag = -1) and (from_number is NULL or from_number = 0)", array($uid)); if($adb->num_rows($checkres) > 0) { if(empty($checkresrow['from_name'])) $checkresrow['from_name'] = "Unknown"; $checkresrow = $adb->fetch_array($checkres); $sql = "UPDATE vtiger_asteriskincomingevents SET from_number=?, to_number=?, timer=?, flag=? WHERE uid=?"; $adb->pquery($sql, array($callerNumber, $extensionCalled, time(), 0, $uid)); // Check if the user has checked Incoming Calls in My Preferences if(checkExtension($extensionCalled, $adb)) { $query = "INSERT INTO vtiger_asteriskincomingcalls (refuid, from_number, from_name, to_number, callertype, flag, timer) VALUES(?,?,?,?,?,?,?)"; $adb->pquery($query,array($uid, $callerNumber, $checkresrow['from_name'], $extensionCalled, '', 0, time())); } } // END } else if($mainresponse['Event']== 'Newexten' && $mainresponse['AppData'] == "DIALSTATUS=CONGESTION" || $mainresponse['Event'] == 'Hangup'){ $status = "missed"; $uid = $mainresponse['Uniqueid']; $extensionCalled = false; } // TODO Need to detect the caller number using the Event Information $callerNumberInfo = $adb->pquery("SELECT from_number, callertype FROM vtiger_asteriskincomingevents WHERE uid=? AND from_number is not NULL LIMIT 1", array($uid)); if($callerNumberInfo && $adb->num_rows($callerNumberInfo)) { $callerNumber = $adb->query_result($callerNumberInfo, 0, 'from_number'); $receiver_callerinfo = getCallerInfo($callerNumber); } if($uid !== false) { // Create Record if not yet done and link to the event for further use $eventResult = $adb->pquery("SELECT * FROM vtiger_asteriskincomingevents WHERE uid = ? and pbxrecordid is NULL AND flag =0", array($uid)); if($adb->num_rows($eventResult)){ $eventResultRow = $adb->fetch_array($eventResult); $callerNumber = $eventResultRow['from_number']; if($extensionCalled === false) { $extensionCalled = $eventResultRow['to_number']; } // If we are not knowing the caller informatio (Asterisk 1.4, Event: Link not yet called) if($callerNumber != 'Unknown' && $callerNumber != '0') { $pbxrecordid = addToCallHistory($extensionCalled, $callerNumber, $extensionCalled , "incoming-$status", $adb, $receiver_callerinfo); $adb->pquery("UPDATE vtiger_asteriskincomingevents SET pbxrecordid = ? WHERE uid = ?", array($pbxrecordid, $uid)); if(!empty($receiver_callerinfo['id'])) { $adb->pquery("UPDATE vtiger_asteriskincomingevents SET relcrmid = ? WHERE uid = ?", array($receiver_callerinfo['id'], $uid)); } } return false; } } return true; } /** * Check if extension is configured to user in vtiger */ function checkExtension($ext, $adb){ $sql = "select 1 from vtiger_asteriskextensions where asterisk_extension=?"; $result = $adb->pquery($sql, array($ext)); if($adb->num_rows($result)>0){ return true; }else{ return false; } }

Unless you are using asterisk 1.4 or 1.6 then the function asterisk_handleEvents() needs to be rewritten to suit (more dirt on your shoes).

the sql statements are basically starting points for the mysql calls mde by adb->whatever, which will replace the queries (question marks) with other data.

a simpler reference point might be

Thanks… :slight_smile: