Store Kannel DLR to MySQL/MariaDB

In our earlier article on Kannel we have learned about how to install Kannel. So, in that configuration, we store SMS Delivery Report (DLR) in memory of the bearerbox process. In that way, if bearerbox crashes or we take the process down, but there are still DLRs open, it may cause problem for SMS Users.

So, to avoid this situation, we can use external DLR storage like MySQL database.In the previous article we used two kannel boxes: bearerbox and smsbox. To store DLR in MySQL database we will use another Kannel program: sqlbox. All communication between bearerbox and smsbox will be done via sqlbox. In operation, sqlbox will act like bearerbox for smsbox and smsbox for bearerbox.

Communication between different processes can be illustrated as below

LAB environment:

Host OS: Debian 10 (buster)
Host IP  : 192.168.10.38

We already have PlaySMS SMS Management application installed on the same host. For PlaySMS installation please check this article.

For better understanding, we have showed the full installation process of Kannel with MySQL support in this article. Please see this page to prepare the Debian host for Kannel Installation.

Install MariaDB Server

So, MySQL has acquired by Oracle Corporation and have both community and Commercial editions.

In this Lab we are going to use MariaDB database, an open-source alternative of MySQL. MariaDB has forked from MySQL open-source project and developed and supported by some of the original developers of MySQL.

So, Install  MariaDB Server with necessary dependencies

# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

Put MariaDB repo info in the file /etc/apt/sources.list.d/MariaDB.list

# MariaDB 10.3 repository list - created 2021-12-28 18:41 UTC
# https://mariadb.org/download/
deb [arch=amd64,arm64,ppc64el] https://download.nus.edu.sg/mirror/mariadb/repo/10.3/debian buster main
deb-src https://download.nus.edu.sg/mirror/mariadb/repo/10.3/debian buster main

Update repo and install MariaDB including necessary dependencies

# apt-get update
# apt-get install –y mariadb-server libmariadb-dev default-libmysqlclient-dev

Enable and Start MariaDB service

# systemctl enable mariadb.service
# systemctl start mariadb.service

Run mysql_secure_installation to set root password and other security directives as below

Set root password? [Y/n] Y
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

Create MariaDB database and user for Kannel.

# mysql -u root –p
MariaDB [(none)]> CREATE DATABASE kannel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE USER 'kannel'@'localhost' IDENTIFIED BY 'kannelPassword';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON kannel.* TO 'kannel'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> quit

Create dlr table for Kannel DLR. Two other tables send_sms and sent_sms will be automatically created by Kannel sqlbox process on first run

CREATE TABLE `dlr` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`smsc` varchar(40) DEFAULT NULL,
`ts` varchar(40) DEFAULT NULL,
`destination` varchar(40) DEFAULT NULL,
`source` varchar(40) DEFAULT NULL,
`service` varchar(40) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`mask` int(10) DEFAULT NULL,
`status` int(10) DEFAULT NULL,
`boxc` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Install Kannel with MySQL support

To store DLR in MySQL we have to compile Kannel with MySQL support

# wget --no-check-certificate https://kannel.org/download/1.4.5/gateway-1.4.5.tar.gz
# tar -zxf gateway-1.4.5.tar.gz
# cd gateway-1.4.5/
# ./configure  --enable-start-stop-daemon --prefix=/usr/local/kannel --with-mysql
# make && make install

Install smsbox program

# cd addons/sqlbox
# ./bootstrap

As we have changed default installation location for Kannel, we will have to provide necessary library directory and C++ flag variables for Kannel and MariaDB with configure script

To find MariaDB flag variable run below command

# mysql_config --cflags --libs

Output:

Run configure script

# ./configure  --prefix=/usr/local/kannel \
--with-kannel-dir=/usr/local/kannel \
LDFLAGS="-L/usr/local/kannel/lib -L/usr/lib/x86_64-linux-gnu/ -lmariadb"  \
CPPFLAGS="-I/usr/local/kannel/include/kannel -I/usr/include/mariadb -I/usr/include/mariadb/mysql"

Compile sqlbox program

# make && make install

Then, create necessary directories

# mkdir /usr/local/kannel/etc
# mkdir /usr/local/kannel/run
# mkdir /var/log/kannel/

After that, put below contents in /usr/local/kannel/etc/kannel.conf file

