Syed Jahanzaib Personal Blog to Share Knowledge !

September 30, 2016

Linux pppoe client disconnects after x minutes issue

Filed under: Linux Related, Mikrotik Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 9:02 PM

dc


Scenario:

Mikrotik is acting as PPPoE server.

We have one Linux (Ubuntu) base client and wants to connect it to pppoe server using pppoe client. We have created the pppoe dialer using ‘pppoeconfig‘ CLI tool. Its connecting fine using pon dsl-rpovider command.


Problem:

PPPoE Dialer is regularly disconnecting after x minutes and ` /var/log/syslog ` is showing following error

Sep 30 20:40:44 ubuntu pppd[4375]: No response to 4 echo-requests
Sep 30 20:40:44 ubuntu pppd[4375]: Serial link appears to be disconnected.
Sep 30 20:40:44 ubuntu pppd[4375]: Connect time 5.5 minutes.
Sep 30 20:40:44 ubuntu pppd[4375]: Sent 6536 bytes, received 2983 bytes.
Sep 30 20:40:44 ubuntu pppd[4375]: restoring old default route to eth0 [192.168.1.1]
Sep 30 20:40:50 ubuntu pppd[4375]: Connection terminated.
Sep 30 20:40:50 ubuntu pppd[4375]: Modem hangup

Possible Reason:

The other side of the PPP link probably doesn’t support LCP echo. I have seen this with Mikrotik base pppoe server. [zaib]


Solution:

Oddly it was a rear issue i guess and internet is a bit silent about it.

Any way put this line in the relevant ppp options file and try again.

[ Example: /etc/ppp/peers/dsl-provider ]

echo-requests

lcp-echo-interval 0

Save and redial connection by

poff dsl-provider

pon dsl-provider


Complete DSL-PROVIDER file for pppoe client config.


# pppoe dialer config sample, by
# syed jahanzaib

noipdefault
defaultroute
replacedefaultroute
hide-password
lcp-echo-interval 0
noauth
persist
#mtu 1492
#persist
#maxfail 0
#holdoff 20
plugin rp-pppoe.so eth0
usepeerdns
maxfail 0
persist
user "PPPOE-USER-ID"

Regard’s
Syed Jahanzaib

Advertisements

September 27, 2016

Howto add Simple VPN (PPTP) Server in Ubuntu

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 9:41 AM

VPN (PPTP) Server in Ubuntu


This post contains short notes on HOW-TO add simple pptp based VPN server in Ubuntu. It it useful in many scenarios example if you have Linux server in the office, and want to connect to it from anywhere in the world. OR you can use it in a situation where you want your billing server to act like a centralized billing system for all the remote locations NAS.

Ideally you should have static Public IP on this vpn server, but you can bypass this requirement by adding an DDNS name as well as a workaround like changeip.com. I remember that I did similar configuration at some philippine network and one at cloud.


Components Used:

OS = Ubuntu 12.4 on Cloud with static Public IP address
Clients = Windows 7 / Mikrotik Routerboards

Let’s Start…


Install PPTPD Package

Install PPTPD package in ubuntu by issuing following command

sudo apt-get -y install pptpd

Now Issue following to add vpn server options like local/remote ip addresses. I used echo command to directly insert text/data into the config files rather than editing by nano/vim. use whatever is easier for you.

# This will be the virtual ip of the vpn server
echo "localip 1.1.1.1" >> /etc/pptpd.conf

# Remote vpn client will get IP from this ip pool range
echo "remoteip 1.1.1.2-10" >> /etc/pptpd.conf

#This is to provide DNS , but in this example I really dont need natting for remote vpn client,
#as remote mikrotik will sue this server for radius autehnticaiton only, not as there default gateway 🙂
echo "ms-dns 8.8.8.8" >> /etc/pptpd.conf 

Add a TEST account for client in /etc/ppp/chap-secrets

echo "YOURID pptpd YOURPASSWORD *" >> /etc/ppp/chap-secrets

Description:

YOURID = username for remote vpn client
pptpd = service type
YOURPASS = Password for remote vpn client
* = any ip from the pool, if you want to provide same ip to client every time, add ip like 1.1.1.2


TIP:
To Assign static ip to any remote vpn client

To assign static ip to remove vpn client, you can use following

zaibid pptpd zaibpassword 192.168.100.25

Finally restart pptpd service …

