How to Save Cisco CDR Data in MySQL

March 25th, 2019
Print Friendly, PDF & Email


How to Save Cisco CDR Data in MySQL

Many organizations use the Cisco Call Manager as their primary communication platform. Cisco Call Manager is one of the most well-known platforms with many features and capabilities. Developers looking to use the Cisco Call Manager call information in their applications such as CRM, billing or helpdesk should spend a lot of time learning to understand the Cisco system and how they can deal with that information directly from the Cisco Call Manager.

Today, I want to show you how you can save your Cisco Call Manager data to a Mysql table with the Node.js script. I use the PBXDom API, powerful call analytics and reporting tool, to achieve that goal. So, if you are interested, let’s go.

Step 1.
First, you should sign up for a PBXDom account. Fortunately, you can try 14 days for free; then you should install the collector software and connect it to your CUCM. All of them take about 10 minutes of your time. I suggest reading the following article about how to connect your Cisco Call Manager to PBXDom and access the dashboard and reports, and also connecting to API.

https://www.pbxdom.com/how-to-create-cisco-callmanager-dashboard-in-10-minutes

Step 2.
After logging in to your PBXDom account, go to Settings –> account and get your API key.

Get-API-Key-Cisco-CDR-Mysql

Step 3.
Before going to the next step, make sure your CUCM is connected to PBXDom, and you can see your call information in the dashboard.

pbxdom-dashboard-cisco-call-manager

Step 4.
PBXDom offers RESTful API, meaning we can connect and use it with any programming language, but today I want to use one of the most and popular back-end programming languages, Node.js

My goal is to connect to PBXDom API and get all the call information, then insert it into a MySQL table. Also, that script should be able to run continuously and fetch new data automatically, so we are expected always have the latest info in our MySQL table. If you want to use the call information for another purpose, you can change the scenario to fit your needs instead of inserting the data into a MySQL.

Step 5.
In the first step, we need to know about the endpoints available and API documentation. You can find all of them at the following address:

https://www.pbxdom.com/api-documentation

The endpoint addresses are:

https://api.pbxdom.com to https://api10.pbxdom.com so we can use any of them, but I chose the first: api.pbxdom.com.

We need to call the /Calls resource to get call information. Let’s look at the Calls resource parameters

https://api.pbxdom.com/Calls?access_token=" + APIKey +
"&fromDate=" + StartDate + "&limit=" + limit +
"&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=" + Start +
"&toDate=" + EndDate

Access_token = Your API key
fromDate = Start date to get call info
Limit = limit the result (it can be up to 10,000)
Start = start offset (I use it to get new data each time we run the script)
toDate = End date to get call info

We don’t need all the other parameters, but if you’d like to play with them, you can find more info about them in the API documentation page.

So, our GET request will be something like this:

https://api.pbxdom.com/Calls?access_token=YOUR_API_KEY&fromDate=2019-3-10+00:00:00&limit=50&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=0&toDate=2019-3-10+23:59:59

The response is like this:

{
   "meta": {
      "count": 50,
      "totalCount": 1
   },
   "calls": [
      {
         "id": 2393677,
         "callDateTime": "2019-03-10 00:50:00",
         "ring": "00:00:00",
         "callDirection": 1,
         "callTypeMapId": 0,
         "dialedPhone": "21633711",
         "callerPhone": "365",
         "callerName": "",
         "ext": "365",
         "extName": null,
         "duration": "00:19:42",
         "cost": 0,
         "spamStatus": 0,
         "co": "0051",
         "coName": null
      }
   ]
}

It has two parts: meta and calls. We use both of them in our script. At first, to make sure your API is working correctly, you can get data from API by Curl command like this:

curl ‘https://api9.pbxdom.com/Calls?access_token=YOUR_API_KEY&fromDate=2019-3-10+00:00:00&limit=50&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=0&toDate=2019-3-10+23:59:59’ -H ‘Accept: application/json, text/plain, */*’

If you get the same result as before, go ahead to the next step.

Step 6.
If I want to use the request package for Nodejs to make a GET request to an API endpoint, it’s straightforward. For more info, you can visit their page at
https://github.com/request/request

To install the request package, write the following command:

npm install request
Var URL=”https://api.pbxdom.com/Calls?access_token=YOUR_API_KEY&fromDate=2019-3-10+00:00:00&limit=50&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=0&toDate=2019-3-10+23:59:59”
request.get(URL, { timeout: 60000 }, function(err, resp) {
    console.log(resp)
});

Save that code into the index.js file and run it by:

Node index.js

You should see the same result as the curl command in your console.

So, until now, we were getting data from API, but we should be getting all data. We should get the new data and always be updated, so with start and limit parameters; we can achieve our goal.

They are like a MySQL limit command:

SELECT * FROM TABLE LIMIT 0,10

This means select from the table from record 0 up to 10 records

And if we want the next 10 records, we write another SQL like this:

SELECT * FROM TABLE LIMIT 10,10

It means select from the table from record 10 up to 10 records

