SMS Server Tools 3
This site is hosted by Kekekasvi.com
 Menu
Basic information:
Additional information:
Support:
Get SMS Server Tools 3:
Additional Options

 Sponsored links

 Search
Custom Search

 Visitor locations
 
 SMS Server Tools 3 Community
Welcome, Guest. Please login or register. Tue Jun 25, 2024 14:42
SMSTools3 Community » Sample scripts / setups Bottom

update; Sample eventhandler to store messages into SQL database

Login and Post Reply

Page:  1

Author Post
Member
Registered:
Jul 2010
Location: Amsterdam, Netherlands
Corrected missing Sender field in database by "FAILED" or "SENT" ;)

#!/bin/bash

# This is an example script that logs all events into an SQL database
# You need a MYSQL database as described in the documentation.
# Please read the documentation before using this script.

SQL_HOST=localhost
SQL_USER=root
SQL_PASSWORD="XxXxXx"
SQL_DATABASE=smsd
SQL_TABLE=sms_log

DATE=`date +"%Y-%m-%d %H:%M:%S"`
#Extract data from the SMS file
FROM=`formail -zx From: < $2 | sed 's/"//g'`

#Extra line for populating "Send" field in case of "FAILED" or "SENT"
MODEM=`formail -zx Modem: < $2 | sed 's/"//g'`

TO=`formail -zx To: < $2`
#Remove plus sign, spaces, minus and short number prefix
TO=`echo "$TO" | sed 's/ //g' | sed 's/+//g' | sed 's/s//g' | sed 's/-//g'`
SUBJECT=`formail -zx Subject: < $2`
SENT=`formail -zx Sent: < $2`
TEXT=`sed -e '1,/^$/ d' < $2`
TEXTSQL=$(echo "$TEXT" | sed 's/\\/\\\\/g' | sed 's/\"/\\\"/g')

#Set some SQL parameters
if [ "$SQL_PASSWORD" != "" ]; then
  SQL_ARGS="-p$SQL_PASSWORD";
else
  SQL_ARGS="";
fi
SQL_ARGS="-h $SQL_HOST -u $SQL_USER $SQL_ARGS -D $SQL_DATABASE -s -e"

#Insert a new entry into the SQL table

if [ "$1" = "FAILED" ] || [ "$1" = "SENT" ]; then
   mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,sender,receiver,msgid,text) values (\"$1\",\"$DATE\",\"$MODEM\",\"$TO\",\"$3\",\"$TEXTSQL\");";
elif [ "$1" = "RECEIVED" ]; then
   mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,receiver,text) values (\"RECEIVED\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\",\"$TEXTSQL\");";
elif [ "$1" = "REPORT" ]; then
   #Extract more data from the status report file
   DISCHARGE=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Discharge_timestamp:`
   MSGID=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Message_id:`
   STATUS=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Status: | cut -f1 -d,`

   if [ "$MSGID" != "" ]; then
     ID=`mysql $SQL_ARGS "select id from $SQL_TABLE where receiver=\"$FROM\" and type=\"SENT\" and msgid=\"$MSGID\" order by id desc limit 1;"`
     mysql $SQL_ARGS "update $SQL_TABLE set received=\"$DISCHARGE\",status=\"$STATUS\" where id=\"$ID\";"
   fi

fi
 
'bash' Syntax Highlight powered by GeSHi


Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
The update overrides "From" field, which was used in the SMS file. But anyways, the script is just a sample, and usually users modify it to fit their purposes. After the initial sample was written, there is also a "Number" field available in the message file, if it was set in the configuration. It could be used, as it's available in received files too.

Login and Post Reply

Page:  1

SMSTools3 Community » Sample scripts / setups Top

 
Time in this board is UTC.  

Privacy Policy   SMS Server Tools 3 Copyright © Keijo Kasvi.