I was recently navigating an Apple iPhone Full File System Extraction for something not currently parsed by any digital forensic software. What I found was not exactly what I was initially looking for, but still proved to be an interesting and challenging digital puzzle. In this article, we will explore this new SQLite database, cover parsing key data through some SQL queries, and discuss additional important factors for the future and understanding this database.

All SQL queries formed through this original research are available here.

Device / Software Used:

Apple iPhone 8 (A1863) with iOS 15.0.2

DB Browser for SQLite Version 3.12.2

Cellebrite Physical Analyzer Version 7.62.2.9

The Database:

The siriremembers.sqlite3 database, simply put, is a combination of Biome and the interactionC database, with some parsing challenges because nothing Biome related can be easy. Here, we have a recording of AppIntents and the 28-day time frame of Biome paired with original application content similar to interactionC. The added benefit is deleted data interactions can still be reviewed here, for a time, whereas they will have been removed from the interactionC and KnowledgeC databases. The siriremembers database also records whether Siri was used during the AppIntent.

The siriremembers.sqlite3 database is located at /private/var/mobile/Library/com.apple.siri.inference/siriremembers.sqlite3 and a Full File System Extraction is required. Testing revealed a File System Advanced Logical Extraction did not obtain the database.

This, and all images within this work, are displayed through DB Browser for SQLite.

The database contains (for iOS 15) 23 tables, of which eight are required for our queries. The main table is the “intents” table, which can be compared to the “ZINTERACTIONS” table of interactionC. This is where the one-to-one comparison between the database similarities stops. Where the interactionC has the “ZCONTACTS” table, siriremembers has a complex combination of tables to include: “intents,” “group_entities,” “intent_entities,” “parameter_names,” and “entities.” This is further complicated when communication direction (sent / received, incoming / outgoing) is added to the mix. The breakdown of directionality will be covered in brief within this article.

The “intents” Table:

The above “intents” table data has been formatted for viewing here.

Each AppIntent, or intent, of the device is recorded within the “intents” table. This data is maintained for about 28 days and the database deletes these records uniformly with the Biome data. As said before, user actions (such as deleting iMessage content) do not impact this database as they do the interactionC and KnowledgeC databases. Actual message content is not stored within this database but metadata for the communication is maintained.

Looking at “intents” Table Column Values:

id – the row ID value for the record.

uuid – the universally unique identifier (UUID) for the siriremembers database of this record. This value can also be found within KnowledgeC and the Biome SEGB file when the value is searched through the Memory Image FileDump.

dkevent_uuid – the application bundle ID of the application generating the record followed by the UUID for the application.

group_id – the group identifier for the entity paired to the record.

domain_id – designates whether the record is related to messages, calls, media, etc. when joined to the “domains” table.

verb_id – designates the AppIntent for the record, such as “SendMessage” when joined to the “verbs” table.

app_id – designates the application bundle ID for the application generating the record when joined to the “apps” table.

direction – directionality for the record: 1 for “outgoing,” 2 for “incoming,” 0 for media records.

donated_by_siri – designates whether Siri was the medium for the AppIntent: 0 for “No,” 1 for “Yes.” Note: Siri involvement is only recorded when the device being examined completed the action.

handling_status – further testing required.

start_date – timestamp for the AppIntent.

duration_seconds – time duration for telephonic calls or Facetime calls.

created_at and updated_at timestamps – matched start_date value within all data, further testing required.

Key Data within the Database Include:

Messages

Applications whose AppIntents for Messages were recorded include, but not necessarily limited to, Messages, Snapchat, Messenger, Instagram, and Signal, possibly more with further testing.

The above is an example of incoming and outgoing Messages application messages as recorded within the siriremembers.sqlite3 database. It is important to note siriremembers does not record sender and recipient information as straightforward as the sms.db, used by the Messages application on iOS devices to store SMS and MMS messages, or the interactionC database. This will be covered more fully later in this article.

