An update to our prior work within Lagging for the Win: Querying for Negative Evidence in the sms.db. This version reports all missing ROWID values in the sms.db up to the message sequence number.

From the initial query we've:

• Moved the timestamps calculations earlier in the query.
• Added guid support.

The core logic of the query remains the same.

This SQL query identifies missing rows in the message table by comparing consecutive ROWID values and calculating the number of skipped rows. It also provides timestamp and guid values for the beginning and end of each missing interval.

SELECT * FROM (
  SELECT 
  CASE 
    WHEN length(DATE) = 18 
    THEN LAG(DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID) 
    WHEN length(DATE) = 9
    THEN LAG(DATETIME(DATE + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID)
    END AS "Beginning Timestamp",
  CASE 
    WHEN length(DATE) = 18 
    THEN DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH') 
    WHEN length(DATE) = 9
    THEN DATETIME(DATE + 978307200, 'UNIXEPOCH')	
    END  AS "Ending Timestamp",
  LAG (guid,1) OVER (ORDER BY ROWID) AS "Previous guid", 
  guid AS "guid", 
  LAG (ROWID,1) OVER (ORDER BY ROWID) AS "Previous ROWID", 
  ROWID AS "ROWID",
  (ROWID - (LAG (ROWID,1) OVER (ORDER BY ROWID)) - 1) AS "Number of Missing Rows"
  FROM message) list
WHERE ROWID - "Previous ROWID" > 1

• Uses the LAG function to access the previous row’s timestamp, guid, and ROWID.
• Retrieves the current row’s timestamp, guid, and ROWID.
• Calculates the number of missing rows by subtracting consecutive ROWID values:

  (ROWID - (LAG(ROWID, 1) OVER (ORDER BY ROWID)) - 1)

• Filters only the rows where gaps exist: ROWID - "Previous ROWID" > 1.

  WHERE ROWID - "Previous ROWID" > 1

Purpose / Highlights:

• Ensures the query detects gaps at the end of the message table that the original missing-ROWID logic could miss, depending on the data.
• Adds a final row if the last message ROWID does not match the maximum sequence number (sqlite_sequence.seq).
• Uses placeholders ("Unknown" for GUID, "Time of Extraction" for timestamp) to represent a single inferred row for missing data at the end of the table.

Detailed Explanation:

1. WITH LastROWID AS (...) Common Table Expression (CTE)

   • Retrieves the last assigned ROWID from the sqlite_sequence table for the message table.
   • Serves as the maximum sequence reference to detect any trailing missing rows.

   WITH LastROWID AS (
   	SELECT seq AS last_rowid
   	FROM sqlite_sequence
   	WHERE sqlite_sequence.name = 'message'
   )

2. Final Row Logic via UNION ALL

   • Uses the CTE LastROWID to obtain the table’s maximum ROWID (last_rowid) as a constant reference value.
   • When the maximum ROWID in the table is less than the reference last_rowid, adds a single synthetic “tail” row to represent the missing messages at the end.
   • Fills missing values with placeholders to indicate inferred data:
     • "Unknown" for the guid
     • "Time of Extraction" for the ending timestamp
   • Calculates Number of Missing Rows as (last_rowid - message.ROWID) to reflect the count of missing rows at the end of the table.

3. Outer Query Behavior

   • The new outermost SELECT * FROM (...) WHERE "ROWID" IS NOT NULL ensures that only valid rows are returned.
   • Key consequence: if the max ROWID equals the last sequence value, no row of null values is returned which prevents a potentially misleading empty row.

Key Takeaway:

• This extension guarantees complete coverage of missing rows, including any trailing gaps, without generating an additional empty row when the table is already complete up to the last sequence value.

WITH LastROWID AS (
  SELECT seq AS last_rowid
  FROM sqlite_sequence
  WHERE sqlite_sequence.name = 'message'
)
SELECT * FROM (
  SELECT * FROM (
    SELECT 
    CASE 
      WHEN length(DATE) = 18 
      THEN LAG(DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID) 
      WHEN length(DATE) = 9
      THEN LAG(DATETIME(DATE + 978307200, 'UNIXEPOCH'),1) OVER (ORDER BY ROWID)
      END AS "Beginning Timestamp",
    CASE 
      WHEN length(DATE) = 18 
      THEN DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH') 
      WHEN length(DATE) = 9
      THEN DATETIME(DATE + 978307200, 'UNIXEPOCH')	
      END  AS "Ending Timestamp",
    LAG (guid,1) OVER (ORDER BY ROWID) AS "Previous guid", 
    guid AS "guid", 
    LAG (ROWID,1) OVER (ORDER BY ROWID) AS "Previous ROWID", 
    ROWID AS "ROWID",
    (ROWID - (LAG (ROWID,1) OVER (ORDER BY ROWID)) - 1) AS "Number of Missing Rows"
    FROM message) list
  WHERE ROWID - "Previous ROWID" > 1

  UNION ALL

  SELECT
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN MAX(CASE
          WHEN length(DATE) = 18 THEN DATETIME(DATE/1000000000 + 978307200, 'UNIXEPOCH')
          WHEN length(DATE) = 9  THEN DATETIME(DATE + 978307200, 'UNIXEPOCH')
        END)
    END AS "Beginning Timestamp",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN "Time of Extraction"
    END AS "Ending Timestamp",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN guid
    END AS "Previous guid",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN "Unknown" 
    END AS "guid",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN MAX(ROWID)
    END AS "Previous ROWID",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN (SELECT last_rowid FROM LastROWID)
    END AS "ROWID",
  CASE
    WHEN message.ROWID != (SELECT last_rowid FROM LastROWID)
    THEN ((SELECT last_rowid FROM LastROWID) - message.ROWID)
    END AS "Number of Missing Rows"
  FROM message)
WHERE "ROWID" IS NOT NULL;

With the addition of the trailing-row logic and the LastROWID CTE, the query now captures metadata for gaps at the end of the message table that were previously invisible.

• Shows a gap of seven missing messages at the end of the table.
• Provides useful metadata, including the previous message’s guid and timestamp, usable either as search terms or as contextual guidance for analysis.
• This ensures complete coverage of all missing messages, including data that would have been omitted in the original query.

• The SQL queries will work in most SQLite database viewers able to execute SQL queries.
• The RowIDetective.xml file is a Magnet AXIOM Custom Artifact, which can be added to Magnet AXIOM Process through Tools > Manage custom artifacts > ADD NEW CUSTOM ARTIFACT.
• The iLEAPP artifact will be updated also, available here.

This query operates on sms.db, the database storing iMessage, SMS, and RCS data on Apple devices. It is found in all iPhone extractions and iTunes backups.