(Smart!) way to retrieve history of calls

(Lorne Gaetz) #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.

(Thomas) #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": [
    "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 …

(Greg) #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.

(Thomas) #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 …

(Dotcom) #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?

(Thomas) #26

For the record, I have now given completely up on the CRM module’s HTTP REST API. Getting useful data out of it, or for that matter support, has simply proven to be impossible for me. I am now going over to manually connect to MySQL, and retrieve the CDR data myself. If people here have some advice in these regards, I would appreciate it deeply … :slight_smile:

(Dave Burgess) #27

If you’d like to start a conversation about CDR and CEL data interaction, I’d suggest getting specific questions together and starting another thread. We could, of course, continue here, but making a new topic for some advanced discussion of CDR and CEL would make searches easier (just my opinion).

(Greg) #28

I would second that notion… @polterguy if you want to have a conversation, send me a message. We can exchange contact info and maybe get something going.

… or start the CDR/CEL thread. :smiley:

(Thomas) #29

Hehe, go for it! What I am particularly interested in, is how to extract “incoming calls”, “outgoing calls”, “originated calls” (which seems to create a “funny” CDR record, with two records in fact), filtering (logically) by extensions and external number (these two values seems to be “flipped” depending upon who makes the call), etc …

If you want to start a thread, feel free to @reply me (if possible), and if somebody could explain this very carefully to (a simple geek, with high knowledge about MySQL and coding though may I add), maybe I’d get some time to create a FOSS project one of these days, returning this thing as understandable data for others … :wink:

Thank you :slight_smile:


(Dotcom) #30

I’m looking for the same overview of calls as you are.

If I find the correct queries in the meanwhile, I’ll update the thread.

(Brandon Brown) #31

Has there been any work done to start a call reporting REST API? If not I would be more than happy to start on a project like this because I have been working with the CDR/CEL records quite a bit recently and feel like I have a pretty good grasp on them and could contribute a lot.

(Dave Burgess) #32

My suggestion (I’m nobody, so ignore if appropriate).

FIll out and sign a Code Donation request (unless you have one on file at Sangoma already).

Do your work. Make it happy. Submit a Feature Request and put your code on it.

They’ll either suck it into the system and give you little to no credit, or they’ll drop it on the donations website and give you credit.

(Lorne Gaetz) #33

All recent API work is in FreePBX 15 with hooks into the new api module. I’m not aware of anything that’s been done with CDR/CEL, but it may have been started.


(Justin) #34

Did you and @polterguy ever follow up on this? I am looking for additional information about the CDR/CEL. I believe I have a situation where a single call contains CEL records which do not have a uniqueid nor a linkedid in common with other records for the same call. My main problem is figuring out how to group all CEL rows by the call they belong to.

(Greg) #35

We didn’t really follow up… tbh I kinda forgot about it.
I think, however, if we were going to write a reporting system (which I have no issue with) we would first need to define exactly WHAT we wanted to report on.
There are so many ways to skin that cat, as it were.

I remember a project that I worked on that showed a lot of cool stuff, including reports of rings before answer, reports of trunk grouping usage, call details (of course), calls by hour, calls by station, all sorts of stuff.

The issue is that there isn’t a “one size fits all” answer. So the solution would be to get a discussion going and pick out the “most wanted” reports and start there.

On a side note, I just wrote a little service in C# that sits there and monitors the AMI stream, and puts incoming calls into a database. The reason is so that users can hit a button and with a single database call get the caller ID information and so forth populated into our system for customer contacts.

So in other words, an agent’s phone rings, they bring up our internal software, hit a button and they have the caller ID information, time stamp, etc. of the person they are talking to on the phone at that moment.