The most important factor to cover in relation to Messages is whether the communication was completed through Siri, or “Donated by Siri” as the siriremembers database records. On the surface, siriremembers uses a simple Boolean ‘0’ = ‘No’, ‘1’ = ‘Yes’ value; however, the data was a bit more complicated.

The above query result depicts how the database stores an additional row with some additional UUID data and “donated_by_siri” values indicating “Yes” and “No.” Well, because I tested this, I know this message WAS sent through Siri, so what is the difference with these records? Comparing our Application UUID, which reflects the UUID that would match the sms.db message UUID in this case, we can see two different values. Searching the Memory Image FileDump Application for UUID CFE44898-DB3C-4FCB-BBDC-4D03584AE0FD does not return a result within the sms.db. That is because our bottom row, UUID 341349BD-455A-4D9E-9372-53CCFF57F1FB with “donated_by_siri” = “No,” is the actual sms.db UUID for this message.

I also observed how the database was inconsistent in recording these rows in relation to the “donated_by_siri” values. That is, in the above example, the Intents ID 19 iMessage was completed using Siri and leads Intends ID 20 data which indicates no Siri involvement. At other points within the database the row with no marker of Siri usage led the data where Siri was used to complete the message.

My solution to this issue, in part, is the following:

A CASE statement is used to determine the “Application UUID” value. If the data is contributed by Siri (donated_by_siri = ‘1’), the query compares timestamps with the next and previous rows. If the timestamps match with the next row, it retrieves the subsequent UUID; if with the previous row, it retrieves the preceding UUID. When Siri is not involved, the UUID is directly extracted. This establishes a dynamic UUID selection based on Siri involvement and timestamp matches, providing flexibility in data retrieval.

But this is only part of the solution as both rows of data will still be present in the overall SQL query results and now the correct UUID will be duplicated. Unfortunately, at least within DB Browser for SQLite, we cannot use a LEAD or LAG function within a WHERE clause.

Another CASE statement is required:

This CASE statement again compares Siri usage and timestamps across rows but ultimately adds an additional column to our results which is not organically within the siriremembers database. This new “Active Row” column can be used within our WHERE clause to provide the correct number of sent and received messages.

Calls

Applications whose AppIntents for Calls were recorded include, but not limited to, Phone and Facetime, possibly more with further testing.

Happily, in comparison to Messages, Calls are much more straightforward. Here is an example of Call records parsed from the database:

Siri usage, with Calls, does not impact the source Application UUID so the overall query is much more straightforward.

Two important notes with Calls:

• There does not appear to be a marker differentiating a FaceTime Audio Call and a FaceTime Video Call.
• Call duration values will match or exceed the length of the call, the time duration begins when the user accesses the Phone application or dialer.

Media

Applications whose AppIntents for Media were recorded include, but not limited to, Music and Youtube, possibly more with further testing.

Here is an example of Media parsed from the database:

Here we can see, at the given timestamp, the Music application was used to “bbc radio 1 tunein” and Siri was not used to initiate the audio. Songs and other audio owned by the user of the device will be listed by name through this query. Natively, there are additional data markers within the “Media” column; however, this query uses a combination of SUBSTR and INSTR functions to provide solely the actual media content.

Dealing with Groups and Entities, Recipients and Senders:

As discussed above, the “intents.group_id” (the “intents” table, “group_id” column) identifies the group for the entity (or entities) paired to the record.

We can review the data for “intents.id” as an example:

From the “intents” table, reviewing left to right, we can see this is the first record present; the UUID for the siriremembers database, the application bundle ID and UUID from said application, our group ID, domain data which corresponds with a message, verb data for a SendMessage AppIntent, that the message was sent, Siri was not used to send the message, and the timestamp (stored in Unix epoch) which decodes to 15:13:01 (UTC+0), December 4, 2023.

Our group ID for this record is “1.” If we were to look at this record manually, we could keep this value in mind and look at the “groups” table.

