Identifying Android Device Owners

I work in a college town.  That means lots of unsecured electronics.  Lots of unsecured electronics means lots of thefts and 'misplaced'--"I'm not as think as you drunk I am!"--devices.

I've seen a trend in recovered stolen devices over the past few years: the bad guys are rapidly restoring devices to factory settings to prevent them from being tracked by the owner or law enforcement.  That leaves me with a problem, though: how do I determine the owner of a device that has been restored?   Allocated data that could show ownership is deleted upon a system restore.  Since, I've discussed other devices in the past, today I'll focus on Androids.

Dispossessed Androids

I've had uneven success with Androids in the past.  This may be due in part to the fact that I've not always know what to look for.  But I received two more such devices this week and decided to apply myself, once again, to the problem of identifying the owners.  Since I became an Android owner myself over the past 18 mos, I had a device with known data with which to experiment.

Nearly all data that contains identifying information is stored in the 'data' partition.  When a device is restored or 'wiped' through the Android recovery system, personal data is removed.  This process is usually quite fast, which leads me to believe that 'wiping' user data is a simple delete in most cases.   There are custom recoveries where this might not be true, but a study of unallocated data in a wiped device reveals a rich data field. 

In Unix-like systems, physical storage devices are attached to the operating system through special files (drivers) called device nodes.  These nodes provide raw access to devices and their partitions.  Thus, if a device node, also referred to as a block device, is addressed, all content is accessible, allocated and unallocated alike.  Block devices can be thought of and addressed by software tools as files.  To access block devices, however, one must have root access to the operating system.  I will not be discussing the various ways to achieve root access to an Android device in this article, however.  I will continue on the assumption that the device has been rooted.

Tinkering under the hood

Access to a running Android device is done through the Android Debug Bridge (adb).  In a stock recovery or Android operating system, adb provides shell user access to the file system.  The shell user has limited access to the device and commands, but the root user has full access.  Root access, when not immediately granted through the adb shell command, is obtained by the su command.

shell@device:/ $
shell@device:/ $ su
root@device:/ # 

Block device files are found in the /dev/block directory. The file representing the entire NAND flash is the /dev/block/mmcblk0 file. Partitions are represented as /dev/block/mmcblk0p1, /dev/block/mmcblk0p2, etc. A paritial directory listing in my device, for example, is:

/dev/block/mmcblk0
/dev/block/mmcblk0p1
/dev/block/mmcblk0p10
/dev/block/mmcblk0p11
/dev/block/mmcblk0p12
/dev/block/mmcblk0p13
/dev/block/mmcblk0p14
/dev/block/mmcblk0p15
/dev/block/mmcblk0p16
/dev/block/mmcblk0p17
/dev/block/mmcblk0p18
/dev/block/mmcblk0p19

We could address the entire memory storage device through mmcblk0, but it would be more efficient to address just the data partition.  But which of these is the data partition?  There are several ways to figure this out, and while not all of the following methods will work on every device, at least one should.

1. If the data partition is mounted, such as would occur in a rooted and running operating system, simply issue the mount command:
   
   # mount | grep /data
   /dev/block/mmcblk0p25 on /data type ext4 (ro,relatime,barrier=1,data=ordered)
   
   
2. Check the contents of the /etc/fstab file:
   
   # cat /etc/fstab
   /dev/block/mmcblk0p24 /system ext4 rw
   /dev/block/mmcblk0p25 /data ext4 rw
   /dev/block/mmcblk0p26 /cache ext4 rw
   /dev/block/mmcblk1p1 /sdcard vfat rw
   /dev/block/mmcblk0p28 /emmc vfat rw
   /dev/block/mmcblk1p2 /sd-ext  rw
   /dev/block/mmcblk0p21 /efs ext4 rw
   
   
3. Look for the 'by-name' directory somewhere in the /dev/block/platform subtree:
   
   # ls /dev/block/platform/msm_sdcc.1/by-name/ -l | grep data  
   lrwxrwxrwx root root 2014-06-24 03:10 data -> /dev/block/mmcblk0p25
   
   Note that the 'by-name' data file is actually link to the /dev/block/mmcblk0p25.