We can do the same things with PBXDom API start and limit parameters. One thing we should care about is that we need to write a script that should be run every minute or every 5 minutes, depending on your requirement and your application. Our script should be run several times a day. On the other hands, as I mentioned before, we can’t fetch more than 10,000 records each time. Also, we should care about the MySQL database load and the performance of our application, so we can choose 50 or 100 or any small number like that; each time we run our script we only receive the 50 records, and then we save the offset into a JSON file, because we need to know for the next time we want to run the script which offset to start from and get new data after that.

For saving and retrieving data to a JSON file, I wrote the following functions. We will use them in our script for saving and retrieving the current offset.

var SavetoConfig = function(Data, cb) {
    var obj = {
        table: []
    };
    obj.table.push(Data);
    var json = JSON.stringify(obj);

    fs.writeFile('config.json', json, function(res) {
        console.log('Done')
        cb()
    });
}

var ReadConfig = function(cb) {
    fs.readFile('config.json', 'utf8', function readFileCallback(err, data) {
        if (err) {
            cb({
                table: [{ offset: 0 }]
            })
        } else {
            obj = JSON.parse(data);
            cb(obj)
        }
    });
}

Thanks https://stackoverflow.com/a/36856787/6619626

SavetoConfig function gets the JSON object and saves it to a JSON file named config.json

ReadConfig function retrieves config.json file data as a JSON object to you

For example, we can use SavetoConfig like this:

 SavetoConfig({ offset: 100}, function(res) {

   })

It will save the offset variable with the value 100 in our JSON file.

To retrieve that data, we will call ReadConfig like this:

ReadConfig(function(res) {
    Var offset=res.table[0].offset
})

Now, the offset variable has our offset value from the config.json file.

Step 7:
Now we should assemble all parts together for getting all call data from your account. Our code now looks like this:

const request = require('request')
const fs = require('fs');


var SavetoConfig = function(Data, cb) {
    var obj = {
        table: []
    };
    obj.table.push(Data);
    var json = JSON.stringify(obj);

    fs.writeFile('config.json', json, function(res) {
        console.log('Done')
        cb()
    });
}

var ReadConfig = function(cb) {
    fs.readFile('config.json', 'utf8', function readFileCallback(err, data) {
        if (err) {
            cb({
                table: [{ offset: 0 }]
            })
        } else {
            obj = JSON.parse(data);
            cb(obj)
        }
    });
}



ReadConfig(function(res) {

    var StartDate = "2019-3-21+00:00:00"
    var EndDate = "2019-3-21+23:59:59"
    var APIKey = "YOUR_API_KEY"
    var Start = res.table[0].offset
    var limit = 50
    var URL = "https://api.pbxdom.com/Calls?access_token=" + APIKey + "&fromDate=" + StartDate + "&limit=" + limit + "&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=" + Start + "&toDate=" + EndDate;
    console.log(URL)
    request.get(URL, { timeout: 60000 }, function(err, resp) {
        if (JSON.parse(resp.body).calls.length > 0) {
            var TotalRecords = JSON.parse(resp.body).meta.totalCount;
            var LastArrId = JSON.parse(resp.body).calls.length - 1
            var LastRecNo = JSON.parse(resp.body).calls[LastArrId].id;
            console.log('Total Records =>', TotalRecords, LastRecNo)
            console.log(JSON.parse(resp.body).calls.length, " Records retrived")
            if (Start + limit < TotalRecords || TotalRecords - Start < limit) {
                if (TotalRecords - Start < limit) {
                    Start = TotalRecords
                    limit = 0
                }
                SavetoConfig({ offset: Start + limit }, function(res) {

                })


            }

        } else {
            console.log("No new records")
        }

    });
})

For clarity, I use different variables for each of the parameters so that we can change them quickly. Also, you should care about the StartDate and EndDate variables and enter the right values. I set the limit to 50, but you can change it to any other number; the max number is 10,000.

If you save the last part into your index.js and run it, the first time, it will try to get config.json file data. Because it can’t find it, it returns 0 objects, so our script reads data from 0 records. But when we call the API and get the result, we then save the new offset to config.json for the next call.

When we run the script for the second time, it reads config.js again and gets the offset value and passes it to API, and we will get the next chunk of data.

That is all the logic of script.

Step 8.

Until now, we wrote a script, and it could get all the data and print it into the console. We can quickly put it to Cron job or Windows scheduler to run every minute. Now, we should add the Mysql part for inserting the retrieved data to a MySQL table.

I use the Mysql package in Nodejs. To install the MySQL package, run the following command:

Npm install MySQL
var connection = mysql.createConnection({
    host: 'YOUR_MYSQL_SERVER_IP',
    user: 'root',
    multipleStatements: true,
    password: '10002000',
    database: CALL
});
connection.connect();

The above code connects to your MySQL. We just need to create an SQL query insert and run it based on the data we received from API.

var sql = 'Insert into CallInfo (id,callDateTime,ring,callDirection,callTypeMapId,dialedPhone,callerPhone,callerName,ext,extName,duration,cost,spamStatus,co,coName) values '
var data = ''
JSON.parse(resp.body).calls.forEach(function(call) {
    data = date + "(" + call.id + ",'" + call.callDateTime + "','" + call.ring + "'," + call.callDirection + "," + call.callTypeMapId + "," + call.dialedPhone + "," + call.callerPhone + "," + call.callerName + "," + call.ext + "," + call.extName + "," + call.duration + "," + call.cost + "," + call.spamStatus + "," + call.co + "," + call.coName + "), "
})
connection.query(sql + data, function(err, rows) {
    console.log("Data inserted successfully to MySQL table")
});

Let’s explain the above code.
At first, set an SQL variable for Insert command with the column information. As you see, you should create a table in your MySQL with that field or change the field name based on your current table. In our example, we will insert data to the CallInfo table.

I use “JSON.parse(resp.body).calls.forEach“ to get all records retrieved from API and create our SQL statement.

Finally, “connection.query“ runs our SQL statement and inserts data to the table.

Our final script with some additional parts is below:

const request = require('request')
const fs = require('fs');
const mysql = require('mysql');
var connection = mysql.createConnection({
    host: ''
    YOUR_MYSQL_SERVER_IP '',
    user: 'root',
    multipleStatements: true,
    password: '10002000',
    database: CALL
});
connection.connect();

var SavetoConfig = function(Data, cb) {
    var obj = {
        table: []
    };
    obj.table.push(Data);
    var json = JSON.stringify(obj);

    fs.writeFile('config.json', json, function(res) {
        console.log('Done')
        cb()
    });
}

var ReadConfig = function(cb) {
    fs.readFile('config.json', 'utf8', function readFileCallback(err, data) {
        if (err) {
            cb({
                table: [{
                    offset: 0
                }]
            })
        } else {
            obj = JSON.parse(data);
            cb(obj)
        }
    });
}



ReadConfig(function(res) {

    var StartDate = "2019-3-21+00:00:00"
    var EndDate = "2019-3-21+23:59:59"
    var APIKey = "YOUR_API_KEY"
    var Start = res.table[0].offset
    var limit = 50
    var URL = "https://api.pbxdom.com/Calls?access_token=" + APIKey + "&fromDate=" + StartDate + "&limit=" + limit + "&q=&reporttype=3&rptId=3&rptType=1&sortBy=callDateTime&sortType=asc&sortcoldef=0&start=" + Start + "&toDate=" + EndDate;
    console.log(URL)
    request.get(URL, {
        timeout: 60000
    }, function(err, resp) {
        if (JSON.parse(resp.body).calls.length > 0) {
            var TotalRecords = JSON.parse(resp.body).meta.totalCount;
            var LastArrId = JSON.parse(resp.body).calls.length - 1
            var LastRecNo = JSON.parse(resp.body).calls[LastArrId].id;
            console.log('Total Records =>', TotalRecords, LastRecNo)
            console.log(JSON.parse(resp.body).calls.length, " Records retrived")
            if (Start + limit < TotalRecords || TotalRecords - Start < limit) {
                if (TotalRecords - Start < limit) {
                    Start = TotalRecords
                    limit = 0
                }
                SavetoConfig({
                    offset: Start + limit
                }, function(res) {

                })


            }

            var sql = 'Insert into CallInfo (id,callDateTime,ring,callDirection,callTypeMapId,dialedPhone,callerPhone,callerName,ext,extName,duration,cost,spamStatus,co,coName) values '
            var data = ''
            JSON.parse(resp.body).calls.forEach(function(call) {
                data = date + "(" + call.id + ",'" + call.callDateTime + "','" + call.ring + "'," + call.callDirection + "," + call.callTypeMapId + "," + call.dialedPhone + "," + call.callerPhone + "," + call.callerName + "," + call.ext + "," + call.extName + "," + call.duration + "," + call.cost + "," + call.spamStatus + "," + call.co + "," + call.coName + "), "
            })
            connection.query(sql + data, function(err, rows) {
                console.log("Data inserted successfully to Mysql table")
            });

        } else {
            console.log("No new records")
        }

    });
})

I added some additional code to prevent an error when the new data is not available. Now, you can customize that script and start using it.

You can add your script to Linux Cron job to run every 5 minutes by adding this line:

*/5 * * * *  node index.js

Finally, I hope this article can help you to write a smarter application for your organization. I’m sure that the scripts could be improved upon, but they are enough to get started, and I’m sure you will write better code than me when you get the idea.

Reza Mousavi

Serial Entrepreneur, Founder & CEO at PBXDom, Software Architect, Frequent reader, Fan of adventure travel
Reza Mousavi
Print Friendly, PDF & Email

Share this article on social networks:


Related Posts


Guess less and know more

Try PBXDOM for 14 days free
no credit card required.


Try free for 14 days 
PBXDom is a Cisco Solution Partner
Click here for more info
SHARE