Our “intents.group_id” value of “1” corresponds to our “groups.id” value of “1.” This table depicts the “groups.ordered_entity_ids” as “1,2,3” meaning the overall database entity values are “1,” “2,” and “3.”

You could also review this same data within the “group_entities” table:

Again, we can see our group_id value of “1” is paired to entity IDs “1,” “2,” and “3.”

But that still tells us little about this record. Keeping in mind our entity values of “1,” “2,” and “3” we turn to the “entities” table.

The “entities.id” value is joined to the “group_entities.entity_id” value and contains the same data as “groups.ordered_entity_ids.” Here, for the first time, we can see contact data for our sent iMessage/SMS/MMS message. We now have “james trickington,” “17175550177,” and “5705550175” but that still not the full picture. We still have to remember our entity values and our original intent ID and turn to the “intent_entities” table.

Here we have our “intents.id,” our group related entity ID values, and parameter_name_id values. Finally, we are almost there! Remembering that entity IDs “1,” “2,” and “3” are paired with parameter_name_id “1,” “2,” and “3” we can turn to the “parameter_names” table.

Granted, you could skip from the “intents” table directly to this table, but the long way allows us to cover the groups tables.

Our sender handle aligns with ID “1,” recipient handle with “2,” and recipient with “3”. Reviewing the “entities” table with these values in line we know can say the iMessage/SMS/MMS message was sent to “james trickington” whose contact number is “570-555-0175” and the message was sent from our device which has contact number “717-555-0177.”

This same data, depicted through DB Browser for SQLite and made to fit would look like:

Additional Important Factors:

Because the SQL in SQLite literally stands for “Structured Query Language,” you would imagine there is a structured and concrete backing to this database. Well, there is and there is not – at least not in the thought of a standard across devices as we see with other databases in mobile forensics. The sms.db, for example, is structured the same from one device to another and the only difference is the contents (outside of iOS version changes). This is what we would expect from a mobile forensics database. We do not have this standardization to the same effect with the siriremembers database.

For example, the “parameter_names” table, which is critical for sender and recipient data in message direction. Having written a myriad of SQL queries, I would (and did) look at this database and start thinking about CASE Statements. Such as: “CASE WHEN parameter_names.id = ‘4’ THEN ‘Sender’ END as Sender.” Through testing across devices, it became clear this table is not standard across devices and data is added following user actions on the device. If, instead of sending or receiving communication, the user of the device accessed music on their device then the id value “1” would instead be paired to “mediaSearchItems” or “mediaContainer” depending how the music was accessed.

This additionally can be seen within the “apps” table, which pairs id values and application bundle IDs; the verbs table, id values and AppIntent values such as “SendMessage” and “StartCall:” and the “domains” table, id values and names such as “Messages,” “Calls,” and “Media.”

So, when querying the siriremembers database, we cannot use id values and must instead use name values.

The “biome_ids” Table:

Finally, I would be remiss if I did not include mention of the “biome_ids” table included in the database. This table has just two columns: “id” and “dt” and provides application bundle ID, UUIDs, and timestamps. However, it is important to note that the timestamp provided is not accurate to the AppIntents themselves but an aggregated value for this table.

Data within this table, on average, was a longer timeframe than data within the “intents” table but this table alone does not provide directionality, sender/recipient, and additional data covered above.

Conclusion:

In conclusion, the siriremembers.sqlite3 database is an interesting find, weaving together Biome and the interactionC database with a distinctive set of parsing challenges. Through its lens, we gain additional insight into AppIntents within a 28-day Biome timeframe, accompanied by original application content reminiscent of interactionC. Having delved into this rich resource, I hope the gathered data proves valuable in unraveling insights and to gain an understanding the siriremembers.sqlite3 database is but a steppingstone. The journey is ongoing, and there is much yet to discover and comprehend in this intricate interplay of data and interactions.