import sqlite3,json,collections
c=sqlite3.connect('file:/var/lib/sma-data/signals.db?mode=ro',uri=True); c.row_factory=sqlite3.Row
for where,label in [("observed_at >= datetime('now','-30 days')",'30d'),('1','all')]:
 rows=c.execute('select source_type,source_platform,source_name,watch_entity_id,COUNT(*) n from signals where '+where+' group by source_type,source_platform,source_name,watch_entity_id order by n desc').fetchall(); print(label,json.dumps([dict(r) for r in rows],ensure_ascii=False,indent=2))
c.close()