icon-bar-chart-o How to Create Free CDR Reports for Asterisk
For a long time, we suffered with buggy third-party visualizers and statistic engines specifically made for Asterisk’s call detail records. Then one day we decided to build our own.
This post is intended to help anyone who has been in the same situation.
We used Asterisk’s built-in cdr_mysql and had it write call records to a Cloud SQL instance on Google Cloud Platform (GCP). You don’t have to do this, but we found it to be the easiest option since we’ll be using Google Data Studio to generate our reports, tables, charts, and graphs.
If you prefer, you could also use something like Metabase. And Data Studio will work with PostgreSQL or an uploaded CSV file if you don’t like MySQL.
We won’t go into detail about connecting Asterisk’s CDR to an SQL database. There are plenty of tutorials out there for that. Just make sure you have a functioning database before you proceed.
We also won’t show you how to build every report. We’ll do one to get started and you can play with the tools to build others. It’s all pretty simple, though we did have to tell Data Studio how to interpret some data, as well as create variables that Asterisk doesn’t create on its own.
PART I — GETTING STARTED
First thing we need to do is go to Google Data Studio and start a new blank report.
To proceed, you’ll have to agree to Google’s terms and opt out of some marketing emails.
After that, you’ll see a mostly blank screen.
Click on “CREATE NEW DATA SOURCE” at the bottom right.
The next screen will have a grid of different data selectors.
If your CDR is hosted on Cloud SQL, choose “Google Cloud Storage”.
Otherwise, you’ll probably be choosing MySQL or PostgreSQL.
Google will make you authorize access to data in Google Drive, just in case you choose to use that.
For the next step we’re going to pretend you chose a generic MySQL database hosted somewhere.
Enter all the credentials of your database and click the “AUTHENTICATE” button.
Assuming it connects and you agree to any popups Google throws at you, you should come to a screen like the one below.
Congratulations!
But we need to take a little detour before we build a report.
PART II — DATA FIELDS & FUNCTIONS
Before we make a report, we need to go back and look at our data fields.
Go back to the navigation home and click on “Data Sources”.
You should only have the one data source, so click that.
That will bring you to a screen like the one below.
Don’t worry that we have functions you don’t (the fx icons). We’ll get to those in a second.
First, make sure you change the Type for “duration” and “calldate”.
As you’ll see above, they need to be changed to “Duration (sec.)” and “Date Hour (YYYYMMDDHH)” respectively.
Once that’s done, click on “Add A Field” in the top right.
We’re going to name this “Date” and the Formula will be: TODATE(calldate, ‘DEFAULT_DASH‘, ‘%Y%m%d‘)
Its Type will be “Date (YYYYMMDD)”.
Got all that? Good! Let’s go build a dang report!
PART III — BUILDING A REPORT
Finally!
Let’s go back to your Untitled Report, or start a new blank report. Whatever gets you to this screen again:
We’re going to make a simple chart that shows our cumulative call duration per day for all calls. We will also add a date control, but no other filters. You can add an accountcode filter later if you want to examine duration per day per customer.
Let’s add the date control first. Click on the “Date Range” icon in the toolbar.
I like to move the date control to the top right, so that’s where you’ll see mine. Feel free to move/resize it however you like.
With the new date control selected, click the “Auto Date Range” drop-down. It should be on the right side of the screen.
When the calendar appears, click the new “Auto Date Range” drop-down at the top right.
Again, feel free to select whatever default date range you want. For this tutorial, we’ll select “Last 7 days” and check the “Include today” checkbox. This means that when we open this unnamed report, it will only show data from the last week, including the current day, until we change the date range in our date control box.
I wrote this post on Dec. 14, 2018. So my date range shows today and the six days before it.
Time to add our chart. Click on “Add a chart” in the toolbar, then click the first “Time series” chart.
Again, I have resized and moved my chart. Feel free to position and size yours however you want.
As you’ll see above, my chart defaulted to the right dates, but it is showing some strange cost metric that I don’t care about right now. Let’s fix that.
Ignore my Data Source. I’m using Cloud SQL, so it’s probably different from yours. Make sure your Date Range Dimension and Time Dimension both have the date field we created earlier.
The Metric needs to be changed if it is not on “duration”. Click the Metric field and change it to “duration”.
Finally, let’s check the Type again for our Dimensions and for our Metric. The little icon to the left of the fields will let you see what those are.
Click the icon next to the field underneath the Date Range Dimension, the Time Dimension, and the Metric. It will pop up a dialogue like the following:
Make sure the Types are the following:
For the fields under “Date Range Dimension” and “Time Dimension”, both need to be “Date (YYYYMMDD)”.
The “duration” field under “Metric” needs to be “Duration (sec.)”.
If you’ve done all that, you should have something like this:
(Note: I’ve secretly filtered my chart so it’s not showing our entire call duration. Your daily call durations are hopefully much larger!)
Great! We have a working chart! Does it look a little boring, though? Again, we won’t go into this much here, but you can style everything to your liking by selecting an object and clicking the “STYLE” tab on the right side.
After some easy styling changes, my chart now looks like the one below. Don’t worry if you didn’t change your styling. It doesn’t matter what it looks like.
Much better! We can see data points, the x-axis is labeled, and the objects stand out from the background.
At this point, we should rename our report so it’s easy to find later. Click on “Untitled Report” in the top left and enter a new name for your chart. I chose “Tutorial Chart” for mine.
Once you’ve renamed your chart, click the “VIEW” button in the top right.
PART IV — VIEWING REPORTS
The “View” mode is for seeing and interacting with the charts once you’ve built them. Notice that you can hover over data points, change the date range, and that the whole thing is now an interactive call detail report. Pretty cool!
Really quickly, let’s look at this refresh button in the top right.
The “Refresh data” button will update your chart. Our charts can sometimes take 10+ seconds to refresh since our SQL instance isn’t very powerful. Your charts will automatically refresh if you’ve been away from them for a while (we’re not sure how many minutes). If you’re looking for a call that you know just happened, you’ll be clicking that refresh button.
There is also a Chrome extension that auto refreshes for you, but we haven’t used it. One day, we’ll have a big screen on the wall with real-time calling charts, but today isn’t that day.
And that’s about it! Feel free to stop reading here. The rest will just be our final thoughts and an example of what we’ve built.
PART V — FINAL THOUGHTS
We wanted to show you a report we built after we’d been using Data Studio for a few weeks.
(Note: again, we have filtered our data so it only shows a slice of our calling — we also edited out the Account Code and Call Cost entries.)
Notice that we can see all our calls, calling charts, total call minutes, and we even calculated a per-second cost to give us a rough idea of how much the call minutes costs us. And the best part is we can filter all this data by any date range or account code we want! We can see details on every accountcode in our system!
Hopefully that gives you an idea of the power of Data Studio. It’s definitely not perfect, but it’s way better than our previous CDR Reporting software.
Good luck building your own reports! And if you happen to live in Indiana, take a moment to check out our business phone service!
