Design a site like this with WordPress.com
Get started

Maximizing iOS Call Log Timestamps and Call Duration Effectiveness: Will You Answer the Call?

By: James R. McGee

Digital Forensic Examiner

United States Army

November 30, 2022

Synopsis: Call Log Artifacts have long been used to link connections between individuals, aided in pattern of life analysis, and utilized in attempts to establish user attribution data. This article will explore call log timestamps within iOS as they are typically parsed as well as establishing a simple and beneficial parsing change which expands the usefulness of these artifacts. Finally, this article will provide an “at a glance” reading of call durations through an advanced query structure.

Tools:

Magnet AXIOM Examine v6.8.0.33717

DB Browser for SQLite Version 3.12.2

Mockaroo (Used for test database values, any identification of persons or contact numbers within the dataset is accidental as the dataset is generated randomly.)

DCode Version 5.5

Background:

If you have used DB Browser for SQLite even minimally then you know the strength and computing power of the tool. A dataset combined with a logical SQL query results in a definite data yield output. Even extremely large datasets can be computed quickly and complicated SQL queries add trivially to the computation time. It was through query input and data output, the thought of “if this works then maybe this works”, and an obsession for syntax that I was inspired to attempt to see what was possible with SQL queries and found some interesting results along the way. All query additions described and shown within this article are expansions of the raw data from the storedata file, which can be validated manually. While limited to iOS in this article, these principles hold for other OS as well.

The queries within this article are based on the typical iOS Call Log Artifacts as commonly parsed both by individuals and through digital forensic software. These queries were expanded through my own work. Images of artifacts within this article are displayed through Magnet AXIOM, all data displayed was generated through Mockaroo, and all data yields were parsed through custom artifacts generated by myself.

NOTE: I hold no restrictions of use, no attempts at any right of intellectual property, nor any other limitations to the use of these queries or the incorporation of this information into open source or licensed software whatsoever. Please feel free to use this information and data as you see fit for the furtherment of the DFIR Community and to seek out the truth, wherever it may be.

Looking at Current iOS Call Log Artifacts:

Commonly, iOS Call Log Artifacts have a file source location of “Library/Application Support/CallHistoryDB/CallHistory.storedata” and contain just what you would imagine you would see when reviewing call log history. That is, a contact number, contact name (if present), direction as to incoming or outgoing, the type of call, call duration (presently in seconds), the application bundle ID, and perhaps a country code (again if present). Displayed through Magnet AXIOM, a layout of this data would likely look like the following:

This image is somewhat small due to number of columns, so let’s examine the interaction between the user of the device and Romy Piddock in more detail:

Here, displayed well through Magnet AXIOM, we can see the full details for this incoming phone call. This data is common to what we see currently and provides the necessary data; however, when examining data like this I quickly come to two questions:

  1. How long is a 1824.240226 second phone call?
  2. What time did this phone call end?

It was these questions that led me to the expansion of the query, adding one column and heavily adjusting another. After parsing, the query resulted in the following updated details for the above phone call from Romy:

Now, at a glance, we can see the total call duration was 30 minutes and 24 seconds, ending at 3:25:37 PM, October 13, 2021. In table view, for our six rows from above, we see the following:

Immediately, the value from these call log artifacts has increased in that there are really no remaining questions when viewing call log artifacts and there are now two timestamps for each answered call log. (Not answered, missed, or rejected calls within call logs will only have an initial call date/time and no call duration so there will be no end call date/time value). So, what is the big deal about having two timestamps for every answered call log artifact? Timeline values.

Let’s look again at Magnet AXIOM’s display of this data but now within the Timeline View, first for how call log artifacts would presently be displayed:

The top of the image displays our full timeline for this data set, the bottom displays two timestamps for two separate calls as well as the partner contact number, and the details pane displays the artifact information for our selected phone call from Romy. NOTE: An actual extraction would contain a myriad of artifacts beyond call logs increasing the significance of the timeline view, this dataset was based on randomized data to portray call logs only.

Now, let’s examine the same Timeline View but with two timestamps for each call log artifact:

The inclusion of a second timestamp within the Call Log Artifact doubles the data within our timeline! Displaying this information within the timeline could be the difference between linking nefarious activity on a device to the subject accused of the offense or potentially clear the accused through the presence of exculpatory data pointing to someone other than the accused. How many times, in your own examinations, have you looked at the initiation of call logs in comparison to timestamps of pertinent data? Is it possible that key data was missed just because it is not presently parsed? The possibility of missed data is especially seen in this case, wherein the incoming call from Romy Piddock was 30 minutes and 24 seconds and overlapped the hour mark by starting at 2:55:13 PM and ending at 3:25:37 PM. If nefarious activity on the device possessed timestamps around 3:30 PM and date/time filters of 3:00 – 3:59 PM were used, the call log artifact from Romy would not be present within the filtered data and could be missed.

