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 [phone]',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 [phone]',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