##CORE
group = core
admin-port = 13000
smsbox-port = 13001
admin-password = sms-gw-admin-passwd
status-password = sms-gw-status-passwd
admin-deny-ip = "*.*.*.*"
admin-allow-ip = "*.*.*.*"
box-deny-ip = "*.*.*.*"
box-allow-ip = "*.*.*.*"
log-level = 0
log-file = "/var/log/kannel/kannel.log"
dlr-storage = mysql
sms-resend-retry = 1


##SMSC
group = smsc
smsc = at
smsc-id = smsc01
my-number = 015XXXXXXXX
allowed-smsc-id = smsc01
preferred-smsc-id = smsc01
modemtype = Huawei
device = /dev/ttyUSB0
speed = 9600
alt-charset = "GSM"
sim-buffering = true
log-file = /var/log/kannel/smsc01-gsm1.log
log-level = 0


##MODEMS
group = modems
id = Huawei
enable-mms = true
init-string = "ATQ0 V1 E1 S0=0 &C1 &D2 +FCLASS=0;+CNMI=1,3,2,2,1;+CMEE=1;+CPMS=\"SM\",\"SM\",\"SM\";+CMGF=0;+CSCS=GSM
speed = 9600
detect-string = "huawei"


##SMSBOX
group = smsbox
bearerbox-host = localhost
sendsms-port = 13013
bearerbox-port = 13010
mo-recode = true
sendsms-chars = "0123456789 +-"
log-file = "/var/log/kannel/smsbox.log"
log-level = 0
access-log = "/var/log/kannel/access.log"


##SMS-USER
group = sendsms-user
user-deny-ip = "*.*.*.*"
user-allow-ip = "*.*.*.*"
# MAKE VERY SURE that you change the PASSWORD for security reasons
username = kannel
password = kannelpasswd
default-smsc = smsc01
concatenation = 1
max-messages = 10


##SMS-SERVICE
group = sms-service
keyword = default
get-url = "http://localhost/index.php?app=call&cat=gateway&plugin=kannel&access=geturl&t=%t&q=%q&a=%a&Q=%Q&smsc=kannel"
accept-x-kannel-headers = true
max-messages = 10
concatenation = true
catch-all = true
omit-empty = true


##MySQL-Connection 
group = mysql-connection
id = mysql-dlr
host = localhost
username = kannel
password = kannelPassword
database = kannel
max-connections = 1


##MySQL DLR Table
##You should not change this group name as bearerbox uses this name for MySQL DLR storage
group = dlr-db
id = mysql-dlr
table = dlr
field-smsc = smsc
field-timestamp = ts
field-destination = destination
field-source = source
field-service = service
field-url = url
field-mask = mask
field-status = status
field-boxc-id = boxc

Create /usr/local/kannel/etc/sqlbox.conf file including below content

##MySQL Connection
group = mysql-connection
id = sqlbox-db
host = localhost
port = 3306
username = kannel
password = kannelPassword
database = kannel


#SQL BOX CONFIG
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox
bearerbox-host = localhost
bearerbox-port = 13001
smsbox-port = 13010
sql-log-table = sent_sms
sql-insert-table = send_sms
log-level = 0
log-file = "/var/log/kannel/sqlbox.log"

Then, create Start/Stop Script /usr/local/kannel/sbin/kannel including below content

#!/bin/bash
# Start/stop the Kannel boxes: One bearer box and one WAP box.
# This is the default init.d script for Kannel. Its configuration is
# appropriate for a small site running Kannel on one machine.
# Make sure that the Kannel binaries can be found in $BOXPATH or somewhere
# else along $PATH. run_kannel_box has to be in $BOXPATH.
#
BOXPATH=/usr/local/kannel/sbin
PIDFILES=/usr/local/kannel/run
CONFDIR=/usr/local/kannel/etc
CONF=$CONFDIR/kannel.conf
SQL_CONF=$CONFDIR/sqlbox.conf


USER=kannel
VERSION=""


RB=$BOXPATH/run_kannel_box$VERSION
BB=$BOXPATH/bearerbox$VERSION
WB=$BOXPATH/wapbox$VERSION
SB=$BOXPATH/smsbox$VERSION
SSD=$BOXPATH/start-stop-daemon$VERSION

PATH=$BOXPATH:$PATH

