Call and SMS history

The history of calls and messages are stored in a MySQL database. For safety reasons (i.e. to not modify the database itself), we make a copy of it and look into it:

$ cp .local/share/history-service/history.sqlite ~
$ sqlite3 history.sqlite
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .schema voice_events
CREATE TABLE voice_events (
accountId varchar(255),
threadId varchar(255),
eventId varchar(255),
senderId varchar(255),
timestamp datetime,
newEvent bool,
duration int,
missed bool,
remoteParticipant varchar(255));
...
sqlite> .schema text_events
CREATE TABLE text_events (
accountId varchar(255),
threadId varchar(255),
eventId varchar(255),
senderId varchar(255),
timestamp datetime,
newEvent bool,
message varchar(512),
messageType tinyint,
messageStatus tinyint,
readTimestamp datetime,
subject varchar(256)
);
...
sqlite> .output messages.txt
sqlite> select * from text_events ;
sqlite> .output calls.txt
sqlite> select * from voice_events ;
sqlite> .quit
$ cat messages.txt
...
ofono/ofono/account0|+49160xxxxxxx|2015-04-18T19:10:23+0200-1|+49160xxxxxxx|2015-04-18T17:10:29.000|0|amor|0|0|2015-04-18T17:11:12.160|
$ cat calls.txt
...
ofono/ofono/account0|089xxxxxxxx|089xxxxxxxx:mié. abr. 22 20:17:35 2015|self|2015-04-22T18:17:35.645|0|0|0|+4989xxxxxxxx

Another easy way to look for something particular is just grep from a .dump output:

$ sqlite3 history.sqlite .dump | fgrep 'Willkommen in '
INSERT INTO "text_events" VALUES('ofono/ofono/account0','FONIC','2015-08-31T10:04:20+0200-1','FONIC','2015-08-31T08:04:24.000',0,'Willkommen in Italien! In Ihrem Tarif zahlen Sie für Gespräche in der EU 0,22 €/min.; eingehende Anrufe 0,05 €/min., SMS 0,07 €. Notruf 112 kostenlos, Info +4917688864243',0,0,'2015-08-31T10:06:25.017','');
INSERT INTO "text_events" VALUES('ofono/ofono/account0','FONIC','2015-09-12T13:14:14+0200-1','FONIC','2015-09-12T11:15:26.000',1,'Willkommen in Österreich! In Ihrem Tarif zahlen Sie für Gespräche in der EU 0,22 €/min.; eingehende Anrufe 0,05 €/min., SMS 0,07€. Jetzt EU-Paket 100 Minuten (ein- und ausgehend/4,95€/7 Tage) buchen: Start FonicEU100Min per SMS an 36642.. Notruf 112 kostenlos, Info +4917688864243',0,0,NULL,NULL);

Note: The column accountId (here: "ofono/ofono/account0") is the reference to the used SIM1 or SIM2 during the call. It can be probed which SIM is which account with:

$ mc-tool dump
...
Account: ofono/ofono/account0
Display Name: SIM 1
Normalized: +49176xxxxxxxx
Enabled: enabled
Icon: im-ofono
Connects: automatically
Nickname: +49176xxxxxxxx
Service: ofono
Presences:
Automatic: available (2) ""
Current: registered (2) "FONIC"
Requested: available (2) ""
Changing: no
(string) modem-objpath = /ril_0

Last updated: Fri Nov 6 13:46:34 CET 2015