I can just paste it here, it is more a script than an app. I took away a lot of stuff that was messy, so haven’t tested this exact version but it’s more or less what I have running to monitor my phones.
On the phones, you need to do:
pkcon install sqlite
usermod -a -G sailfish-radio defaultuser
And the google sheet has a sheet called SMS, and looks like:
Where if you write in an SMS in C, and put a phone number in A, it will send that and then add the timestamp to B.
import os
import logging
import time
from pathlib import Path
import configparser
import gspread
from google.oauth2.service_account import Credentials
from paramiko import SSHClient, AutoAddPolicy, SSHException
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime
logging.basicConfig(level=logging.INFO, format='[%(asctime)s] %(message)s', datefmt='%m-%d %H:%M:%S')
logger = logging.getLogger(__name__)
# =============================================================
# CONFIGURATION — edit these values or override via env vars
# =============================================================
BASE_DIR = Path("/path/to/base/dir")
SERVICE_ACCOUNT_FILE = BASE_DIR / "service_account.json"
LAST_SYNC_FILE = BASE_DIR / "last_sync.txt"
SPREADSHEET_ID = "your-google-spreadsheet-id"
SHEET_NAME = "SMS"
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
# Email — can also be set via env vars SMTP_HOST / SMTP_PORT / SMTP_USERNAME / SMTP_PASSWORD / SMTP_FROM / SMTP_TO
SMTP_CFG = {
'host': os.getenv('SMTP_HOST', 'mail.example.com'),
'port': int(os.getenv('SMTP_PORT', '587')),
'username': os.getenv('SMTP_USERNAME', 'user@example.com'),
'password': os.getenv('SMTP_PASSWORD', 'password'),
'from': os.getenv('SMTP_FROM', 'user@example.com'),
'to': os.getenv('SMTP_TO', 'notify@example.com'),
}
# =============================================================
# server.cfg — one section per SMS server, e.g.:
#
# [SS1]
# ssh.host = 192.168.1.100
# ssh.username = defaultuser
# ssh.password = password
# db.url = /home/user/commhistory.db
# =============================================================
cfg = configparser.ConfigParser()
cfg.read(BASE_DIR / "server.cfg")
# ========== State ==========
email_queue = []
# ========== Helpers ==========
def load_server_config():
return {section: dict(cfg[section]) for section in cfg.sections()}
def get_sheet_client():
creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
return gspread.authorize(creds)
def get_sheet(gs_client):
return gs_client.open_by_key(SPREADSHEET_ID).worksheet(SHEET_NAME)
def get_last_synced_epoch():
if LAST_SYNC_FILE.exists():
with open(LAST_SYNC_FILE) as f:
return int(f.read().strip())
return int(time.time()) - 86400
def update_last_synced_epoch(epoch):
with open(LAST_SYNC_FILE, 'w') as f:
f.write(str(epoch))
# ========== Email ==========
def queue_email(subject, body):
email_queue.append({'subject': subject, 'body': body})
def send_queued_emails():
if not email_queue:
return
logger.info(f"📧 Sending {len(email_queue)} email(s)...")
sent = 0
for email in email_queue:
for attempt in range(2):
try:
msg = MIMEMultipart()
msg['From'] = SMTP_CFG['from']
msg['To'] = SMTP_CFG['to']
msg['Subject'] = email['subject']
msg.attach(MIMEText(email['body'], 'plain'))
with smtplib.SMTP(SMTP_CFG['host'], SMTP_CFG['port'], timeout=30) as server:
server.starttls()
server.login(SMTP_CFG['username'], SMTP_CFG['password'])
server.send_message(msg)
logger.info(f"✅ Email: {email['subject'][:50]}")
sent += 1
time.sleep(1)
break
except Exception as e:
logger.error(f"❌ Email failed (attempt {attempt+1}/2): {e}")
if attempt == 0:
time.sleep(3)
logger.info(f"📧 Sent {sent}/{len(email_queue)}")
email_queue.clear()
# ========== SSH ==========
def fetch_sms_via_ssh(host, port, username, password, db_path, last_epoch):
sql = (
f"sqlite3 {db_path} \"SELECT remoteUid || '\x1F' || "
f"replace(replace(freeText, '\n', ' '), '|', '/') || '\x1F' || startTime "
f"FROM Events WHERE type=2 AND direction=1 AND startTime >= {last_epoch} "
f"ORDER BY startTime ASC LIMIT 1000\""
)
for attempt in range(3):
try:
logger.info(f"🔌 SSH {host} (attempt {attempt+1}/3)")
client = SSHClient()
client.set_missing_host_key_policy(AutoAddPolicy())
client.connect(hostname=host, port=port, username=username, password=password, timeout=15)
stdin, stdout, stderr = client.exec_command(sql)
out = stdout.read().decode('utf-8', errors='replace')
err = stderr.read().decode('utf-8', errors='replace')
exit_code = stdout.channel.recv_exit_status()
client.close()
if exit_code != 0:
logger.error(f"❌ SSH failed (exit {exit_code}): {err}")
if attempt < 2:
time.sleep(5 * (attempt + 1))
continue
return ""
count = len([l for l in out.strip().split('\n') if l.strip()]) if out else 0
logger.info(f"{'✅' if count else '📭'} {host}: {count} message(s)")
return out
except (SSHException, TimeoutError, OSError) as e:
logger.error(f"❌ SSH error on {host}: {e}")
if attempt < 2:
time.sleep(5 * (attempt + 1))
return ""
def send_sms_via_ssh(host, port, username, password, phone_number, message):
escaped = message.replace('"', '\\"').replace('$', '\\$').replace('`', '\\`')
cmd = (
f'sudo dbus-send --system --print-reply --dest=org.ofono /ril_0 '
f'org.ofono.MessageManager.SendMessage '
f'string:"{phone_number}" string:"{escaped}"'
)
for attempt in range(3):
try:
logger.info(f"📡 SSH {host} → {phone_number} (attempt {attempt+1}/3)")
client = SSHClient()
client.set_missing_host_key_policy(AutoAddPolicy())
client.connect(hostname=host, port=port, username=username, password=password, timeout=10)
stdin, stdout, stderr = client.exec_command(cmd)
out = stdout.read().decode()
err = stderr.read().decode()
client.close()
if err:
raise RuntimeError(f"dbus error: {err}")
logger.info(f"✅ SMS sent to {phone_number}: {out.strip()[:80]}")
return True
except (SSHException, TimeoutError, OSError, RuntimeError) as e:
logger.warning(f"Retry {attempt+1}/3: {e}")
time.sleep(5 * (attempt + 1))
logger.error(f"❌ Failed to send SMS to {phone_number} after 3 attempts")
return False
# ========== Core Processing ==========
def process_incoming_sms(line, server_name, sheet):
if not line.strip():
return False, 0
parts = line.split("\x1F")
if len(parts) < 3:
logger.error(f"❌ Bad line: {line[:100]}")
return False, 0
uid_raw, text, timestamp = parts[0], parts[1], parts[2]
try:
ts_int = int(timestamp)
except ValueError:
logger.error(f"❌ Invalid timestamp: {timestamp}")
return False, 0
logger.info(f"📨 {uid_raw}: {text[:50]}")
for attempt in range(3):
try:
sheet.append_row([
uid_raw,
datetime.fromtimestamp(ts_int).isoformat(),
text,
'incoming',
'',
server_name,
])
logger.info(f"✅ Sheet ← {uid_raw}")
queue_email(
subject=f"📨 SMS from {uid_raw}",
body=(
f"From: {uid_raw}\n"
f"Server: {server_name}\n"
f"Time: {datetime.fromtimestamp(ts_int)}\n\n"
f"{text}"
),
)
return True, ts_int
except Exception as e:
logger.error(f"❌ Sheet write error (attempt {attempt+1}/3): {e}")
if attempt < 2:
time.sleep(2)
logger.error(f"❌❌ Failed to save {uid_raw}")
return False, 0
def process_outgoing_sms(sheet, servers):
"""
Sends queued outgoing messages from the sheet.
Row format: [phone_number, timestamp, message, direction, server_name, ...]
Unsent rows have phone + message filled but timestamp empty.
"""
logger.info("📤 Checking outgoing SMS...")
try:
rows = sheet.get_all_values()
except Exception as e:
logger.error(f"❌ Sheet read error: {e}")
return
if len(rows) < 2:
return
sent = 0
for i, row in enumerate(rows[1:], start=2):
if len(row) < 3:
continue
phone = str(row[0]).strip()
timestamp = row[1].strip()
message = row[2].strip()
server_name = row[4].strip() if len(row) > 4 else ""
if not phone or not message or timestamp:
continue
if not server_name:
logger.warning(f"⚠️ Row {i}: no server specified")
continue
server_cfg = servers.get(server_name)
if not server_cfg:
logger.warning(f"⚠️ Row {i}: unknown server '{server_name}'")
continue
success = send_sms_via_ssh(
server_cfg['ssh.host'], 22,
server_cfg['ssh.username'], server_cfg['ssh.password'],
phone, message,
)
if success:
sent += 1
now = datetime.now().isoformat()
try:
sheet.update_cell(i, 2, now)
sheet.update_cell(i, 4, 'outgoing')
except Exception as e:
logger.error(f"❌ Failed to update row {i}: {e}")
queue_email(
subject=f"📤 SMS sent to {phone}",
body=(
f"To: {phone}\n"
f"Server: {server_name}\n"
f"Time: {now}\n\n"
f"{message}"
),
)
logger.info(f"📤 Sent {sent} outgoing message(s).")
# ========== Main Loop ==========
def process():
logger.info("🚀 Starting SMS Sync Service")
try:
servers = load_server_config()
gsheets_client = get_sheet_client()
sheet = get_sheet(gsheets_client)
logger.info("✅ Connected to Google Sheets")
except Exception as e:
logger.error(f"❌ Startup failed: {e}")
return
cycle = 0
while True:
cycle += 1
logger.info(f"\n{'='*60}\n🔄 CYCLE #{cycle} — {datetime.now()}\n{'='*60}")
last_synced = get_last_synced_epoch()
logger.info(f"⏰ Fetching since: {datetime.fromtimestamp(last_synced)}")
try:
process_outgoing_sms(sheet, servers)
except Exception as e:
logger.error(f"❌ Outgoing error: {e}")
total_processed = 0
highest_timestamp = last_synced
for server_name, server_cfg in servers.items():
required = ('ssh.username', 'ssh.password', 'ssh.host', 'db.url')
if not all(k in server_cfg for k in required):
continue
try:
sms_data = fetch_sms_via_ssh(
server_cfg['ssh.host'], 22,
server_cfg['ssh.username'], server_cfg['ssh.password'],
server_cfg['db.url'], last_synced,
)
if not sms_data or not sms_data.strip():
continue
lines = [l for l in sms_data.strip().split('\n') if l.strip()]
logger.info(f"📦 {server_name}: {len(lines)} message(s)")
for line in lines:
parts = line.split("\x1F")
if len(parts) < 3:
continue
try:
ok, msg_ts = process_incoming_sms(line, server_name, sheet)
if ok:
total_processed += 1
highest_timestamp = max(highest_timestamp, msg_ts)
except Exception as e:
logger.error(f"❌ Line error: {e}")
except Exception as e:
logger.error(f"❌ Server {server_name} error: {e}")
logger.info(f"✅ Processed {total_processed} incoming message(s)")
if email_queue:
send_queued_emails()
if highest_timestamp > last_synced:
update_last_synced_epoch(highest_timestamp + 1)
logger.info(f"⏰ last_synced → {datetime.fromtimestamp(highest_timestamp + 1)}")
logger.info("💤 Sleeping 90s\n")
time.sleep(90)
if __name__ == "__main__":
process()
