Grandstream provisioning server

Document created on 2014-03-12 by Nicolas Bondier
Switzernet

Introduction

This document describe the solution developed for provisioning new Grandstream devices and how to use it.

How it works

The provisioning server provides XML configuration files to Grandstream devices through the HTTP server. There are two steps in the provisioning process.

The first one consists to configure provisioning. In this step, the provisioning server gives new provisioning login/password to a device.

The second one is the regular provisioning, which will provide all SIP related data. In this step, the devices have login information for authentication on the provisioning server.

Configuring a new device

With your browser, login to provisioning.switzernet.com, enter the new account number to configure, select the device model.

1. Add a new device

Then click on the “Add” button. Your new device is now in the pending list of devices to configure.

2. Devices pending list

You must now prepare the device. Get the device out of the box, connect the handset and connect the power. The device must not be connected to the network yet.

Prepare the special router. This router is configured for sending DNS requests to our provisioning server to bypass the Grandstream provisioning server configured in the device.

Before plugin the phone, click on the “configure” button that correspond to the account you are going to setup in the “Action” column.

At this moment, a modal window will open and give a time interval (default is 5 minutes) to the device to perform the first provisioning.

3. Auto-provisioning session opened

Plug the phone on power and connect it to the router. After it boots, the phone will get the first provisioning file for authentication, restart again, get the second provisioning file with SIP data and connect to Switzernet SIP servers.

4. Provisioning configuration done

Now, test incoming and outgoing calls.

Configuration & Code documentation

Table of content :

· Table of content : 1

· Router configuration. 1

· DNS server. 1

· Web interface. 1

· XML provisioning. 1

· Local database update scripts. 1

· Local database architecture. 1

· Setting up HTTPS. 1

Router configuration

The router used in the office is configured with our provisioning server as DNS, as in the image below:

5. Router configuration

DNS server

The DNS server is configured on the provisioning server for answering to all requests to fm.grandstream.com zone and giving the address of our provisioning server. Configuration file /etc/bind/grandstream.com.zone:

$ORIGIN .

$TTL 86400 ; 1 day

grandstream.com IN SOA provisioning.switzernet.com. hostmaster (

2014022002 ; serial

3600 ; refresh

1800 ; retry

43200 ; expire

3600 ; minimum

)

$TTL 14400 ; 4 hours

NS provisioning.switzernet.com.

$ORIGIN grandstream.com.

fm IN A 37.187.66.210;

It also answers to all other DNS request and ask to the master of switzernet.com zone. This is specified in /etc/bind/named.conf.options:

options {

directory "/var/cache/bind";

//[...]

forwarders {

195.154.7.28;

8.8.8.8;

};

dnssec-validation auto;

auth-nxdomain no; # conform to RFC1035

listen-on-v6 { any; };

allow-recursion { any; };

};

Web interface

The first page of the web interface list all accounts the staff is going to configure on devices. Users are able to add or remove new accounts and select one for starting the configuration of the provisioning.

File : /var/www/index.php

Code

Comments

<?php

session_start();

include_once "./include/dbconnect.php";

$devices = array("DP715" => "Grandstream DP715", "GXP1100" => "Grandstream GXP1100", "GXP1400" => "Grandstream GXP1400");