# On Debian, the most likely reason for the bearerbox not being available
# is that the package is in the "removed" or "unconfigured" state, and the
# init.d script is still around because it's a conffile. This is normal,
# so don't generate any output.
# test -x $BOXPATH/$BB || exit 0

case "$1" in
  start)
    echo -n "Starting WAP gateway: bearerbox"
    $SSD --start --quiet --pidfile $PIDFILES/kannel_bearerbox.pid --exec $RB -- --pidfile $PIDFILES/kannel_bearerbox.pid $BB -- $CONF
    sleep 2
    echo -n " sqlbox"
    $BOXPATH/sqlbox -d $SQL_CONF
    sleep 2
    echo -n " smsbox"
    $SSD --start --quiet --pidfile $PIDFILES/kannel_smsbox.pid --exec $RB -- --pidfile $PIDFILES/kannel_smsbox.pid $SB -- $CONF
    sleep 1
    echo "."
    ;;

  stop)
    echo -n "Stopping WAP gateway: "
    echo -n " smsbox"
    $SSD --stop --quiet --pidfile $PIDFILES/kannel_smsbox.pid --exec $RB
    sleep 1
    echo -n " sqlbox"
    pkill "sqlbox"
    sleep 1
    echo -n " bearerbox"
    $SSD --stop --quiet --pidfile $PIDFILES/kannel_bearerbox.pid --exec $RB
    echo "."
    ;;

  status)
    CORE_CONF=$(grep -r 'group[[:space:]]*=[[:space:]]*core' $CONFDIR | cut -d: -f1)
    ADMIN_PORT=$(grep '^admin-port' $CORE_CONF | sed "s/.*=[[:space:]]*//")
    ADMIN_PASS=$(grep '^admin-password' $CORE_CONF | sed "s/.*=[[:space:]]*//")
    STATUS_URL="http://127.0.0.1:${ADMIN_PORT}/status.txt?password=${ADMIN_PASS}"
    curl $STATUS_URL
    ;;

  reload)
    # We don't have support for this yet.
    exit 1
    ;;

  restart|force-reload)
    $0 stop
    sleep 3
    $0 start
    ;;

  *)
    echo "Usage: $0 {start|stop|status|reload|restart|force-reload}"
    exit 1

esac
exit 0

Make the file /usr/local/kannel/sbin/kannel executable

# chmod +x /usr/local/kannel/sbin/kannel

Add Kannel to startup using systemd

Now create systemd unit file /etc/systemd/system/kannel-gateway.service including below contents

[Unit]
Description=Kannel - WAP and SMS Gateway
Documentation=https://www.kannel.org/doc.shtml
After=network-online.target
Wants=network-online.target

[Service]
Type=forking
ExecStart=/usr/local/kannel/sbin/kannel start
ExecReload=/usr/local/kannel/sbin/kannel  restart
ExecStop=/usr/local/kannel/sbin/kannel stop

[Install]
WantedBy=multi-user.target

Reload systemd

# systemctl daemon-reload

Start and enable kannel-gateway service

# systemctl enable kannel-gateway.service
# systemctl start kannel-gateway.service
# systemctl status kannel-gateway.service

DLR-MASK and DLR-URL

Kannel provides bellow delivery report types

1: delivery success
2: delivery failure
4: message buffered
8: smsc submit
16: smsc reject

So, for multiple delivery report types, you have to sum the values correspond to each type and have to set as dlr-mask while sending message.

Example: for success, failure and/or buffered delivery status report of an SMS, we have to set dlr-mask as 1+2+4=7.

With dlr-mask it is necessary to send dlr-url to send back delivery report with an SMS.

If we follow a Send SMS request from PlaySMS application we will find the html request as below

Here we understand that PlaySMS is asking success, failure, buffered and/or smsc submit request on dlr-mask (15) and also send it’s dlr-url with the request.

Send SMS Logs

So, we have sent a SMS from PlaySMS web portal and checked different states from MariaDB console

SMS Sent but yet to receive by Recipient:

MariaDB [kannel]> select * from dlr;
+----+---------------------+--------+------+---------------+-------------+---------+------------------------------------------------------------------------------------------------------------------+------+--------+------+
| id | datetime | smsc | ts | destination | source | service | url | mask | status | boxc |
+----+---------------------+--------+------+---------------+-------------+---------+------------------------------------------------------------------------------------------------------------------+------+--------+------+
| 1 | 2022-01-08 23:07:51 | smsc01 | 165 | 88017XXXXXXXX | 015XXXXXXXX | kannel | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | 15 | 0 | |
+----+---------------------+--------+------+---------------+-------------+---------+------------------------------------------------------------------------------------------------------------------+------+--------+------+
1 row in set (0.000 sec)

MariaDB [kannel]> select * from sent_sms;
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
| sql_id | momt | sender | receiver | udhdata | msgdata | time | smsc_id | service | account | id | sms_type | mclass | mwi | coding | compress | validity | deferred | dlr_mask | dlr_url | pid | alt_dcs | rpi | charset | boxc_id | binfo | meta_data | priority | foreign_id |
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
| 1 | MT | 015XXXXXXXX | 88017XXXXXXXX | NULL | MySQL+DLR+Test | 1641661668 | smsc01 | kannel | admin | NULL | 2 | NULL | NULL | 0 | NULL | NULL | NULL | 15 | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | DLR | 015XXXXXXXX | 88017XXXXXXXX | NULL | ACK%2F | 1641661671 | smsc01 | kannel | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | 8 | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | NULL | NULL | NULL | NULL | NULL | NULL | ?orig_msg?dlr_mask=15& | NULL | 165 |
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
2 rows in set (0.001 sec)

After SMS received by the destination Mobile Phone and got Success DLR from GSM Modem

MariaDB [kannel]> select * from dlr;
Empty set (0.000 sec)

MariaDB [kannel]> select * from sent_sms;
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
| sql_id | momt | sender | receiver | udhdata | msgdata | time | smsc_id | service | account | id | sms_type | mclass | mwi | coding | compress | validity | deferred | dlr_mask | dlr_url | pid | alt_dcs | rpi | charset | boxc_id | binfo | meta_data | priority | foreign_id |
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
| 1 | MT | 015XXXXXXXX | 88017XXXXXXXX | NULL | MySQL+DLR+Test | 1641661668 | smsc01 | kannel | admin | NULL | 2 | NULL | NULL | 0 | NULL | NULL | NULL | 15 | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | DLR | 015XXXXXXXX | 88017XXXXXXXX | NULL | ACK%2F | 1641661671 | smsc01 | kannel | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | 8 | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | NULL | NULL | NULL | NULL | NULL | NULL | ?orig_msg?dlr_mask=15& | NULL | 165 |
| 3 | DLR | 015XXXXXXXX | 88017XXXXXXXX | NULL | Success | 1641661727 | smsc01 | kannel | NULL | NULL | 3 | NULL | NULL | NULL | NULL | NULL | NULL | 1 | http://localhost//index.php?app=call&cat=gateway&plugin=kannel&access=dlr&type=%d&smslog_id=20&uid=1&smsc=kannel | NULL | NULL | NULL | NULL | NULL | NULL | ?orig_msg?dlr_mask=15& | NULL | 165 |
+--------+------+-------------+---------------+---------+----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+------------------------------------------------------------------------------------------------------------------+------+---------+------+---------+---------+-------+------------------------+----------+------------+
3 rows in set (0.000 sec)

Incoming SMS:

MariaDB [kannel]> select * from sent_sms where momt='MO';
+--------+------+----------------+-------------+---------+-----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+-----------+----------+------------+
| sql_id | momt | sender | receiver | udhdata | msgdata | time | smsc_id | service | account | id | sms_type | mclass | mwi | coding | compress | validity | deferred | dlr_mask | dlr_url | pid | alt_dcs | rpi | charset | boxc_id | binfo | meta_data | priority | foreign_id |
+--------+------+----------------+-------------+---------+-----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+-----------+----------+------------+
| 4 | MO | +88017XXXXXXXX | 015XXXXXXXX | NULL | SQLBOX+incoming | 1641663435 | smsc01 | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | NULL | NULL | NULL | NULL | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------+----------------+-------------+---------+-----------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+-----------+----------+------------+
1 row in set (0.000 sec)

SMSBOX Log:

PlaySMS Portal outgoing log:

From Recipient’s Phone:

References

• https://www.kannel.org/download/kannel-userguide-snapshot/userguide.html