Getting to the Point

Ok, we know how to identify and address the data partition, but for what do we search?  After some experimentation with my own device, it appears that a very profitable target are application license files. The com.application.vending domain contains application licensing information.  On my device, I found 16 binary files in the /data/data/com.android.vending/cache/main/ directory that appear to be application licenses from applications downloaded from the Google Play store.  While I could not find specific information about these files, a reading of Android developer page for licensing applications suggests this files purpose.  Importantly all contained my username in the form of:

account="androiduser@gmail.com"

Crafting a search of the data partition of a restored device with this knowledge is fairly simple:

# strings mmcblk0p25 | egrep -o 'account="?.{1,25}@gmail.com"?'

Note: the strings and egrep commands are available through busybox which can be temporarily installed to the /dev/ folder (a temporary file system in RAM) if not already present in your environment using the adb push busybox /dev/ command.

Output of the search can be sorted and counted using a sort | uniq pipeline for clean results.

# strings -td mmcblk0.raw | \egrep -o 'account="?.{1,25}@gmail.com"?' | \sort | uniq -c | sort -n

1 account=user1@gmail.com
13 account=user2@gmail.com
970 account="user2@gmail.com"
2161 account="user1@gmail.com"

From the output, we can see there have been two user accounts.  Did they both exist on the system at the same time.  Has the device changed hands?  We don't know, but we have two email addresses for contacting people who might know!

Searching for Searches

In a recent examination of smart phone content, it became necessary to know the personal interests of the device's owner.  You can browse internet and app history, but that can be extensive to review every URLs to every clicked link and served page.  To get directly to the point, I decided to search for his browser/app search query history.  I was hoping to craft a regular expression (or several) that would assist in giving me a good idea of the person's interests.

I studied some top search engine results and reviewed some browser history and crafted the following GNU extended regular expression:

[?&](k|p|q|query)=[a-zA-Z0-9+_%-]+

This search, run against strings output of files, found search queries for Google, Yahoo!, Bing, Ask, Aol, Faceboot, YouTube, Vimeo and some x-rated sites as well as app content such as Twitter.  Search results appear (depending on what you feed and how you configure GNU grep) similar to:

https://www.google.com/search?q=you+found+me
http://m.youtube.com/results?q=some%20video%20i%20like
https://m.facebook.com/search/?query=that%20guy%20

An added benefit to this expression is that it also hits on additional page results, Google images page refreshes, etc.   With little command line wiz-bangery, it's even possible to sort and count the results to get a histogram of searches:

strings History.plist | egrep -o '[?&](k|p|q|query)=[a-zA-Z0-9+_%-]+' | sed 's/.*=//' | sort | uniq -c | sort -nr

I'll explain the command above:

1. strings History.plist # extract ascii strings from the iPhone Safari History.plist
2. egrep -o '[?&](k|p|q|query)=[a-zA-Z0-9+_%-]+' # grep for the regular expression described above
3. sed 's/.*=//' # strip off the query tag at the front of the user typed query
4. sort # sort the results alphabetically
5. uniq -c  # count the matching lines
6. sort -nr # reverse sort, placing the most frequent query terms first.

Results of the command look similar to the following:

   21    I+search+for+this+a+lot

   11    this%20one%20a%20little%less

    2    why+would+anyone+read+linux+sleuthing

    1    testing%20one%20two

The expression could be run against all logical files in a device and against unallocated space, if applicable.  I only demonstrate it using the History.plist because it's easy illustrate.

I post this short article both because I want to remember this regular expression (the whole reason for my blog in the first place) and to solicit favorite search box/engine regular expressions you might have. Please share them in a comment if you get a chance.  Happy searching!

Finding Serial Numbers on Locked iPhones

Apple iDevices have their serial number engraved on the back, right? So why the article? Because it's not true of newer devices like the iPhone 5, 5s, and 5c. Also, original cases can be replaced and serial numbers obliterated through unprotected use or deliberate act. Now I have your attention again, I hope.

Getting the Message

