(Smart!) way to retrieve history of calls

FreePBX Development
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.

1 Like
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

8

Thank you all of you.

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.

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)

11

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

Thx guys though :slight_smile:

3 Likes
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.

3 Likes
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.

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.

1 Like
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

2 Likes
16

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

I know of one site that still uses it.

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:

1 Like
18

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

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 …

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.

1 Like
21

There is really no reason to be intimidated by the thought of sharing your code. We all have to start somewhere, and getting feedback from sharing code snippets is the fastest way to get up to speed. There are people who publish distros who can barely read code, let alone write it. If they can do it, so can anyone else who genuinely want to learn the process.

6 Likes
22

If somebody here are able to develop something that actually works, and allows me to consume it as an HTTP REST API, doing queries into the CDR, getting intelligent feedback, I’ll encourage my manager to revoke the purchase for the CRM REST module, and I’ll encourage him to commercially obtain a license for whatever you guys build in these regards. We paid $275 for the CRM HTTP REST API thing, and it’s 110% broken!

We don’t need “callbacks”, only the ability to make queries into the CDR database, and we can set it as a requirement that it’s stored in MySQL. However, we’ll need intelligent return
values, such as e.g.

{
  "calls": [
    "caller-id":66778899
    "extension":1234
    "started":2018-08-20 14:57:00
    "answered":2018-08-20 15:57:37
    "ended":2018-08-20 15:59:47
  ]
}

You get the idea. Parsing the CDR myself, seems like a nightmare, and being able to consume something that does it for me, and return more structured data, would be worth a lot for me and my employer …

23

But see, therein lies the gotcha.

That is the simplest scenario… it rang for 37 seconds, the user talked for 2 minutes and 10 seconds, and that was it.

But what if the call was transferred? What if it came into a queue? Was it put on hold? Which line did it come in on?

There is so much more information in the CDR than is being represented there.

When I was writing the CDR reporting system, there were a lot of things that the managers wanted (and were required) to know.

Average ring times… graphed ring times (how many calls answered <1 second, <5 seconds, <10 seconds, etc.)

Total time on hold. Maximum lines in use (and for how long). Average weighted call time (and it needs to be weighted, because for example you don’t want to include the conference room phone, which may be on a call for hours, because it skews the other users)

Queue wait time, shortest, longest, average. Agent logons and logoffs.

Who was on the phone the most? Who was on the phone the least? Who had the most calls? Who had the least calls? (stuff for call centers or sales centers, etc.)

I guess what I’m trying to say is there are a thousand ways to massage and report on the CDR data, and everyone has different needs, desires and even regulations. A “full blown” reporting system needs to think about ALL those scenarios.

If we were to start the “be all and end all” of reporting systems, we would really need to get a sampling of users together and say “What reports do you NEED, and what reports would you LIKE”, then work backwards from there.

2 Likes
24

Hmm, I see your point, but what we need isn’t as much a “reporting system”, but a general way to query, getting structured data back. E.g. a rest API, setting filtering parameters and such ourselves.

From that raw but structured data, we can then (more easily) create our own reports, in everything from JavaScript to WinForms apps to iPhone apps, by using JSON as the transferring mechanism for the data …

Of course, now if I simply look at the CDR through FreePBX, it gives me headache simply thinking about that I’ll need to figure out an intelligent way to actually parse this, and display it as understandable data in my app. With YALOA (Yet Another Layer Of Abstraction), structuring the data slightly better, exposing it securely through JSON, my job creating my “reports” would be 75% done!

If I knew more about the CDR structure, and had some spare time, I’d create it myself. There simply has to be a market for something like this out there …

2 Likes
25

It seems the “Generic API” part of the paid CRM module is still in BETA (as of July 2017).

When will the stable version be released?