(Smart!) way to retrieve history of calls


(Thomas) #1

Hi guys (gals?) and thank you for previous great help here :slight_smile:

So, today my task is to be able to query the CDR database for its historical calls, and I figure there must exist some pre-configured action i Asterisk to do this, through its AMI parts, but then again, maybe not …?

If not, what would be the smart way to achieve what I want to do, which is to retrieve historical calls, given an extension, and/or date/time span, etc …?
Basically, in our own (in-house) developed system, which internally is using Asterisk (and more specifically FreePBX), we’ll need to display grids of calls, according to filter criteria, and allow our users to download and view meta data about historical calls (my industry is regulated such that we’re legally obligated to record BTW (finance))

My guess would be to configure Asterisk to use MySQL for its CDR, and then simply connect directly to that database myself. Which again if true, brings me to the question of the structure of this database. How do I recognize what is what in it …?
And where do I start if I want to configure Asterisk to use MySQL for its CDR …?
Thank you in advance for the awesome answers I know will pop in here … :slight_smile:

Have a nice day,

Thomas


(Lorne Gaetz) #2

The paid commercial CRM module will probably do it using the API:
https://wiki.freepbx.org/display/FPG/REST+API


(Greg) #3

I spent over a year developing a call reporting system for Asterisk (911 call centers).

At first, we attempted to just replicate the CDR information from mysql over to MS SQL server, but performance was horrible. So we ended up connecting to mysql directly from a windows program for call reports.

What I found is with the “raw” CDR data, it’s… well… intense to say the least. Since we were using CAMA trunks, I had to report on things like “Call arrival to first ring”, “First ring to call picked up”, “Average (weighted) time on call”, and so forth.

One of the more interesting reports was “Number of lines in use at any time span”… so, say you had 4 incoming CAMA 911 trunks, how many seconds (for a time frame) were all 4 trunks in use (indicating that if someone called in over that time, they would get a busy signal)

Since I was working for a company at the time doing this, and it was a commercial product (well, an add-on to their CAD (Computer Aided Dispatch) system), I don’t have the code. But I am familiar with the CDR database.

Anyway, [tl;dr] it’s a daunting task to sift through all those records, and follow a call all the way from start to finish, especially when you consider call transfers, time on hold, parked, all those little caveats and gotchas that need to be considered in your calculations.

Maybe I should start writing the “be all/end all” CDR reporting system on my own.


(Thomas) #4

Interesting. How do I get my hands on that one …?
It certainly would make a lot of things much simpler for me …


(Thomas) #5

Maybe I should start writing the “be all/end all” CDR reporting system on my own.

Please …!! :smiley:

Doing the “raw thing” quite frankly seems like rocket science to be honest. I’ve worked with this thing now for some 3-4 weeks, and I am barely starting to get a grasp on it to be honest with you …!! :stuck_out_tongue:

Push it up here (GitHub link) when you’re done please :slight_smile:


(Greg) #6

All that being said, my install uses a mysql database already for CDR data (I don’t know if this is the “norm” or not, since I’m using Xorcom on top of Asterisk)

BUT… if you do:

asterisk -rx 'cdr show status'

And you see “Adaptive ODBC” then yours should be in mysql as well.

Fire up mysql

mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
| asteriskcdrdb      |
| mysql              |
| qstatslite         |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Ahhh… there’s my CDR data…

mysql> use asteriskcdrdb;
mysql> show tables;
+-------------------------+
| Tables_in_asteriskcdrdb |
+-------------------------+
| cdr                     |
| cel                     |
+-------------------------+
2 rows in set (0.00 sec)

Then, doing a simple “describe cdr;” shows me the schema

mysql> describe cdr;
+---------------+--------------+------+-----+---------------------+-------+
| Field         | Type         | Null | Key | Default             | Extra |
+---------------+--------------+------+-----+---------------------+-------+
| calldate      | datetime     | NO   | MUL | 0000-00-00 00:00:00 |       |
| clid          | varchar(80)  | NO   |     |                     |       |
| src           | varchar(80)  | NO   |     |                     |       |
| dst           | varchar(80)  | NO   | MUL |                     |       |
| dcontext      | varchar(80)  | NO   |     |                     |       |
| channel       | varchar(80)  | NO   |     |                     |       |
| dstchannel    | varchar(80)  | NO   |     |                     |       |
| lastapp       | varchar(80)  | NO   |     |                     |       |
| lastdata      | varchar(80)  | NO   |     |                     |       |
| duration      | int(11)      | NO   |     | 0                   |       |
| billsec       | int(11)      | NO   |     | 0                   |       |
| disposition   | varchar(45)  | NO   |     |                     |       |
| amaflags      | int(11)      | NO   |     | 0                   |       |
| accountcode   | varchar(20)  | NO   | MUL |                     |       |
| uniqueid      | varchar(32)  | NO   |     |                     |       |
| userfield     | varchar(255) | NO   |     |                     |       |
| did           | varchar(50)  | NO   |     |                     |       |
| recordingfile | varchar(255) | NO   |     |                     |       |
| cnum          | varchar(40)  | NO   |     |                     |       |
| cnam          | varchar(40)  | NO   |     |                     |       |
| outbound_cnum | varchar(40)  | NO   |     |                     |       |
| outbound_cnam | varchar(40)  | NO   |     |                     |       |
| dst_cnam      | varchar(40)  | NO   |     |                     |       |
+---------------+--------------+------+-----+---------------------+-------+
23 rows in set (0.00 sec)