/etc/init.d/pptpd restart

Make sure the service is started

service pptpd status

or

ps aux |grep pptpd

If you see it’s running, our Server End is probably OK.


VPN CLIENT in WINDOWS 7

Create a VPN dialer in Windows

Snapshots just for reference …

windows-7-vpn-client


VPN (pptp) CLIENT in Mikrotik RouterOS

vpn-connected-from-mikrotik-client


VPN Server Log Window.

(You can enable log in by uncomment word #debug in /etc/pptpd.conf)

tail -f /var/log/syslog
[Ubuntu]

Sep 27 04:15:22 radius pptpd[7582]: MGR: Launching /usr/sbin/pptpctrl to handle client
Sep 27 04:15:22 radius pptpd[7582]: CTRL: local address = 192.168.0.1
Sep 27 04:15:22 radius pptpd[7582]: CTRL: remote address = 192.168.0.234
Sep 27 04:15:22 radius pptpd[7582]: CTRL: pppd options file = /etc/ppp/pptpd-options
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Client X.X.X.X control connection started
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Received PPTP Control Message (type: 1)
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Made a START CTRL CONN RPLY packet
Sep 27 04:15:22 radius pptpd[7582]: CTRL: I wrote 156 bytes to the client.
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Sent packet to client
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Received PPTP Control Message (type: 7)
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Set parameters to 10000000 maxbps, 100 window size
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Made a OUT CALL RPLY packet
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Starting call (launching pppd, opening GRE)
Sep 27 04:15:22 radius pptpd[7582]: CTRL: pty_fd = 6
Sep 27 04:15:22 radius pptpd[7582]: CTRL: tty_fd = 7
Sep 27 04:15:22 radius pptpd[7582]: CTRL: I wrote 32 bytes to the client.
Sep 27 04:15:22 radius pptpd[7582]: CTRL: Sent packet to client
Sep 27 04:15:22 radius pptpd[7583]: CTRL (PPPD Launcher): program binary = /usr/sbin/pppd
Sep 27 04:15:22 radius pptpd[7583]: CTRL (PPPD Launcher): local address = 192.168.0.1
Sep 27 04:15:22 radius pptpd[7583]: CTRL (PPPD Launcher): remote address = 192.168.0.234
Sep 27 04:15:22 radius pppd[7583]: Plugin /usr/lib/pptpd/pptpd-logwtmp.so loaded.
Sep 27 04:15:22 radius pppd[7583]: pppd 2.4.5 started by root, uid 0
Sep 27 04:15:22 radius pppd[7583]: Using interface ppp0
Sep 27 04:15:22 radius pppd[7583]: Connect: ppp0 <--> /dev/pts/1
Sep 27 04:15:22 radius pptpd[7582]: GRE: Bad checksum from pppd.
Sep 27 04:15:22 radius pptpd[7582]: GRE: accepting packet #0
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #1
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #2
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #3
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #4
Sep 27 04:15:23 radius pppd[7583]: peer from calling number "X.X.X.X" authorized
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #5
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #6
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #7
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #8
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #9
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #10
Sep 27 04:15:23 radius pppd[7583]: MPPE 128-bit stateless compression enabled
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #11
Sep 27 04:15:23 radius pptpd[7582]: GRE: accepting packet #12
Sep 27 04:15:24 radius pptpd[7582]: GRE: accepting packet #13
Sep 27 04:15:24 radius pptpd[7582]: GRE: accepting packet #14
Sep 27 04:15:24 radius pppd[7583]: Cannot determine ethernet address for proxy ARP
Sep 27 04:15:24 radius pppd[7583]: local IP address 192.168.0.1
Sep 27 04:15:24 radius pppd[7583]: remote IP address 192.168.0.234
Sep 27 04:15:52 radius pptpd[7582]: CTRL: Received PPTP Control Message (type: 5)
Sep 27 04:15:52 radius pptpd[7582]: CTRL: Made a ECHO RPLY packet
Sep 27 04:15:52 radius pptpd[7582]: CTRL: I wrote 20 bytes to the client.
Sep 27 04:15:52 radius pptpd[7582]: CTRL: Sent packet to client
Sep 27 04:15:53 radius pptpd[7582]: GRE: accepting packet #15

TIP:

Forward PPTP port from mikrotik to local VPN server


/ip firewall nat
add action=dst-nat chain=dstnat comment="Forward PPTP protocol TCP/1723 to Local Radius where VPN server is configured" dst-address=PUBLI.IP dst-port=1723 protocol=tcp \
to-addresses=192.168.100.1

September 7, 2016

Sample PHP page to insert data into kannel/sqlbox

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:56 AM

centralized-sms-ystem


Other kannel references:

Before Proceeding further, read the following links for Kannel Installation and configure SLQBOX.

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

https://aacable.wordpress.com/2016/09/06/kannel-sms-gateway-delivery-reports-in-mysql-via-sqlbox/


Following is a sample PHP file which can be called by BASH/CURL or any remote system to send SMS via inserting the message into SND database which will send SMS to destination with the message. So it will act as a centralized SMS submission process that will keep record of every outgoing Sent SMS along with any detail that we requires, example sender (the requesting party), recipient, and the message (OR etc etc)

This is just for reference purposes of DOABLE things, and by no means it should be deployed in production environment. These are just short references, and must be modified according to the safety standards.

 


Background:

Currently we are using KANNEL as SMS gateway to send messages to users,admins about various events and information. This SMS Gateway is used by the Linux , Mikrotik Router, and windows base systems. But the problem is that using standard kannel HTTP submission method, we cannot track any message and its status. Its like blind system. And recently it was asked by few OP to track the Sent sms Numbers, status for a particular given time or over all month report.

 

Standard Examples of sending SMS via kannel HTTP submission:

Linux Example:

curl "$KURL/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$USR.sms
s

Windows Example:

C:\wget\wget.exe  "http://192.168.0.1:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=03333021909&text=INFO: [TimeAndDate] UPS is working on KESC now"

Mikrotik Example:

/tool fetch url="$KURL\?username=$KID&password=$KPASS&to=$cell1+$cell2&text=$MSGUPSMS"

Requirements:

I wanted to have a simple PHP page that can be called by any system with only three fields

1- Sender (Example server1 or ExpiryDaemon)
2- To (mobile number of the recipient)
3- Message (which is usually large with multiple lines)

One Example for Linux base system as following:

The scheduled bash script runs on Linux base billing system which submits various messages to users like account expiry, password change info, etc via submitting messages with destination numbers using CURL.


Solution:

For this purpose we made simple PHP page that can be called from any remote system and will act using the provided url parameters. thanks to mr tapan.kumar for providing sample php page for this purpose.

Example of CURL/BASH script which fetch data from mysql table for  logged in alert for manager/admin into panel.

KANNELURL="127.0.0.1"
SENDER="Billing-adminlogin"
MOBILE="1234567890"
MSG="Dear $MANAGER ,
You have successfully logged-in to billing admin panel.
ID = $ID
DATE = $DATE
Thank You"

# Curl example that will call test.php with 3 parameters ...
curl -G "http://$KANNELURL/test.php?sender=php&to=$MOBILE" --data-urlencode "message=$MSG"

 

TEST.PHP SAMPLE


<?php
$host = 'localhost';
$user = 'root';
$password = 'SQLPASS';
$database = 'kannel_db';
$port = 3306;
$conn = mysql_connect($host, $user, $password);
mysql_select_db($database, $conn);
if (!$conn) {
die("Unable to connect with MySQL database.\n");
}
$sender = trim($_GET['sender']);
$to = trim($_GET['to']);
$message = trim($_GET['message']);
// I have already configured the SQLBOX so any entry made into SND DB, it will automatically move
// data into send_sms and will update the SND status as well on receiving DLR
$sqlQuery = "insert into snd (snd_sender,snd_to,snd_txt) values('$sender','$to','$message')";
mysql_query($sqlQuery);
echo 'OK';
?>

When the curl will call test.php , it will simply enter the data into SND DB.

Example:

snd_example

and from mikrotik it would be like

mikrotik-send-sms

 

This way we can have a centralized SMS submission system , which will keep track of every SENT sms with the details we require according the local need. It can be modified as per the requirements. These are just my findings from Google and tips with some working sample code from few resources.

 

Regard’s
Syed Jahanzaib

September 6, 2016

Kannel SMS Gateway Delivery Reports in mySQL via sqlbox

Filed under: Linux Related — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 11:47 AM

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/kannel.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

final-success-1

 

#snd table

final_scucess

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

%d bloggers like this: