CDR records into MySQL

[Webslave]

I've seen a few people trying to get their CDR records into a MySQL database. We were looking to do the same so I've knocked-up and open sourced a simple node.js application to do just that. It's my first node.js application so if you know how to do it better than I do then I'd love your feedback.

Find the repo here:
https://github.com/jmwebslave/CDR2MySQL

 

[Evolute IT]

I've seen a few people trying to get their CDR records into a MySQL database. We were looking to do the same so I've knocked-up and open sourced a simple node.js application to do just that. It's my first node.js application so if you know how to do it better than I do then I'd love your feedback.

Find the repo here:
https://github.com/jmwebslave/CDR2MySQL

Not bad, but @VoIPTools has a Exporter module that does exactly that and more.

You definitely need a check to avoid duplicates, and also, keep in mind that the CDR file will change during the call itself, so you may want to wait for the "save" event and not "add".

Finally, MySQL can obviously be set for unique columns, but nothing in the CDR is truely unique, not even the "call-id".

 

[Webslave]

Yeah, I looked at the VoIPTools one, but it appeared to be a paid thing, and that didn't really suit what I want to do with it.

As far as avoiding dupes goes my thinking for v1 is that between having a single CDR file per call which is deleted upon the insert being completed, and also setting the callid field as unique in MySQL is going to get you most of the way until I find out it doesn't and decide to deal with it (it's listed in the todo).

Oddly enough I only seem to get CDRs generated when a call completes which I assumed was the intention (which is why I just process the file when it's generated). Do you not?

 

[Evolute IT]

Behavior of the CDR may have changed. We don't really use it much, and when we do we use other tools.

Deleting the file after DB insertion might not be the greatest thing, and as I explained, Call ID isn't unique. It will reset every time the server or services restart. You may need a combination of datetime + call-id to determine the uniqueness.

Yes, VoIPTools is a paid thing but with heavy development behind it.

By the way, not bad for your first Node script! Little advice: be careful with Chokidar as it uses system watchers and there's a limit to those, and also it can use a lot of RAM with time. (We have a similar script for recordings.)

 

[Webslave]

Agreed, the VoIPTools thing looked really good, and inspired me to go digging through Postgres for the queue data. Since writing this app I've actually changed around to using the CDR to write an initial record, and then going looking for the corresponding queue record in postgres for other data the CDR doesn't have. Fairly sure the VoIPTools stuff already does that. If it's of interest to anyone I"m happy to open-source it too.

Thanks for the tip re: Chokidar. I'd been warned off using fs because I'd read somewhere it could use up quite a bit of CPU if you weren't careful. I'll have to read up a bit more on Chokidar.

 

[Evolute IT]

VoIPTools thing looked really good, and inspired me to go digging through Postgres for the queue data. Since writing this app I've actually changed around to using the CDR to write an initial record, and then going looking for the corresponding queue record in postgres for other data the CDR doesn't have. Fairly sure the VoIPTools stuff already does that.

DO NOT, under any circumstances, touch the Postgres database. This will render the install unsupported and nobody will use your script if it does that. This is a big risk.

If you are a programmer, try to get the Call Control API instead, which can give you queue data, but other than that just wait. An API is coming for 3CX.

Or use one of dozens of unsecure open-source "APIs" that are on Github lol

Chokidar. I'd been warned off using fs because I'd read somewhere it could use up quite a bit of CPU if you weren't careful. I'll have to read up a bit more on Chokidar.

Chokidar also uses fs but more efficiently. There are ways to avoid RAM eating and issues like that.

 

[Webslave]

DO NOT, under any circumstances, touch the Postgres database. This will render the install unsupported and nobody will use your script if it does that. This is a big risk.

If you are a programmer, try to get the Call Control API instead, which can give you queue data, but other than that just wait. An API is coming for 3CX.

Or use one of dozens of unsecure open-source "APIs" that are on Github lol

I looked into the call control API but the whole thing about 3CX deprecating APIs and either not documenting or hiding documentation for APIs really turned me off waiting them out. Giving read access to Postgres to be able to get the required data out of it unfortunately has to be the way for the time being until they come up with something reasonable and supportable, and doesn't pose any risk that I can ascertain.

 

[Evolute IT]

I looked into the call control API but the whole thing about 3CX deprecating APIs and either not documenting or hiding documentation for APIs really turned me off waiting them out. Giving read access to Postgres to be able to get the required data out of it unfortunately has to be the way for the time being until they come up with something reasonable and supportable, and doesn't pose any risk that I can ascertain.

The Call Control API is undocumented simply because it is risky and if you don't know what you're doing, it can break the system. Same goes for the database, even in read access.

Feel free to ignore my advice on that, but I will say that no one will use anything that touches the database.

@VoIPTools and @edossantos can comment on that.

All I know is a REST API is coming soon(TM).

 

[Webslave]

Yeah, I've been seeing a lot of scuttlebutt and promises about APIs coming soon(tm) and when that day comes I'll be very happy to use the front door. But if 3CX doesn't see fit to document their existing API then unfortunately they have to expect people will find their own way around it. Having an API that you refuse to publish proper documentation on is risky.

A read-only database account really does not pose a risk. You and I both know this. If nobody wants to use the code I'm completely cool with that. If, on the other hand, someone is trying to solve the same problem I am and appreciate a head-start I'm cool with that too. The ball is really in 3CX's court on that one.

 

[Evolute IT]

Having an API that you refuse to publish proper documentation on is risky.

There is documentation for the CC API. Just not public.

Agreed that a read-only user shouldn't cause any damages, but I will stick to avoiding it and using APIs like Call Control or VoIPTools (which uses the CC API in the backend.)