Study guide
14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists
Computer Hacking Forensic Investigator CHFI 312-49
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Foundations of Digital Forensics & Cybercrime Investigation
1.1 Cybercrime Classifications, Forensic Science Principles & Legal Admissibility 1.2 RFC 3227 Order of Volatility, Live vs. Dead Acquisition & Forensic Readiness 1.3 Indicators of Compromise (IoCs), Cyber Attribution & Threat Intelligence 1.4 AI-Assisted Forensics, GitOps, Automation/Orchestration & Forensics-as-a-Service (FaaS)
2 Chapter 2: Modern Forensic Environments & Emerging Threats
2.1 Fileless Malware, In-Memory Execution & Living-off-the-Land (LotL) 2.2 IoT & Operational Technology (OT/SCADA) Forensic Fundamentals 2.3 Dark Web, Tor Network, Onion Routing & Hidden Services Forensics 2.4 Blockchain Transactions, Cryptocurrency Tracing & Digital Asset Investigations
3 Chapter 3: Legal Frameworks, Search Warrants & Evidence Rules
3.1 Federal Rules of Evidence (FRE 702, 901, 902, 1001-1003) & Daubert/Frye Standards 3.2 Fourth Amendment, Warrant Exceptions, Workplace Search & Consent Protocols 3.3 International Standards: ISO/IEC 27037, ACPO Principles & ENFSI Guidelines 3.4 Cybercrime Statutes, Privacy Compliance, CAN-SPAM & International Cooperation
4 Chapter 4: eDiscovery, Chain of Custody & Professional Ethics
4.1 Electronic Discovery Reference Model (EDRM) & Legal Spoliation Holds 4.2 Chain of Custody Documentation, Evidence Packaging & Tamper-Evident Handling 4.3 Expert Witness Testimony, Report Writing Standards & EC-Council Code of Ethics
5 Chapter 5: Storage Architecture, Boot Sequences & Media Forensics
5.1 Storage Media Architecture: Sectors, Clusters, RAM/File Slack & Unallocated Space 5.2 Partition Tables & Boot Structures: MBR, GPT, VBR, BIOS vs. UEFI Secure Boot 5.3 RAID Reconstruction (RAID 0, 1, 5, 6, 10) & SSD Forensic Challenges (TRIM, Wear Leveling)
6 Chapter 6: Windows File Systems: NTFS & FAT
6.1 NTFS Architecture: Master File Table ($MFT) Record Structure & Attribute Headers 6.2 NTFS Artifacts: Alternate Data Streams (ADS), Volume Shadow Copies (VSS) & $LogFile 6.3 FAT12/16/32 and exFAT Architecture, Directory Entries & File Deletion Mechanics
7 Chapter 7: Non-Windows File Systems & File Signatures
7.1 Linux File Systems: ext2/ext3/ext4 Inode Architecture, Superblocks & Journaling Forensics 7.2 macOS File Systems: HFS+ and Apple File System (APFS) Architecture & Snapshots 7.3 File Signatures, Magic Bytes, Hex Headers/Footers & File Carving Techniques
8 Chapter 8: Investigation Lifecycle, Lab Setup & First Response
8.1 Forensic Laboratory Accreditation: ISO/IEC 17025 Standards & Hardware/Software Controls 8.2 First Responder Duties: Crime Scene Containment, Electronic Triage & Volatile Triage 8.3 Physical Evidence Packaging, Anti-Static/Faraday Protection & Transport Security
9 Chapter 9: Forensic Acquisition & Verification Methodology
9.1 Hardware and Software Write-Blockers: Operational Validation & Bridge Verification 9.2 Bit-Stream Disk Imaging, Physical vs. Logical vs. Targeted Acquisition Methodology 9.3 Forensic Image Formats (RAW/DD, E01, AFF) & Cryptographic Hashing Verification (MD5, SHA-256)
10 Chapter 10: Anti-Forensics Techniques & Countermeasures
10.1 Steganography Detection: Spatial/Frequency Domain Steganalysis & Hidden Carrier Extraction 10.2 Timestomping Countermeasures: Comparing $STANDARD_INFORMATION vs $FILE_NAME Timestamps 10.3 Secure Wiping, Degaussing, File Shredding Countermeasures & Residual Artifact Recovery
11 Chapter 11: Windows Operating System Forensics
11.1 Windows Registry Forensics: SYSTEM, SOFTWARE, SAM & NTUSER.DAT Hive Analysis 11.2 Execution Artifacts: Prefetch (.pf), Shimcache (AppCompatCache), Amcache.hve & UserAssist 11.3 User Activity Artifacts: ShellBags, LNK Shortcut Files, Jump Lists & Recycle Bin ($I/$R) 11.4 Windows Event Log Forensics: Security Audit IDs (4624, 4625, 4688, 4720, 7045) & EVTX Parsing
12 Chapter 12: Memory Forensics & Malware Analysis
12.1 Volatile Memory Acquisition: RAM Capture, Crash Dumps, Hiberfil.sys & Pagefile.sys 12.2 Memory Analysis with Volatility 3: pslist, pstree, malfind, netscan & Vadinfo 12.3 Static Malware Triage: Strings, PE Headers, Import Tables, Hashes & YARA Rule Matching 12.4 Dynamic Malware Analysis: Sandboxing, Process Monitor (Procmon), Regshot & Network Callouts 12.5 .NET Managed Malware Analysis & the GOOTLOADER Fileless Infection Chain
13 Chapter 13: Network, Web Application & Email Forensics
13.1 Network Forensics: PCAP Analysis, Protocol Dissection, Snort/Suricata & Flow Analysis 13.2 Web Server Forensics: Apache/IIS Access Logs, SQL Injection, XSS & Web Shell Detection 13.3 Email Forensics: RFC 5322 Headers, Authentication (SPF, DKIM, DMARC) & Mbox/PST Extraction 13.4 SIEM Event Correlation, Cross-Source Timeline Analysis & Super-Timeline Construction 13.5 Investigating Directory Traversal, Command Injection, Parameter Tampering, XXE, Cookie Poisoning & Brute Force 13.6 Network Device & Service Log Forensics: Firewall, IDS, Honeypot, Router, DHCP, Switch, VPN, DNS & SSH 13.7 Wireless Network Forensics: Rogue APs, Evil Twins, MAC Spoofing, Deauthentication & Jamming
14 Chapter 14: Mobile, Cloud & IoT Forensics
14.1 Mobile Device Forensics: Manual, Logical, File System & Physical Extraction (Android/iOS) 14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists 14.3 Cloud Forensics: AWS CloudTrail, Azure Activity Logs & Multi-Tenant Evidence Collection 14.4 IoT Forensics: UART/JTAG Physical Interfaces, Firmware Extraction & Sensor Telemetry
15 Chapter 15: Commercial & Open-Source Forensic Suites
15.1 Commercial Forensic Platforms: EnCase Forensic, FTK (Forensic Toolkit) & FTK Imager 15.2 Open-Source Forensic Platforms: Autopsy, The Sleuth Kit (TSK) & SIFT/CAINE Workstations 15.3 Specialized DFIR Utilities: Eric Zimmerman Tools (EZ Tools) & Sysinternals Forensic Suite
16 Chapter 16: Analysis Toolkits, Python Scripting & Tool Validation
16.1 Reverse Engineering & Password Cracking: Ghidra, IDA Pro, Hashcat & John the Ripper 16.2 Python for Digital Forensics: Parsing Raw Artifacts, Custom Scapy Scripts & Evidence Automation 16.3 Forensic Tool Testing & Validation: NIST CFTT Methodology, ISO/IEC 17025 Standards & Daubert Compliance
17 Chapter 17: Database Forensics & Web Application Firewalls
17.1 MySQL Forensics: Internal Architecture, Data Directory, Information Schema & Utility Programs 17.2 SQL Server Forensics: SSMS, ApexSQL, SQLCMD Transaction-Log Collection, Plan Cache & Trace Files 17.3 Web Application Firewall (WAF) Architecture, Audit Logs, Benefits & Limitations
18 Chapter 18: Social, Virtualized, Multimedia & Browser Investigations
18.1 Social Media, Social Engineering, Insider Threat & Identity Theft Forensics 18.2 Virtualization, Container & Microservices Forensics, Bluetooth and Electron Chat Applications 18.3 Multimedia Forensics: Image, Audio & Video Authentication, EXIF, PRNU and Deepfake Detection 18.4 Browser Artifact Recovery, Private Browsing, USB Enumeration, Partition Recovery & Packer Unpacking Tools
14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists
Key Takeaways Explain This Topic AI
Mobile platforms store application and system records in SQLite databases, which utilize Write-Ahead Logging (WAL); acquiring the main .db file without .db-wal and .db-shm results in incomplete forensic evidence.
Deleted SQLite records reside in unallocated cell blocks and database freelist pages, enabling recovery of purged chats, calls, and SMS messages until the database undergoes a VACUUM operation.
Android stores critical telephony records in contacts2.db (call logs) and mmssms.db (SMS/MMS), with individual application sandboxes structured under /data/data/<package_name>/ containing databases, shared_prefs XMLs, and cache repositories.
iOS stores system configurations in binary property lists (bplist00), with communications in sms.db and CallHistory.storedata; timestamps use Mac Absolute Time (seconds or nanoseconds since January 1, 2001).
Cellular site analysis correlates handset location artifacts (iOS routined Local.sqlite and consolidated.db) with telecommunication provider Call Detail Records (CDRs), utilizing tower coordinates, antenna azimuths (typically 120° sectors), and Timing Advance (TA) metrics.
Last updated: September 2026
14.2 Mobile Artifact Analysis: Call Logs, SMS/Chat SQLite Databases, Location Data & Plists
Quick Answer: Both Android and iOS rely fundamentally on SQLite databases for operational data storage. SQLite operates using Write-Ahead Logging (WAL) , meaning an examiner must acquire the main database ( .db / .sqlite ), the write-ahead log ( .db-wal ), and the shared memory index ( .db-shm ) simultaneously to prevent evidence omission. Deleted records remain recoverable from SQLite freelist pages and unallocated cell slack until purged by a VACUUM command. Key Android artifacts reside in /data/data/ (e.g., contacts2.db , mmssms.db ), while iOS artifacts reside in /private/var/mobile/ (e.g., sms.db , CallHistory.storedata , binary .plist files). Handset geolocation caches (iOS routined and consolidated.db ) correlate with carrier Call Detail Records (CDRs) through antenna azimuth sectors (typically 120° beam coverage) and Timing Advance (TA) distance calculations.
SQLite Database Architecture & Record Recovery
Unlike enterprise relational databases running dedicated background service engines, SQLite is a serverless, self-contained, file-based database engine embedded directly into mobile operating systems and applications. +-------------------------------------------------------------------------+
- | SQLITE STORAGE & WAL ARCHITECTURE |
- +-------------------------------------------------------------------------+
- | |
- | +------------------+ +------------------+ |
- | | contacts.db | | contacts.db-wal | |
- | | (B-Tree Pages: | | (Sequential New | |
- | | 4096-byte blocks| | Transactions: | |
- | | Allocated Cells | | Uncommitted | |
- | | & Freelist Pgs) | | Writes & Edits) | |
- | +------------------+ +------------------+ |
- | ^ ^ |
- | | Checkpoint Operation | Shared Memory Index |
- | | (Commits WAL to Main DB) | |
- | +----------------------------+ |
- | | |
- | +------------------+ |
- | | contacts.db-shm | |
- | | (WAL Index Table)| |
- | +------------------+ |
- +-------------------------------------------------------------------------+
- 1. Page Allocation & Write-Ahead Logging (WAL)
Page Structures: SQLite databases are structured into fixed-size blocks called pages (ranging from 512 to 65,536 bytes; 4,096 bytes is the modern mobile default). Pages are organized into B-trees for tables and B*-trees for indexes.
The WAL Mechanism: In legacy rollback journal mode, changes were written to a .journal file before updating the database. In modern mobile systems, SQLite utilizes Write-Ahead Logging (WAL) :
Transactions ( INSERT , UPDATE , DELETE ) are appended sequentially to the *-wal file instead of altering the primary .db file immediately.
A memory-mapped index file ( *-shm ) tracks transaction offsets within the WAL.
The Checkpoint Operation: Periodically, the SQLite engine executes a checkpoint (PASSIVE, FULL, RESTART, or TRUNCATE), flushing WAL records back into the main database file's B-tree pages.
Critical Forensic Rule: If a forensic examiner copies or extracts sms.db without sms.db-wal and sms.db-shm , recent messages, deleted status flags, and updated contact links are completely lost from the analysis.
2. Deleted Record Recovery Mechanics
When a user deletes a message, call record, or contact, SQLite does not wipe the underlying data bytes:
Cell Unallocated Space: The record's cell header is marked as unallocated, and its offset is added to the page's internal freeblock linked list.
Database Freelist: If an entire 4,096-byte page is emptied of active records, the page is removed from the active B-tree and placed onto the database freelist ( freelist trunk pages and freelist leaf pages ). The freelist page count is tracked in bytes 36–39 of the SQLite database header.
VACUUM Operation: Deleted data remains in the database file indefinitely until the application explicitly executes VACUUM; or auto-vacuuming is triggered during low disk storage, which rebuilds the database file from scratch, discarding all freelist pages.
Recovery Workflows: Examiners carve raw deleted records from SQLite freelist pages using forensic suites (Cellebrite Physical Analyzer, Magnet AXIOM, Oxygen) or command-line utilities such as undark : # Dump deleted and unallocated records from an SQLite database
- undark -d mmssms.db --freelist > carved_freelist_messages.txt
- Android Forensic Artifacts +-------------------------------------------------------------------------+
- | ANDROID CRITICAL FORENSIC PATHS |
- +-------------------------------------------------------------------------+
- | Artifact Category | File System Path |
- |--------------------|----------------------------------------------------|
- | Call History | /data/data/com.android.providers.contacts/ |
- | & Address Book | databases/contacts2.db |
- |--------------------|----------------------------------------------------|
- | SMS & MMS Messages | /data/data/com.android.providers.telephony/ |
- | | databases/mmssms.db |
- |--------------------|----------------------------------------------------|
- | Chrome Browser | /data/data/com.android.chrome/app_chrome/ |
- | History & Cookies | Default/History, Cookies |
- |--------------------|----------------------------------------------------|
- | Wi-Fi Networks | /data/misc/wifi/wpa_supplicant.conf |
- | & Passwords | (or apexdata/com.android.wifi/WifiConfigStore.xml) |
- |--------------------|----------------------------------------------------|
- | App Sandboxes | /data/data/<package_name>/ (or /data/user/0/) |
- +-------------------------------------------------------------------------+
- 1. Contacts and Call Logs ( contacts2.db )
Located within the contacts provider package: /data/data/com.android.providers.contacts/databases/contacts2.db .
calls Table: Stores full incoming, outgoing, and missed call metadata:
number : Dialed or receiving telephone number string.
date : Timestamp of the call formatted as a 13-digit Unix Epoch timestamp in milliseconds (milliseconds since January 1, 1970 UTC).
duration : Call length in integer seconds.
type : Call disposition code:
- 1 : Incoming (Answered)
- 2 : Outgoing
- 3 : Missed
- 4 : Voicemail
- 5 : Rejected
- 6 : Blocked
raw_contacts & data Tables: Contain contact display names, associated email addresses, synced Google accounts, and social profile links.
2. SMS and MMS Messages ( mmssms.db )
Located at: /data/data/com.android.providers.telephony/databases/mmssms.db .
sms Table: Contains native short messaging text entries:
_id : Unique message index.
address : The telephone number of the sender or recipient.
date : Unix Epoch timestamp in milliseconds.
read : Read status ( 0 = unread, 1 = read).
type : Message directionality ( 1 = Received, 2 = Sent, 3 = Draft).
body : Plaintext contents of the SMS message.
pdu , part , and addr Tables: Handle Multimedia Messaging Service (MMS) artifacts, linking binary images, audio attachments, and multi-party recipient structures.
3. Application Sandboxing Architecture
Every installed Android package receives an isolated sandbox at /data/data/<package_name>/ (or symlinked to /data/user/0/<package_name>/ ). Standard subdirectories include:
databases/ : Holds application-specific SQLite databases (e.g., msgstore.db in WhatsApp, wa.db ).
shared_prefs/ : Extensible Markup Language ( .xml ) files recording application preferences, user account credentials, auth tokens, and last-sync timestamps.
files/ : Local assets, cached document downloads, and media files.
cache/ : Temporary transient objects and network thumbnail caches.
- 4. Wi-Fi Profiles & Network Discovery
- Legacy Android (Android 9 and earlier): /data/misc/wifi/wpa_supplicant.conf
Contains cleartext or pre-shared key (PSK) Wi-Fi access credentials, Service Set Identifiers (SSIDs), and network security configurations (WPA2/WPA3): network={
- ssid="Target_Corporate_WiFi"
- psk="CompanySecretKey2026!"
- key_mgmt=WPA-PSK
- priority=1
- }
Modern Android (Android 10+): Stored as XML configuration containers under /data/misc/apexdata/com.android.wifi/WifiConfigStore.xml .
- iOS Forensic Artifacts +-------------------------------------------------------------------------+
- | IOS CRITICAL FORENSIC PATHS |
- +-------------------------------------------------------------------------+
- | Artifact Category | File System Path |
- |--------------------|----------------------------------------------------|
- | SMS / iMessage | /private/var/mobile/Library/SMS/sms.db |
- |--------------------|----------------------------------------------------|
- | Call History | /private/var/mobile/Library/CallHistoryDB/ |
- | | CallHistory.storedata |
- |--------------------|----------------------------------------------------|
- | Safari History | /private/var/mobile/Library/Safari/History.db |
- |--------------------|----------------------------------------------------|
- | Location Caches | /private/var/mobile/Library/Caches/ |
- | | com.apple.routined/Local.sqlite |
- |--------------------|----------------------------------------------------|
- | Photos Metadata | /private/var/mobile/Media/PhotoData/Photos.sqlite |
- |--------------------|----------------------------------------------------|
- | Property Lists | *.plist (System & Application Preferences) |
- +-------------------------------------------------------------------------+
- 1. Property List Files ( .plist )
Property lists store user preferences, device configurations, and application states on iOS and macOS systems.
Formats: Exist in two distinct formats: human-readable XML format and high-density Binary format .
Binary Plist Signature: Binary plists begin with the 8-byte magic header bplist00 (hex: 62 70 6C 69 73 74 30 30 ).
Forensic Conversion: Examiners inspect and convert binary plists into readable XML using command-line utilities: # Convert binary plist to human-readable XML format
- plutil -convert xml1 com.apple.preferences.plist -o preferences_readable.xml
- 2. SMS and iMessage Database ( sms.db )
Located at /private/var/mobile/Library/SMS/sms.db .
message Table: Contains message records across SMS, MMS, and Apple iMessage:
text : Plaintext content of the message.
handle_id : Foreign key referencing the handle table to determine the sender or recipient phone number / Apple ID email.
is_from_me : Directionality indicator ( 0 = Received message, 1 = Sent message).
date : Timestamp recording the transmission time.
Apple Timestamp Mechanics (Mac Absolute Time / Cocoa Core Data Time):
iOS records timestamps in seconds (or nanoseconds) relative to January 1, 2001 00:00:00 UTC (epoch offset = 978,307,200 seconds after Unix epoch).
Nanosecond timestamps are stored as 18-digit integers (e.g., 717082800000000000 ). To convert to Unix time, divide by 10 9 10^9 1 0 9 and add 978 , 307 , 200 978,307,200 978 , 307 , 200 .
chat_message_join Table: Maps individual messages to specific multi-party group chats or one-on-one threads.
3. Call History ( CallHistory.storedata )
Located at /private/var/mobile/Library/CallHistoryDB/CallHistory.storedata (a CoreData SQLite database):
ZCALLRECORD Table: Core table storing telephony metadata:
ZADDRESS : Binary blob or string representation of the phone number.
ZDATE : Mac Absolute Time timestamp.
ZDURATION : Float or integer representing call duration in seconds.
ZORIGINATED : Boolean flag ( 0 = Incoming call, 1 = Outgoing call).
ZANSWERED : Boolean flag ( 0 = Unanswered / Missed, 1 = Answered).
4. iOS Geolocation Artifacts
Significant Locations ( routined Daemon): iOS tracks frequently visited locations (home, workplace, frequent coffee shops) to optimize predictive maps and battery routines.
Stored at /private/var/mobile/Library/Caches/com.apple.routined/Local.sqlite .
Contains ZRTLEARNEDLOCATIONOFINTERESTMO and ZRTLEARNEDVISITMO tables recording GPS latitude, longitude, horizontal accuracy radius (meters), arrival timestamp, and departure timestamp.
Historical Cell & Wi-Fi Geolocation ( consolidated.db / cellular.db ): Caches neighboring cell tower IDs and Wi-Fi BSSID geographical coordinates gathered by Apple location services to expedite GPS lock.
5. Photos Metadata & Photos.sqlite
Located at /private/var/mobile/Media/PhotoData/Photos.sqlite .
EXIF Metadata: Media files captured by the onboard camera (JPEG, HEIC, MOV) store Exchangeable Image File Format (EXIF) tags directly within file headers: GPS Latitude, Longitude, Altitude, Timestamp, Lens Model, and Sub-second shutter times.
ZGENERICASSET Table: Records photo capture state, albums, facial recognition tags, hidden status, and deletion tracking. The column ZTRASHEDSTATE flags whether a photo is active ( 0 ) or resides in the user's "Recently Deleted" album ( 1 ) awaiting permanent 30-day deletion.
Cellular Telecommunications & Call Detail Records (CDRs)
Handset location artifacts are frequently cross-examined against external carrier records provided via subpoena or search warrant. +-------------------------------------------------------------------------+
- | CELL SITE SECTORIZATION & AZIMUTH |
- +-------------------------------------------------------------------------+
- | |
- | Sector 1 (Alpha) |
- | Azimuth = 0° / 360° |
- | /\ |
- | / \ |
- | / \ |
- | / \ |
- | / __ \ |
- | / / \ \ |
- | / \__/ \ |
- | +-------|------+ |
- | | |
- | Sector 3 (Gamma) | Sector 2 (Beta) |
- | Azimuth = 240° | Azimuth = 120° |
- | \ | / |
- | \ | / |
- | \ | / |
- | \ | / |
- | v v v |
- | Beamwidth: ~65° Cellular Tower Beamwidth: ~65° |
- +-------------------------------------------------------------------------+
- 1. Call Detail Records (CDR) Architecture
Telecommunication service providers (AT&T, Verizon, T-Mobile) log network transactions generated at Mobile Switching Centers (MSC) and base stations:
Identifiers:
MSISDN (Mobile Station International Subscriber Directory Number): The standard phone number.
IMSI (International Mobile Subscriber Identity): Unique 15-digit code identifying the SIM card on the cellular network (MCC + MNC + MSIN).
IMEI (International Mobile Equipment Identity): 15-digit hardware serial number identifying the physical handset.
Event Types: Voice calls, SMS delivery (note: CDRs log message metadata, not message body text), data sessions, and location area updates.
Cell Global Identity (CGI): Every cellular tower antenna is uniquely identified by four parameters: MCC-MNC-LAC-CI (Mobile Country Code, Mobile Network Code, Location Area Code, and Cell ID).
2. Cell Site Analysis & Geospatial Triangulation
Antenna Sectors & Azimuth: Standard cellular macro towers feature three directional antenna faces spaced at 120-degree intervals, known as sectors (Alpha, Beta, Gamma):
Sector 1: Typically points North (Azimuth = 0 ∘ 0^\circ 0 ∘ ).
Sector 2: Typically points Southeast (Azimuth = 120 ∘ 120^\circ 12 0 ∘ ).
Sector 3: Typically points Southwest (Azimuth = 240 ∘ 240^\circ 24 0 ∘ ).
Beamwidth: Antennas concentrate radio energy across an approximate 60° to 65° horizontal beamwidth.
Timing Advance (TA): In LTE and 5G networks, the base station measures the round-trip signal propagation delay between the handset and the tower. The Timing Advance metric is converted into an accurate distance ring (radius from the tower) where the handset operated during the call.
Forensic Corroboration: Examiners overlay the tower coordinates, antenna azimuth angle, and Timing Advance distance arc over local mapping data, cross-referencing this footprint with the handset's internal SQLite location caches ( Local.sqlite or Google Maps history) to conclusively place a suspect at a physical crime scene.
- Practical Forensic Case: Resolving an Armed Robbery via Multi-Artifact Correlation
- The Incident
An armed robbery occurred at a jewelry boutique at 14:22 UTC. Surveillance cameras recorded a masked individual fleeing in a vehicle. Two hours later, a suspect was apprehended during a traffic stop; an iPhone 13 Pro was seized in AFU state.
Investigative Correlation Steps
Extraction & Database Acquisition: The forensic lab executed an Advanced Logical / Full File System extraction. Examiners verified that sms.db , sms.db-wal , and sms.db-shm were all present.
SMS Deletion Analysis: Querying the active message table in sms.db yielded no relevant communications. However, running an SQLite freelist carving tool against unallocated blocks in sms.db and the active .wal file recovered three deleted incoming messages received between 14:15 and 14:18 UTC reading: "Meet me at the back alley behind the jeweler now."
Timestamp Alignment: The message timestamps were stored in Mac Absolute Time ( 717084900 ). Converting to Unix Epoch ( 717084900 + 978307200 = 1695392100 717084900 + 978307200 = 1695392100 717084900 + 978307200 = 1695392100 ) aligned the messages to 14:15 UTC on the robbery date.
Geolocation Triangulation: Analysis of /private/var/mobile/Library/Caches/com.apple.routined/Local.sqlite revealed a visit entry in ZRTLEARNEDVISITMO placing the iPhone within a 15-meter horizontal accuracy radius of the jewelry store between 14:10 and 14:35 UTC.
Carrier CDR Validation: Carrier CDR records confirmed the suspect's IMEI and IMSI connected to Cell Tower #4021, Sector 2 (Azimuth 120°), with a Timing Advance indicating a distance of 450 meters—directly intersecting the jewelry store.
Result: The convergence of carved deleted SQLite messages, handset GPS artifacts, and carrier CDR cell sector logs defeated the suspect's alibi, resulting in a guilty plea.