Let’s examine the SQL Query which parses this vital data, available here.

The SQL Query – End Call Date / Time and Time Duration:

The full query is rather lengthy, mainly because of the time duration format change so it will be easier to examine in sections. The first several sections of the query are rather straightforward:

[ZADDRESS] – The contact information, whether in Apple ID, telephone number, WhatsApp number, etc.

[ZNAME] – The contact name, if present on the device.

[ZORIGINATED] – The direction of the communication, a simple case statement is used for incoming or outgoing.

[ZSERVICE_PROVIDER] – This column designates the application bundle ID associated with the communication, again a case statement defines whether this communication was through phone, FaceTime, or third party application.

[ZDATE] – The date/time for the call initiation (or timestamp for not answered, missed, rejected, etc. communications).

[ZDATE] and [ZDURATION] – The above case statement was used to obtain the end call date/time of the communications. This statement can be read as “when the initial timestamp is equal to the initial timestamp plus the time duration of the communication then nullify the value; else, when the initial timestamp does not equal the initial timestamp plus the time duration provide a date/time format for initial date/time plus the total time duration.”

What does this look like for raw data from the storedata file? Well, let’s look again at the incoming call from Romy Piddock. The ZDATE value was “655829713.4”, which DCode decodes to 2:55:13.400 PM, October 13, 2021, roughly the same as the timestamp within Magnet AXIOM. Our query would then add the ZDURATION value of “1824.240226” to the ZDATE value of “655829713.4”, equaling “655831537.640226”. DCode decodes this value as 3:25:37.640 PM, October 13, 2021 – again, the same as the end timestamp within Magnet AXIOM (with added 0.640 seconds).

This function provides the logical output because the datetime SQL function can convert added values to timestamps.

Slightly more complicated than this datetime function is our query for ZDURATION in minute(s) and second(s):

NOTE: I realize this text is small, again the full query is available in both .txt and .sql files here.

In short, SUBSTR and INSTR functions are used against the ZDURATION value to first differentiate whether the minute value would equal “1” or not equal “1”, as in “1 Minute”. If the minute value equals “1” then syntax is used to obtain a final value of “1 Minute and XX Second(s)” for the call duration. If the minute value would equal “0” or more than one minute syntax is used for “XX Minutes and XX Second(s)”.

Then, equally for the occurrence of one minute or the occurrence of 0 to multiple minutes, case statements are used in conjunction with SUBSTR functions against datetime functions for the initial timestamp plus the call duration value minus the initial call timestamp to obtain the number of seconds for the call duration. If the subtracted second value is negative then 60 seconds are added to the value. If the subtracted value is “1” then syntax is added for “Second” as opposed to “Seconds” used if the subtracted value is 0 or multiple seconds. When 60 seconds are added to the negative number of seconds the correct positive number of seconds is obtained but an additional minute does not need to be added to the total amount of minutes as the minutes value was obtained separately, and prior, to the number of seconds.

Initially, not detailed within this article, a more complicated combination of SUBSTR and INSTR functions was used to arrive at the number of seconds but was found to be inconsistent when used in comparison to the timestamp values for start and end date/times. (At times the second value was off by +/- a second.) Using the method within this SQL query, all second values are obtained from the rounding methods within the datetime functions and provide accurate time duration values compared to start and end timestamps.

Finally, we end the SQL query:

[ZSERVICE_PROVIDER] – The application bundle ID.

[ZISO_COUNTRY_CODE] – The Country code, if present.

A FROM clause defines the table containing the pertinent data as the ZCALLRECORD table.

Conclusion:

This article covered Call Log Artifacts as we have typically seen their data portrayed, the potential for the full data obtainable for Call Log Artifacts from the storedata file, and a breakdown of an advanced query capable of providing an “at a glance” review of call duration values. With the power of the tools within our toolbox and the understanding of this query for validation it is imperative we fully utilize all possible data points within our examinations. Who knows what critical data could be left unparsed? Will you use this query within your examinations, your software, your own .XML or Python scripts? Will you answer the call?

NOTE: While I am a huge fan of the Magnet Forensics Artifact Exchange, I will not be submitting the custom artifact generated through this work as additional research into the matter is required. Namely, consistent “application/octet-stream” value returns from the ZADDRESS column if data therein is a mix of phone numbers and Apple ID values. The main test data for this article was solely phone numbers so the error was not present. All variables of datatypes and categories were attempted within the .xml fragments which were still met with error for mixed ZADDRESS data values.

Sources/References:

Images used within this article were obtained from Magnet AXIOM, a Magnet Forensics product, and DB Browser for SQLite.

One response to “Maximizing iOS Call Log Timestamps and Call Duration Effectiveness: Will You Answer the Call?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: