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 | |
| destination | char(32) | YES | | NULL | |
| discharge | datetime | YES | | NULL | |
| status | int(11) | 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),
-> destination char(32),
-> discharge datetime,
-> status int
-> );
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 destination="491722056395";
To find out wich short messages were sent to a destination number, enter:
mysql> select * from sms_log where type="SENT" AND destination="491722056395";
To list all status reports from a destination number, enter:
mysql> select * from sms_log where type="REPORT" AND sender="491722056395";
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 | destination | discharge | status |
+----+----------+---------------------+---------------------+--------------+--------------+---------------------+--------+
| 1 | RECEIVED | 2000-02-21 22:26:23 | 2002-06-06 12:16:23 | 491722056395 | MODEM1 | NULL | NULL |
| 2 | SENT | 2002-06-06 12:16:34 | NULL | somebody | 491722056395 | NULL | NULL |
| 3 | FAILED | 2002-06-06 12:16:48 | NULL | somebody | 491722056395 | NULL | NULL |
| 4 | REPORT | 2000-02-21 22:26:23 | 2002-06-06 12:16:59 | 491722056395 | MODEM1 | 2000-02-21 22:27:02 | 0 |
+----+----------+---------------------+---------------------+--------------+--------------+---------------------+--------+