What's the best way of extracting call data which has been written to flat files?

Asterisk Asterisk Support
1

I’m dealing with a blind charity phone information system which writes
its logs to two flat csv files
(Although the log COULD actually now be written to dynamoDB or
sqlite3, too if needed).

The first file contains basic call information, one line per call and
a unique call ID (distinct from “caller ID”!)
The second file relates the call ID to more information about what
happened during that call.

I’d really just like to extract info like: What was the most popular
caller region? Time of day? Item in the menu?
What did people listen to most or skip or hang up during? What
percentage were mobiles?

The information is all there, but I have NO idea how to extract it,
nor the skills to do so myself without a steer in the right direction.

So my question is really: What now? I’ve written this, and some
example log entries, to

If I don’t get any luck here, I’ll try asterisk-biz later.
Alternatively, where to find help?
PeoplePerHour? Or some other web-worker site? What should I look to
pay? What is this kind of data-wrangling even called?

Basically, I’m hoping that either someone will know of something that
exists already which is very cost-effective and I could learn in a day
or two.
Or, know of someone who could write something for me, for a reasonable amount.

I have an idea that the answer lies in something like Dynamo/MongoDB,
but need some pointers.

(But what’s not going to be possible is someone offering to do it for
“just” 4 days at $1200 per day plus tax!)

Thanks.

2

Well if this was MYSQL, MSSQL or Postgresql with a few queries this could be solved

3

Yeah, my first step would be to define a table for each of the flatfiles and get them imported.

4

Seems like currently, the file is some sort of CSV or a log file. Correctly said by @ambiorixg12, had it been in a db, it would have been easier to directly query and check because db will have a fixed set of columns and fixed kind of data set. Also, it will be better to query in the future. But yes, the first step will be log file parsing.

Go for PHP, C or Python programming languages which is easy to use (with lots of functions to parse a file and easy to learn). Then either you directly parse the file and get the analysis or store the parsed data in a db for future analysis.

If it contains lot of unwanted text, then you may have to clear them first.

5

Thanks everyone - I think MYSQL is the only way forward for this? It’s a shame because I wanted to keep my system nice and clean and light!
I guess maybe it could be done with sqlite3, but I think I would spend more time learning.
At least with mysql I know there are simple ways of importing the existing csv files, so I’ll start with that, and then find someone on PPH who is good with SQL!
Thanks

1 Like
6

If you re talking about export the CSV CDR file you dont have to, just configure the MYSQL ODBC connector and create the DB and the call detail records will be stored on the DB automatically

1 Like