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 |
+----+----------+---------------------+---------------------+--------------+--------------+--------+-------+