SMS trend analysis - Android - Aravinda Vishwanathapura

Now a days my incoming and outgoing SMS trend looks like this for some(known) reason :)

Incoming SMS trend(Nov 2012 - Apr 2013)

Outgoing SMS trend(Nov 2012 - Apr 2013)

PS: Sometimes I delete telemarketing SMS as and when I receive, so incoming trend may not show actual :) Also while playing with Android OS I lost all the SMS prior to Nov 2012.

***

Continue reading if you are wondering how I extracted the SMS data from the phone.

prerequisites:

Android phone
Your Android phone should be rooted one.
Android SDK should be installed.
Debugging should be enabled in your android phone.

Idea:

In Android, SMS data is stored in sqlite database, and it is located in the following directory(in phone).

/data/data/com.android.providers.telephony/databases/mmssms.db

So we can run sql queries to get the details from sms table as per our need. To access the data from db, Android phone should be rooted.

Add adb path(in my machine adb path is /home/aravinda/android-sdk/platform-tools/) to environment variable PATH, and run adb as root.

adb root

In sqlite we can see the list of tables present in a database using .tables (sqlite3 <db path> ".tables") query.

adb shell 'sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db ".tables"'

and we can get schema of the required sqlite table using .schema <table name> query.

adb shell 'sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db ".schema sms"'

    CREATE TABLE sms
    (
        _id                INTEGER PRIMARY KEY,
        thread_id          INTEGER,
        address            TEXT,
        person             INTEGER,
        date               INTEGER,
        date_sent          INTEGER DEFAULT 0,
        protocol           INTEGER,
        read               INTEGER DEFAULT 0,
        status             INTEGER DEFAULT -1,
        type               INTEGER,
        reply_path_present INTEGER,
        subject            TEXT,
        body               TEXT,
        service_center     TEXT,
        locked             INTEGER DEFAULT 0,
        error_code         INTEGER DEFAULT 0,
        seen               INTEGER DEFAULT 0
    );

Now we know how to run queries inside phone using adb, let us list down some of the ideas which we can implement using available sms data.

Number of SMS sent/received per day, to see the trend or if you have number of SMS limit per day/month.
SMS sent/received trend per hour, to see what time of the day I spend more in sending/receiving SMS.
Number of SMS per day per number, to see the trend per number.
Identify the messages/number to which I never interacted.
Identify the trend of teledirect numbers(Regex for non number phone number).
Backup all SMS database.
SMS book/conversations book.
Check how many sent messages are just less than 10 chars.
Live SMS notification in Laptop/Computer.

The only limitation is your imagination :D

Example: Number of sms sent per day.

Query is

    SELECT STRFTIME("%Y%m%d", DATETIME(date/1000, "unixepoch", "localtime")) dte,
           COUNT(_id)
    FROM sms
    WHERE type = 2
    GROUP BY dte
    ORDER BY dte

where type 2 is sent and 1 is received.

adb shell 'sqlite3 /data/data/com.android.providers.telephony/databases/mmssms.db \
  "SELECT STRFTIME(\"%Y%m%d\", DATETIME(date/1000, \"unixepoch\", \"localtime\")) dte,  \
  COUNT(_id) FROM sms WHERE type = 2 GROUP BY dte ORDER BY dte"'

If you want to find number of sms sent per day for a particular phone number then add the condition in WHERE clause as follows

    SELECT STRFTIME("%Y%m%d", DATETIME(date/1000, "unixepoch", "localtime")) dte,
           COUNT(_id)
    FROM sms
    WHERE type = 2
    GROUP BY dte
    ORDER BY dte
    AND REPLACE(REPLACE(address, "+91",""), " ", "") = "XXXXX"

I created Python script to extract data as described above. Additionally script generates csv output which can be used to plot as required. Use any familiar library(Matplotlib, PhantomJS and d3, flot etc..) or just use Spreadsheet to plot the graph as required. I used Libreoffice Calc to create these charts.

Let me know if you find this blog useful and need any help in writing SQL queries :)