So, while digging through a little, it looks as if “CDR” is the “Header”, and “CEL” is the “Body” (or the events). So, once you find the main information and the uniqueid from the CDR, you select from the CEL where uniqueid=(uniqueid from the CDR) to get all of the details.

Hope this helps… the details don’t look nearly as hairy as on the Zetron system (that goes down to 1000ths of a second, and records EVERYTHING including bridging, etc, etc, etc that I had to sift through). So, writing a CDR reporting system wouldn’t be as daunting a task.


(Greg) #7

If you find that your CDR records are NOT in a database, here is a good article on how to get them there.

http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/database_storing-cdr.html


(Thomas) #8

Thank you all of you.


(Dave Burgess) #9

The only thing ‘daunting’ about the CDR database (and the CEL database, for that matter) is that they are 1st normal form databases. Different call details and different classes of events are all stuffed into the database without a thread indicator or any other key that can make advanced analysis simpler.

The historical reason for that is pretty simple - it’s an Open Source project and the guy(s) that wrote the original CDR/CEL code weren’t looking for advanced call history information.

So, to my way of thinking, there are two approaches. The first is to write a higher fidelity call tracking system module for Asterisk. It would have to be a commercial product, since the people that are going to really want to use it (911 call centers, for example) are going to want indemnification, and the company will need to be there as a shield for the individual developers.

The second is to start up a GitHub project with a few people that are interested in improving the performance of the current database by either filtering and “renorming” the data, or just use the data as it exists and figure out what can be assumed by the current data. From the discussion above, we know that a lot of information can be gleaned from the data that isn’t actually there, so this approach wouldn’t be a bad one. The tricky part is getting a community of people together that can write and maintain the code without compensation for a number of years.

In the short term, finding out how the CDR and CEL database “connect” is a little challenging, but isn’t something that you’ll forget once you figure it out. Skulling out the simple stuff your local database is actually pretty simple. The one thing that you need to know is that the CDR database is essentially “post call” - all of the data is written at the end of the individual call leg, and that you aren’t look at “a call”. When a call comes into the system it is answered by the system, then split out to whatever components need to be includes (IVR, queue, ring-group, individual phones, etc.). A single phone call is almost always going to result in two records - the “inbound” leg and the “outbound” leg.

One of the most common complaints we hear about the CDR is that it records “too much” data (every extension that rings gets recorded) making it hard to “read”. Your requirement (data heuristics) is why the perspective changed back after Asterisk 1.8.

It shouldn’t take much more “figuring” for you to get to where the database is as simple as it’s going to get.


(Greg) #10

I don’t know if I’d say it’s “too much data”.

I might want a report on who never answers their phone… (or, say, who in a call center spends the most or least time on the phone).

The data there is certainly usable. The reporting system I wrote a couple of years ago was a commercial add-in for the CAD software. All kinds of pretty charts and so forth, all generated from the CDR data (which like I said was a LOT more intense than I’m looking at for the CDR/CEL tables).