I've written in the past about the libimobiledevice library and it's utilities.  One, which is quite handy for gathering device information is ideviceinfo.  It provides information such as the device description (color), device class (iPhone, iPod, iPad), device name, etc.  When the device is unlocked, you can retrieve the serial number, as well.  Basically, you retrieve the contents of the Info.plist.

But ideviceinfo is not so informative with a locked device.  In fact, it won't show you any output unless you use the -s simple option.  While you can obtain some information, such as the description, class, name, UDID (unique identifier), and Mac address, you can't display the serial number.  But never fear, there is a way...

Linux has a system log that tracks systems events, included the plugging and unplugging of devices. The system log can be dumped the the terminal with the dmesg command.  Run by itself, you dump the entire log and it's quite a lot of information to sift through, though in truth what you want will be found at or near the end of the log.  You can shorten the output to the content you need with

$ dmesg syslog

But an even niftier trick is to set up your system to display the log as it is created and watch the output:

$ tail -f /var/log/syslog

This will display the last 10 lines of the system log and the "follow" it until you cancel with ctrl-c. Now you can hotplug your iDevice and watch the data that the system log records about the device. Unfortunately, you will see that it displays the device UDID and not the serial number in the "SerialNumber" field for a locked iDevice.

Recovering the Serial Number

The serial number is recoverable in Recovery Mode, however.  Pressing and holding the hardware power button brings up the software power off slide button.  Power off the device, and then replug it into your Linux box while holding the hardware home button.  The device will boot into recovery mode.  Now check your syslog with either of the two methods discussed above.  Two serial numbers are displayed in the syslog after the product (iPhone, etc) and manufacturer (Apple) are listed.  The first is the UDID, but the second includes several key:value pairs, one of which is the device serial number (key SRNM).  

When you are done collecting the device data revealed in the syslog, reboot it, if required, by pressing and holding the power button approximately 10 seconds until the recovery screen goes blank.  The device will then reboot into the operating system, probably feeling very ashamed of itself for revealing its secrets so readily.

Identifying Owners of Locked Android Devices

Locked Devices are not Always Secure

I was handed a device I’ve never seen before: A Verizon Ellipsis 7" tablet. The device was suspected to be stolen, but it was password locked with no sd card or sim card installed. USB debugging and mass storage mode were disabled, too, checked by plugging the device into a computer while the device was booted into the normal operating system. What to do now?

I’ve learned through much hands-on experience to put a device through a few checks before I give up hope. Is there a bootloader mode? How about recovery? I’ve been surprised to find full access to devices in recovery mode, left wide open by the phone’s distributor. More often I find limited access, and sometimes none.

With a little online research—the forensic community owes a debt of gratitude the the modder community—I found that the way to put the Ellipsis into recovery mode: Press and hold between the up and down volume button while powering the device (pressing up and down at the same time did not work). I plugged the device into my PC again, ran adb devices and observed that the Ellipsis was running the adb daemon in recovery mode! I dropped into the ADB shell and determined I was the shell user, which meant limited privileges.

Getting the lay of the land in Android

$ adb shell
shell@android:/ $ printenv
_=/system/bin/printenv
LD_LIBRARY_PATH=/vendor/lib:/system/lib
HOSTNAME=android
TERM=vt100
PATH=/sbin:/vendor/bin:/system/sbin:/system/bin:/system/xbin
LOOP_MOUNTPOINT=/mnt/obb
ANDROID_DATA=/data
ANDROID_ROOT=/system
SHELL=/system/bin/sh
MKSH=/system/bin/sh
USER=shell
ANDROID_PROPERTY_WORKSPACE=8,49664
EXTERNAL_STORAGE=/storage/sdcard0
RANDOM=17656
SECONDARY_STORAGE=/storage/sdcard1
HOME=/data
ANDROID_BOOTLOGO=1
PS1=$(precmd)$USER@$HOSTNAME:${PWD:-?} $
shell@android:/ $

The printenv command reveals some other interesting details about the device. For example, I know where the user data is mounted (HOME=/data), where the operating system files are located (ANDROID_ROOT=/system), and where the sdcards are mounted (EXTERNAL_STORAGE=/storage/sdcard0, SECONDARY_STORAGE=/storage/sdcard1). I know the system path, i.e., the location of executable files that can be called from anywhere in the system. I can also see what partitions are mounted:

Mountpoints of Verizon Ellipsis in Recovery Mode

shell@android:/ $ mount
rootfs / rootfs ro,relatime 0 0
tmpfs /dev tmpfs rw,nosuid,relatime,mode=755 0 0
devpts /dev/pts devpts rw,relatime,mode=600 0 0
proc /proc proc rw,relatime 0 0
sysfs /sys sysfs rw,relatime 0 0
none /acct cgroup rw,relatime,cpuacct 0 0
tmpfs /mnt/obb tmpfs rw,relatime,mode=755,gid=1000 0 0
emmc@android /system ext4 ro,noatime,noauto_da_alloc,commit=1,data=ordered 0 0
emmc@usrdata /data ext4 rw,nosuid,nodev,noatime,nodelalloc,noauto_da_alloc,commit=1,data=ordered 0 0
/emmc@cache /cache ext4 rw,nosuid,nodev,noatime,discard,noauto_da_alloc,data=ordered 0 0
/emmc@protect_f /protect_f ext4 rw,nosuid,nodev,noatime,nodelalloc,noauto_da_alloc,commit=1,data=ordered 0 0
/emmc@protect_s /protect_s ext4 rw,nosuid,nodev,noatime,nodelalloc,noauto_da_alloc,commit=1,data=ordered 0 0
/emmc@fat /storage/sdcard0 vfat rw,dirsync,nosuid,nodev,noexec,relatime,uid=1000,gid=1015,fmask=0702,dmask=0702,allow_utime=0020,codepage=cp437,iocharset=iso8859-1,shortname=mixed,utf8,errors=remount-ro 0 0
shell@android:/ $

I see that the /data partition is mounted read/write, but upon exploration, I’ll see there is little I can see or retrieve from there because the shell user does not have sufficient rights. But where can I look to find information about the owner, then? Take a close look at that last entry:

Internal SDCard Mount Point

/emmc@fat /storage/sdcard0 vfat rw,dirsync,nosuid,nodev,noexec,relatime,uid=1000,gid=1015,fmask=0702,dmask=0702,allow_utime=0020,codepage=cp437,iocharset=iso8859-1,shortname=mixed,utf8,errors=remount-ro 0 0
shell@android:/ $ ls -dl storage/sdcard0
d---rwxr-x system   sdcard_rw          1969-12-31 16:00 sdcard0

Members of the sdcard_rw group have read/write/execute privileges in the /storage/sdcard0 directory, and other users can read and execute there. A little more exploration of the root directory, we see that /sdcard is a link to /storage/sdcard0, so we can shortcut our typing a bit.

What remains is figure out who owns this device from the data I can read in the /sdcard mount point. One thing all Androids have in common is that the users register them with Google and create associate the device with a gmail account. I performed a simple search:

Finding the Owner of a Device from SDCard Data

shell@android:/ $ ls -R sdcard/ | grep "\@gmail.com"
...
sdcard//Android/data/com.google.android.apps.books/files/accounts/somebody@gmail.com/volumes/######/res2:
sdcard//Android/data/com.google.android.apps.books/files/accounts/somebody@gmail.com/volumes/######/segments:
...

Note	The email address and path has been altered above to protect privacy. It is offered as an example of what can be expected from such a search.

I found over 230 instances of that email address (modified above for privacy) in the file paths alone, without looking inside any files at all. In fact, I found two accounts. I was able to contact those persons and determine the device was in fact stolen. There are certainly other ways to find user information, and I did in fact find that some of the apps that stored user namest hat corroborated the gmail accounts I found in the file paths.

I’ve known investigators to hear a device description of "Locked with no USB debugging" and declare, "There is nothing that can be done." I hope this quick post demonstrates otherwise. While it is true that some devices are buttoned up pretty tight, I find that the vast majority provide at least some access. Maybe now you’ll be inspired to look a little more closely, too.

---

Last updated 2014-02-19 17:28:28 PST

Making Sense of the Senseless

SQLite to the Rescue

One of the tasks I’m asked to perform is to geolocate mobile phone calls from Call Detail Reports (CDR). These usually arrive from a carrier as spread sheets: one with details of calls to and from a particular number, and one or more cell tower listings. I’ve tried a variety of ways to process these over time such as BASH scripting and python coding. But by far the easiest and most flexible way to process these records is by importing them into a SQLite database.

The long term difficulty in processing CDRs is that they change over time. It seems that every time I get new records to process, the format has changed which breaks previous code. It takes much more effort to recode a script than it does to write a SQL query on the fly, and I’m certainly no SQL guru. SQLite has enough built in functions to handle nearly any problem you might encounter.

Lets take some Sprint records I recently processed as an example. I was tasked with plotting the locations of the voice calls on a map. There were over 3,600 records for a 12 day period with text message details mixed with voice call details. Only the call details contained references to the tower records, however. Call records included five digit integers that represented the first and last cell towers the mobile phone used during the communication. Text messages contained only zeros in these columns.

The challenge was to retrieve the call records for mapping, ignoring the text messages that did not contain cell tower details. SQLite seemed the easiest way to accomplish this in light of the follow up requirement of looking up each cell tower integer in any one of four associated tower record spread sheets.

Creating the Database

The first step was to create a SQLite database. Fortunately, creating a database is a simple, straight forward process. I performed the work using in the SQLite command line program. However, GUI tools like the excellent SQLite Manager can accomplish the same thing and I recommend them if you are new to SQLite as they can be good teachers.

To create a new database, I simply provided the new database name when I opened the command line program. I called my database cdr.sqlite.

Creating a SQLite database

$ sqlite3 cdr.sqlite
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Next, I needed a table to hold the call records. I made this something easy to type, so I called it simply cdr. The columns I named for the columns found in the spreadsheet sent by Sprint.

SQLite Create Table Statement for the Call Sprint Detail Report

sqlite> CREATE TABLE "cdr"
   ...> (
   ...> "Calling_NBR" TEXT,
   ...> "Called_NBR" TEXT,
   ...> "Dialed_Digits" TEXT,
   ...> "Type" TEXT,
   ...> "Start_Date" TEXT,
   ...> "End_Date" TEXT,
   ...> "Duration" INTEGER,
   ...> "NEID" INTEGER,
   ...> "Repoll" TEXT,
   ...> "First_Cell" INTEGER,
   ...> "Last_Cell" INTEGER,
   ...> );
sqlite>

Note	In the SQLite command line program, all SQL queries must end in a semi-colon or the interpreter assumes you are adding lines to the statement. If you forget to put the semi-colon at the end of your command, you can enter it on the next line.

To import the CDR data from the spreadsheet, simply export the data without the header row, in text file with comma separated values (CSV). In this case, I called the file "call_records.csv". I had to tell SQLite how the data was delimited (SQLite uses pipes by default), and import the CSV file into the "cdr" table.

Importing the CDR data into the cdr table

sqlite> .separator ","
sqlite> .import call_records.csv cdr

Note	The "dot" commands are special SQLite functions (use .help to view them all) and do not require a semi-colon.

To import the cell tower details, I followed the same process: I created a table I called "towers" using the column headers from the spreadsheet as the column names of the table. Then I exported the cell tower data from each spreadseet to a CSV file and imported the CSVs into the tower table. While I won’t repeat the full process, I will display the table layout (schema) below.

Towers table schema

sqlite> .schema towers
CREATE TABLE "towers" (
    "Cell" INTEGER,
    "Cascade" TEXT,
    "Switch" TEXT,
    "NEID" INTEGER,
    "Repoll" INTEGER,
    "Site_Name" TEXT,
    "Address1" TEXT,
    "Address2" TEXT,
    "City" TEXT,
    "County" TEXT,
    "State" TEXT,
    "Zip" TEXT,
    "Latitude" TEXT,
    "Longitude" TEXT,
    "BTS_Manufacturer" TEXT,
    "Sector" TEXT,
    "Azimuth" TEXT,
    "CDR_Status" TEXT
);
sqlite>

Looking Up Records

Now it was time to lookup the call records in the tower tables to find that latitude and longitude of the tower used to initiate the call an place the mobile device in time and space. I expected it to be straight forward: take the 5-digit tower number from the first_call field of the cdr table, match it to the cell field in the towers table, and return the map coordinates. Easy peasy, right? The SQL equivalent of show me the latitude and longitude of the tower where the CDR first_cell integer matches the Tower cell integer.

First attempt to match call tower numbers to tower coordinates

sqlite> select latitude, longitude from towers, cdr where first_cell = cell;
sqlite> # Ruh roh, raggy, no matches!
sqlite> select first_cell from cdr where first_cell != 0 limit 5;
40385
10962
10962
20962
30392
sqlite> select cell, latitude, longitude from towers;
<redacted>
385|34.046944|-118.448056
385|34.046944|-118.448056
385|34.046944|-118.448056
392|34.063806|-118.30366
392|34.063806|-118.30366
392|34.063806|-118.30366
962|37.657222|-122.094653
962|37.657222|-122.094653
962|37.657222|-122.094653
385|37.838333|-122.298611
385|37.838333|-122.298611
392|37.693|-122.0939
392|37.693|-122.0939
392|37.693|-122.0939
385|37.403633|-121.89436
385|37.403633|-121.89436
385|37.403633|-121.89436
<redacted>
sqlite>

Whoa, the cdr first_cell and towers cell values do not jibe! And, as we can see, there is more than one entry in the tower table for each cell designator. Take cell 385 for example: there are three distinct groupings of tower 385 with three different map coordinates for each group. It turns out that cell towers are grouped by the switch they are part of, recorded in the CDR and tower records as the NEID. The appropriate cell tower record can be further reconciled by the sector number, or side of the tower from which the call originated. The first_cell value is actually a concatenation of the sector and the tower number. How did I figure all this out? The answer came from reading the documentation (RTFM) that came with the records and some analysis of the spreadsheets.

I’ll demonstrate below the values that make a tower record unique and that must be considered when matching call records to tower details. I’ll focus on tower 385

Values that make the tower records unique.

sqlite> .headers on
sqlite> .mode columns
sqlite> select cell, sector, neid, azimuth, latitude, longitude from towers
   ...> where cell = 385;
Cell        Sector      NEID        Azimuth     Latitude    Longitude
----------  ----------  ----------  ----------  ----------  -----------
385         1           65          60          34.046944   -118.448056
385         2           65          200         34.046944   -118.448056
385         3           65          290         34.046944   -118.448056
385         1           512         55          37.838333   -122.298611
385         2           512         155         37.838333   -122.298611
385         1           95          0           37.403633   -121.89436
385         2           95          110         37.403633   -121.89436
385         3           95          190         37.403633   -121.89436
sqlite>

Now it is easy to see that the three different groupings of tower 385 are a result of that tower designator being used in three different switches, or NEIDs. Further, each tower can be resolved to a sector, which corresponds to a unique asimuth or direction the cell tower antenna points.

SQLite Substrings

The remaining problem in querying this data is the configuration of the first_cell value in the call details. Recall that it is the sector concatentated to the tower number. I needed a way to take the first digit from the integer and assign it to a sector value, and use the remaining four digits as the tower designator. Fortunately, SQLite has a built-in substring function to make this easy.

substr(X,Y,Z), substr(X,Y)

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

http://www.sqlite.org/lang_corefunc.html
— SQLite

From the SQLite documentation, we see that the substr function takes 2-3 arguments and returns a substring of the of the input string based according to those documents. To return the sector, I needed to take the first digit from the first_cell string in this manner: substr(first_cell, 1, 1). To return the tower identification, I needed to skip the first digit and return the rest of the string thusly: substr(first_cell, 2).

Note	I did not need to specify the third argument in the second substr() expression because I wanted the entirety of the string past the first digit.

Finally, I needed to include the NEID from the call detail records to ensure I’ve looked up the correct tower. Putting it all together, we can see how to created the values we need from the call records to find the matching tower details. I’ve added a second query to demonstrate, using the ltrim() function to strip the leading zeros from the cell column.

Using the SQLite substr() function

sqlite> select substr(first_cell, 1, 1) as sector,
   ...> substr(first_cell, 2) as cell, neid
   ...> from cdr where first_cell != 0 limit 5;
sector      cell        NEID
----------  ----------  ----------
4           0385        95
1           0962        169
1           0962        169
2           0962        169
3           0392        512
sqlite> select substr(first_cell, 1, 1) as sector,
   ...> ltrim(substr(first_cell, 2), 0) as cell, neid
   ...> from cdr where first_cell != 0 limit 5;
sector      cell        NEID
----------  ----------  ----------
4           385         95
1           962         169
1           962         169
2           962         169
3           392         512
sqlite>

Putting it All Together

As usual, the explanation is more step intensive that the actual work. The whole process can be done in one query, but I wanted to break it down so that it would be easier to recognize the elements of the query. To make it more legible, I’ll write it across several lines.

Matching the CDR call record to the correct tower details

sqlite> select start_date as Date, calling_nbr as Number, latitude, longitude
   ...> from cdr, towers
   ...> where substr(first_cell, 1, 1) = towers.sector and
   ...> ltrim(substr(first_cell, 2), 0) = cell and
   ...> cdr.neid = towers.neid limit 5;
Date                 Number      Latitude    Longitude
-------------------  ----------  ----------  -----------
2012-12-10 07:36:39  ##########  37.657222   -122.094653
2012-12-10 08:24:21  ##########  37.657222   -122.094653
2012-12-10 08:26:09  ##########  37.657222   -122.094653
2012-12-10 09:59:40  ##########  37.693      -122.0939
2012-12-10 10:00:26  ##########  37.705128   -122.047417
sqlite>

This can be converted to a CSV file suitable for mapping through a website like gpsvisualizer.com[GPS Visualizer] or a program like GPSBabel. First, I change the output mode to CSV and I change the columns names to comply with the mapping software’s requirements, printing a sample to ensure I have the format I am seeking. Then I output the data to a file for import to the mapping program.

Exporting the data for mapping

sqlite> .mode csv
sqlite> select start_date as name, calling_nbr as desc, latitude, longitude
   ...> from cdr, towers
   ...> where substr(first_cell, 1, 1) = towers.sector and
   ...> ltrim(substr(first_cell, 2),0) = cell and
   ...> cdr.neid = towers.neid limit 5;
name,desc,Latitude,Longitude
"2012-12-10 07:36:39",##########,37.657222,-122.094653
"2012-12-10 08:24:21",##########,37.657222,-122.094653
"2012-12-10 08:26:09",##########,37.657222,-122.094653
"2012-12-10 09:59:40",##########,37.693,-122.0939
"2012-12-10 10:00:26",##########,37.705128,-122.047417
sqlite> .output call_map.csv
sqlite> select start_date as name, calling_nbr as desc, latitude, longitude
   ...> from cdr, towers
   ...> where substr(first_cell, 1, 1) = towers.sector and
   ...> ltrim(substr(first_cell, 2),0) = cell and
   ...> cdr.neid = towers.neid;
sqlite>

So, in its simplest form, you can see this is not necessarily a difficult process. It can be distilled into three basic steps:

1. Review the records and determine the relationships between them

2. Import the data into a SQLite database

3. Query the database for the output needed

Though there is commercial mapping software available, the software I’ve seen either lacks flexibility to deal with differences in records or in output. Further they usually require configuration that can take as long or longer than importing the data into SQLite and writing the specific query you need for your investigation. If you have the software and a happy with it, use it. If you find it is lacking the flexibility you need, consider doing the work by hand. You’ll be better for it!

Note

In the interest of full disclosure, the results demonstrated here are over simplified. For the real casework, I interpreted the call direction with a case statement to select the called number or calling number as appropriate. The result was a Google Earth map with waypoints named for the date and time of the call. Clicking the waypoint showed call details, e.g., (To: ()-## for 37 secs, azimuth 270).

---

Last updated 2014-02-17 13:34:39 PST