Hi guys (gals?) and thank you for previous great help here
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 ā¦
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.
Maybe I should start writing the ābe all/end allā CDR reporting system on my own.
Please ā¦!!
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 ā¦!!
Push it up here (GitHub link) when youāre done please
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.
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.
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)
Notwithstandingā¦ Now that youāve opened āPandoraās CDRā, Iād like to continue down the rabbit hole youāve pointed out.
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.
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.
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.
@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.
Perl: Very handy, write only language. I hate looking at stuff I did 10 minutes ago and wondering what is the meaning of life.
Java: Grossā¦ just donāt
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)
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ā¦
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 ā¦
