Syed Jahanzaib Personal Blog to Share Knowledge !

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

August 24, 2016

Radius Manager Connection Tracking System for Mikrotik

Filed under: Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:35 AM

trackme

As requested by few OP, following is a short technical reference guide on how you can enable TCP/UDP connections in Mikrotik to be stored in Radius manager connection tracking database so that you can view user base connection tracking report. In my personal view, it’s not much useful and at low end hardware it creates bottleneck, but if you have good resources with some fast storage (like SSD or RAID-10) it is better to set it up.

CTS is best described in the manual by dmasoftlab manual as mentioned below

Radius Manager has a special feature: the Connection Tracking System. It is available only in Radius Manager CTS version or higher. With the help of it the system can track and log all the TCP and UDP connections for all registered (online) users.

By default when You install the CTS enabled version of Radius Manager, it will use the default CTS database (CONNTRACK). It is strongly recommended to use a separate database host for the CONNTRACK database, due to the enormous amount of data stored daily. It can be even a 100-500 MegaBytes (and in my personal experience it can grow as much as 3-5 GB on busy network, ZAIB) per day. Fast disks (like SSD in RAID 10 mode,  zaib) are also recommended to be able to seek and store the data in real time. Radius Manager periodically stores the traffic data to CONNTRACK database (typically in
every 5–60 seconds).


Mikrotik (6.x) Configuration to enable Firewall Logging to remote server (RM)

If you have already configured the radius manager, then the conntrack database also get configured via the installation script. Next step is to enable the firewall logging in the mikrotik router so that mikrotik can send the categorized TCP/UDP data to radius manager conntrack database.

In mikrotik, open terminal and issue following commands …

In below example, we have following IP scheme.

PPPoE users ip pool = 172.16.0.1-172.16.0.255
Radius Manager IP  = 101.11.11.254

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=tcp connection-state=new action=log

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=udp connection-state=new action=log

/system logging action add name=rmctszaib remote=101.11.11.254 target=remote remote-port=4950

/system logging add topics=firewall action=rmctszaib

If you don’t see any errors , you are good to Go.


RADIUS MANAGER SECTION:

1. Radius Manager should be licensed with CTS level. Basic license wont gonna work ..

2. Make sure that  RMCONNTRACK service is UP and running.To make sure it’s running , get its process by

ps aux |grep rmconntrack

and you should get result something like following

root@radius:/# ps aux |grep rmconntrack
root xxxx 0.0 0.0 xxxxxx xxx ? Ssl 13:22 0:00 /usr/local/bin/rmconntrack

if you see its running, proceed further , otherwise you may start it manually by

rmconntrack –x

Now login to RM ACP  >  Reports  >  Connection Report

Here you can get report for all or individual user.

As showed in the image below …

c1

 

c2

 


Regard’s
Syed Jahanzaib

 

 

1

August 8, 2016

UPS Monitor BASH Script with Multi Matching ‘IF Statements’

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:26 PM

Following script is just For my personal reference Purpose only!!!

 

script ups output

 

Emerson UPS Info via DUDE

kesc

 


Scenario:

In our datacenter , we have Emerson 10kva UPS x 2 in failover + load balancing mode. Yesterday we got some electric failure resulting in UPS shutdown after its batteries banks got drained. This UPS doesn’t have advance alerting system other than simple traditional alerts of light failure and restore via email. Therefore I made some customized scripts which are now monitoring the UPS status with more deeper digging.

Example: If the Electricity failure occurs (or low voltage under xxx value) AND the remaining backup time goes below our defined threshold, then it should send email + sms alert to the admin. So basically we will match at least two conditions.



#!/bin/bash
# Function: DATA CENTER UPS KE INPUT and VOLTAGE ALERT SCRIPT
# Scheduled Script to check data center KE INPUt + UPS Backup remain time via SNMP after every 1 mnt.
# If it found lower than our defined limit, send sms or email Alerts, but do not repeat it until next status change.
# Script Designed by Syed Jahanzaib
# aacable at hotmail dot com
# http://aacable . wordpress . com
# Created : 8th-Aug-2016  @ AGP
# set -x

# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
DATE=`date`

# COMPANY NAME
COMPANY="ZABBO Pvt Ltd."
FOOTER="Powered by Syed Jahanzaib / AGP Pvt. Ltd."

# Hostname
HOSTNAME=`hostname`

# KANNEL SMS Gateway Info
KANNELURL="KANNEL-URL:13013"
KANNELID="kannel"
KANNELPASS="KANNEL-PASS"
CELL1="03333021909"

# GMAIL DETAILS
GMAILID="YOURMGAILID@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="aacable@hotmail.com"
SENDMAILAPP="/temp/sendEmail-v1.56/sendEmail"
STATUS_HOLDER="/tmp/upsmon.txt"

# UPS IP / SNMP RELATED INFO
UPSIP="$1"
SNMPSTR="public"
# OID For Emerson rack base UPS 10kvs with snmp web card
UPSINPUTOID="1.3.6.1.4.1.13400.2.16.2.2.1.0"
UPSTIMEOID="1.3.6.1.4.1.13400.2.16.2.5.2.0"

# Minimum Backup time threshold limit, below this action can be triggered if matched with second trigger
UPSTIMELIMIT="30"
# Minimum KE INput electric voltages limit, below this , both condition can be matched.
KELIMIT="190"

# Check if UPS IP is accessible or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c 1 $UPSIP) == @(*100% packet loss*) ]]; then
echo "ALERT ..... UPS IP $UPSIP is DOWN ..."
exit 1
fi

# Check if SNMP query is accessible or not, if not then EXIT immediately
SNMPRESULT="/tmp/snmpoutput.txt"
touch $SNMPRESULT
snmpwalk -v1 -c agp $1 $UPSINPUTOID > $SNMPRESULT
if [ ! -s $SNMPRESULT ]; then
echo "ALERT ALERT: UPS IP $UPSIP - SNMP NOT Responding"
exit 1
fi

# If temporary status holder is not present , then create it,
# forumla is being applied to prevent repeated attempt of file creation / zaib
if [ ! -f $STATUS_HOLDER ]; then
echo -e "Creating Status Holder for first time usage"
touch $STATUS_HOLDER
fi

# Check for KE INPUT via SNMP query, make sure to chhange it accordingly
# currently i am using emerson UPS with snmp web card, as example
TIMEQ=`snmpwalk -v2c -Oqv -c $SNMPSTR $UPSIP $UPSTIMEOID`
KEINPUT=`snmpwalk -v2c -Oqv -c $SNMPSTR $UPSIP $UPSINPUTOID`
# divide time formula which comes like 2100 , so divide with /100 so we get 21 actually, just an example
KE=$(($KEINPUT / 100))

# Message template for up n down alerts
LOWMSG="$COMPANY Data Center UPS K.E Input have failed.
UPS IP = $UPSIP
Current K.E Input = $KE Volts
Remaining backup time = $TIMEQ mnts
Please check urgent"

OKMSG="$COMPANY Data Center UPS KESC Input have restored.
UPS IP = $UPSIP
Current K.E Input = $KE Volts
Remaining backup time = $TIMEQ mnts"

NORMSG="All Seems OK !"
UPMSG="/tmp/upmsg.sms"
DOWNMSG="/tmp/downmsg.sms"

# SMS and email msg fromat for up n down
MSG_UP="$COMPANY Alert:

$OKMSG @ $DATE
$FOOTER"

LOWMSG="$COMPANY Alert:

$LOWMSG @ $DATE
$FOOTER"

# Print Current fetched Values
echo -e "
UPS IP / PING response = $UPSIP / Ping Responding OK
UPS SNMP Response = OK / SNMP query Accessible
Minimum Time Threshold = $COL_GREEN $UPSTIMELIMIT mnts $COL_RESET
Minimum KE Input Threshold = $COL_GREEN $KELIMIT volts $COL_RESET
Current Backup Time = $COL_RED $TIMEQ mnts $COL_RESET
Current K.E Input = $COL_RED $KE volts $COL_RESET"

# Check if ke failure alert have already been sent, if yes then inform accordingly.
if [[ "$TIMEQ" -lt "$UPSTIMELIMIT" ]] && [[ "$KE" -lt "KELIMIT" ]]; then
echo -e "$COL_RED $LOWMSG $COL_RESET"
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 1 ]; then
echo -e "$COL_RED SMS/Email for DOWN have already been sent $COL_RESET"
fi
fi

# Matching Formula starts here .. zaib
# IF KE INPUT result is less than our defined limit and remaining time is low as well, , 
# then send sms and email, IF NOT ALREADY SENT
if [[ "$TIMEQ" -lt "$UPSTIMELIMIT" ]] && [[ "$KE" -lt "KELIMIT" ]]; then
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 0 ]; then
echo -e "$COL_RED ALERT: $LOWMS $(date) / SENDING SMS/Email .... $COL_RESET"
echo "$LOWMSG" > $DOWNMSG

# Sending DOWN SMS via KANNEL
cat $DOWNMSG | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

# Sending Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$LOWMSG @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DOWNMSG -o message-content-type=text

echo "TEMP" > $STATUS_HOLDER
fi
else
echo -e "$COL_GREEN $NORMSG ... $COL_RESET"
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 1 ]; then
echo -e "$COL_GREEN $COMPANY ALERT : $OKMSG $(date) / SENDING OK SMS/Email .... $COL_RESET"
echo "$OKMSG" > $UPMSG

# Sending UP SMS via KANNEL
cat $UPMSG | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

# Sending UP / OK Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$OKMSG @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$UPMSG -o message-content-type=text
sed -i "/TEMP/d" "$STATUS_HOLDER"
fi
fi

# UPS Monitor Script Ends Here
# Syed Jahanzaib / aacable @ hotmail . com
# http:// aacable . wordpress . com


 

failure

 


Second Version / uPDATED 16-8-2016  12:05pm


#!/bin/bash
#set -x

# Function: DATA CENTER UPS KE INPUT and VOLTAGE ALERT SCRIPT
# Scheduled Script to check data center KE INPUt + UPS Bacnkup remain time via SNMP after every 1 mnt.
# If it found high or above our defined limit, send sms or email Alerts, but donot repeat it untill next status change.
# Script Designed by Syed Jahanzaib
# aacable at hotmail dot com
# http://aacable . wordpress . com
# Colors Config . . . [[ JZ . . . ]]

ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
DATE=`date`

# COMPANY NAME
COMPANY="*AGP*"
FOOTER="Powered by Syed Jahanzaib / AGP Pvt. Ltd."
TEMPWORD="UPSMON"

# Hostname
HOSTNAME=`hostname`

# KANNEL SMS Gateway Info
KANNELURL="127.0.0.1:13013"
KANNELID="KANNEL-USER"
KANNELPASS="KANNEL-PASS"
CELL1="03333XXXXXX"

# GMAIL DETAILS
GMAILID="YOURGMAIL@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="TO-1@hotmail.com"
SENDMAILAPP="/temp/sendEmail-v1.56/sendEmail"
STATUS_HOLDER="/tmp/upsmon.txt"

# UPS RELATED INFO
UPSIP="$1"
SNMPSTR="public"
UPSINPUTOID="1.3.6.1.4.1.13400.2.16.2.2.1.0"
UPSTIMEOID="1.3.6.1.4.1.13400.2.16.2.5.2.0"
UPSTIMELIMIT="10"
KELIMIT="180"

# Check if UPS is accessibel or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c 1 $UPSIP) == @(*100% packet loss*) ]]; then
echo "ALERT ..... UPS IP $UPSIP is DOWN ..."
exit 1
fi
SNMPRESULT="/tmp/snmpoutput.txt"
touch $SNMPRESULT
snmpwalk -v1 -c agp $1 $UPSINPUTOID > $SNMPRESULT
if [ ! -s $SNMPRESULT ]; then
echo "ALERT ALERT: UPS IP $UPSIP - SNMP NOT Repsonding"
exit 1
fi

# If temporary status holder is not present , then create it,
# forumla is being applied to prevent repeated attempt of file creation / zaib
if [ ! -f $STATUS_HOLDER ]; then
echo -e "Creating Status Holder for first time usage"
touch $STATUS_HOLDER
fi

# Check for KE INPUT via SNMP query, make sure to chhange it accordingly
# currently i am using emerson UPS with snmp web card, as example
TIMEQ=`snmpwalk -v2c -Oqv -c $SNMPSTR $UPSIP $UPSTIMEOID`
KEINPUT=`snmpwalk -v2c -Oqv -c $SNMPSTR $UPSIP $UPSINPUTOID`
KE=$(($KEINPUT / 100))

# divide time formula which comes like 2100 , so divide with /100 so we get 21 actuall, just an example
LOWMSG="UPSMON - $COMPANY UPS CRITICAL ALERT: Data Center UPS working on batteries @$DATE & remainging timings are $TIMEQ mnts.

UPS IP / PING response = $UPSIP / Ping Responding OK
UPS SNMP Response = OK / SNMP query Accessible
Minimum Time Threshold = $UPSTIMELIMIT mnts
Minimum KE Input Threshold = $KELIMIT volts
Current Backup Time = $TIMEQ mnts
Current K.E Input = $KE volts
###############################################################
FINAL RESULT = ALERT / K.E failed. UPS working on Batteries. #
###############################################################

$FOOTER"

OKMSG="UPSMON - $COMPANY Data Center UPS K.E Input have restored @ $DATE

UPS IP / PING response = $UPSIP / Ping Responding OK
UPS SNMP Response = OK / SNMP query Accessible
Minimum Time Threshold = $UPSTIMELIMIT mnts
Minimum KE Input Threshold = $KELIMIT volts
Current Backup Time = $TIMEQ mnts
Current K.E Input = $KE volts

#################################################################
FINAL RESULT = OK now / K.E restored. UPS working on K.E Input #
#################################################################

$FOOTER"

NORMSG="All Seems OK @ $DATE !"
UPMSG="/tmp/upmsg.msg"
DOWNMSG="/tmp/downmsg.msg"

UPMSG_SMS="/tmp/upmsg.sms"
DOWNMSG_SMS="/tmp/downmsg.sms"
# SMS and email msg fromat for up n down
MSG_UP_SUB="UPSMON - $COMPANY UPS Info: Data Center UPS K.E restored @ $DATE"
MSG_DOWN_SUB="UPSMON - $COMPANY UPS CRITICAL Alert: UPS working on batteries @$DATE & remainging timings are $TIMEQ mnts."
# Print Ok or ALERT Values according to the following formula
if [[ "$TIMEQ" -lt "$UPSTIMELIMIT" ]] && [[ "$KE" -lt "$KELIMIT" ]]; then
# PRINT FAILURE MESSAGE BY ZAIB ---------
echo "
######################################################################
UPSMON - Script to test KE. Input and battery remaining time @$DATE. #
######################################################################

UPS IP / PING response = $UPSIP / Ping Responding OK
UPS SNMP Response = OK / SNMP query Accessible
Minimum Time Threshold = $UPSTIMELIMIT mnts
Minimum KE Input Threshold = $KELIMIT volts
Current Backup Time = $TIMEQ mnts
Current K.E Input = $KE volts

###############################################################
FINAL RESULT = ALERT / K.E failed. UPS working on Batteries. #
###############################################################

$FOOTER"
else
echo "
######################################################################
UPSMON - Script to test KE. Input and battery remaining time @$DATE. #
######################################################################

UPS IP / PING response = $UPSIP / Ping Responding OK
UPS SNMP Response = OK / SNMP query Accessible
Minimum Time Threshold = $UPSTIMELIMIT mnts
Minimum KE Input Threshold = $KELIMIT volts
Current Backup Time = $TIMEQ mnts
Current K.E Input = $KE volts

#########################
FINAL RESULT = ALL OK. #
#########################

$FOOTER"
fi

# Matching Formula starts here .. zaib
# IF KE INPUT result is greater the our defined limit, then give alert
if [[ "$TIMEQ" -lt "$UPSTIMELIMIT" ]] && [[ "$KE" -lt "$KELIMIT" ]]; then
echo ""
if [ $(grep -c "$TEMPWORD" "$STATUS_HOLDER") -eq 1 ]; then
echo "SMS/Email for DOWN have already been sent"
fi
fi

# IF KE INPUT result is less then our defined limit, then send sms and email, IF NOT ALREAY SENT
if [[ "$TIMEQ" -lt "$UPSTIMELIMIT" ]] && [[ "$KE" -lt "$KELIMIT" ]]; then
if [ $(grep -c "$TEMPWORD" "$STATUS_HOLDER") -eq 0 ]; then
echo "
$MSG_DOWN_SUB / SENDING SMS/Email ....
"
echo "$LOWMSG" > $DOWNMSG
echo "$MSG_DOWN_SUB" > $DOWNMSG_SMS

# Sending DOWN SMS via KANNEL
cat $DOWNMSG_SMS | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

# Sending Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$MSG_DOWN_SUB" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DOWNMSG -o message-content-type=text

echo "$TEMPWORD" > $STATUS_HOLDER
fi
else
# echo -e "$NORMSG ..."
echo -e ""
if [ $(grep -c "$TEMPWORD" "$STATUS_HOLDER") -eq 1 ]; then
echo -e "
$MSG_UP_SUB / SENDING OK SMS/Email ....
"
echo "$OKMSG" > $UPMSG
echo "$MSG_UP_SUB" > $UPMSG_SMS
# Sending UP SMS via KANNEL
cat $UPMSG_SMS | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

# Sending Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$MSG_UP_SUB" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$UPMSG -o message-content-type=text
fi
fi

# UPSMON.SH Script Ends Here
# Syed Jahanzaib / aacable @ hotmail . com
# http:// aacable . wordpress . com


 

Regard’s
Syed Jahanzaib

 

August 5, 2016

BASH script to monitor Remote Host Port with Ping

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

portmonitor

Following script was designed for an OP who wanted to monitor his FTP Media sharing server network connectivity along with application running status. Previously the OP was using simple ping script to monitor the FTP server connectivity status, but later realized that most of time the FTP server network connectivity remains OK, but the FTP application (in this particular case its HFS) get crashes after prolonged use. So it was must to monitor the application PORT along with the network connectivity as well.


Requirements:

  • Check remote host network connectivity by ping, if found not responding, do not check further to query port status, send alert ping connectivity down alert 1 time until next status change,
  • If Ping responds ok, then check the port status, if found not responding, then send alert 1 time until next status change.

Solution:

I made following script which checks for network connectivity by PING and then query port running status using ‘nc’. This is just an example. You can use your own techniques to acquire the same result. This is fully tested and working script. There are many other ways to do the same like using mikrotik netwatch tool which is way too simple, or use any NMS app like Nagios, or DUDE which have good GUI control so no need to do coding in the dark : )

Surely this contains too much junk or some unwanted sections, so you may want to trim it according to your taste and requirements.

Regard’s
Syed Jahanzaib

 

the Script!

  • mkdir /temp
  • cd /temp
  • touch portmon.sh
  • chmod +x portmon.sh
  • nano portmon.sh

and paste following, make sure to edit all info accordingly…


#!/bin/sh
#set -x
# Script to check HOST ping and specific port. / zaib
# If found Down, then send one time sms/email until the next status change.
# which prevent repeatedly sms for same status.
# useful to monitor any application.
# Querying port is done by using 'nc' tool. you can use your own customized methods to query about any thing.
# For SMS we used local KANNEL as sms gateway
# For Email, we have used SENDEMAIL to send email via GMAIL. its a very simple tool and you can use your gmail account.
# This script took around 6 hours to complete because of multiple checks for up n down with prevention of repeated sms/email..
# You are free to use ,modify or distribute it. just keep the header intact.
# Regard's / # Syed Jahanzaib
# Email: aacable at hotmail dot com / http : // aacable . wordpress . com
# 5th August, 2016 / Jummah-tul-mubarak, 1st - yakum zilqad, 1437 Hijri

HOST="$1"
PORT="$2"
APPNAME="MIKROTIK"
HOSTNAME="hostname"
TEMP="temp"

# Check if folder exists, if not create one and continue, if already exists, ignore and process further ...
if [ ! -d "/$TEMP" ]; then
echo
echo
echo "/$TEMP folder not found, Creating it so all ping results should be saved there . . ."
mkdir /$TEMP
fi

COMPANY="ZABBO (Pvt) Ltd."
DATE=`date`

# GMAIL DETAILS
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="YOURMAIL@hotmail.com"
SENDMAIL="/temp/sendEmail-v1.56/sendEmail"

# SMS RELATED and KANNEL INFO
# KANNEL SMS Gateway Info
KANNELURL="127.0.0.1:13013"
KANNELID="kannel"
KANNELPASS="KANNEL-PASS"
CELL1="03333021909"

#########################################
# SMS/EMAIL Messages for PING UP / DOWN #
#########################################

MSG_DOWN_PING="ALERT: $DATE

$HOST not responding to ping request. Check connectivity.

$COMPANY"

MSG_UP_PING="INFO: $DATE

$HOST is reachable now. OK!

$COMPANY"
# Temporary file holder for storing sms/email PING related msgs which will be sent to admin.
MSGDOWNHOLDER_PING="/$TEMP/$HOST.down.msg"
MSGUPHOLDER_PING="/$TEMP/$HOST.up.msg"
touch $MSGDOWNHOLDER_PING
touch $MSGUPHOLDER_PING

echo "$MSG_DOWN_PING" > $MSGDOWNHOLDER_PING
echo "$MSG_UP_PING" > $MSGUPHOLDER_PING

#########################################
# SMS/EMAIL Messages for PORT UP / DOWN #
#########################################

# SMS/EMAIL Messages for PORT UP / DOWN
MSG_DOWN_PORT="ALERT: $DATE

$HOST not responding to $port port request. Check $APPNAME.

$COMPANY"

MSG_UP_PORT="INFO: $DATE

$HOST $PORT port is reachable/responding now. OK!

$COMPANY"

# Temporary file holder for storing sms/email PORT related msgs which will be sent to admin.
MSGDOWNHOLDER_PORT="/$TEMP/$HOST.port.down.msg"
MSGUPHOLDER_PORT="/$TEMP/$HOST.port.up.msg"
touch $MSGDOWNHOLDER_PORT
touch $MSGUPHOLDER_PORT
echo "$MSG_DOWN_PORT" > $MSGDOWNHOLDER_PORT
echo "$MSG_UP_PORT" > $MSGUPHOLDER_PORT

HOST_DOWN_ALERTONSCREEN="ALERT ..... HOST $HOST PING is DOWN @ $DATE ..."
HOST_UP_ALERTONSCREEN="INFO ..... HOST $HOST PING is OK @ $DATE ..."
HOST_PORT_DOWN_ALERTONSCREEN="ALERT .... System ping is responding OK, BUT PORT number $PORT on $HOST is down @ $DATE"
HOST_PORT_UP_ALERTONSCREEN="INFO .... $APPNAME server port nummber $PORT on $HOST is responding OK @ $DATE"

# How many PING attempts
PING_ATTEMPTS=1

# Temporary file holder for host ping status
HOST_PING_STATUS="/$TEMP/$HOST.ping"

# Temporary file holder for host port status
HOST_PORT_STATUS="/$TEMP/$HOST.port"
# Create temp file if not already present, usually for 1st time execution
touch $HOST_PING_STATUS
touch $HOST_PORT_STATUS

### START ACTION
#################################
# CHECK PING SECTION USING PING #
#################################

##################################
### CHECK PING STATUS - for DOWN #
##################################

# Check if HOST is accessibel or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c $PING_ATTEMPTS $HOST) == @(*100% packet loss*) ]]; then
echo "$HOST_DOWN_ALERTONSCREEN"
if [ $(grep -c "$HOST" "$HOST_PING_STATUS") -eq 1 ]; then
echo "Host DOWN SMS have already been sent to $CELL1 ... "
fi
if [ $(grep -c "$HOST" "$HOST_PING_STATUS") -eq 0 ]; then
echo "SENDING HOST PING DOWN SMS/EMAIL ..."
echo "$HOST" > $HOST_PING_STATUS

# Sending PING DOWN ALERT via EMAIL
$SENDMAIL -u "$HOST_DOWN_ALERTONSCREEN" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER_PING -o message-content-type=text

# Sending PING DOWN ALERT via SMS
cat $MSGDOWNHOLDER_PING | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

echo "SMS/EMAIL Sent DONE only 1 time until next status change ..."
fi
# If host is DOWN, then send sms/email alert & exit.
exit 1

################################
### CHECK PING STATUS - for UP #
################################

else
echo "$HOST_UP_ALERTONSCREEN"
if [ $(grep -c "$HOST" "$HOST_PING_STATUS") -eq 1 ]; then
echo "$HOST is responding OK now, and SMS/EMAIL Sent for UP sent DONE only 1 time until next status change ..."
sed -i "/$HOST/d" "$HOST_PING_STATUS"
# Sending PING UP ALERT via EMAIL
$SENDMAIL -u "$HOST_UP_ALERTONSCREEN" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGUPHOLDER_PING -o message-content-type=text

# Sending PING DOWN ALERT via SMS
cat $MSGUPHOLDER_PING | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

fi
fi

####################################
# CHECK PORT SECTION USING NC TOOL #
####################################

################################
### CHECK PORT STATUS - for UP #
################################

CHKPORT=`nc -z -w 1 $HOST $PORT; echo $?`
if [ $CHKPORT -eq 0 ]; then
echo -e "$HOST_PORT_UP_ALERTONSCREEN"

if [ $(grep -c "$HOST" "$HOST_PORT_STATUS") -eq 1 ]; then
echo "Sending UP SMS 1 time only"

# Sending PORT DOWN ALERT via EMAIL
$SENDMAIL -u "$HOST_PORT_UP_ALERTONSCREEN" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGUPHOLDER_PORT -o message-content-type=text

# Sending PORT DOWN ALERT via SMS
cat $MSGUPHOLDER_PORT | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

sed -i "/$HOST/d" "$HOST_PORT_STATUS"
fi
fi

##################################
### CHECK PORT STATUS - for DOWN #
##################################

if [ $CHKPORT -eq 1 ]; then
echo "$HOST_PORT_DOWN_ALERTONSCREEN"
if [ $(grep -c "$HOST" "$HOST_PORT_STATUS") -eq 1 ]; then
echo "Host PORT DOWN SMS have already been sent to $CELL1 ... "
fi
if [ $(grep -c "$HOST" "$HOST_PORT_STATUS") -eq 0 ]; then
echo "SENDING HOST PORT DOWN SMS ..."
echo "$HOST" > $HOST_PORT_STATUS
echo "SMS Sent FOR PORT DOWN DONE only 1 time until next status change ..."

# Sending PORT DOWN ALERT via EMAIL
$SENDMAIL -u "$HOST_PORT_DOWN_ALERTONSCREEN" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER_PORT -o message-content-type=text

# Sending PORT UP ALERT via SMS
cat $MSGDOWNHOLDER_PORT | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

fi
fi
####################
# SCRIPT ENDS HERE #
# SYED JAHANZAIB #
####################


Usage:

change the IP and port number.

  • /temp/portmon.sh 192.168.20.1 1235

You can add entry in cron like this

# Check for Service remote host port status
*/5 * * * * /temp/portmon.sh 192.168.20.1 1235

RESULT:

If ping not respond …

1- ping

 

If port not respond …

2- port

 

Email Alerts:

3- mail alert

 

SMS Alerts:

 

port alert1 (1)

 

port alert1 (2)

 


 

July 30, 2016

Data Center Temperature Monitoring via BASH Script

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

2016-07-30 06.21.20


Following is an bash script to monitor data center temperature via SNMP query from Emerson UPS. We can monitor the temperature via manageable ups builtin feature as well, but since i was required to do some extra functions which were not supported by the ups web panel, so i made following. although i removed few functions, but sharing it for general public as an example.

Following is an example code of SNMP query of temperature monitor from Emerson UPS ITA 10k UPS. [Sensor IRM-S02TH-001]

root@linux:/temp# snmpwalk -v2c -Oqv -c public  10.0.0.2  1.3.6.1.4.1.13400.2.62.2.1.2.0

2070

#!/bin/bash
# Function: DATA CENTER TEMPERATURE ALERT SCRIPT
# Scheduled Script to check data center temperature via SNMP after every 5 minutes.
# If it found high or above our defined limit, send sms or email Alerts, but donot repeat it untill next status change.
# Script Designed by Syed Jahanzaib
# aacable at hotmail dot com
# http://aacable . wordpress . com
#set -x

# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
DATE=`date`

# COMPANY NAME
COMPANY="ZABBO"

# Hostname
HOSTNAME=`hostname`

# KANNEL SMS Gateway Info
KANNELURL="10.0.0.1:13013"
KANNELID="kannel"
KANNELPASS="kannelpass"
CELL1="03333021909"

# GMAIL DETAILS
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="YOURGMAIL-PASS"
ADMINMAIL1="ADMIN-MAIL@hotmail.com"
SENDMAILAPP="/temp/sendEmail-v1.56/sendEmail"
STATUS_HOLDER="/tmp/datacentertemperature.txt"

# If temporary status holder is not present , then create it,
# forumla is being applied to prevent repeated attempt of file creation / zaib
if [ ! -f $STATUS_HOLDER ]; then
echo -e "Creating Status Holder for first time usage"
touch $STATUS_HOLDER
fi

# SMS and email msg fromat for up n down
MSG_UP="$COMPANY Alert:

$LOWMSG $DATE
$FOOTER"

MSG_DOWN="$COMPANY Alert:

$HIGHMSG @ $DATE
$FOOTER"

# UPSIP , example i m using is emerson UPS
UPSIP="10.0.0.2"
UPSTEMPOID="1.3.6.1.4.1.13400.2.62.2.1.2.0"
UPSSNMPCOM=public"

# set Temperaturte limit
TEMPLIMIT="24"

# Check for temperature via SNMP query, make sure to chhange it accordingly
# currently i am using emerson UPS with snmp web card, as example
TEMPRATURE=`snmpwalk -v2c -Oqv -c $UPSSNMPCOM $UPSIP $UPSTEMPOID`
# divide temperature formula which comes like 2100 , so divide with /100 so we get 21 actuall, just an example
TEMPFINAL=$(($TEMPRATURE / 100))

HIGHMSG="$COMPANY Data Center Temperature is HIGH, that is $TEMPFINAL"
LOWMSG="$COMPANY Data Center Temperature is OK , that is $TEMPFINAL"
FOOTER="Powered by Syed Jahanzaib"
UPMSG="/tmp/upmsg.sms"
DOWNMSG="/tmp/downmsg.sms"
# Print Values
echo -e "Maximum Temperature Allowed = $COL_GREEN $TEMPLIMIT c $COL_RESET
Current Temperature = $COL_RED $TEMPFINAL c$COL_RESET"

# Matchign Formula starts here .. zaib
# IF temperature result is greater the our defined limit, then give alert
if [ "$TEMPFINAL" -gt "$TEMPLIMIT" ]; then
echo -e "$COL_RED $HIGHMSG $COL_RESET"
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 1 ]; then
echo -e "$COL_RED SMS/Email have already been sent $COL_RESET"
fi
fi

# IF temperature result is greater the our defined limit, then send sms and email, IF NOT ALREAY SENT
if [ "$TEMPFINAL" -gt "$TEMPLIMIT" ]; then
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 0 ]; then
echo -e "$COL_RED ALERT: $HIGHMSG $(date) / SENDING SMS/Email .... $COL_RESET"
echo "$MSG_DOWN" > $DOWNMSG

# Sending DOWN SMS via KANNEL
cat $DOWNMSG | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-
# Sending Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$HIGHMSG @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DOWNMSG -o message-content-type=text

echo "TEMP" > $STATUS_HOLDER
fi
else
echo -e "$COL_GREEN $LOWMSG ... $COL_RESET"
if [ $(grep -c "TEMP" "$STATUS_HOLDER") -eq 1 ]; then
echo -e "$COL_GREEN $COMPANY ALERT : $HEADING $LOWMSG $(date) / SENDING SMS/Email .... $COL_RESET"
echo "$MSG_UP" > $UPMSG
# Sending UP SMS via KANNEL
cat $UPMSG | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-
# Sending Email via sendEmail tool app using GMAIL
$SENDMAILAPP -u "$LOWMSG @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$UPMSG -o message-content-type=text
sed -i "/TEMP/d" "$STATUS_HOLDER"
fi
fi

# Script Ends Here
# Syed Jahanzaib / aacable @ hotmail . com
# http:// aacable . wordpress . com

alert


Result:

High Temperature Alert Email Sample:

mailhigh

 

Low Temperature Alert Email Sample:

lowtemp


SMS RESULT:

2016-07-30 06.21.20

July 28, 2016

Lotus Notes / Copy – Duplicating prohibtited

Filed under: IBM Related, Uncategorized — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 5:02 PM

pmail.jpg


In our company, we have IBM Lotus Domino Mail Server which i managed myself. Getting Lotus Domino support is quite a tough job, especially if you dont have any support SLA with the IBM, which generally costs heavy amount in $. Therefore I have to manage things on my own mostly using google and with some common sense lol.

Today we received an email from a valid client, and when we tried to copy or reply him with history, we receive following error.

w2.PNG

It also happens if user have selected following in mail delivery options.

w1.PNG

Without going in much details (which is already available in greater details on the internet), here is how I managed to sort it.


Requirements: Domino Admin Client.

Open user mail file via Domino Admin Client.
Goto Create / Agent,

As showed in the image below …

formula.PNG

 

Make sure you select FORMULA as shown above, and copy paste following code …

FIELD $KeepPrivate := @DeleteField;

Save it with any name like “remove keep private” and exit.


Lotus Notes Client:

Now open Lotus Notes Client , goto inbox and open the affected email,

Now goto Action / and you will see the newly created agent name. click on it.

As showed in the image below …

agent.png

it may take just a second or two most, and will remove the restriction🙂

Enjoy !

Syed Jahanzaib

 

July 20, 2016

Mikrotik reboot alert with false detection checks

Filed under: Mikrotik Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 12:05 PM

reboot

Scenario:

We have a routerboard which have 2 wan pppoe-outX dialers. Gmail account is configured to send alerts. On the same LAN we have KANNEL sms gateway server which acts as a central sms gateway for sending receiving sms.

Disclaimer:
The script is designed for some specific network, it may not fit general public requirements, but still its a good idea to collect various scripting ideas for learning purposes and it may help in other tasks as well.


Requirements:

  • If the Router is rebooted , it should send us Email and SMS with the new WAN ip addresses.
  • It should check for both WAN connections status before acquiring IP addresses, if it won’t check for interface status and the dialer aren’t connected, the script will terminate, therefore this check must be added
  • It should check for UPTIME , if the UPTIME is less than X Minutes, then it should consider the RB is actually rebooted, this check was required to prevent false detection of reboot. In some ROS, it was a bug that the RB doesn’t gets rebooted but reload the OS and the scripts consider that the RB got rebooted while it actually dont. so this check need to be added.
  • Make sure you have already configured the /tools/emails section in RB to make email alerts work.

the SCRIPT !

use the following script, modify it as required. schedule it to run on system reboot only,

# Mikrotik reboot alert / UPTIME CHECK SCRIPT, with prevention of sending FALSE ALARM with optional Email and SMS Alert
# We are using local KANNEL as SMS gateway and GMAIL as mail relay server
# By Syed Jahanzaib
# https://aacable.wordpress.com
# Email : aacable at hotmail dot com
# Script Last Modified : 20th-JUL-2016 / 1100 Hours

# Get Uptime
:local UPTIME [/system resource get uptime]

# Set UPTIME Limit
:local UPTIMELIMIT "00:05:00"

# SET DATE TIME
:local date;
:local time;
:set date [/system clock get date];
:set time [/system clock get time];

# if uptime is less then uptime limit threshold value, then consider router is actually rebooted, and take action / zaib
:if ($UPTIME<$UPTIMELIMIT) do={
:log error "ALERT: Router was rebooted just before $UPTIMELIMIT Minutes therefore sending Reboot SMS / Email Alert";

# Sleep , this is added so that RB and the KANNEL services may start properly / zaib
:delay 180s

# GMAIL Setup
:local gmailid "YOUR-GMAIL-ID@gmail.com"
:local GMAILPASS "YOUR-GMAIL-PAS"
:global gmailsmtp
:set gmailsmtp [:resolve "smtp.gmail.com"];
:local COMPANY "ZAIB"

# KANNEL SMS Configuration
#If you dont have kannel sms gateway ignore this.
:local KURL "192.168.100.1"
:local KID "kannel"
:local KPASS "kannelpassword"

#Mobile numbers of Admin
:local cell1 "03333021909"
:local cell2 "0333xxxxxxx"

:global WAN1IP
:global WAN2IP

# in this RB,we have two pppoe-outx wan dialers, Check if dialer is present and connected,
# this check is added because if dialer is not connected script was terminating, so this check is added now

if ([/interface get pppoe-out1 disabled] = yes) do={ :log error "pppoe-out1 Interface disabled" }\
else={[:global WAN1IP [/ip address get [find where interface=pppoe-out1] address];]}

if ([/interface get pppoe-out2 disabled] = yes) do={ :log error "pppoe-out2 Interface disabled" }\
else={[:global WAN2IP [/ip address get [find where interface=pppoe-out2] address];]}

# email recipients, Set your email where you want to receive the alert
:local mailsendto
:set mailsendto aacableAThotmailDOTcom

:local mailsendto2
:set mailsendto2 secondadmin@gmailDOTcom
# Set Email Subject
:local es "$[/system identity get name] $[/system clock get date] $[/system clock get time] $COMPANY MIKROTIK got rebooted ! new ips $WAN1IP $WAN2IP and Uptime is $UPTIME"

# Set Email Body
:local eb "$[/system identity get name] $[/system clock get date] $[/system clock get time] $COMPANY MIKROTIK got rebooted ! \nNew ip address are \n WAN1IP = $WAN1IP \n WAN2IP = $WAN2IP \n\n Current Uptime is $UPTIME \n\n This script is powered by SYED JAHANZAIB !"

# Finally send email to both amind email addresses
/tool e-mail send to=$mailsendto subject=$es start-tls=yes body=$eb password=$GMAILPASS
/tool e-mail send to=$mailsendto2 subject=$es body=$eb start-tls=yes password=$GMAILPASS

:log warning "Email Done! for REBOOT ..."

:log warning "SENDING SMS FOR REBOOT ALERT VIA KANNEL RADIUS GATEWAY ."

# Send SMS using local KANNEL sms gateway
/tool fetch url="http://$KHOST:13013/cgi-bin/sendsms\?username=$KID&password=$KPASS&to=$cell1+$cell2&text=$COMPANY+MIKROTIK+Router+was+rebooted+and+now+restored+at+$date+$time+and+new+ips+are+$WAN1IP+$WAN2IP+++++++[$COMPANY+Pvt+Ltd]"

# if uptime is above then uptime limit threshold value, then no need to send SMS, this is to prevent false alarm.
} else={
:log error "System is above then $UPTIMELIMIT, so no need to send reboot sms/email alert!"
}

# Script , Ends Here
# Syed Jahanzaib
# aacable [at] hotmail.com


Results~

Screenshot_2016-07-20-11-52-17

 

rebootmail

 


Regard’s
Syed Jahanzaib~

July 13, 2016

Fighting with Spoofed Emails in IBM Lotus Domino using Symantec SMSDOM

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

s1

From the Diary / 12th July, 2016


We are using Symantec IBM lotus Domino as our mailing system for inbound/outbound emails & Symantec Mail Security as anti-spam mechanism. Managing heavily used production email server & fighting with the spam is a really tough job to do and requires continuous monitoring and most times requires additional work to do on regular basis.

From past few days, our email users were receiving lot of spoofed (faked advertisement / malware) emails pretending to be coming from there own email address and sometimes other legitimate users as well.. Subject was different every time , and source was dynamic too in the header. It was really annoying as user does not wants to block his email address in filters.

