How to pull phone reports

FreePBX Applications / Modules
1

Greetings everyone! I just started working for a company that uses FreePBX for their phone system. This is a first for me as my last company we used Momentum Telecom and it seems like the portal was much more user friendly than what I’m having to support with at this new company and FreePBX. I do IT support and I have a request regarding phone reports. A manager would like to pull phone reports for his entire department, he would like to have them automated if possible to pull reports every day and email them to him.

We just bought a subscription/license to Pin Set Pro. I’ve looked around the portal and I’m having an extremely difficult time trying to figure out on my own how to pull phone reports. Any help would be greatly appreciated, thanks!

2

BLUF: I’m in a poor humor this morning, so I shouldn’t probably be answering questions today. Forgive me if I sound a little more curt than usual.

What phone reports are you looking for?

Looks expensive. In fact, it looks a little like a rebranded FreedomVoice.

What does that have to do with getting phone reports?

I’m confused. Your question appears to be “Why doesn’t the free, open source system have as many features as the expensive, proprietary system?” If that’s your question, then well, it’s free.

If the question is “What kind of data sources to I have and what kinds of reports can I buy?”, we can help you with the former. If you want the latter, you can contact [email protected] (they hang out here with us users from time to time) for information on the different commercial call reporting modules.

So, the datasource for all things call related in the asteriskcdrdb database on the phone server. By default, you can only access it from the phone server itself. You can, with a little research, add users to the database engine that allow non-localhost access to the database.

Once you have access to the database, there are two tables you can access: cdr and cel. CDR is Call Data Recordset and CEL is Call Event List. They serve similar purposes. CDR is all of the data about calls as they pass through the Back to Back User Agent (which is what Asterisk is). This tells you what calls came in, how they are responded to, and what internal calls were generated to connect to the incoming calls. You can get recording information, disposition of calls, and other info from the CDR. CEL is more about Call Events and (in my mind) is more like a report of what happens in the Asterisk Realtime Interface (ARI). This tells you things like number of rings, dispositions. and what happened “to” the call while it was in flight. There is some duplicate data, and you can create a complex and complete description of a call from use of both.

There are all kinds of “third party” data reports out there - I’d probably start with the FreePBX repository on GitHub. I don’t personally use any of them, but I hear that some of them are super simple, and others are ridiculous.

1 Like
3

Forgive me, I’m just not used to supporting this type of phone system. More than willing to learn however I have no documentation from the gent that spun this system up and managed it previously.

I have a manager that is currently running phone reports for his department. I believe what he is doing is running these via CDR reports but he is having run the report per user.

What I’m wondering is, if it’s possible for him or me to run one report with everyone in his department. This would obviously be a lot easier than running multiple reports and would save a lot of time. The manager simply wants to see how many outbound calls his team/department is placing each day.

Again, I am completely new to this system so bear with me if I’m not explaining this properly although I am trying my best.

4

The system’s CDR database report is essentially just a dump of the CDR database, so you could write a report-generator (or use one of the many that are already out there) in PHP or PERL and pull the report directly from the database. You can then format the data in a way that is conducive to your external process (dump it to a native Excel spreadsheet, for example) and pull all of the extensions associated with that office.

So, something like this:
SELECT * FROM cdr WHERE extension in (“extension”,“extension”,…) and calldate between “something yesterday and today” …

Use the ‘crontab’ command to set the report up (Tuesday through Saturday at 2100, for example) and have it mailed to your manager.

If the number is small, you could write one report per manager, if there are lots of reports, you could easily come up with a file with the extension numbers and email address and curry the report against the list.

You can also expose the CDR table to your local network and use an ODBC connector (set up per manager) and let them mangle their own data (one guy wants daily, one guy wants counts, one wants weeklies, etc.).

There are lots of ways to skin this cat.

1 Like
5

The departments are rather small, about nine employees per department. As of right now, I only have two departments that are requesting the reporting. I’ve never wrote my own code before as I’ve always had a hosted system with support so as of right now PHP and PERL are out of the question as the manager(s) would like these reports as soon as possible and it would take me some time to learn how to write a report generator.

I went into Admin > Contact Manager and then selected the user and in next to “Department” I filled in the value of (1) for the recruiting department.

I then went to Reports > CDR Reports and next to “Account Code” I entered the value of (1) and attempted to run a report but it yields no results.

6

Applications>Extensions>Advanced>Account Code

Try adding it there.

7

Just tested, same issue. Does not yield any results :frowning:

8

For the extension you added this to, can you look at the call you made in the phone record and see if something is populated in the account code field?

Are you also able to tell what version of FreePBX you are running?

9

What do you mean exactly by this? Sorry, I’m brand new to this system.

It looks like we are using FreePBX 14.0.13.4

10

https://wiki.freepbx.org/display/F2/CDR+User+Guide

Look for the call you made, without any filtering, so you can see what all of the fields are populating as.

11

Can you clarify what you mean by “look for the call you made?”

I haven’t made any calls…

pbx1
pbx12197×892 160 KB

12

The system really doesn’t track anything by users - it’s all about the extensions. Setting up account codes anywhere but the extensions isn’t going to give you the satisfaction you seek.

Yeah - that’s not going to fill in anything in the past, even if it had worked (which it wouldn’t have).

Here’s another option that doesn’t really require any programming.
First, get a ‘vi command’ guide - they’re all over the web. I’m sure you can find one.

Log into the server as ‘root’ (through the console or an SSH session).

In your home directory, type “vi marketing.sql”. This will start up a ‘vi’ session and create the file “marketing.sql”. In the subsequent window. hit ‘i’. This will put you in ‘insert’ mode.

Start typing:

USE asteriskcdrdb;
SELECT * FROM cdr WHERE src IN (‘111’,‘112’,‘113’,‘114’) AND calldate > CONCAT(CURDATE()," 00:00:00");

Hit ‘escape’ and shift ZZ.

This will pull the CDR records for the extensions in the list for today. Run it before midnight.

In your crontab command, use something like this:

50 23 * * * * mysql -u root asteriskcdrdb < marketing.sql | mail -u [email protected] -s “Daily phone logs”

Every night as 11:50 PM, the MySQL command will run with the script and send the output to whoever you send it to.

Now, if you get the accountcodes working, you can search for "accountcode = ‘XXX’ " instead of “src in (…)”

This probably won’t work ‘verbatim’, but it should get you close. Get the ‘mysql’ command line working first, then put that in your sql script, then put that into crontab.

2 Likes
13

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.