Advanced

This chapter covers power-user topics that go around the normal web interface rather than through it: direct database access and SQL, and command-line database cleaning scripts. Most users will never need this chapter.

Connecting directly to the SMSEagle database

SMSEagle’s database runs on PostgreSQL. You can access it directly for reading or writing SMS messages via SQL queries, instead of going through the web interface or the API.

Database access for external applications is disabled by default. Enable it under Settings > Global settings > Application > Access to DB for external applications.

Once enabled, connect to the database from an external application using:

Host: the device’s IP address
Database name: smseagle
User: smseagleuser
Password: postgreeagle

Injecting a short SMS using SQL

A short text message (up to 160 characters):

INSERT INTO outbox (
    DestinationNumber,
    TextDecoded,
    CreatorID,
    Coding,
    Class,
    SenderID
) VALUES (
    '1234567',
    'This is a SQL test message',
    'Program',
    'Default_No_Compression',
    -1,
    'smseagle1'
);
INSERT INTO user_outbox (
    id_outbox,
    id_user
) SELECT CURRVAL(pg_get_serial_sequence('outbox','ID')), 1;

The message above belongs to the user with id_user 1 (the default admin user) - other users’ id_user values are in table public."user". The SenderID field identifies which modem sends the message: smseagle1 for modem 1, smseagle2 for modem 2.

Injecting a long SMS using SQL

Multipart messages also need a UDH header, stored as a hex string in the UDH field. Unless you have a specific reason to do this manually, use the API instead (see API and Developer Integration).

For a long text message, the UDH starts with 050003, followed by one byte used as a message reference (any hex value, but different for each message - D3 below), one byte for the total number of parts (02 below, unique per message sent to the same number), and one byte for the current part number (01 for the first part, 02 for the second, and so on).

A two-part long message looks like this:

INSERT INTO outbox (
    "DestinationNumber",
    "CreatorID",
    "MultiPart",
    "UDH",
    "TextDecoded",
    "Coding",
    "Class",
    "SenderID"
) VALUES (
    '1234567',
    'Program',
    'true',
    '050003D30201',
    'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, qui',
    'Default_No_Compression',
    -1,
    'smseagle1'
)
INSERT INTO outbox_multipart (
    "ID",
    "SequencePosition",
    "UDH",
    "TextDecoded",
    "Coding",
    "Class"
) SELECT
    CURRVAL(pg_get_serial_sequence('outbox','ID')),
    2,
    '050003D30202',
    's nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.',
    'Default_No_Compression',
    -1;
INSERT INTO user_outbox (
    id_outbox,
    id_user
) SELECT
    CURRVAL(pg_get_serial_sequence('outbox','ID')),
    1;

Adding a UDH leaves less room for text - the example above allows only 153 characters per message part.

Database cleaning scripts

A few scripts are available for deleting SMS messages from the database via the Linux CLI, located at /mnt/nand-user/scripts/:

Script	Usage	Purpose
`db_delete`	`./db_delete YYYYMMDDhhmm`	Delete SMS from Inbox and SentItems older than the given date
`db_delete_7days`	`./db_delete_7days`	Delete SMS from Inbox and SentItems older than 7 days
`db_delete_allfolders`	`./db_delete_allfolders`	Clean Inbox, SentItems, and Outbox - designed to run periodically via cron
`db_delete_select`	`./db_delete_select {inbox\|outbox\|sentitems\|trash}`	Delete SMS from one chosen folder

Adding a script to cron: create a file under /etc/cron.d/ (for example db_cleaner) with content such as:

0 0 1 * * root /mnt/nand-user/scripts/db_delete_allfolders

This example runs the cleaning script on the 1st of every month.

Forwarding logs to an external server

SMSEagle writes its logs through rsyslog. To send a copy of them to a central syslog server, add a forwarding rule over SSH. There is no setting for this in the web interface.

Log in to the device over SSH as root.
Create the file /etc/rsyslog.d/005-remote.conf with this rule, replacing the address, port and protocol with your server’s:
```
*.* action(type="omfwd" target="192.0.2.250" port="514" protocol="udp"
           action.resumeRetryCount="10"
           queue.type="linkedList" queue.size="10000")
```
- target - IP address or host name of the syslog server.
- port - port the server listens on, usually 514.
- protocol - udp or tcp.
- The queue settings keep up to 10000 messages on the device while the server is unreachable, and send them once it is back.
Check the configuration, then restart rsyslog:
```
rsyslogd -N1
systemctl restart rsyslog
```

Keep the file name. SMSEagle’s own logs (Application, API, Message, and the email, call, Signal and WhatsApp services) are written by rules in /etc/rsyslog.d/010-*.conf to 018-*.conf, and each of those rules stops further processing of its messages. A forwarding rule placed after them, for example at the end of /etc/rsyslog.conf, only forwards the system logs. The 005- prefix makes rsyslog read the forwarding rule first, so every message is forwarded.

To encrypt the log traffic with TLS, see the rsyslog guide Encrypting Syslog Traffic with TLS.