E-mail spoofing is the forgery of an e-mail header so that the message appears to have originated from someone or somewhere other than the actual source. Example you can receive email pretending to be coming from your own email address, strange and annoying as well too.

Adding SPF record is a good idea and every mail server mx record should have it. However I took another route which is a kind of workaround BUT at least it’s working amazingly good for me !.

Just to share my story. / z@ib

 

I made following rule in SMSDOM Content Filter Rules Section.


Description: BLOCK SPOOFED EMAILS RULE

Classification: Compliance Rule

This rule is for: Email Routing

Flow: Inbound

This rule is applied: Conditionally

Condition: Unless

Attributes: Sender/Author > myself@mycompany.com
[Exempt my few local servers email ids that are used to send backup/alerts emails to admin via using batch scripts]

Rule Expression: if Internet Domain Contains MYCOMAPNY.COM

Action: QUARANTINE the Document


Save the rule.

 

Images of rules,

1

2

3

4

 


End Results:

:) & now I can see many spoofed emails dropping in  the quarantine box and user’s inbox is clean and shiny.

spoofed_results_in_smsdom_quarantine

 


TIPS:

Test Spoofing

To test sending spoofed email, you can use following web site to do so ..

https://www.wormly.com/test_smtp_server

 

SPF RECORD: / zaib

To make SPF record on the DNS server, you can use following syntax

v=spf1 mx ip4:1.2.3.4 -all

Above record will allow all your MX records + IP 1.2.3.4 to send email from your domain, everything else is prohibited. But the mail servers or relays must support SPF protocol.

Or if you have two ISP links for primary and secondary mx, (two ip addresses), You can use following

v=spf1 mx ip4:1.2.3.4 ip4:5.6.7.8 -all

whereas 1.2.3.4 , 5.6.7.8 are the Public ip address of your email server.

OR something like

spf1

In above image, 1.2.3.4 is primary internet link IP for email server, and 5.6.7.8 is seconday backup internet link IP , so I added both in the record.


Regard’s

Syed Jahanzaib

 

July 1, 2016

Postfix as GMAIL relay to send email

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 1:45 PM

postfix-logo


This post was made to illustrate howto send emails via postfix mail server using Gmail. Previously I was using sendmail with gmail in combination but few times sendmail made troubles in new installation, therefore i switched to postfix which is quite simple as compared to sendmail complexity. I have tested it at various networks and so far found it reliable.

It can be used by other mail applications like mail utility, radius manager web bulk mail function, or any other you like.  We will use GMAIL as relay to send our emails using our gmail account. You need functional gmail account for this purpose, and make sure ‘allow less secure application’ is turned on to availe this function.

TIP:
We can also use this as centralized email server gateway so that all of our devices on the LAN like Mikrotik Router, Cisco Switches, Mobile Devices and others can send via this email gateway so that we can get rid of configuring email services at each system separately.

That’s why in some specific situation, I say “Work Smarter, Not Harder” / Za!b


Software Used:

OS : Ubuntu 12.4 / 32bit
Email Server : POSTFIX 2.9.6

Let’s Start …

First we need to update ubuntu apt-get and then install the postfix mail server application

Step#1

apt-get update && apt-get install postfix mailutils libsasl2-modules

When prompted for “General type of mail configuration” choose Internet Site.
When prompted for a “Mail name,” you can use default name.

Once above installation is done,

Create and edit new file which will store the Gmail ID and Password

touch /etc/postfix/sasl_passwd
nano /etc/postfix/sasl_passwd

and paste following [Make sure you replace YOURGMAILID+YOURPASS with valid gmail credentials.

[smtp.gmail.com]:587 YOURGMAILID@gmail.com:YOURPASS

Save & Exit.

Step#2

Now Make it accessible for root

chmod 600 /etc/postfix/sasl_passwd

Step#3

Edit postfix main configuration File by

nano /etc/postfix/main.cf

Remove all previous lines and paste following ….

#Postfix main configuration file / Syed Jahanzaib / aacable at hotmail dot com / http:// aacable . wordpress . com 
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no
append_dot_mydomain = no
readme_directory = no
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
myhostname = radius.localhost
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
myorigin = /etc/mailname
mydestination = radius.localhost, localhost.localhost, , localhost
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = all
relayhost = [smtp.gmail.com]:587
smtp_use_tls = yes
smtp_sasl_auth_enable = yes
smtp_sasl_security_options =
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_tls_CAfile = /etc/ssl/certs/ca-certificates.crt

Save & Exit.

Step#4

Use postmap command to compile and hash the contents of sasl_passwd. The results will be stored in your Postfix configuration directory in the file sasl_passwd.db.

postmap /etc/postfix/sasl_passwd

Step#5

Create folder to hold mails

mkfifo /var/spool/postfix/public/pickup

Step#6

Change the FROM address. It will be displayed at user inbox.

chfn -f 'YOUR COMPANY NAME' root

Step#7

Make sure you have Enable “Less Secure Apps” In Gmail
https://www.google.com/settings/security/lesssecureapps

Step#8

If sendmail was previously installed, then remove it and stop its service

apt-get remove sendmail
service sendmail stop

Step#9

Finally Restart POSTFIX service

sudo /etc/init.d/postfix restart

FINAL Step#10 / TESTiNG the Ride !

Now try to send email by using command in the terminal, change the email address to your email address

mail -s "Test subject from postfix by Syed.Jahanzaib" aacable@hotmail.com

After this it will ask cc: , just press enter
it will show blank cursor where you can type the email body, type it any text or leave it blank , then press press CTRL+D to finally send the email

Now at the same time in other window, Check mail log for any error

tail -f /var/log/mail.log

psotfix log

 

Result:

3

Older Posts »

Create a free website or blog at WordPress.com.