Converting asterisk CDR to CSV file every 15 minutes .

Asterisk telephony system can save call detail records in the database . Usually, Asterisk-based telephony systems such as AsteriskNow , FreePBX or Elastix are saving in Asteriskcdrdb database and cdr table . In a project, my customer needed to export the Datatime , src , dst and duration fields to a CSV file and this is the PHP script that I have written .

<?php

$servername = "localhost";

$username = "root";

$password = "mypass";

$dbname = "asterisk";  //asterisk database . in asterisknow it is asteriskcdrdb

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

$sql = "select calldate,src,dst,duration from cdr";

$result = $conn->query($sql);

$row = $result->fetch_assoc();

$date = $row["calldate"];

$source = $row["src"];

$destination = $row["dst"];

$duration = $row["duration"];

$sql = "select calldate,src,dst,duration,userfield from cdr  where userfield LIKE '' "  ;

$result = $conn->query($sql);

$file = fopen('cdr-'.date("Y-m-d").date("-H-i-s").'.csv', 'w');

fputcsv($file, array('date', 'source', 'destination', 'duration'));

if($result->num_rows > 0) {

    $sql2 = "update cdr set userfield = 1" ;

    $result2 = $conn->query($sql2);

    // output data of each row

    while($row = $result->fetch_assoc()) {

      $date = $row["calldate"];

      $source = $row["src"];

      $destination = $row["dst"];

      $duration = $row["duration"];

      $data = array( 

          array($date,$source,$destination,$duration));

              foreach ($data as $row)

                {

                    fputcsv($file, $row);

                }

        }    

    }

// Close the file

fclose($file); 

    

?> 

and you can run it in crontab to run every 15 minutes .

*/15     *         *       *        *    /usr/bin/php      /root/cdr.php