So, it really boils down to a few personal questions for me:
o Do I have the TIME to do it?
o Do I really want everyone critiquing my code (Over the years I’ve written a few open source things, but they were mainly little utilities… writing something for GitHub would be intimidating)
o What do I want to write it IN? (My first thought is Visual Studio… C#, since that’s what I’m used to. I’m certainly capable of writing it in perl or even php with a little brushing up, but for web controls and stuff C#.net is just SO much faster for development)


(Thomas) #11

I actually just bought the CRM plugin, realizing its API is exactly what I need …

Thx guys though :slight_smile:


No CDR in cdr MySQL Table
(Dave Burgess) #12

Notwithstanding… Now that you’ve opened “Pandora’s CDR”, I’d like to continue down the rabbit hole you’ve pointed out. :wink:

I agree, but there are lots of people out there that don’t care that 8 people in a ring group didn’t answer the call… You can’t please everyone, and why ignore the bounty when you can whine about having too much of what you need. :slight_smile:

Three good questions. The first isn’t limited to today, though. If you start down this path, it’s going to be like birthing a child. It’s going to be your (and whatever “code wives” join the project) responsibility to make is work for the foreseeable future. With the recent changes to CDR and CEL, the current phone data management structure will be in place for at least another 10 years.

I wouldn’t worry about people critiquing your code, for two reasons: the first is that the number of people that are going to see your code is 1% of the people that want to use it, and the second is that code review is a “best practice” for software development and has been for as long as I’ve been doing this. You only get better when you play with people that challenge you.

Finally, there’s the question of development language. While your C# skills are probably excellent, it’s important to remember that this system is largely based on a web-hosted model. The Rule of Least Astonishment will be violated if this becomes a piece that is licensed “per workstation”, and then, per “Windows Workstation”. The rest of Asterisk and FreePBX is based around a distributable model that runs “in the cloud” - creating something in VS that can make that happen is going to be really challenging. If you want it to be “device” limited, I’d think you’d steer toward Java (to get the multi-platform/multi-store crowd covered) or to stick with the PHP/PERL model that the rest of the system uses.

One final note - while C# does well for applications that run on your PC, it is a hog. The resource load at execution is terrible, and development isn’t the only time that’s important. You will be maintaining this over years, and what happens when Microsoft does another “flag day” upgrade on VS (like they did a couple of years ago with VB?).

In my development, the differentiator for PHP and PERL is PHP is for web pages, and PERL is for server utilities. I always use PHP if I’m writing something that interacts with the user through a web page. PERL is my goto language for things that interact with files and file systems. To say it another way, PHP is my “JavaScript” and PERL is my “Java”. So similar, but different enough that they deserve to be differentiated.


(Greg) #13

I agree… one of my thoughts was “Of course, VS is a lot of ‘bloatware’”. I wonder if there’s a LAMP development studio that would give me the same (or similar) functionality for designing HTML5/etc. stuff. It would be nice to just have it as an installable module or whatever that can run right on the Asterisk box.

I’m just learning PHP, but I’ve been doing C#, VB and Perl for a long long time. The only down side is not having the .NET controls (such as the graphs and so forth) but there are probably libraries that I could include to get similar functionality.

All good points though. Thank you.


(Dave Burgess) #14

@jfinstrom has done a “template” that’s available on one of the GitHub collections that creates an installable “module” and includes all the various pieces and parts that FreePBX needs to be installed. Using that as a framework, developing the PHP code should be a relative snap for you (the differences between PHP and PERL at the development level are that some of the functions and equality operators are a little different) so once you get to the point that you are ready to start pulling the info from the system, you should be ready to integrate it with the modules that FreePBX already uses for chart and graphs.


(TheJames) #15

Okay first let’s get a little dogmatic.

  1. Perl: Very handy, write only language. I hate looking at stuff I did 10 minutes ago and wondering what is the meaning of life.
  2. Java: Gross… just don’t
  3. PHP: Bad reputation based on lazy developers. Ultra portable. FreePBX is 98% PHP so you can tie in direct and call all native classes/methods/functions direct. This keeps things from breaking typically if there is a schema change or something. Libaries for dang near everything (https://packagist.org)
  4. The new hotness is nodejs. Ultra portable. You can make cross platform including mobile apps with things line React. Libraries for dang near everything (https://www.npmjs.com/)

One note, CEL actually gives you all the events of a call…

mysql> select eventtype, eventtime, channame from cel where linkedid = '1517833831.823';
+--------------+---------------------+----------------------------------+
| eventtype    | eventtime           | channame                         |
+--------------+---------------------+----------------------------------+
| CHAN_START   | 2017-08-02 16:26:02 | SIP/fpbx-1-NNNNN-00000000 |
| CHAN_START   | 2017-08-02 16:26:03 | PJSIP/1000-00000000              |
| HANGUP       | 2017-08-02 16:26:15 | PJSIP/1000-00000000              |
| CHAN_END     | 2017-08-02 16:26:15 | PJSIP/1000-00000000              |
| HANGUP       | 2017-08-02 16:26:15 | SIP/fpbx-1-NNNNN-00000000 |
| CHAN_END     | 2017-08-02 16:26:15 | SIP/fpbx-1-NNNNN-00000000 |
| LINKEDID_END | 2017-08-02 16:26:15 | SIP/fpbx-1-NNNNN-00000000 |
+--------------+---------------------+----------------------------------+
7 rows in set (0.00 sec)
mysql> select * from cel limit 1 \G
*************************** 1. row ***************************
         id: 1
  eventtype: CHAN_START
  eventtime: 2017-08-02 16:26:02
   cid_name: JAMES
    cid_num: 16025551212
    cid_ani:
  cid_rdnis:
   cid_dnid:
      exten: 4805551212
    context: from-pstn
   channame: SIP/fpbx-1-NNNNN-00000000
    appname:
    appdata:
   amaflags: 3
accountcode:
   uniqueid: 1517833831.823
   linkedid: 1517833831.823
       peer:
userdeftype:
      extra:
1 row in set (0.00 sec)

https://wiki.asterisk.org/wiki/display/AST/Asterisk+12+CEL+Specification


(Greg) #16

heh… I remember when the “New Hotness” was Cold Fusion.

I know of one site that still uses it.


(Dave Burgess) #17

My wife hired a guy to build her a website that uses Cold Fusion for her dog rescue, so unless you’re talking about him, you now know two. :slight_smile:


(Greg) #18

Nope… a site that I have been on for… umm… 13 or 14 years now? www.tek-tips.com


(Thomas) #19

Actually, VS code isn`t too bad …

You could also want to check out one I created once called Hyper IDE (integrated into Phosphorus Five) if all you need is HTML, JS and CSS things. It runs on Apache, and you consume it through your browser …


(TheJames) #20

Our devs use vi, vscode and atom. I use to use sublime.
I went Sublime->atom->vscode

Now that atom and vscode are both owned by microsoft we may see a lot more related functionality.