Kannel SMS Gateway Delivery Reports in mySQL via sqlbox

kannel sqlbox sms report with DLR / modified tables and triggers/ zaib

Following is a reference guide on “Sending messages via kannel using SQLBOX / Triggers or PHP and storing the DLR in tables for tracking purposes” subject. This is not complete guide , just short notes that I was able to achieve.

There are few methods doing the same stuff. following is a mesh up configurations. You may find few parts useful or as reference purposes. All of these information was gathered from google, kannel mailing list, different blogs like (http://www.blogalex.com/archives/322) , 50-60 hours of eyes crushing efforts , mainly due to my slow pickup speed and efforts-in-the-wrong-direction approach.

My thoughts on this particular case are:
I have to admit that after spending so many years in this field, I am still unable to make a deep dive in this ocean. I am still doing basic surfing , nothing more then that. / z@iB

---

Requirements:

We are running SMS gateway using KANNEL and submit via URL http api. The OP required monthly report for the SMS sent to the users, delivery status of how many failed/delivered.

Solution:

We can make this happen by few methods. In this post, we have used SQLBOX in which we don’t require any external script like php which can update the status (although using external is more preferred and can perform many other functions as well ) . In this method we will simply insert our message in mysql SND table, and then the sqlbox and triggers will move this message to send_sms table, from where sqlbox will send the sms and move this entry to sendt_sms table, from there trigger will update the delivery status automatically (once dlr received).

Let’s Start

Components Used in this guide:

OS Used : Ubuntu 12.4 / 32bit
Mysql Version : mysql  Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (i686) using readline 6.2
Kannel Version : Kannel bearerbox version `1.4.3′. Build `Nov 24 2011

I assume you have working kannel configuration and can send SMS successfully. For kannel installation and basics you can go through following guide.

https://aacable.wordpress.com/2012/11/26/howto-configure-your-local-http-gateway-using-kannel-on-ubuntu/

Once you have managed to configured the kannel in working state, follow below …

---

1- INSTALL SQLBOX

Install SQLBOX

sudo apt-get install kannel-sqlbox

---

2- Edit KANNEL.CONF

Now edit Kannel Config file

nano /etc/kannel/kannel.conf

and paste following data. Make sure to change the required fields

# Syed Jahanzaib
# https://aacable.wordpress.com
# aacable [at] hotmail.com
# KANNEL CONFIGURATION FILE WITH SQLBOX/MYSQL SUPPORT
# LAST MODIFIED AT 06-AUG-2016

group = core
admin-port = 13000
smsbox-port = 13001
admin-password = zaibadmin
status-password = zaibstatus
log-file = "/var/log/kannel/bearerbox.log"
log-level = 0
box-deny-ip = "*.*.*.*"
box-allow-ip = "127.0.0.1"
#Send sms only once. No retry
sms-resend-retry = 1
# DLR storage set to mysql
dlr-storage = mysql

group = smsc
smsc = at
smsc-id = zaibgsmid
modemtype = teltonika # in this example i have used teltonika serial modem g/10 / zaib
device = /dev/ttyS0
#transceiver-mode = true
# Change above port to match your port, it could ACM1 or USB0, if you dont set it correctly, it will not work and you will get error in logs that unable to connect to device)
speed = 115200 # make sure to change the speed or kannel wont be able to connect with your modem
my-number = 0333302100000 # can be any
# Following SMSC number is for zong
# sms-center = 00923040000011
sim-buffering = true
log-level = 0

# for teltonkia only, if you are using other brand you may need to get there INIT strings, use WDIALCONFIG for quering string
# But what I have seen that this string worked for dlink and huawei modem as well.
group = modems
id = teltonika
name = "Teltonika E12"
detect-string = "Undefined"
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"
# or i used this for dlink as well.
#init-string = "ATQ0 V1 E1 S0=0 &C1 &D2 +FCLASS=0"
speed = 115200
message-storage = "SM"

group = smsbox
bearerbox-host = localhost
sendsms-port = 13013
global-sender = 13013
sendsms-chars = "0123456789 +-"
log-file = "/var/log/kannel/smsbox.log"
log-level = 0
access-log = "/var/log/kannel/access.log"

group = sendsms-user
# MAKE VERY SURE that you change the PASSWORD for security reasons
username = kannel
password = kannelpass
concatenation = 1
max-messages = 10

group = sms-service
keyword = default
get-url = "http://localhost/playsms/index.php?app=call&cat=gateway&plugin=kannel&access=geturl&t=%t&q=%q&a=%a&Q=%Q" # this is for playSMS
accept-x-kannel-headers = true
max-messages = 0
concatenation = true
catch-all = true
text = "Galaxy ROBOT by Zaib"
omit-empty = true

################# MYSQL CONFIGURATION #############
# SQL BOX CONFIG
# Database connection examples. Please uncomment as needed
# Example MYSQL Connection

group = mysql-connection
id = sqlbox-db
host = localhost
username = root
password = rootpassword
database = kannel_db

# DLR SETUP
#mysql connection
group = mysql-connection
id = mydlr
host = localhost
username = root
password = rootpassword
database = kannel_db

group = dlr-db
id = mydlr
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

Save & exit.

Now edit SQLBOX Config file

nano /etc/kannel/sqlbox.conf

and paste the following

#SQL BOX CONFIG
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox
global-sender = "03333021909" # change this to SIM number
bearerbox-host = localhost
bearerbox-port = 13001
sql-log-table = sent_sms
sql-insert-table = send_sms
log-level = 0
log-file = "/var/log/kannel/kannel-sqlbox.log"

group = mysql-connection
id = sqlbox-db
host = localhost
username = root
password = rootpassword # change this for sure
database = kannel_db

Save & exit.

---

3- Creating Database for SQLBOX/SND/DLR and import

Create database by following command

mysql -uroot -pSQLPASS -e "create database kannel_db;"

Now create a file which will contain all the tables that are required for the kannel_db database. This is LAB testing version, but 100% working.

mkdir /temp
touch /temp/kannel_db_tables.sql
nano /temp/kannel_db_tables.sql

and paste the following data

-- MySQL dump 10.13 Distrib 5.5.49, for debian-linux-gnu (i686)
--
-- Host: localhost Database: kannel_db
-- ------------------------------------------------------
-- Server version 5.5.49-0ubuntu0.12.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `dlr`
--

DROP TABLE IF EXISTS `dlr`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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,
`time` bigint(20) NOT NULL,
`smsc_id` varchar(40) NOT NULL,
`sms_id` bigint(20) NOT NULL,
`msgdata` text NOT NULL,
`number` text NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=443 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dlr`
--

LOCK TABLES `dlr` WRITE;
/*!40000 ALTER TABLE `dlr` DISABLE KEYS */;
/*!40000 ALTER TABLE `dlr` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dlr2`
--

DROP TABLE IF EXISTS `dlr2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dlr2` (
`id_sms` bigint(20) NOT NULL AUTO_INCREMENT,
`smsc` varchar(40) DEFAULT NULL,
`msgid` text NOT NULL,
`destination` varchar(40) DEFAULT NULL,
`sendertype` text,
`url` varchar(255) DEFAULT NULL,
`mask` int(10) DEFAULT NULL,
`status` text,
`boxc` varchar(40) DEFAULT NULL,
`sms_insert_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`sms_delivery_date` timestamp NULL DEFAULT NULL,
`sms_message` text NOT NULL,
`status2` text NOT NULL,
PRIMARY KEY (`id_sms`)
) ENGINE=InnoDB AUTO_INCREMENT=293 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dlr2`
--

LOCK TABLES `dlr2` WRITE;
/*!40000 ALTER TABLE `dlr2` DISABLE KEYS */;
/*!40000 ALTER TABLE `dlr2` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `send_sms`
--

DROP TABLE IF EXISTS `send_sms`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `send_sms` (
`sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
`momt` enum('MO','MT') DEFAULT NULL,
`sender` varchar(20) DEFAULT NULL,
`receiver` varchar(20) DEFAULT NULL,
`udhdata` blob,
`msgdata` text CHARACTER SET utf8,
`time` bigint(20) DEFAULT NULL,
`smsc_id` varchar(255) DEFAULT NULL,
`service` varchar(255) DEFAULT NULL,
`account` varchar(255) DEFAULT NULL,
`id` bigint(20) DEFAULT NULL,
`sms_type` bigint(20) DEFAULT NULL,
`mclass` bigint(20) DEFAULT NULL,
`mwi` bigint(20) DEFAULT NULL,
`coding` bigint(20) DEFAULT NULL,
`compress` bigint(20) DEFAULT NULL,
`validity` bigint(20) DEFAULT NULL,
`deferred` bigint(20) DEFAULT NULL,
`dlr_mask` bigint(20) DEFAULT NULL,
`dlr_url` varchar(255) DEFAULT NULL,
`pid` bigint(20) DEFAULT NULL,
`alt_dcs` bigint(20) DEFAULT NULL,
`rpi` bigint(20) DEFAULT NULL,
`charset` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`boxc_id` varchar(255) DEFAULT NULL,
`binfo` varchar(255) DEFAULT NULL,
`meta_data` text,
`status` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sql_id`)
) ENGINE=MyISAM AUTO_INCREMENT=782 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `send_sms`
--

LOCK TABLES `send_sms` WRITE;
/*!40000 ALTER TABLE `send_sms` DISABLE KEYS */;
/*!40000 ALTER TABLE `send_sms` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sent_sms`
--

DROP TABLE IF EXISTS `sent_sms`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sent_sms` (
`sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`momt` enum('MO','MT','DLR') DEFAULT NULL,
`sender` varchar(20) DEFAULT NULL,
`receiver` varchar(20) DEFAULT NULL,
`udhdata` blob,
`msgdata` text,
`smsc_id` varchar(255) DEFAULT NULL,
`service` varchar(255) DEFAULT NULL,
`account` varchar(255) DEFAULT NULL,
`id` bigint(20) DEFAULT NULL,
`sms_type` bigint(20) DEFAULT NULL,
`mclass` bigint(20) DEFAULT NULL,
`mwi` bigint(20) DEFAULT NULL,
`coding` bigint(20) DEFAULT NULL,
`compress` bigint(20) DEFAULT NULL,
`validity` bigint(20) DEFAULT NULL,
`deferred` bigint(20) DEFAULT NULL,
`dlr_mask` bigint(20) DEFAULT NULL,
`dlr_url` varchar(255) DEFAULT NULL,
`pid` bigint(20) DEFAULT NULL,
`alt_dcs` bigint(20) DEFAULT NULL,
`rpi` bigint(20) DEFAULT NULL,
`charset` varchar(255) DEFAULT NULL,
`boxc_id` varchar(255) DEFAULT NULL,
`binfo` varchar(255) DEFAULT NULL,
`status` text,
`time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`sql_id`),
KEY `ix_sent_sms_dlr_url` (`dlr_url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sent_sms`
--

LOCK TABLES `sent_sms` WRITE;
/*!40000 ALTER TABLE `sent_sms` DISABLE KEYS */;
/*!40000 ALTER TABLE `sent_sms` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_sent_sms_insert` AFTER INSERT ON `sent_sms`
FOR EACH ROW BEGIN
UPDATE snd SET zaibstatus = 'Submitted to sqlbox' WHERE snd_id = NEW.dlr_url;

IF NEW.momt = 'DLR' THEN
CASE NEW.dlr_mask
WHEN 1 THEN UPDATE snd SET snd_success = FROM_UNIXTIME(NEW.time) , zaibstatus = 'SUCCESS' WHERE snd_id = NEW.dlr_url;
WHEN 2 THEN UPDATE snd SET snd_failure = FROM_UNIXTIME(NEW.time) , zaibstatus = 'FAILED wrong no' WHERE snd_id = NEW.dlr_url;
WHEN 4 THEN UPDATE snd SET snd_buffered = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
WHEN 8 THEN UPDATE snd SET snd_submitted = FROM_UNIXTIME(NEW.time) , zaibstatus = 'Pending' WHERE snd_id = NEW.dlr_url;
WHEN 16 THEN UPDATE snd SET snd_rejected = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
WHEN 32 THEN UPDATE snd SET snd_intermediate = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
ELSE UPDATE snd SET snd_last = NEW.dlr_mask WHERE snd_id = NEW.dlr_url;
END CASE;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;

--
-- Table structure for table `snd`
--

DROP TABLE IF EXISTS `snd`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `snd` (
`snd_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Every message has a unique id',
`snd_sender` varchar(255) DEFAULT NULL COMMENT 'The number that the message is sent from',
`snd_to` varchar(255) DEFAULT NULL COMMENT 'The number that the message is sent to',
`snd_txt` longtext COMMENT 'The body text of the message',
`snd_smsc` varchar(255) DEFAULT NULL COMMENT 'The number that the message is route to',
`snd_sentat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The time that the message is inserted',
`snd_success` datetime DEFAULT NULL COMMENT 'The time that the message was delivered to the hand set',
`snd_failure` datetime DEFAULT NULL COMMENT 'The time that the message failed',
`snd_submitted` datetime DEFAULT NULL COMMENT 'The time that the message is submitted to the smsc',
`snd_buffered` datetime DEFAULT NULL COMMENT 'The time that the message is buffered at the smsc',
`snd_rejected` datetime DEFAULT NULL COMMENT 'The time that the message was rejected',
`snd_intermediate` datetime DEFAULT NULL COMMENT 'Time of intermediate status',
`snd_last` int(11) NOT NULL DEFAULT '0',
`zaibstatus` text,
PRIMARY KEY (`snd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This table holds the messages that are sent, including DLR values.';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `snd`
--

LOCK TABLES `snd` WRITE;
/*!40000 ALTER TABLE `snd` DISABLE KEYS */;
/*!40000 ALTER TABLE `snd` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_snd_insert` AFTER INSERT ON `snd`
FOR EACH ROW BEGIN
INSERT INTO send_sms (momt, sender, receiver, msgdata, smsc_id, sms_type, dlr_mask, dlr_url, boxc_id, coding)
VALUES ('MT', NEW.snd_sender, NEW.snd_to, NEW.snd_txt, NEW.snd_smsc, 2, 31, NEW.snd_id, 'sqlbox', '2');
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-09-06 10:32:14

Save & exit.

Now import these tables into mysql kannel_db database. use below command

mysql -uroot -pSQLPASS kannel_db < kannel_db_tables.sql

Login to mysql and see if you can see the database kannel_db and its tables.

mysql -uroot -pSQLPASS -e "use kannel_db; show tables from kannel_db;"

# and if all good, you will see tables as showed below ..
+---------------------+
| Tables_in_kannel_db |
+---------------------+
| dlr |
| dlr2 |
| send_sms |
| sent_sms |
| snd |
+---------------------+

---

5- Restarting the Services

stop the kannel and start the kannel bearerbox, smsbox and sqlbox in 3 separate terminals , to see for any errors.

service kannel stop

# wait few seconds

Now start the process , each process in separate terminal

bearerbox -v 0 /etc/kannel/kannel.conf

smsbox -v 0 /etc/kannel/kannel.conf

sqlbox -v 0 /etc/kannel/sqlbox.conf

If you see no errors you are all good to go … verify it by

 

root@ubuntu:~# ps aux |grep kannel
kannel 20324 0.0 0.0 2248 308 ? Ss 08:39 0:00 /usr/sbin/run_kannel_box --pidfile /var/run/kannel/kannel_bearerbox.pid --no-extra-args /usr/sbin/bearerbox -v 4 -- /etc/kannel/kannel.conf
kannel 20326 1.8 0.2 127896 10360 ? Sl 08:39 2:32 /usr/sbin/bearerbox -v 4 -- /etc/kannel/kannel.conf
kannel 20337 0.0 0.0 2248 356 ? Ss 08:39 0:00 /usr/sbin/run_kannel_box --pidfile /var/run/kannel/kannel_wapbox.pid --no-extra-args /usr/sbin/wapbox -v 4 -- /etc/kannel/kannel.conf
kannel 20341 0.0 0.0 2248 304 ? Ss 08:39 0:00 /usr/sbin/run_kannel_box --pidfile /var/run/kannel/kannel_smsbox.pid --no-extra-args /usr/sbin/smsbox -v 4 -- /etc/kannel/kannel.conf
kannel 20344 0.0 0.1 71236 4656 ? Sl 08:39 0:00 /usr/sbin/smsbox -v 4 -- /etc/kannel/kannel.conf

root@ubuntu:~# ps aux |grep sqlbox
root 20421 0.0 0.1 35096 4356 ? Ssl 08:39 0:01 sqlbox -d /etc/kannel/sqlbox.conf

---

Now try to insert data directly into snd table by using following command

mysql -uroot -pSQLPASS -e "use kannel_db; INSERT INTO snd (snd_sender,snd_to,snd_txt,snd_smsc) VALUES ('12345','03333021909', 'First Message sent from Kannel / SQLBOX and update DLR as well by zaib','zaibgsmid');"

in bearerbox logs, you will see something like following (Make sure your operator supports DLR)

2016-09-06 11:06:07 [8571] [10] DEBUG: boxc_receiver: sms received
2016-09-06 11:06:07 [8571] [10] DEBUG: send_msg: sending msg to boxc: <sqlbox>
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: TP-Validity-Period: 24.0 hours
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: --> AT+CMGS=76^M
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- AT+CMGS=76
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- >
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: send command status: 1
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: --> 0031000B813033031209F90000A746C6B47C4E0735CBF379F85C06CDCB6E3AC82C7FB741CBB0DB5D66835EA06994297C6241613719548793C3F43288C89482C273D0BDCC6683C479903E9C1603
2016-09-06 11:06:09 [8571] [6] DEBUG: AT2[zaibgsmid]: --> ^Z
2016-09-06 11:06:12 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- > 0031000B813033031209F90000A746C6B47C4E0735CBF379F85C06CDCB6E3AC82C7FB741CBB0DB5D66835EA06994297C6241613719548793C3F43288C89482C273D0BDCC6683C479903E9C1603
2016-09-06 11:06:12 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- +CMGS: 161
2016-09-06 11:06:12 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- OK
2016-09-06 11:06:12 [8571] [6] DEBUG: AT2[zaibgsmid]: send command status: 0
2016-09-06 11:06:12 [8571] [6] DEBUG: DLR[mysql]: Adding DLR smsc=zaibgsmid, ts=161, src=0333302100000 # can be any, dst=03333021909, mask=31, boxc=sqlbox
2016-09-06 11:06:12 [8571] [6] DEBUG: sql: INSERT INTO dlr (smsc, ts, source, destination, service, url, mask, boxc, status) VALUES ('zaibgsmid', '161', '0333302100000 # can be any', '03333021909', '', '6', '31', 'sqlbox', '0');
2016-09-06 11:06:12 [8571] [6] DEBUG: SMSC[zaibgsmid]: creating DLR message
2016-09-06 11:06:12 [8571] [6] DEBUG: SMSC[zaibgsmid]: DLR = 6
2016-09-06 11:06:12 [8571] [11] DEBUG: send_msg: sending msg to boxc: <sqlbox>
2016-09-06 11:06:12 [8571] [11] DEBUG: boxc_sender: sent message to <127.0.0.1>
2016-09-06 11:06:15 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- +CDSI: "MT",301
2016-09-06 11:06:15 [8571] [6] DEBUG: AT2[zaibgsmid]: +CMTI incoming SMS indication: +CDSI: "MT",301
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: --> AT+CPMS="MT"^M
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- AT+CPMS="MT"
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- +CPMS: 9,350,1,50,1,50
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- OK
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: --> AT+CMGR=301^M
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- AT+CMGR=301
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- +CMGR: 0,,25
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- 079129330320111006A10B813033031209F9619060116021026190601160310200
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: received message from SMSC: +923330021101
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: got STATUS-REPORT for message <161>:
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: Numeric receiver <03333021909>
2016-09-06 11:06:16 [8571] [6] DEBUG: DLR[mysql]: Looking for DLR smsc=zaibgsmid, ts=161, dst=03333021909, type=1
2016-09-06 11:06:16 [8571] [6] DEBUG: sql: SELECT mask, service, url, source, destination, boxc FROM dlr WHERE smsc='zaibgsmid' AND ts='161';
2016-09-06 11:06:16 [8571] [6] DEBUG: Found entry, row[0]=31, row[1]=, row[2]=6, row[3]=0333302100000 # can be any, row[4]=03333021909 row[5]=sqlbox
2016-09-06 11:06:16 [8571] [6] DEBUG: DLR[mysql]: created DLR message for URL <6>
2016-09-06 11:06:16 [8571] [6] DEBUG: removing DLR from database
2016-09-06 11:06:16 [8571] [6] DEBUG: sql: DELETE FROM dlr WHERE smsc='zaibgsmid' AND ts='161' LIMIT 1;
2016-09-06 11:06:16 [8571] [11] DEBUG: send_msg: sending msg to boxc: <sqlbox>
2016-09-06 11:06:16 [8571] [11] DEBUG: boxc_sender: sent message to <127.0.0.1>
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- OK
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: --> AT+CMGD=301^M
2016-09-06 11:06:16 [8571] [6] DEBUG: AT2[zaibgsmid]: <-- AT+CMGD=301

Now query the snd table, and you will the SUCCESS status (success status will appear only when DLR will be received from the mobile operator, which means if the receiver mobile is off, you will not receive DLR, once the message is delivered to the phone , then DLR will receive and status message will be udpated)

root@ubuntu:~# mysql -uroot -pSQLPASS -e "use kannel_db; select * from snd;"
+--------+------------+-------------+------------------------------------------------------------------------+-----------+---------------------+---------------------+-------------+---------------+--------------+--------------+------------------+----------+------------+
| snd_id | snd_sender | snd_to | snd_txt | snd_smsc | snd_sentat | snd_success | snd_failure | snd_submitted | snd_buffered | snd_rejected | snd_intermediate | snd_last | zaibstatus |
+--------+------------+-------------+------------------------------------------------------------------------+-----------+---------------------+---------------------+-------------+---------------+--------------+--------------+------------------+----------+------------+
| 6 | 12345 | 03333021909 | First Message sent from Kannel / SQLBOX and update DLR as well by zaib | zaibgsmid | 2016-09-06 11:06:07 | 2016-09-06 11:06:16 | NULL | NULL | NULL | NULL | NULL | 0 | SUCCESS |
+--------+------------+-------------+------------------------------------------------------------------------+-----------+---------------------+---------------------+-------------+---------------+--------------+--------------+------------------+----------+------------+

Final Success Screenshots

#send_sms table

 

#snd table

Now as all messages can be stored here in this snd_table along with their status , you can create a nice front end to fetch your required results.

---

Special thanks to

1- Mr. Tapan Kumar Thapa for sqlbox remote assistance for the triggers as I was not able to create the triggers due to mySQL syntax errors. He can be reached at “https://in.linkedin.com/in/tapan-thapa-02b68b5”

2- Mr. Julien , who provided logics and few directions which helped me in dlr.php approach.

---

Regard’s
Syed Jahanzaib