SMS Server Tools

Logging into a mysql database

It is very easy to log events into a mysql database. During installation the script mysmsd will be copied to /usr/local/bin/mysmsd. This script adds a new entry to a table in the sql database whenever you sent or received a message.

Install this script as an eventhandler in /etc/smsd.conf. Please read configuring to learn how to set up an eventhandler.

If you need another eventhandler AND the sql log you can simply write a script that calls both programs.

Please ensure that you have the following programs installed:
mysql daemon
mysql client
kmysql or mysql navigator (optional)
formail
sed
cut

Modify the script /usr/local/bin/mysmsd if you use a different database than the default. The password may be empty if the user who starts smsd does not need a password to log into the mysql database.

Create a log database with the following structure

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  |      | PRI | NULL    | auto_increment |
| type        | char(16) | YES  |     | NULL    |                |
| sent        | datetime | YES  |     | NULL    |                |
| received    | datetime | YES  |     | NULL    |                |
| sender      | char(32) | YES  |     | NULL    |                |
| receiver    | char(32) | YES  |     | NULL    |                |
| status      | char(3)  | YES  |     | NULL    |                |
| msgid       | char(3)  | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

by entering these commands:

stefan@server> mysql -u root
mysql> create database smsd;
Query Ok...
mysql> use smsd;
Database changed.
mysql> create table sms_log (
    -> id int auto_increment not null,
    -> primary key(id),
    -> type char(16),
    -> sent datetime,
    -> received datetime,
    -> sender char(32),
    -> receiver char(32),
    -> status char(3),
    -> msgid char(3)
    -> );
Query Ok...

If you are searching for a graphical program that gives you mysql access then try the mysql navigator or kmysql.
But you can also use the mysql command line client. Example sql queries:

To count the messages sent to a destination number, enter:

mysql> select count(*) from sms_log where type="SENT" AND receiver="491722056395";

To find out wich short messages were sent to a destination number, enter:

mysql> select * from sms_log where type="SENT" AND receiver="491721234567";

To list all received messages, enter:

mysql> select * from sms_log where type="RECEIVED";

To show the whole table, enter:

mysql> select * from sms_log;

This is an example table with one of each possible event types:

+----+----------+---------------------+---------------------+--------------+--------------+--------+-------+
| id | type     | sent                | received            | sender       | receiver     | status | msgid |
+----+----------+---------------------+---------------------+--------------+--------------+--------+-------+
|  1 | RECEIVED | 2000-02-21 22:26:23 | 2002-06-06 12:16:23 | 491721234567 | MODEM1       |   NULL |  NULL |
|  2 | SENT     | 2002-06-06 12:16:34 | 2002-06-06 12:16:59 | somebody     | 491721234567 |      0 |   117 |
|  3 | FAILED   | 2002-06-06 12:16:48 | NULL                | somebody     | 491721234567 |   NULL |  NULL |
+----+----------+---------------------+---------------------+--------------+--------------+--------+-------+