if ( isset($_POST['account_id']) && isset($_POST['model']) && isset($_POST['add']) && $_POST['add'] == "yes" && preg_match('/^41[0-9]{9}$/',$_POST['account_id']) && array_key_exists($_POST['model'], $devices) ){

$sql = "INSERT INTO `Customer_Devices_Provisioning` (Account_ID,Device_Model,Subscrition_Date) VALUES ('".$_POST['account_id']."','".$_POST['model']."',NOW())";

if (!mysqli_query($con,$sql)) {

die('Error: ' . mysqli_error($con));

}

if( isset($_GET['del']) && preg_match('/^[0-9]+$/',$_GET['del'])){

$sql = "DELETE FROM `Customer_Devices_Provisioning` WHERE ID='".$_GET['del']."'";

if (!mysqli_query($con,$sql)){

die('Error: ' . mysqli_error($con));

}

PHP session start.

Connection to the local database.

Here we get the values of the posted data for adding or removing a new line in the device list database.

<head>

<title>Device provisioning auto-configuration</title>

var reload_modal_interval = 0;

var point_interval = 0;

var update_time_interval = 0;

</script>

</head>

<body>

<h1>Device provisioning auto-configuration</h1>

</div>

<br>

<h2>Add new device</h2>

<legend>Enter a new phone to configure</legend>

<option value="no">Select model</option>

<?php

foreach ($devices as $code => $desc) {

echo '<option value="'.$code.'">'.$desc.'</option>';

}

</select>

</fieldset>

</form>

HTML document definition and header with style formatting and required JavaScript libraries.

Below, then we create a form for adding a new device to the device pending list.

When submitting the form, the page is reloaded with the posted contents.

<br>

<h2 class="content-subhead">Configuration pending list</h2>

<thead>

<tr>

<th>Account ID</th>

<th>Model</th>

<th>Submition date</th>

<th>Status</th>

<th>Actions</th>

</tr>

</thead>

<tbody>

The column titles of the pending devices table.

<?php

$sql = "SELECT ID, Account_ID, Device_Model, Subscrition_Date FROM Customer_Devices_Provisioning WHERE Init_Date IS NULL;";

if ($result = $con->query($sql)) {

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

echo "<tr>";

echo "<td>".$row['Account_ID']."</td>";

echo "<td>".$row['Device_Model']."</td>";

echo "<td>".$row['Subscrition_Date']."</td>";

echo '<td class="cell_action">Wait.</td>';

echo '<td class="cell_action">

<a href="?del='.$row['ID'].'" role="button" data-toggle="modal"><img src="http://icons.iconarchive.com/icons/visualpharm/must-have/256/Delete-icon.png" height="24" width="24" /></a>';

echo "</tr>";

}

For this list, we select all device in the database that have no “Init_Date” set. It means, the phone has never been configured for the provisioning.

</tbody>

</table>

</div>

function load_configuration_modal(row_id){

var reload_init_sec = 10;

$('#init_info').load("include/configuration_modal.php?id="+row_id+"&start_modal=yes&int_time="+reload_init_sec+"&ie="+(new Date().getTime()));

$('#myModal').modal({ keyboard: false });

reload_modal_interval = setInterval(function(){$('#init_info').load("include/configuration_modal.php?id="+row_id+"&int_time="+reload_init_sec+"&ie="+(new Date().getTime()))},(1000*reload_init_sec));

}

Closing the table.

This JavaScript function load the modal window, which will launch session for configuring the provisioning.

JavaScript load the content of “include/configuration_modal.php” in the “init_info” div of the modal window. It is reloaded every 10 seconds in our example. This can be changed according the server charge.

<h2 id="myModalLabel">New device auto-configuration</h2>

Session will close in : <span id="session_close_timer"></span>

</div>

</div>

</div>

<hr>

<p><i>Created on 2014-02-17 by Nicolas Bondier</i> <address> © <?= date('Y')?> <a href="http://switzernet.com">Switzernet</a></address> </p>

</div>

</body>

</html>

The modal window div. The “init_info” div is empty and is loaded by JavaScript. The span “session_close_timer”

<?php

$con->close();

We close the connection to the DB.

The page described below is the page permitting to open a session for one provisioning. It update the status in real time for monitoring all configuration provisioning steps.

File : /var/www/include/configuration_modal.php

Code

Comments

<?php

include_once "./dbconnect.php";

PHP page start.

Database connection.

if ( isset($_GET['id']) && preg_match('/^[0-9]+$/',$_GET['id']) ){

$data = get_Device_data($_GET['id']);

} else {

exit;

}

First, we get the number id for which we will return data. If the id is not alphanumeric, we do not return anything.

$allowed_seconds = '10';

if ( isset($_GET['int_time']) && preg_match('/^[0-9]+$/',$_GET['int_time']) ){

$allowed_seconds=$allowed_seconds+$_GET['int_time'];

}

Then we get the “int_time” parameter. This is the reload interval of this PHP page. We add 10 seconds to it, in order to have some margin. Later we will update the database to inform that this page is opened and that the device can be initialized until the time margin is over.

if ( isset($_GET['start_modal']) && $_GET['start_modal'] = 'yes' ){

if ( ( ( strtotime($data['Init_Session_Until']) - time() ) <= 0 || $data['Init_Session_Until'] == '' ) && $data['MAC_Address'] == '' ){

reset_Init_Session_Until($_GET['id']);

}

} else {

if ( ( strtotime($data['Init_Session_Until']) - time() ) <= 0 && $data['MAC_Address'] == '' && $data['Init_Date'] == '' ){ ?>

clearInterval(reload_modal_interval);

clearInterval(update_time_interval);

clearInterval(point_interval);

reload_modal_interval = 0;

update_time_interval = 0;

point_interval = 0;

$('#myModal').modal('hide');

</script>

<?php

} else {

opened_Session_Update($_GET['id']);

}

$data = get_Device_data($_GET['id']);

If we just opened the modal window and the device has no MAC Address assigned and the last session for configuring the provisioning is closed (“Init_Session_Until” is in the past), then we reset the initialization of the device in the database.

But if the session is over and the device is not configured (no “Init_Session_Until”, no “MAC_Address” and no “Init_Date”), we stop refreshing the page and close the modal.

In all other cases, we update the database for keeping the provisioning session opened and we launch the function for returning information about provisioning steps.

And finally we get all data for the device for displaying it later.

<p>

Account ID : <?= $data['Account_ID'] ?><br>

Device Model : <?= $data['Device_Model'] ?><br>

Mac Address : <?= $data['MAC_Address'] ?>

<h3>Instructions</h3>

This window open enable a session for a new provisionning autoconfiguration.<br>

Open and plug the new phone in the router.<br>

<h3>Status</h3>

Displaying of some information about the current configuration.

<?php

$finished = false;

$three_points = '<span id="three_points"></span>';

$continue = true;

if( $_SERVER['REMOTE_ADDR'] != $data['Init_IP'] ){

echo "Initialing :".$three_points."<br>";

$continue = false;

} else {

echo "Initialing : DONE<br>";

}

if ( $continue ){

if( $data['MAC_Address'] == '' && $data['Init_Date'] == '' ){

echo "Waiting for new device provisioning #1 :".$three_points."<br>";

$continue = false;

} else {

echo "Waiting for new device provisioning #1 : DONE"."<br>";

echo "Found new device with MAC Address ".$data['MAC_Address']."<br>";

}

if ( $continue ){

if( $data['HTTP_Login'] == '' && $data['HTTP_Password'] == '' ){

echo "Configuring new HTTP access :".$three_points."<br>";

$continue = false;

} else {

echo "Configuring new HTTP access : DONE"."<br>";

}

if ( $continue ){

if( $data['First_Update'] == '' ){

echo "Waiting for provisioning #2 :".$three_points."<br>";

$continue = false;

} else {

echo "Waiting for provisioning #2 : DONE"."<br>";

}

if ( $continue ){

echo "Configuration finished !<br>";

}

Every step is displayed for the current configuration.

$('#session_close_timer').text("<?= date('H:i:s', max(strtotime($data['Init_Session_Until']) - time(),0) ); ?>");

if(point_interval === 0){

point_interval = setInterval(update_points,1000);

}

if(update_time_interval === 0){

update_time_interval = setInterval(update_time,1000);

}

Here we set a timer for the session. It is calculated with PHP and displayed with JavaScript.

We set up the loops for the point loading and timer update.

function update_points(){

var d = new Date();

var s = d.getSeconds();

var n = s - parseInt((s/4))*4;

var t = ' ';

for (var i = 1; i <= n; i++) {

t = t+'.';

};

$('#three_points').text(t);

}

This function only display “.”, “..” or “…” every seconds for showing the page is not frozen.

function update_time(){

time = $('#session_close_timer').text();

h = time.substr(0, 2);

m = time.substr(3, 2);

s = time.substr(6, 2);

total = parseInt(h)*3600+parseInt(m)*60+parseInt(s);

total = total - 1;

n_h = parseInt( total / 3600 );

n_m = parseInt( (total - n_h*3600)/60 );

n_s = parseInt(total - n_h*3600 - n_m*60 );

$('#session_close_timer').text( pad(Math.max(0,n_h),2) + ":" + pad(Math.max(0,n_m),2) + ":" + pad(Math.max(0,n_s) ,2));

}

Updating the timer with JavaScript.

function pad(num, size) {

var s = num+"";

while (s.length < size) s = "0" + s;

return s;

}

Inserting “0” before numbers for formatting.

</script>

End of JavaScript.

<?php

$con->close();

Closing database connection.

function moving_points(){

$n = date('s') - ( (int)(date('s') / 4) * 4 ) ;

$t = ' ';

for ($i=1; $i <= $n; $i++) {

$t .= '.';

}

return $t;

}

Simple function for displaying loading points.

function reset_Init_Session_Until ($id){

global $con;

global $allowed_seconds;

$sql = "UPDATE `Customer_Devices_Provisioning` SET Init_IP='".$_SERVER['REMOTE_ADDR']."',Init_Closing_Time='".date('Y-m-d H:i:s',mktime ( date("H") , date("i"), date("s") + $allowed_seconds , date("n") , date("j") , date("Y") ))."' , Init_Session_Until='".date('Y-m-d H:i:s',mktime ( date("H") , date("i") + 5 , date("s") , date("n") , date("j") , date("Y") ))."' WHERE ID='".$id."';";

if (!mysqli_query($con,$sql)) {

die('Error: ' . mysqli_error($con));

}

return true;

}

This function is called when we need to reset the session for a new one. It updates all needed fields.

function get_Device_data ($id){

global $con;

$sql = "SELECT * FROM `Customer_Devices_Provisioning` WHERE ID='".$id."';";

$data = array();

if ($result = $con->query($sql)) {

$data = $result->fetch_assoc();

} else {

echo "No results !";

exit;

}

return $data;

}

Function for getting all data stored in the database for on device.

function opened_Session_Update($id){

global $con;

global $allowed_seconds;

$sql = "UPDATE `Customer_Devices_Provisioning` SET Init_Closing_Time='".date('Y-m-d H:i:s',mktime ( date("H") , date("i"), date("s") + $allowed_seconds , date("n") , date("j") , date("Y") ))."' WHERE ID='".$id."';";

if (!mysqli_query($con,$sql)) {

die('Error: ' . mysqli_error($con));

}

return true;

}

Function for updating the database and keeping the session opened by incrementing the Init_Closing_time parameter.

XML provisioning

As we said, configuration is made in two step. First, the phone try to reach the provisioning XML file at “http://provisioning.grandstream.com/gs/cfgxxxxxxxxxxxx.xml” where “xxxxxxxxxxxx” is the MAC address of the device.

Our router with a custom DNS server gives the IP address of our own provisioning server and the device try to get the “http://provisioning.switzernet.com/gs/cfgxxxxxxxxxxxx.xml” instead.

Each device must have different settings and instead of creating thousands of configuration files, we created only one PHP page on which we redirect all HTTP request with an URL like “http://server.domain/gs/cfgxxxxxxxxxxxx.xml” to “http://server.domain/gs/cfgxxxxxxxxxxxx.php” keeping the MAC address. For these we have to create a .htaccess file in the /var/www/gs/ directory and add a RewriteRule directive with a regular expression as in the example bellow. We also specify that there is not any authentication for this directory.

Options +FollowSymlinks

RewriteEngine On

RewriteRule ^cfg[0-9A-z]{12}.xml$ cfgxxxxxxxxxxxx.php [L]

ErrorDocument 404 /not_found.html

AuthType None

Require all granted

Satisfy Any

Then, in our PHP file, we can recognise each device with its MAC address contained in the URL. The server variable for getting the URL after redirection is $_SERVER['REDIRECT_URL'].

/var/www/gs/cfgxxxxxxxxxxxx.php

Code

Comments

<?php

if ( 'xxx.xxx.xxx.xxx' != $_SERVER["REMOTE_ADDR"] ){

exit;

}

First, we verify the IP address of connection. Actually, the only authorized address for the provisioning configuration is the Swiss office.

include "../include/dbconnect.php";

$URL = '';

if ( isset( $_SERVER['REDIRECT_URL'] ) ){

$URL = basename($_SERVER['REDIRECT_URL']);

}

if( !preg_match('/^cfg([0-9A-z]{12})\.xml$/', $URL, $param) ){

exit;

}

$model = '';

$mac = $param[1];

Getting the URL of the XML file and retrieving the MAC address included in it.

if( preg_match('/.*Grandstream.*GXP1400.*/', $_SERVER['HTTP_USER_AGENT']) ){

$model = 'GXP1400';

} elseif ( preg_match('/.*Grandstream.*DP715.*/', $_SERVER['HTTP_USER_AGENT']) ) {

$model = 'DP715';

} elseif ( preg_match('/.*Grandstream.*GXP1100.*/', $_SERVER['HTTP_USER_AGENT']) ) {

$model = 'GXP1100';

}

Each device may have different parameters key. We mustknow which device is getting the page and we use the HTTP user agent for that.

$sql = "SELECT ID FROM Customer_Devices_Provisioning WHERE MAC_Address IS NULL AND HTTP_Login IS NULL AND HTTP_Password IS NULL AND Init_Closing_Time > NOW() AND Init_Session_Until > NOW() AND Init_IP='".$_SERVER['REMOTE_ADDR']."' AND Device_Model='".$model."' LIMIT 1;";

//echo $sql;

$ID = '';

if ($result = $con->query($sql)) {

if( $result->num_rows != 1 ){

exit;

}

$row = $result->fetch_assoc();

$ID = $row['ID'];

$result->close();

} else {

exit;

}

We select the ID of the pending device to configure. The IP address of the device must be the same as the one used by the user who opened the configuration window in its browser. The model must match too.

$pass = randomPassword();

$sql = 'UPDATE `Customer_Devices_Provisioning` SET MAC_Address = "'.$mac.'", HTTP_Login="' . $mac . '", HTTP_Password="' . $pass . '", Init_Date=NOW() WHERE ID="' . $ID . '";';

if (!mysqli_query($con,$sql)) {

die('Error: ' . mysqli_error($con));

}

Generating a new random password and update the table of device with the login data for the device.

$con->close();

echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n"?>

Starting the generation of the XML file. This is written in PHP because the “<?” id considered as PHP open tag if written directly in the file.

<gs_provision version="1">

<P237>provisioning.switzernet.com/gs/protected</P237>

</config>

</gs_provision>

Generation of XML with basic information.

- MAC address

- Path of the protected XML file for next provisioning.

- Login and passwords for protected XML file.

<?php

function randomPassword() {

$alphabet = "abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789";

$pass = array(); //remember to declare $pass as an array

$alphaLength = strlen($alphabet) - 1; //put the length -1 in cache

for ($i = 0; $i < 8; $i++) {

$n = rand(0, $alphaLength);

$pass[] = $alphabet[$n];

}

return implode($pass); //turn the array into a string

}

Function for the generation of a random password.

The first provisioning step is done. All next provisioning requests from the device come on the new provided provisioning address “http://provisioning.switzernet.com/gs/protected/cfgxxxxxxxxxxxx.php”. We write the same RewriteRule directive in the .htaccess file of the directory /var/www/gs/protected.

RewriteEngine On

RewriteRule ^cfg[0-9A-z]{12}.xml$ cfgxxxxxxxxxxxx.php [L]

ErrorDocument 404 /not_found.html

We do not specify any authentication. The authentication is included in the PHP delivering the XML content.

File : /var/www/gs/protected/cfgxxxxxxxxxxxx.php

Code

Comments

<?php

# URL parameters

$URL = '';

if ( isset( $_SERVER['REDIRECT_URL'] ) ){

$URL = basename($_SERVER['REDIRECT_URL']);

}

if( !preg_match('/^cfg([0-9A-z]{12})\.xml$/', $URL, $param) ){

exit;

}

$mac = $param[1];

$mac = strtolower($mac);

Getting the URL of the XML file and retrieving the MAC address included in the string.

# Getting login and passwords

include "../../include/dbconnect.php";

$sql="SELECT * FROM Customer_Devices_Provisioning WHERE MAC_Address = '".$mac."';";

$users = array();

$device = array();

if ($result = $con->query($sql)) {

if( $result->num_rows != 1 ){

exit;

}

$row = $result->fetch_assoc();

$users[$row['HTTP_Login']] = $row['HTTP_Password'];

$device = $row;

$result->close();

}

From this mac address we retrieve all data in the local database.

# HTTP authentication

if ( !isset($_SERVER['PHP_AUTH_USER']) ||

!isset($users[$_SERVER['PHP_AUTH_USER']]) ||

$users[$_SERVER['PHP_AUTH_USER']] != $_SERVER['PHP_AUTH_PW'] ) {

header('WWW-Authenticate: Basic realm="'.$realm.'"');

header('HTTP/1.0 401 Unauthorized');

echo 'Wrong Credentials (basic) !';

exit;

}

First step with those data, is to verify the login and password provided by the device. If it is correct we proceed with the authentication and go to next steps. We use basic authentication because some devices does not support DIGEST authentication (GXP1400, GXP1100).

# SIP servers

$rand_sip = array();

$h_sip = array();

$sql = 'SELECT name, weight FROM Sip_Servers;';

if ($result = $con->query($sql)) {

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

for ($i=0; $i < $row['weight']; $i++) {

array_push($rand_sip, $row['name']);

$h_sip[$row['name']] = 1;

}

$result->close();

}

We select to two SIP servers we will use from the Sip_Servers table. Each server has a weight, which is calculated according to the load by another script. They are inserted in an array with the weight as the number of time the must be present.

$sip1 = '';

$sip2 = '';

if ($device['Device_Model'] == 'DP715'){

$P_sip1 = 'P47';

$P_sip2 = 'P967';

} elseif ( $device['Device_Model'] == 'GXP1400' ) {

$P_sip1 = 'P47';

$P_sip2 = 'P2312';

} elseif ( $device['Device_Model'] == 'GXP1100' ) {

$P_sip1 = 'P47';

$P_sip2 = 'P2312';

}

Definition of the parameters used for SIP servers for each device model.

# Changing sip every 10 provisioning

if ( ((int) ($device['Count_Updates']/10)) != ($device['Count_Updates']/10) ){

$test_string = $device['Count_Updates'] - ((int) ($device['Count_Updates']/10))*10;

$sql = 'SELECT * FROM Customer_Devices_Provisioning_Values WHERE Device_ID = '.$device['ID'].' AND Parameter_ID = \'' . $P_sip1 . '\';';

if ( $result = $con->query($sql) ) {

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

if ( isset($h_sip[$row['Value']]) ){

$sip1 = $row['Value'];

}

} else {

exit;

}

$sql = 'SELECT * FROM Customer_Devices_Provisioning_Values WHERE Device_ID = '.$device['ID'].' AND Parameter_ID = \'' . $P_sip2 . '\';';

if ( $result = $con->query($sql) ) {

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

if ( isset($h_sip[$row['Value']] ) ){

$sip2 = $row['Value'];

}

} else {

exit;

}

For GXP1400 and GXP1100, it is necessary to not change randomly the server each time the device make provisioning or it will reboot repeatedly.

Every provisioning is counted. If this count is not divisible per 10, we select the precedent provisioning server if it is in the list of currently available SIP servers.

But every 10^th provisioning we do not verify the precedent configured servers.

if ( $sip1 == '' ){

$sip1 = $rand_sip[rand ( 0 , (count ($rand_sip) - 1) )];

}

if ( $sip2 == '' ){

$sip2 = $sip1;

$tmp = 0;

while ( $sip2 == $sip1 && $tmp < 10 ) {

$sip2 = $rand_sip[rand ( 0 , (count ($rand_sip) - 1) )];

$tmp++;

}

Then, if one server is missing, meaning it is the 10^th time the device is provisioning or the precedent configured server is no more available, we randomly select new servers.

$sql = 'INSERT INTO `Customer_Devices_Provisioning_Values` (Device_ID,Parameter_ID,Value) VALUES ('.$device['ID'].',\''.$P_sip1.'\',\''.$sip1.'\') ON DUPLICATE KEY UPDATE Value = \''.$sip1.'\';';

mysqli_query($con,$sql);

$sql = 'INSERT INTO `Customer_Devices_Provisioning_Values` (Device_ID,Parameter_ID,Value) VALUES ('.$device['ID'].',\''.$P_sip2.'\',\''.$sip2.'\') ON DUPLICATE KEY UPDATE Value = \''.$sip2.'\';';

mysqli_query($con,$sql);

Final step concerning servers. We update the database with the servers we just choose.

# VoIP Passwords

$update_voip_pass = TRUE;

if ( $device['i_account'] == '' || !isset($device['i_account'])){

$con_ps=mysqli_connect('xxx.xxx.xxx.xxx','xxxxxxxxxxxxx','xxxxxxxxxxxxx',"xxxxxxxxxxxxx");

if (mysqli_connect_errno()){

echo "Failed to connect to MySQL: " . mysqli_connect_error($con_ps);

}

$sql = "SELECT i_account, h323_password as password FROM `porta-billing`.Accounts WHERE id = '".$device['Account_ID']."';";

if ($result = $con_ps->query($sql)) {

if( $result->num_rows != 1 ){

exit;

}

$row = $result->fetch_assoc();

$sql = "UPDATE `Customer_Devices_Provisioning` SET i_account='" . $row['i_account'] . "' WHERE MAC_Address = '".$mac."';";

mysqli_query($con,$sql);

$sql = "INSERT INTO `Accounts_passwords` (i_account, password) VALUES ('" . $row['i_account'] . "', '" . $row['password'] . "') ON DUPLICATE KEY UPDATE password='" . $row['password'] . "';";

mysqli_query($con,$sql);

$device['i_account'] = $row['i_account'];

$device['password'] = $row['password'];

} else {

exit;

}

$con_ps->close();

}

Verifying if the device has an i_account defined. If not, it means this is the first time the device access to the protected xml provisioning file.

In this case, we connect to the master DB and we get the i_account and VoIP password.

We then update the local database with these data.

$sql = "SELECT password FROM `Accounts_passwords` WHERE i_account='".$device['i_account']."';";

if ( $result = $con->query($sql) ) {

if( $result->num_rows != 1 ){

exit;

}

$row = $result->fetch_assoc();

$device['password'] = $row['password'];

} else {

exit;

}

In all cases, we get the accounts passwords to submitting it in the XML provisioning file.

echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n";

<gs_provision version="1">

Beginning of the creation of the provisioning file.

if ( $device['Device_Model'] == 'DP715' ){

<P192>fm.grandstream.com/gs</P192>

<P237>provisioning.switzernet.com/gs/protected</P237>

<?php if ( isset($device['HTTP_Login']) && $device['HTTP_Login'] != '' && isset($device['HTTP_Password']) && $device['HTTP_Login'] != '' ){ ?>

<?php } ?>

The following setting are for ‘DP715’ phone. Other Grandstream models may have different properties numbers.

The first settings are general settings about provisioning. XML comments describes each of them.

Comments directly come from the Grandstream template configuration file.

<?php if ( $update_voip_pass && isset($device['password']) ){ ?>

<?php } ?>

Settings for the VoIP line.

Codecs settings.

</config>

</gs_provision>

End of the configuration file.

<?php } elseif ( $device['Device_Model'] == 'GXP1400' ) { ?>

<P192>fm.grandstream.com/gs</P192>

<P237>provisioning.switzernet.com/gs/protected</P237>

<?php if ( isset($device['HTTP_Login']) && $device['HTTP_Login'] != '' && isset($device['HTTP_Password']) && $device['HTTP_Login'] != '' ){ ?>

<?php } ?>

The following setting are for ‘GXP1400’ phone. The first settings are general settings about provisioning. XML comments describes each of them.

<?php if ( $update_voip_pass && isset($device['password']) ){ ?>

<?php } ?>

Settings for the VoIP line.

Codecs settings.

<?php } elseif ( $device['Device_Model'] == 'GXP1100' ) { ?>

End of the configuration file.

<P192>fm.grandstream.com/gs</P192>

<P237>provisioning.switzernet.com/gs/protected</P237>

<?php if ( isset($device['HTTP_Login']) && $device['HTTP_Login'] != '' && isset($device['HTTP_Password']) && $device['HTTP_Login'] != '' ){ ?>

<?php } ?>

The following setting are for ‘GXP1100’ phone. The first settings are general settings about provisioning. XML comments describes each of them.

<?php if ( $update_voip_pass && isset($device['password']) ){ ?>

<?php } ?>

Settings for the VoIP line.

Codecs settings.

<?php } ?>

</config>

</gs_provision>

Endo of the configuration file.

<?php

if ( !isset( $device['First_Update'] ) || $device['First_Update'] = '' ){

$sql = "UPDATE Customer_Devices_Provisioning SET First_Update = NOW() WHERE MAC_Address = '".$mac."';";

mysqli_query($con,$sql);

}

$sql = "UPDATE Customer_Devices_Provisioning SET Last_Update = NOW(), Count_Updates = ( Count_Updates + 1 ) WHERE MAC_Address = '".$mac."';";

mysqli_query($con,$sql);

At the end, if this is the first update, we set the date in the database.

In all cases we increment the count of updates.

$con->close();

Closing connection to local DB.

Local database update scripts

In order to have an updated database with VoIP passwords, a script running from crontab is launched every 5 minutes.

File : /root/scripts/update_passwords.pl

Code	Comments
#!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper; my $pb_db = "porta-billing"; my $pb_host = "xxx.xxx.xxx.xxx"; my $pb_user = "xxxxxxxxxxxxxxxxxx"; my $pb_pass = "xxxxxxxxxxxxxxxxxx"; my $pr_db = "Provisioning"; my $pr_host = "127.0.0.1"; my $pr_user = "xxxxxxxxxxxxxxxxxx"; my $pr_pass = "xxxxxxxxxxxxxxxxxx"; my $pb_hash = {}; my $pr_hash = {}; my $sth; my $sql = ''; my $pb_dbh = DBI->connect( "dbi:mysql:dbname=$pb_db;host=$pb_host;", $pb_user, $pb_pass ) or die "Connexion impossible à la base de données $pb_db !"; my $pr_dbh = DBI->connect( "dbi:mysql:dbname=$pr_db;host=$pr_host;", $pr_user, $pr_pass ) or die "Connexion impossible à la base de données $pr_db !";	Connecting to the local and master DB.
my $i_account_max_per_request = 50; my @i_accounts = ();	Setting up the max accounts per requests. In order to not have too big request or one request per account for each verification.
$sql = "SELECT i_account FROM Customer_Devices_Provisioning WHERE i_account IS NOT NULL AND i_account != '';"; $sth = $pr_dbh->prepare($sql); $sth->execute(); while ( my $row = $sth->fetchrow_hashref ){ push( @i_accounts, $row->{'i_account'} ); if ( @i_accounts >= ( $i_account_max_per_request ) ){ my $h = get_passwords_for_i_account_array(@i_accounts); @{$pb_hash}{keys %$h} = values %$h; @i_accounts = (); } } if (@i_accounts > 0){ my $h = get_passwords_for_i_account_array(@i_accounts); @{$pb_hash}{keys %$h} = values %$h; }	Selecting the accounts present in the local database. When we have enough accounts (equal to $i_account_max_per_request), we get the passwords with “get_passwords_for_i_account_array” function which return them and we insert them in the pb_hash hash. This is done until all accounts have been processed.
$sql = "SELECT i_account, password FROM Accounts_passwords;"; $sth = $pr_dbh->prepare($sql); $sth->execute(); while ( my $row = $sth->fetchrow_hashref ){ $pr_hash->{ $row->{'i_account'} } = $row->{'password'}; }	Then we get all passwords from the local database.
foreach my $i_account ( keys( %$pb_hash ) ) { $sql = ''; if ( defined ( $pr_hash->{$i_account} ) && $pr_hash->{$i_account} ne $pb_hash->{$i_account} ){ $sql = 'UPDATE Accounts_passwords SET password = "'.$pb_hash->{$i_account}.'" WHERE i_account = '.$i_account.';'; } elsif ( ! defined ( $pr_hash->{$i_account} ) ) { $sql = 'INSERT INTO Accounts_passwords (i_account,password) VALUES ('.$i_account.',\'' . $pb_hash->{$i_account} . '\');'; } if ( $sql ne '' ){ print "$sql \n"; $sth = $pr_dbh->prepare($sql); $sth->execute(); } } $pr_dbh->disconnect(); $pb_dbh->disconnect(); exit;	If there are differences, we update the local database.
sub get_passwords_for_i_account_array { my @i_accounts = @_; my $hash = {}; $sql = "SELECT i_account, h323_password as password FROM Accounts WHERE i_account IN (".join(',',@i_accounts).") AND h323_password IS NOT NULL;"; my $sth = $pb_dbh->prepare($sql); $sth->execute(); while ( my $row = $sth->fetchrow_hashref ){ $hash->{ $row->{'i_account'} } = $row->{'password'}; } return $hash; }	The function for returning the passwords for each i_account passed as argument with “IN (a,b,c)” in MySQL.

Another script is used for updating currently available sip servers and the weight for the server assignation. A server with high weight is not used enough and will support more devices than a server with low weight.

File : /root/scripts/update_sip_servers.pl

Code

Comments

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

use Storable;

use File::Spec::Functions qw(rel2abs);

use File::Basename;

use List::Util qw(min max);

my $dirname = dirname(rel2abs($0));

chdir $dirname;

Perl libraries used in this document.

my $pb_db = "porta-billing";

my $pb_host = "xxx.xxx.xxx.xxx";

my $pb_user = "xxxxxxxxxxxxxxxxxx";

my $pb_pass = "xxxxxxxxxxxxxxxxxx";

my $pr_db = "Provisioning";

my $pr_host = "127.0.0.1";

my $pr_user = "xxxxxxxxxxxxxxxxxx";

my $pr_pass = "xxxxxxxxxxxxxxxxxx";

my $pb_dbh = DBI->connect( "dbi:mysql:dbname=$pb_db;host=$pb_host;", $pb_user, $pb_pass ) or die "Connexion

impossible à la base de données $pb_db !";

my $pr_dbh = DBI->connect( "dbi:mysql:dbname=$pr_db;host=$pr_host;", $pr_user, $pr_pass ) or die "Connexion

impossible à la base de données $pr_db !";

Connections to local and master databases.

my $timestamp = time;

my $h1 = {};

my $h2 = {};

my $h3 = {};

my $h4 = {};

my $h5 = {};

my $h6 = {};

Creation of a timestamp for filtering the new values in the database later.

Creation of the hash we will use later.

for (my $i = 1; $i <= 200; $i++) {

my $t = `dig sip$i.youroute.net \@ns1.youroute.net | grep CNAME | awk '{print \$1","\$5'}`;

if ( $t =~ /([^,]+)\.[,]+([^,]+)\./ ){

$h1->{$1} = $2;

if ( defined( $h2->{$2} ) ){

$h2->{$2}++;

} else {

$h2->{$2} = 1;

}

Discovering all sipX.youroute.net CNAMEs (astradX.switzernet.com) with DNS.

For each we save in a hash and count the repeated CNAME.

foreach my $name (keys %$h2) {

my $ip = `dig $name \@ns1.switzernet.com | grep '^$name' | awk '{print \$5}'`;

chomp $ip;

$h3->{$ip} = $name;

}

For each CNAME, we get the IP from DNS servers.

my $sql = 'SELECT a.ip, b.count, n.h323_id

FROM ( SELECT substring_index(an_address,":",1) as ip FROM `porta-sip`.active_nodes WHERE an_env = 1 AND an_expires > NOW() ) a

LEFT JOIN ( SELECT COUNT(username) as count, domain FROM `porta-sip`.location WHERE expires > NOW() GROUP BY domain ) b ON a.ip=b.domain

LEFT JOIN `porta-billing`.Nodes n ON a.ip = n.ip WHERE b.count IS NOT NULL ORDER BY count;';

my $sth = $pb_dbh->prepare($sql);

$sth->execute();

my $c1 = 0;

my $c2 = 0;

while ( my $row = $sth->fetchrow_hashref ){

if ( defined($h3->{$row->{'ip'}}) ){

$h4->{$h3->{$row->{'ip'}}} = $row->{'count'};

$c1 = $c1 + $row->{'count'};

$c2 = max $c2, $row->{'count'};

}

Then, in the master database, we get all SIP servers and the number of devices registered on each one. We count only the servers that updated the master with keep alive with “an_expires > NOW()”. This way, if a server is down, we do not select it.

We also get the total registration number and max number of clients per server.

my $c3 = 0;

foreach my $a (keys %$h4) {

foreach my $s (keys %$h1) {

if ( $h1->{$s} eq $a ){

my $t = ($c2 - $h4->{$a} ) / $h2->{$a};

$h6->{$s} = $t;

$c3 = $c3 +$t;

}

For each sip server sipX.youroute.net, we give a weight.

It is: weight = (max registration per server – registration for the server) / number of sipX.youroute.net for the CNAME.

foreach my $s (keys $h6) {

my $w = int( ( $h6->{$s}*100/$c3 ) + 0.5 );

my $sql = "INSERT INTO Sip_Servers (name,weight,timestamp) VALUES ('$s',$w,".$timestamp.") ON DUPLICATE KEY UPDATE weight = $w, timestamp = $timestamp;";

#print "$sql\n";

$sth = $pr_dbh->prepare($sql);

$sth->execute();

}

The precedent weigh is transformed in percent and rounded.

Then, the values are inserted in the database. There are sipX.youroute.net server name, weight and timestamp.

$sql = "SELECT MAX(timestamp) as max FROM Sip_Servers;";

$sth = $pr_dbh->prepare($sql);

$sth->execute();

my $max = 0;

while ( my $row = $sth->fetchrow_hashref ){

$max = $row->{'max'};

}

if ( $max != 0 ){

$sql = "DELETE FROM Sip_Servers WHERE timestamp < ".$max.";";

$sth = $pr_dbh->prepare($sql);

$sth->execute();

}

Then we select the higher timestamp and remove the older ones from the database.

This must be done at the end for removing the server which are no more available, without empty the database or some devices may receive no server when provisioning.

$pr_dbh->disconnect();

$pb_dbh->disconnect();

Disconnecting from the databases.

Local database architecture

Here are the main tables used by the scripts above:

DROP DATABASE IF EXISTS `Provisioning`;

CREATE DATABASE `Provisioning`;

USE `Provisioning`;

CREATE TABLE `Customer_Devices_Provisioning` (

ID INT(10) NOT NULL AUTO_INCREMENT,

MAC_Address VARCHAR(12) DEFAULT NULL,

Account_ID VARCHAR(11) NOT NULL,

Device_Model VARCHAR(30) NOT NULL,

HTTP_Login VARCHAR(30) DEFAULT NULL,

HTTP_Password VARCHAR(30) DEFAULT NULL,

Subscrition_Date DATETIME NOT NULL,

Init_IP VARCHAR(15) DEFAULT NULL,

Init_Closing_Time DATETIME DEFAULT NULL,

Init_Session_Until DATETIME DEFAULT NULL,

Init_Date DATETIME,

First_Update DATETIME,

Last_Update DATETIME,

Count_Updates INT(10) DEFAULT 0,

i_account INT(10) unsigned DEFAULT NULL,

sha1_pass VARCHAR(40) DEFAULT NULL,

PRIMARY KEY (ID),

UNIQUE KEY (MAC_Address),

UNIQUE KEY (HTTP_Login),

KEY (Account_ID),

KEY (Device_Model),

KEY (Subscrition_Date)

);

CREATE TABLE `Customer_Devices_Provisioning_Values` (

Device_ID INT(10),

Parameter_ID varchar(10),

Value varchar(255),

KEY (Device_ID),

KEY (Parameter_ID),

UNIQUE KEY (Device_ID,Parameter_ID)

);

CREATE TABLE `Customer_Devices_Provisioning_Parameters` (

Parameter_ID varchar(10),

Device_Model VARCHAR(30) NOT NULL,

Description TEXT,

Default_Value varchar(255),

KEY (Parameter_ID),

UNIQUE KEY (Parameter_ID,Device_Model)

);

CREATE TABLE Accounts_passwords (

i_account INT(10) unsigned,

password VARCHAR(40),

UNIQUE KEY (i_account)

);

CREATE TABLE Sip_Servers (

name varchar(50),

weight int(5),

timestamp int(12),

UNIQUE KEY (name)

);

Setting up HTTPS

HTTP provisioning does not protect against sniffing the network to gather VoIP or Provisioning logins and passwords. For this reason, it is required to use HTTPS instead of HTTP. For this purpose, we created a self-signed certificate. It is not necessary to have an approved one.

Here is the apache configuration file for HTTPS:

ServerAdmin webmaster@localhost

DocumentRoot /var/www

Options FollowSymLinks

AllowOverride None

</Directory>

Options Indexes FollowSymLinks MultiViews

AllowOverride all

Order allow,deny

allow from all

</Directory>

ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/

AllowOverride None

Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch

Order allow,deny

Allow from all

</Directory>

ErrorLog ${APACHE_LOG_DIR}/error.log

# Possible values include: debug, info, notice, warn, error, crit,

# alert, emerg.

LogLevel warn

CustomLog ${APACHE_LOG_DIR}/ssl_access.log combined

SSLEngine on

SSLCertificateFile /root/self-certificate/provisioning.switzernet.com.crt

SSLCertificateKeyFile /root/self-certificate/provisioning.switzernet.com.key

SSLOptions +StdEnvVars

</FilesMatch>

SSLOptions +StdEnvVars

</Directory>

BrowserMatch "MSIE [2-6]" \

nokeepalive ssl-unclean-shutdown \

downgrade-1.0 force-response-1.0

# MSIE 7 and newer should be able to use keepalive

BrowserMatch "MSIE [17-9]" ssl-unclean-shutdown

</VirtualHost>

</IfModule>

Files

All files: [zip]

* * *