Photos.sqlite Query Documentation & Notable Artifacts

As many of you are aware, I recently updated my Photos.sqlite queries. Since releasing the different query iterations, I have received several questions about how I was able to decode the data included in the queries. That’s a great question! I also noticed several questions being posted to the listservs and DFIR Discord about the different photo storage locations and iCloud asset behavior. I hope to answer some of those questions with this write-up.

Prior to getting started, I would like to point out most of the data and documentation contained within this write-up is from an iPhone X with iOS 14.7. I will mention artifacts that were discovered when working on the iOS 15 Photos.sqlite query, but keep in mind, I have not tested iOS 15 as much as iOS 14. The iOS 15 queries that have been previously released were tested up to iOS 15.3.1. Further iOS 15 testing will be needed to verify if anything is missing or if the decoding needs to be updated. This write-up should be considered a working document from this point forward. I will be adding additional iOS 15 test results very soon.

This write-up will outline some notable artifacts and how I was able to decode relevant data stored in Photos.sqlite and store.cloudphotodb for the queries. This will include what you might find when an asset is captured, saved, shared, deleted, or changed within the photo’s application (com.apple.mobileslideshow), an iCloud asset, or a shared album and associated assets. The data breakdown is based on what I observed during testing. Like I’ve stated in the past, there is no way for me to test and document every situation that can occur on an iPhone. This is my attempt to document some behaviors that might assist you with getting started with your own research and testing for a particular artifact.

I debated about breaking this write-up into smaller components or separate blogs but couldn’t find areas that made sense to break it apart, so I left this as a very long write-up. When reviewing the write-up, I would suggest using the find feature to discover specific artifacts you are interested in analyzing. When using my queries, I would again suggest using the find feature and search using query output column names you are analyzing. These column names can be listed multiple times throughout the write-up as they can contain different data depending on the asset. If you cannot locate any information about the data you are interested in analyzing, please feel free to contact me and I will help the best I can. 

NOTE: The example videos (Figures) within this write-up have accelerated playback speeds. The playback speeds are accelerated to make the videos shorter in length. If you are having difficulties reviewing the data with the videos, you might want to use a video player that allows you to play the video at a slower rate, like VLC.

NOTE: In some videos, the ratio might not be ideal for viewing on one monitor or on a mobile device. If you are having difficulty with the video ratio, try stretching your player (VLC) across multiple monitors to view the data/video in a better ratio.

NOTE: Within this write-up the term asset will be used to describe a photo, video, other file, and/or an item listed in the Photos.sqlite database.

Test Devices

  • Scooter Scott
    • iPhone X A1865 NQCP2LL/A
    • iOS 14.7 (18G69)
    • SIM and data plan
  • Dexter Scooter
    • iPhone 7 A1660 MNAJ2LL/A
    • iOS 15.1 (19B74)
    • Wi-Fi only
  • Bandit Scooter
    • iPhone 6s Plus A1687 MKV22LL/A
    • iOS 15.3.1
    • Wi-Fi only

Using Photos.sqlite queries

How to use my Photos.sqlite queries:

  1. Load your data acquisition using your favorite SQLite tool
  2. Navigate to the Photos.sqlite files within the device file system location
  3. Extract the database and WAL file to a container
  4. Create a working copy of the extracted files
  5. Load the working copy database using your favorite SQLite tool
  6. Navigate to the current Photos.sqlite queries found on GitHub
  7. Locate the query for the iOS you are analyzing
  8. Select the appropriate query
  9. Highlight all query text, using the shift key might help
  10. Reopen the SQLite tool, in this example using DB Browser
  11. Navigate to the Execute SQL tab and paste the query text into the top portion of the window
  12. Click the play button and watch for errors. In DB Browser pay special attention to the lower right corner of the application and notice the red stop sign. Once the stop sign is no longer visible the processing has completed
  13. In this example the execution finished without errors and resulted with 503 rows
  14. We can now review that data

The query should provide you the necessary information you may need for your analysis, but here are some additional instructions you may want to apply to narrow the results. In Figure #0.0, I will narrow down the results to only the assets where a face has been detected.

  1. Notice at the bottom of the query there are several WHERE statements that have been ignored, indicated by the – – at the beginning of the WHERE statement
  2. Remove the – –  from the WHERE statement you would like to use
  3. Press the play button and observe the results

In Figure #0.0 you will notice another example of narrowing down the results using the date created column data.

Figure #0.0

So, let’s get analyzing!!

Preparation for factory reset

Prior to this round of testing, the device, an iPhone X A1865 (iPhone10,3) with iOS14.7 (18G69), was factory reset and the following steps were taken prior to reset:

  • All assets from photos application including all shared assets and shared albums were moved to the recently deleted, then permanently deleted
  • All assets saved in iCloud Photos for the test device Apple ID were moved to recently deleted then permanently deleted

NOTE: There was a Shared Album, which contained Photo Cloud Sharing Data assets, that was missed and not deleted during the factory reset. During figures/examples in this write-up, you may notice leftover artifacts from this Shared Album. These items will be discussed in detail after iCloud Photos and Shared Albums is turned ON   

Figure #1.1

Factory reset

  • Factory reset was started on 2/25/2022 at 5:49 PM (UTC-8) or 2/26/2022 at 01:49 AM (UTC-0)
  • Test device setup started on 2/25/2022 at 5:50:42 PM (UTC-8) or 2/26/2022 at 1:50:42 AM (UTC-0)
    • com.apple.purplebuddy.plist
      • guessedCountry – 2/26/2022 at 1:50:55 AM (UTC-0)
      • SetupLastExit – 2/26/2022 at 1:56:34 AM (UTC-0)
    • com.apple.assistant.backedup.plist
      • Cloud Sync Enabled Modification Date – 2/26/2022 1:55:26 AM (UTC-0)
    • com.apple.cloudphotod.plist
      • _CPLLastCPLUpgrade – 2/26/2022 1:56:35 AM (UTC-0)
      • _CPLLastOSUpgrade – 2/26/2022 1:56:35 AM (UTC-0)
    • com.apple.MobileBackup.plist
      • AccountEnabledDate – 2/26/2022 1:55:40 AM (UTC-0)
  • After the factory reset the previously used Apple ID was used to set up the device as a new device
  • The device was not restored from iCloud Backup
  • Applications were downloaded and installed
  • Device settings were accessed, and iCloud Photos and Shared Albums were turned OFF
    • Just a reminder, these settings can be verified via the property list (plist) being stored at the following locations:
    • \private\var\mobile\Media\PhotoData\private\com.apple.assetsd\cloudServiceEnableLog.plist
      • Settings can be accessed on the Device via Settings > Apple ID > iCloud
        • This plist will have a timestamp (UTC) when iCloud Photo Library (CPL) was enabled or disabled indicated via a True or False value
        • Based on testing False indicates the setting was turned OFF
        • This plist keeps history of the device settings
    • \private\var\mobile\Media\PhotoData\private\com.apple.accountsd\cloudServiceEnableLog.plist
      • Device settings accessed via Settings > Apple ID > iCloud > Photos
        • This plist will have a timestamp (UTC) when iCloud Photo Library (CPL) and Shared Albums were enabled or disabled indicated via a True or False value
        • Based on testing False indicates the setting was turned OFF
        • This plist keeps a history of the device settings
        • Figure #1.2 at 0:02:11 for a video showing these changes to the plist
Figure #1.2

Photos.sqlite tables which contained data after the factory reset

Figure #2 is of the device after factory reset. This video shows some of the data that was left behind in the Photos.sqlite database after factory reset. Notice the following tables contained data:

  • ACHANGE
  • ATRANSACTION
  • ATRANSACTIONSTING
  • ZALBUMLIST
  • ZGENERICALBUM
  • ZGLOBALKEYVALUE
  • ZMIGRATIONHISTORY – After this factory reset, the timestamp (UTC) listed in the migration history entry was the correct date and time of the factory reset
Figure #2

Migration History Table

Figure #3 is the data that remained in the ZMIGRATIONHISTORY table after the factory reset. Using the data in the ZMIGRATIONHISTORY table and files listed above, I’m confident to say a factory reset occurred and Scooters, iPhone X with iOS 14.7 (18G69) was restored on 2/25/2022 at 5:50:42 PM (UTC-8) or 2/26/2022 at 1:50:42 AM (UTC-0).

  • The following are some additional plists that were located which contained versions and timestamps that indicated when the factory reset took place. If you are interested in artifacts related to factory resets and when a device was upgraded, I would recommend conducting a file system search using the term migration
  • \private\var\mobile\Library\Preferences\
    • com.apple.migration.plist
      • buildVersion – indicates the build version installed during the last migration
      • LastSystemVersion – indicated the build version of the last version prior to the migration
  • \private\var\installd\Library\MobileInstallation\
    • MigrationInfo.plist
      • Stores data about the current operating system and the full version
  • \private\var\mobile\Media\PhotoData\Caches\
    • PLDataMigrationInfo.plist
      • Stores migration build version and migration date matching the Photos.sqlite migration table
  • \private\var\installd\Library\MobileInstallation\
    • RoleUserMigration.plist
      • Stores migration build version and migration date matching the Photos.sqlite migration table
  • NOTE: At this time, I am unsure if this data will remain after a major update (iOS 14 to iOS 15)
Figure #3

Live Photo captured with Scooters iPhone X – iCloud Photos off

Figure #4 is of me using the iOS 14 Photos.sqlite query. As a result of the query, there are 0 rows of data. ZASSET and ZADDEDASSETATTRIBUTES tables have 0 records. The device is then used to capture the live photo, IMG_0001.HEIC.

  • In this example the asset captured was a live photo, but if the asset type is unknown, you can analyze the following columns to determine the asset type:
    • zAsset-Kind – indicates if the asset is a Photo or a video
    • indicates the type of photo or video. Decoded values include:
      • Still-Photo-0
      • Live-Photo-2
      • SpringBoard-Screenshot-10
      • Video-100
      • Slow-Mo-Video-101
      • Time-lapse-Video-102
    • zIntResou-Resources Type – will assist you with determining the difference between asset types. Decoded values include:
      • Photo-0
      • Video-1
      • Live-Photo-3
      • HEIC-with-Adjustment-Made-Prior-to-Capture-5
      • Screenshot-6
      • MOV-13
    • zAsset-Video Duration – will provide you with the length of the video in seconds
    • zAsset-Video CP Duration – will provide you with the length of the Live Photo in seconds
  • Each asset will have a zAsset-UUID and can assist you with determining why an asset is being listed multiple times in the query output. The most common explanation is due to an asset being listed multiple times in the same table. The most common tables to have multiple entries for the same asset are:
    • ACHANGE
    • ATRANSACTION
    • ATRANSACTIONSTRING
    • ZINTERNALRESOURCE
    • ZDETECTEDFACE
    • ZMEMORY
    • ZPHOTOHIGHLIGHTS
  • zAsset-UUID – Apple defines a UUID as “A universally unique value that can be used to identify types, interfaces, and other items.” Additionally, their developer documentation states: “UUID: Create and returns a new UUID with RFC 4122 version 4 random bytes”.Additional documentation can be found on Apple Developer website.
  • IMG_0001.HEIC was the first asset created after the factory reset but notice the primary key/row ID is 14. Not exactly sure why this occurred at the time, but it was later discovered that it was due to the missing Shared Album and shared assets. This could also be an indicator of a factory reset or deleted assets
  • There is no data in the ZCLOUDMASTER table for the captured asset because iCloud Photos is off
  • This asset was created using iOS 14.7 and notice there isn’t any data populated for the ZEXTENDEDATTRIBUTES table columns. During testing, this data was only populated when a device was using a version of iOS 15
  • zAddAssetAttr-Imported By column indicates Native Back Camera-1. The data located in this column will provide insights into how the asset was imported into the Photo Library and Photos Application. In this example, this is an indication of which camera (front or back) camera was used to capture the asset. Decoded values include:
    • Cloud-Other-0
    • Native-Back-Camera-1
    • Native-Front-Camera-2
    • Third-Party-App-3
    • Third-Party-App-6
    • Native-App-8
    • Native-App-9
    • Additional values were observed during testing, but I have yet to confidently decoded them
  • zAsset-Orientation and zAddAssetAttr-Original Orientation indicate Vertical-Camera-(top)-6 which means the device was vertical and the camera was at the top of the device at the time of the capture. Decoded values include:
    • Video-Default-Adjustment-Horizontal-Camera-(left)-1 – is the default value & value for all videos
    • Horizontal-Camera-(right)-2
    • Horizontal-Camera-(right)-3
    • Horizontal-Camera-(left)-4
    • Vertical-Camera-(top)-5
    • Vertical-Camera-(top)-6
    • Vertical-Camera-(bottom)-7
    • Vertical-Camera-(bottom)-8
  • zAsset-Latitude and zAsset-Longitude are populated with the correct location coordinates, but the zAddAssetAttr-Reverse Location Data Valid and zAddAssetAttr-Shifted Location Valid data indicates not valid, this is because the locations have not been analyzed. You will see in later videos, these values will change to valid locations
  • The following timestamps are off by a few seconds. This is normal due to the amount of time it takes to process, save, and record assets after being captured. These timestamps can be the same or off by a few seconds. At this point during testing, there has not been any user interaction with the asset:
    • zAsset-Date Created – the timestamp (UTC) the asset was created
    • zAsset-Local Date Created – The query uses the local workstation timestamp and time zone settings to convert the zAsset-Date Created timestamp into the local UTC offset timestamp. This is the only timestamp within the query where a conversion of the UTC offset is changed   
    • zAddAssetAttr-EXIF Timestamp String – is a timestamp, in most cases, based on what I have observed during testing, this timestamp is recorded based on the capturing device date, time, and its time zone setting when the asset was captured. An example of this can be found in Figure #24.
      • NOTE: Use caution when stating an EXIF timestamp string is the timestamp when an asset was captured. During testing, I encountered two instances when the zAddAssetAttr-EXIF Timestamp String was not the correct timestamp for when the asset was captured. Those two instances:
        • Using Scooters iPhone X (iOS14.7), accessing Snapchat memories, then saving a Snap from the Snapchat application to the device Photo Library. When doing this action, the EXIF timestamp string indicated a timestamp for when the asset was saved/imported to the Photo Library, not when it was captured within Snapchat
        • When using Dexter’s iPhone 7, I modified the EXIF timestamp string on the device, then sent the asset to Scooters iPhone X and saved it to the Photo Library
        • When reviewing the data in Photos.sqlite for both instances, the EXIF timestamp string was not the timestamp the assets were captured nor when the original was created   
    • zAsset-Add Date – in this example the add date timestamp matches the EXIF timestamp string. This asset as not been added to any albums
  • In Figure #4, you will notice some Photos.sqlite decoded data that has not been discussed. My goal with these videos and short descriptions is to document some of the data that exists to provide you with examples you can reference if you encounter something similar during your analysis
Figure #4

Still Photo captured with Scooters iPhone X – iCloud Photos off

Figure #5 is of a still photo (IMG_0002.HEIC) being captured and the data populated as the result of the asset being captured. After the asset was captured, the asset was viewed in the Photo Library and the asset (IMG_0002.HEIC) was given a description. Asset (IMG_0001.HEIC) was and liked/marked as a favorite by pressing the heart.

  • If you look closely at the device there is a small preview window in the lower left corner of the camera application, you will notice the preview image updates after the new asset is captured
  • zAsset-Visibility State is highlighted in Figure #5 which can provide insight into whether a particular asset is visible in the Photo Library. During testing, most of the assets listed in the Photos.sqlite will be visible in the Photo Library, but shared assets between different Apple ID’s may not be visible in the Photo Library and only in the Shared Albums area of the photo’s application
  • zAsset-Modification Date updates after the description was made and the asset was liked/marked as a favorite. During testing, it was discovered the modification date updates after user and/or system actions. This makes it difficult to determine exactly what caused the modification date to update or change
  • zAsset-Favorite will indicate if the asset (IMG_0001.HEIC) was liked or marked as a favorite. In this example the asset was marked as a favorite by pressing on the heart and now indicates Asset Favorite-1
  • zAddAssetAttr-Asset Description and zAssetDes-Long Description indicates if the asset has a description. After the description was added to IMG_0002.HEIC, the data was updated and now contains the text that was entered
  • Based previously published research and this testing, the normal operation when an asset is captured with the native camera application, is the asset(s) will be stored in the following location:
    • \private\var\mobile\Media\DCIM\<*>APPLE
  • \private\var\mobile\Media\PhotoData\MISC\
    • DCIM_APPLE.plist – will provide you with the following information:
      • DCIMLastDirectoryNumber key, meaning the last folder number used
        • Example: if the value for this key is 100 than the last folder containing data should be 100APPLE; and
      • DCIMLastFileNumber key, is the last asset number used for a filename
        • Example: if the value listed for this key is 30 then the last asset should have a filename starting with IMG_0030
    • NOTE: There is a different plist if you are analyzing shared folders and shared assets, which will be covered later in this write-up
  • \private\var\mobile\Media\PhotoData\Thumbnails\V2\DCIM\<*>APPLE\
    • Will store thumbnails for assets saved to
      • \private\var\mobile\Media\DCIM\<*>APPLE
        • The folder name is the original asset filename with file extension. The asset named folder, will contain a thumbnail file with a filename of 5005.JPG. Based on testing, all the thumbnails located within each asset folder will have the same filename, 5005.JPG
  • Just a reminder, at this time during testing, iCloud Photos and Shared Albums are not being used and the device has not been synced with iCloud
Figure #5

Assets added to Generic Album – iCloud Photos and Shared Albums off

Figure #6 shows the creation of a Generic Album and placing two assets, IMG_0001.HEIC and IMG_0002.HEIC, into the album. The copy function was used to place one of the assets into the album, but this action did not trigger a zAddAssetAttr-Destination Asset Copy State value to be populated.

  • zGenAlbum-Cloud Local State indicates if an asset is being stored in a Generic Album or in a Shared Album. Decoded values include:
    • iCldPhotos ON=Asset Share Album/iCldPhotos OFF=Asset in GenAlbum-0
    • iCldPhotos ON=Asset in Generic Album-1
    • In Figure #6 and other examples, you might notice some incorrect decoding. In this example, the data indicates Asset in Cloud Shared Album-0, but neither of these assets were in a Shared Album. The decoding in previous queries was incorrect and has been corrected in the most recent release
    • During the recent testing, it was determined the values in this column could have different definitions depending on device settings
    • The correct decoding is:
      • When iCloud Photos is OFF, a value of 0 indicates an asset is in a Generic Album
      • While iCloud Photos is OFF, no assets had a value of 1 & further testing is needed
      • When iCloud Photos is ON, a value of 0 indicates the asset is in a Shared Album
      • When iCloud Photos is ON, a value of 1 indicates the asset is in a Generic Album
  • Generic Albums will have a parent album of (–Root-Folder–)
    • NOTE: In most cases Generic Albums will have the root folder listed as the parent album, but if a user creates folders and then creates albums in those folders, there will be different parent albums listed
  • The zGenAlbum-Title is the album title
  • The zGenAlbum-Creation Date is the date (UTC) the album was created
  • The zGenAlbum-Start Date is based on the asset in the album with the earliest creation date
  • The zGenAlbum-End Date is based on the asset in the album with the latest creation date
Figure #6

Adjustments / Mutations made before and after assets were captured – iCloud Photos off

Figure #7 is of IMG_0003.HEIC and IMG_0004.HEIC being captured. Adjustments / Mutations were made before and after the assets were captured.

  • IMG_0003.HEIC was captured with the device after the flash was turned ON and back camera orientation was changed from top to bottom of the device, then the database was updated
    • There is only one entry in the query output for the captured asset
    • While watching Figure #7, pay special attention to empty fields associated with IMG_0003.HEIC, specifically column names which include the term Cloud or Cld. If the device had iCloud Photos ON, majority of these columns would contain data
    • zAddAssetAttr-Imported By can assist you with determining how the asset was imported to the device Photo Library. In this instance the data indicates Native-Back-Camera-1
    • zAsset-Orientation can assist you with determining the device orientation at the time the asset was captured, in this instance it indicates Vertical-Camera-(bottom)-8 meaning the device was vertical and the camera lens was at the bottom of the device
    • zAsset-Has Adjustments will indicate if the asset has been adjusted / has mutations, in this case IMG_0003.HEIC does not have any adjustments at this time
  • At 1:46 in Figure #7 I have highlighted the area of the query output that will assist with determining if an asset has been viewed or played
    • IMG_0001.HEIC has 2 Pending Views
    • IMG_0002.HEIC has 8 Pending Views
    • IMG_0003.HEIC has 0 Pending Views
    • zMedAnalyAstAttr-Media Analysis Timestamp – will indicate the latest timestamp the asset was analyzed. The view and play counts will not change from pending to viewed or played until the asset is analyzed again and the media analysis timestamp is updated
  • IMG_0003.HEIC is then opened and viewed
    • Adjustments / Mutations were made to the asset after it was captured, which resulted in data being populated in the ZUNMANAGEDADJUSTMENTS table. Additionally, data was recorded in the zAsset-Has Adjustments and zUnmAdj-Adjustment Format Version that can be used to indicate the type of adjustments that occurred.
    • After the database was reloaded, now there are two entries in the query output for IMG_0003.HEIC
    • zAsset-Has Adjustments indicates the asset has adjustments by indicating Yes-Adjustments-1 and additional data has now been populated about the adjustment
  • Prior to IMG_0004.HEIC being captured, a filter was applied, and then the Live Photo was captured. Then the database was reloaded
    • There are six entries in the query output for IMG_0004.HEIC
    • zUnmAdj-Adjustment Timestamp (UTC) indicates when the latest adjustment took place
    • zAddAssetAttr-Edit Bundle ID will indicate the bundle ID of the application used to adjust/mutate the asset. This column might not contain data, even after an adjustment has been made.
      • NOTE: This data column was added to Photos.sqlite after one of the dot iOS 14 updates and may not exist in all versions of iOS 14. This is one of the data columns that may cause the query to fail.
    • zUnmAdj-Editor Localized Name will indicate the application name used to adjust/mutate the asset. This column might not contain data, even after an adjustment has been made
    • zUnmAdj-Adjustment Format ID will indicate the format ID used to adjust/mutate the asset. This column might not contain data, even after an adjustment has been made
    • zIntResou-Data Store Sub Type is one of those columns that you can analyze to determine why one asset has so many entries/rows in the query output. The decoding of this column is a work in progress and additional testing is required. Decoded values include:
      • No Cloud Inter Resource-0
      • Main-Asset-Org-Size-1
      • Photo-with-Adjustments-2
      • JPEG-Cloud-Share-3
      • JPEG-Med-Data-4
      • JPEG-Small-Data-5
      • Video-Med-Data-6
      • Video-Small-Data-7
      • MP4-Cloud-Share-RecipeID-131079-8
      • PNG-CPLAsset-Markup-13
      • Has-Markup-and-Adjustments-15
      • Video-Length-Edit-16
      • Live-Photo-Video-Org-Size-18
      • Live-Photo-with-Adjustments-19
      • Settings-Changed-Prior-to-Capture-22
      • MOV-CPLAsset-Small-Data-26
    • zIntResou-Uniform Type ID will assist you with determining the uniform type for each for asset row in the query output. Decoded values include:
      • zIntResou-MetaData-1
      • Main-Asset-Live-Photo-Local-Device-2
      • MPEG4-Asset-via-Cloud-3
      • Main-Asset-HEIC-Local-Device-4
      • Main-Asset-PNG-Local-Device-5
      • HEIC-6
      • Live-Photo-Video-Info-7
      • HEIC-Adjustments-Filter-8
      • GIF-9
    • zAsset-Modification Date (UTC) will be updated after an asset adjustment
    • zUnmAdj-Adjustment Format Version indicates the version of the adjustment / mutation. Decoded values include:
      • Markup-1.0
      • Slow-Mo-1.1
      • Filter-1.4
      • Adjust-1.5
      • Video-Trim-1.6
      • ScreenshotServices-2.0
      • NOTE: I attempted to use different adjustments / mutations before and after an asset was captured. I was only able to decode a few of them. I’m sure there are additional adjustment format versions that have not been reproduce and decoded. Additional values have been observed during testing, but I was unable to confidently decode them, and additional testing is required
Figure #7

Adjustments / Mutations stored files and file paths

Figure #8 is of the asset locations for the files related to the adjusted / mutated asset.

  • \private\var\mobile\Media\PhotoData\Mutations\DCIM\100APPLE\<OriginalAssetFileNameWithNoExtention>\Adjustments\<FullSizeRender or other filename>
    • This location will store the asset that was created as the result of the adjustments or mutations
    • On the device, when navigating to Photo Application > Photo Library > Recents, the asset displayed on the device is the file saved in the above location
  • \private\var\mobile\Media\DCIM\<*Apple>
    • As it relates to adjustments / mutations, this location will store the unedited asset. Example: When IMG_0004.HEIC was captured then adjusted and saved, the unadjusted asset was stored here
    • There is also a IMG_0004.MOV saved at this location because this asset was a Live Photo
    • Notice there is a IMG_0004.AAE file stored in this location
      • This file is a plist which contains metadata for the adjustments. This data is also stored in the Photos.sqlite database in the ZUNMANAGEDADJUSTMENTS table
      • This was only tested twice and appears this file is created when adjustments / mutations are made prior to the asset being captured
  • During testing, both HEIC files were exported and hashed; the hashes did not match

Adjusted asset marked as recently deleted then permanently deleted

  • During testing, the asset, IMG_0004.HEIC, was marked for recently deleted and changes were made in the Photos.sqlite
    • When IMG_0004.HEIC was marked as recently deleted, the entries still existed in the database and the assets still existed I the above file paths
    • The asset was marked for recently deleted at 7:35 PM, notice the zAsset-Modification Date
    • zAsset-Trash State now indicates the asset is in Recently Deleted
    • zAsset-Trashed Date (UTC) provides the date timestamp the asset was marked as recently deleted
  • IMG_0004.HEIC is then permanently deleted from the device and all the previously viewed assets from the above file paths are no longer available, not discussed in this section, but the thumbnail in V2 was also deleted    
Figure #8

Asset marked as recently deleted then restored

Figure #9 is of IMG_0004.HEIC being marked as recently deleted and then restored. Things to notice about the changes in Figure #9:

  • zAsset-Modification Date timestamp (UTC) will be updated after the asset is marked as recently deleted
  • Assets in recently deleted are not allowed for analysis. This can be analyzed by viewing the zAddAssetAttr-Allowed for Analysis column. This will limit some of the data that might be viewed in the database. Some of these data areas include ZMOMENT and ZPHOTOSHIGHLIGHT tables
  • When the asset is recovered it will change the zAsset-Modification Date and notice the data for moments and photo highlights has repopulated
Figure #9

Assets captured with people and faces detected

Figure #10 is of two assets being captured, where a face and a person was detected. After a face is detected in an asset, it must be analyzed by the OS to populate additional data. In this video, a face crop was also created. I have not been able to decode all the values related to detected faces, but during testing, I used more than one-hundred headshot photographs to decode as much as possible for both iOS 14 and iOS 15.

  • On 2/26/2022 the two assets (IMG_0006.HEIC and IMG_0007.HEIC) were captured as noted by the zAsset-Date Created timestamps
  • The zAsset-Modification Date for IMG_0006.HEIC is 2/26/2022 at 19:31:49 and IMG_0007.HEIC is 2/26/2022 at 19:32:00
  • The Native Front Camera was used to capture both assets and the orientation was recorded
  • Notice the assets were allowed for analysis, but the following columns are empty or zero:
    • zAddAssetAttr-Scene Analysis Version
    • zAddAssetAttr-Scene Analysis Timestamp
    • zAsset-Analysis State Modification Date
  • zFaceCrop-Face Area Points will indicate if the asset contains detected face(s)
  • In 2020, Jared Barnhart posted a write-up about some artifacts he discovered within Photos.sqlite and PhotoData file system areas, which included detected faces. This information is a continuation to his research: http://www.mac4n6.com/blog/2020/7/19/follow-on-to-dfir-summit-talk-lucky-ios-13-time-to-press-your-bets-via-bizzybarney
  • Immediately after the assets were captured the data coming from ZDETECTEDFACE, ZFACECROP and ZPERSON were empty
  • Detected Face and Person data will not be populated until asset analysis takes place by the OS

Asset analysis by the iOS produces additional data

  • Asset analysis can be triggered by plugging the device into a charger, turning the screen off and no user activity
  • Normally after a day of testing, the asset analysis would take place at night while I slept, and the device was plugged into a charger
  • During testing, I was able to get the OS to analyze newly captured assets after approximately two (2) hours. The time required to analyze the assets was dependent upon the number of assets stored in the photos application which were pending analysis
  • At 0:02:19 in Figure #10, you will notice the assets, IMG_0006.HEIC and IMG_0007HEIC, now have a zAddAssetAttr-Scene Analysis Timestamp:
    • IMG_0006.HEIC – 2/26/2022 at 19:31:47 UTC
    • IMG_0007.HEIC – 2/26/2022 at 19:31:57 UTC
  • zAsset-Analysis State Modification Date is different from the zAddAssetAttr-Scene Analysis Timestamp. The difference between these two timestamps was not tested or researched
  • The created date, EXIF date, and Add date remained the same as when we first reviewed the assets, but the zAsset-Modification Date has been updated
  • This is important to know and understand for at least to two reasons:
    • If the device data is acquired within a few hours of an asset being captured, the asset may not have been analyzed and the face and person data may not be populated. This might be important if you want to know if an asset contains faces and people
    • If the examined device is powered on and plugged into a charger for an extended amount of time, prior to the data being acquired, new data might be populated to include new timestamps will be generated within Photos.sqlite. Some of those timestamps are labeled as modification dates and do not necessarily indicate that the asset was modified, changed, or edited. The modification timestamps might only indicate the OS analyzed the asset
  • In Figure #10, you will notice a review of the detected face and detected person data from iOS 14. I will cover this data in detail later in this write-up and will use iOS 15 data. iOS 15 Photos.sqlite contains additional fields that are not included in iOS 14
    • NOTE: ZDETECTEDFACE data can help but can also be very misleading. The analyzing of the assets is good but not great. During testing, if the asset was captured in low light situations it might indicate a person had specific color of hair, but they were wearing a beanie, toque, or stocking cap, depending on where you are from. Additionally depending on facial features, the ethnicity of the detected person could be incorrect

Face Crop Created

  • At 0:04:02 in Figure #10, you will notice the columns related to ZFACECROP table are empty
  • A face crop asset can be created by navigating to an asset, then swiping up on the asset
  • There are some different options here but notice the area labeled People
  • Clicking on the People option will open the asset associated with Person that has been detected
  • A user can add a name to the asset, thus adding a name to the personID. Notice when typing in the name field, it begins looking through the contacts to recommend a person’s name. Once selecting the contact or finish typing the person’s name, there will be an indication a person was added
  • Navigating on the device Photo Application > Albums > People & Places there is one person listed
  • Immediately after the face crop is created and the database data is refreshed, we can see new data is populated in the database, specifically the ZPERSON and ZFACECROP table data. Some key data to analyze as the result of a face crop is the following:
    • zPerson-Contact Matching Dictionary will contain data if a contact was used when the face crop was created
    • As Josh Hickman mentioned in a previous post, zFaceCrop-Resource Data is a BLOB JPG of the asset that was used for the face crop. This BLOB JPG will remain in the database even after the asset that was used to create the face crop has been deleted
    • NOTE: Prior to the factory rest of Scooters iPhone X, several people were identified using face crop data. During testing, that data used to identify those subjects still existed in the face crop data after the factory reset.  
Figure #10

Timelapse Video Captured then a new asset was created by editing the length of the original video

Figure #11 is a time-lapse video (IMG_0008.MOV) being captured, edited, then saved as a new asset (IMG_0011.MOV). Things to notice about the changes in Figure #11:

  • zAsset-Kind Sub Type – indicates Time-Lapse-Video-102. Decoded values include:
  • zAsset-Playback Style – indicates Video-4. Decoded values include:
    • Image-1
    • Image-Animated-2
    • Live-Photo-3
    • Video-4
    • Video-Looping-5
  • No adjustments were made or recorded for the original asset IMG_0008.MOV
  • At this point during testing, every MOV asset that was captured was saved to:
    • \var\mobile\Media\DCIM\100APPLE\
  • In addition to the MOV Photo Library asset the following location had two related files (JPG & THM) saved at:
    • \var\mobile\media\PhotoData\Metadata\DCIM\100APPLE\
      • IMG_0008.JPG
      • IMG_0008.THM
  • The original asset (IMG_0008.MOV) was edited by shorting / trimming the length of the video. After the edit, a new asset (IMG_0011.MOV) was created by using the option Save Video as New Clip
  • zAsset-UUIDs for the original video (IMG_0008.MOV) and the newly created shorter video (IMG_0011.MOV) are different
    • NOTE: Several columns that contain UUIDs and GUIDs for the asserts has been moved to the beginning of the SQLite query output. These unique identifiers can be used to search for related data in other databases, like store.cloudphotodb, and other files, such as plist, to show a relationship between assets listed in Photos.sqlite and those other files
  • The newly created asset has a zAsset-Filename of IMG_0011.MOV, and the zAddAssetAttr-Original Filename is IMG_0008.mov. Notice the file extension is lower case. I haven’t noticed this before and not sure why it used lower case mov vs MOV
  • Asset filenames IMG_0009 and IMG_0010 were skipped. I don’t know why that happened
  • zAsset-Date Created timestamp and the zAddAssetAttr-EXIF Timestamp String (2022-02-26 19:38:16) are the same for both assets
  • zAsset-Add Dates are different for each of the assets because they are added to the Photo Library at different times. If you pay close attention to Figure #11, you will notice its 11:41 AM when the original asset was edited.
    • NOTE: Neither of these assets have been placed into an album nor are they being synced with iCloud
  • IMG_0011.MOV
    • zAddAssetAttr-Creator Bundle ID – indicates the bundle id used to create the asset, in this example it indicates com.apple.mobilslideshow
    • zAddAssetAttr-Imported by Display Name – indicates the application name used to import the asset, in this case it indicates Photos for the photo application
    • zAddAssetAttr-Imported by – indicates Native Application-8, which is indicating the asset was created by using an Apple Native Application
  • Even after IMG_0008.MOV was edited and the new asset (IMG_0011.MOV) was created, no indication adjustments / mutations were made. This is because the original asset (IMG_0008.MOV) was not edited, or adjusted, and a new asset was created versus adjusting the original

Timelapse Video frame cropped and saved; asset now has Adjustments / Mutations

In Figure #11, adjustments were made to IMG_0008.MOV by cropping what was being displayed in the video then saved/overwritten after the adjustments were made. A new asset was not created:

  • IMG_0008.MOV is opened and the video is cropped and clicked done.
  • Paying close attention to the video, you should have noticed the original asset, IMG_0008.MOV, only had two entries in the query results. The zAsset-UUID is 4E316CD2-F9AC-4332-A568-6D0A77B5F64F
  • After the adjustments were made and the database is refreshed, we can see there are five rows related to the asset, IMG_0008.MOV
  • zUNMANAGEDADJUSTMENTS table data is now populated
  • Notice the small overlay notification indicating Preparing Video
  • In the video, it scrolls through fast but wanted to point out, after the asset was adjusted, there were no changes to the following timestamps:
    • zAsset-Date Created
    • zAddAssetAttr-EXIF
    • zAsset-Add Date
  • zAsset-Has Adjustments has changed and now indicates Yes-Adjustments-1 and there are two timestamps that indicate when the asset was adjusted:
    • zAsset-Adjustment Timestamp (UTC)
    • zUnmAdj-Adjustment Timestamp (UTC)
  • zAddAssetAttr-Editor Bundle ID indicates com.apple.mobileslideshow
  • zUnmAdj-Editor Localized Name indicates Photos
    • These are indicators of which application was used to make the adjustment
Figure #11

Adjustments / Mutations asset storage locations

In Figure #12 we can see after an asset is adjusted / mutated a folder is created in the following location:

  • Using the query, we can adjust the query to isolate only those assets that have adjustments/mutations
    • WHERE ZASSET.ZHASADJUSTMENTS = 1
    • Caution: When using the WHERE statements, it’s possible to omit data that might be related to the asset you are attempting to analyze. Meaning you could have rows of data that contain information about the asset you want to analyze, but if zAsset-Has Adjustments column indicates No-Adjustments-0 it would be omitted if using the WHERE statement
  • ZUNMANAGEDADJUSTMENT table will store the data for assets that have been adjusted / mutated
  • After the query results populate, we can see there are five assets that have adjustments/mutations
  • Using ArtEx Live Connection, I navigated to the above Mutations file system location
  • NOTE: Each folder name is the original assets filename, but without the file extension. The asset named folder will contain an Adjustments folder which will contain a plist and additional files. The additional files listed in the Adjustments folder might be dependent upon on the type of adjustment that was made to the asset. During testing, I had the following results for the following file types:
    • Original asset was a HEIC – Still Photo (IMG_0003.HEIC) Filter 1.4 adjustment
      • Adjustments.plist
      • FullSizeRender.heic
    • Original asset was a  HEIC – Live Photo (IMG_0004.HEIC) Filter 1.4 adjustment
      • Adjustments.plist
      • FullSizeRender.heic
      • FullSizeRender.mov
    • Original asset was a MOV (IMG_0008.MOV) Video Trim 1.6 adjustment
      • Adjustments.plist
      • FullSizeRender.jpg
      • FulSizeRender.mov
      • IMG_0008.JPG
    • Original asset was a MOV (IMG_0012.MOV) Slow-Mo 1.1 adjustment
      • Adjustments.plist
      • IMG_0012.JPG
  • I opened one of the Adjustments.plist and notice the data stored in the plist matches some of the data populated in the ZUNMANAGEDADJUSTMENT table.
Figure #12

Slow-Motion video captured – iCloud Photos is off

In Figure #13 a Slow-Motion video is captured (IMG_0012.MOV):

  • Notice the asset has adjustments, but no manual adjustments were made to the asset. These adjustments were performed by the OS. The only user interaction was the capture of a slow-motion video.
  • zAsset-Add Date and the zUnmAdj-Adjustment Timestamp are the same.
  • zUnmAdj-Adjustment Format ID is com.apple.video.slomo. This data can be compared to the data being stored in zAsset-Kind Sub Type as a type of asset validation
Figure #13

Video captured – iCloud Photos off

In Figure #14 a regular video was captured (IMG_0030.MOV). We will review the data stored in the Photos.sqlite, files created because of the video was captured, and some file storage locations.

  • Prior to the asset being captured the last asset listed in *\DCIM\100APPLE and Photos.sqlite was IMG_0029.HEIC
  • A video was captured, and the duration was approximately 10 seconds
  • The data being stored in the Photos.sqlite is refreshed showing the newly created asset, IMG_0030.MOV
  • IMG_0030.MOV was captured using the device back camera. The device was vertical with the camera at the top of the device
  • zAsset-Orientation – indicates Video-Default/Adjustment/Horizontal-Camera-(left)-1
  • zAddAssetAttr-Original Orientation – indicates Video-Default/Adjustment/Horizontal-Camera-(left)-1
  • Rows 60 and 61 are both containing data for a single asset, IMG_0030.MOV. Notice the difference between zUnID-Conforms to Image and zUnID-Conforms to Movie. The data being displayed in row 60 and row 61 are different. This is another example of why you might have multiple rows of data in the Photos.sqlite query output for a single asset
  • ZINTERNALRESOURCE table data can provide key information / metadata about assets. Some of the data has yet to be decoded and further testing is required

Asset analysis additional information:

  • It has been a few days since I have had time to work on this write-up, notice the differences between the newly created asset and the older assets. The older assets have:
    • zAddAssetAttr-Scene Analysis Timestamp
    • zAsset-Analysis State Modification Date
    • Analysis has taken place for the older assets, but has not occurred for the new asset, IMG_0030.MOV
  • zAsset-Video Duration will indicate the exact length of a video that was captured (in seconds). In this example the video asset is 10.265 seconds
  • Because the analysis has not been completed for the newly created asset, the ZCOMPUTEDASSETATTRIBUTES table data has not been populated. Review Figure #10 and related notes for additional asset analysis information

Video captured – iCloud Photos off – Asset file storage locations

At 4:11 of Figure #14, I navigate to the following locations to review the files created as the result of the captured asset with the native camera application:

  • \private\var\mobile\Media\DCIM\<*APPLE>\
  • \private\var\mobile\Media\PhotoData\Thumbnails\V2\DCIM\<*APPLE>\
  • \private\var\mobile\Media\PhotoData\Thumbnails\V2\PhotoData\PhotoCloudSharingData\<personID example:16266678879>\<shared album GUID example:AF9E19A6-1D51-4C35-9199-552DBF9A7249>\<*CLOUD>\
    • This is some of that data that was left behind and not deleted prior to the factory reset. This is a Shared Album that contained shared assets. Additional information will be discussed after iCloud Photos and Shared Albums is turned on and Photo Cloud Shared Data is covered in the write-up
  • \private\var\mobile\media\PhotoData\Metadata\DCIM\<*APPLE>\
  • \private\var\mobile\media\PhotoData\Videos\
  • \private\var\mobile\Media\PhotoData\Mutations\DCIM\<*APPLE>\
  • \private\var\mobile\Media\PhotoData\CPL\storage\filecache\
  • \private\var\mobile\Media\PhotoData\CPL\storage\temp\
  • \private\var\mobile\Media\PhotoData\CameraMetadata
  • \private\var\mobile\Media\PhotoData\private
Figure #14

Portrait and Square photos captured – iCloud Photos off

Figure #15 is of several photos being captured with the device’s native camera application and the data that was created is reviewed within Photos.sqlite:

  • IMG_0014.HEIC is a captured photo using the portrait option
  • IMG_0015.HEIC is a captured photo using the square option
    • The camera orientation was changed
  • Notice both assets are indicating there are no adjustments

Photos Captured while a Video was being captured – Items out of order – iCloud Photos OFF

  • IMG_0020.MOV was captured using the front camera. While the video was recording several photos were captured using the assistance touch button to the left of the red stop button
  • IMG_0017.JPG, IMG_0018.JPG, and IMG_0019.JPG were all created prior to the video being stopped
    • IMG_0017.JPG – Primary Key 28 – Date Created 2-26-2022 at 20:12:54
    • IMG_0018.JPG – Primary Key 29 – Date Created 2-26-2022 at 20:13:04
    • IMG_0019.JPG – Primary Key 30 – Date Created 2-26-2022 at 20:13:05
  • While watching Figure #15, notice the Photos.sqlite database was reloaded prior to the video being stopped. Doing this prevented the captured video (IMG_0020.MOV) from being listed in the query output
  • After the data is reloaded again, we can see the asset (IMG_0020.MOV) has a zAsset-zPK of 31, but the created/captured timestamp is before the created/captured timestamps for the photos that were made
    • IMG_0020.MOV – Primary Key 31 – Date Created 2-26-2022 at 20:12:44
  • NOTE: I believe there was a question posted on one of the DFIR boards about how items with sequential primary keys / row ids and sequential filenames could be out of order. This is one example of why that may occur.
Figure #15

Files loaded into iCloud Photos via Windows web iCloud – iCloud Photos off

Figure #16 is of the Web iCloud opened via Google Chrome using Windows 11. The iCloud account used is the same Apple ID being used on the iPhone X test device. I navigated to the six (6) test JPG files, and they were uploaded to iCloud Photos. The original filenames are:

  • Windows_Loaded_iCloud_Test_Photo_#1.jpg
  • Windows_Loaded_iCloud_Test_Photo_#2.jpg
  • Windows_Loaded_iCloud_Test_Photo_#3.jpg
  • Windows_Loaded_iCloud_Test_Photo_#4.jpg
  • Windows_Loaded_iCloud_Test_Photo_#5.jpg
  • Windows_Loaded_iCloud_Test_Photo_#6.jpg

Web iCloud Photos Actions – iCloud Photos OFF on device

  • After uploading the files a few different actions took place within Web iCloud. Remember, the test device associated with this iCloud account/Apple ID has iCloud Photos and Shared Albums turned OFF, but the device is connected to the network via mobile data (SIM Card) and Wi-Fi

Web iCloud Asset Interaction – asset was Liked

  • On 3/6/22 at 11:36 AM, local time, Windows_Loaded_iCloud_Test_Photo_#6.jpg was liked via the iCloud web interface

Web iCloud Asset Interaction – Comment made on asset

  • At 11:37 AM, a comment (iCloud Comment Made via Windows Web iCloud 3/6/22 11:37 AM) was added to Windows_Loaded_iCloud_Test_Photo_#3.jpg via the iCloud web interface

Web iCloud Asset Interaction – New Album created

  • At 11:38 AM, a new album (Album Made in Web iCloud 3/6/22 11:38 AM Test #1 and #2) was created, then at 11:40 AM, Windows_Loaded_iCloud_Test_Photo_#1.jpg and Windows_Loaded_iCloud_Test_Photo_#2.jpg were added to the album

Web iCloud Asset Interaction – Shared iCloud Link created

  • At 11:41 AM, a Share iCloud Link was created for Windows_Loaded_iCloud_Test_Photo_#5.jpg. This Share iCloud Link is also known as a Cloud Master Moment Share, which we will review in detail when reviewing the data stored in Photos.sqlite
Figure #16

Analysis of assets added via web iCloud Photos – iCloud Photos and Shared Albums off

Figure #17 is a video after the six files were uploaded to iCloud Photos. After uploading, one of the assets is shared via an iCloud Share Link. Upon remapping the device data, I navigated to Photos.sqlite to analyze the changes.

  • zCLOUDMASTER table now has one (1) new entry. This entry contained data from the asset that was shared via the iCloud Share Link. Within the Photos.sqlite, these types of assets are also referred to as Cloud Master Moment Share assets and can be found in:
    • iOS 12 and 13 – ZMOMENTSHARE table
    • iOS 14 and 15 – ZSHARE table
  • ZCLOUDMASTER table ZMOMENTSHARE column contains the data needed to join the ZCLOUDMASTER table and the ZSHARE table
  • ZCLOUDMASTERMEDIAMETADATA table ZDATA column is a binary plist, which contains some metadata for the asset
  • ZASSET table – notice some important data:
    • The new asset is Z_PK 46
    • zAsset-Directory provides the file system location where the asset is located:
      • \private\var\mobile\Media\PhotoData\CMMAssets\<zShare-UUID>\
    • zAsset-Filename – 21E88755-1278-43A3-95D1-DFAE4B98A2C8.JPG
  • ZADDATIONALASSETATTRIBUTES table – Notable data:
    • The scene analysis has not taken place
    • There is no ZEXIFTIMESTAMPSTRING, because this image was made with Windows Paint program and was not captured with a camera
    • ZORIGINALFILENAME is Windows_Loaded_iCloud_Test_Photo_%235.jpg
      • The ascii character # from the original filename has been replaced with the hexadecimal representation %23
  • ZSHARE table – Notable data:
    • zAddAssetAttr-Share Type is not in the ZSHARE table but is data from the ZADDITIONALASSETATTRIBUTES table that indicates if the asset is a Cloud Master Moment Shared asset
    • zShare-Status indicates how the asset was shared, Asset Shared via Copy iCloud Link-1. Currently this is the only decoded value for this column
    • zShare-Creation Date is the date the iCloud Share Link was created
    • zShare-Start Date is the created date for the earliest/first asset being shared via the link
    • zShare-End Date is the created date for the latest/last asset being shared via the link
    • zShare-Expire Date is the date when the iCloud Shared link will expire
    • zShare-Title will contain the subtitle/verbose description if one is listed
    • zShare-Share URL is the link that was created to share the assets
    • zShare-Asset Count is the number of assets being shared, the other count data columns will indicate the number of files that have been shared based on file type (Photos, Uploaded Photos, Videos and Uploaded Videos)
    • zShare-Force Sync Attempted indicates if the asset was synced while iCloud Photos was ON or OFF
    • zShare-Scope & zShare-Local Cloud Publish State currently decoding is unknown
    • zShare-Public Permission indicates if public permission was granted
    • zShare-UUID is unique identifier which contains the assets shared via the iCloud Shared Link
    • zShare-Originating Scope ID currently decoding is unknown
    • zShare-Preview Data is a BLOB that contains a JPG of the shared file
    • zShare-Should Ignore Budgets currently decoding is unknown
    • zShare-Should Notify ON Upload indicates if the user will be notified upon upload
  • I used the iOS 14 query to view the database data:
    • One of the first things you might notice is the asset is located at the top of the query output
      • The query output is sorted by created date
      • At this time, this file was created prior to all the other assets listed in Photos.sqlite
      • Notice the zAsset-zPK for this asset is 46, which is the most recently added primary key to the database
    • CMzCldMastMedData-Data column contains a plist which contains metadata for the asset which have a cloud master asset. I will detail this information in another section of the write-up, but wanted to mention it because it can be a valuable plist to analyze
    • zAsset-Saved Asset Type is displaying a new asse type of CMMAsset-8 or Cloud Master Moment Asset. Decoded values include:
      • Saved-via-other-source-0
      • DCIM-APPLE-3
      • PhotoCloudSharingData-4
      • CPLAssets-6
      • CMMAssets-8
      • Additional values were observed during testing, but I have yet to confidently decoded them
    • zAsset-Visibility State indicates if the asset can be viewed via the Photo Library. In this example, we must view the asset from the For You section of the Photos Application.
      • On the device you may notice a message that indicates the iCloud link available until Apr 5. This matches the zShare-Expiry Date
    • zAsset-Filename will provide you with the filename for the asset on the physical device. During testing, when an asset stored in the photo’s application is derived from a Cloud Master Moment Asset or other cloud assets, the zAsset-Filename will match the zAsset-Cloud Asset GUID. This is an example of that
    • zAsset-Date Created is when the asset was created
    • zAsset-Add Date is when the asset was added to the device Photos Application, this can be verified by reviewing Figure #16
    • As stated previously, sometimes it is difficult to determine why the zAsset-Modification Date changed or was updated. If you analyze the other timestamps listed for the specific asset, you might be able to find similar timestamps and work from there in determining a possible cause for the modification date change
    • zCldMast-Creation Date is the timestamp when the cloud master file was created for the asset. In this instance, it is when the file was created, not when it was uploaded to iCloud Photos or when it was added to the device
    • zIntResou-iCloud Master Date Created is the timestamp when the internal resource asset has a cloud master asset created, see zCldMast-Creation Date for additional information
    • zIntResou-Cloud Last Prefetch Date is when an asset was last fetched by the device. I have not been able to increase the prefetch count over a value of one (1), but I’m confident it’s possible, just haven’t been able to do it in a testing environment
Figure #17

iCloud shared links and Cloud Master Moment asset (CMMAssets)

In Figure #18, I navigated to the file system location where the asset (21E88755-1278-43A3-95D1-DFAE4B98A2C8.JPG) should be stored:

  • In Figure #18 we can see the asset is on the device, but the asset is not being stored at:
    • \private\var\mobile\Media\DCIM\100APPLE\
  • I navigated around attempting to find other artifacts related to the Shared iCloud Link/Cloud Master Moment asset:
    • \private\var\mobile\Media\PhotoData\CPL\
      • Did not appear to contain data related to the shared asset
    • Metadata\PhotoData\CMMAssets\<zShare-UUID>\
      • Based on the GUID, this location appears to be related to the asset, but the file path is empty, at this time. This file path was checked several days later after iCloud Photos and Shared Albums was turned ON and remained empty
    • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\
      • Was checked and did not appear to contain data related to the shared asset
    • \private\var\mobile\Media\PhotoData\Thumbnails\V2\PhotoData\CMMAssets\<zShare-UUID>\<zAsset-Cloud_Asset_GUID>\5005.JPG
      • As you can see, the thumbnail filename is the typical 5005.JPG
    • \private\var\mobile\Media\PhotoData\Thumbnails\V2\PhotoData\PhotoCloudSharingData\16266678879\AF9E19A6-1D51-4C35-9199-552DBF9A7249\100CLOUD is not related to this CMMAsset but is something that was left behind after the factory reset. This is related to other shared assets prior to the factory reset. This will be discussed in detail later in the write-up.
  • CMzCldMastMedData-Data – column contains data (plists and other types of data) which contain metadata for the asset. During testing, most of the metadata will be stored in a plist. The data in this plist, is very similar with the data contained in the iOS 15 ZEXTENDEDATTRIBUTES table. If analyzing Photos.sqlite, do not skip over this data, it contains lots of valuable information
  • Currently, the plist has lots of data I was not able to decode. There is an area within the plist {MakerApple} which, based on limited testing, could hold specific camera settings when the asset was captured. Dedicated time will be needed to decode all the values. If anyone has completed any validation in this area, please contact me so we can compare notes
  • AAAzCldMastMedData-Data – is a column to the right that has a similar name. The data in these columns come from the ZCLOUDMASTERMEDIAMETADATA table ZDATA column. The raw data in the database contains a data for two different types of assets. In the ZCLOUDMASTERMEDIAMETADATA table there are two columns ZADDITIONALASSETATTRIBUTES and ZCLOUDMASTER. These columns contain row IDs identifying the asset for which the ZDATA column is related. These row IDs were used to join the ZCLOUDMASTERMEDIAMETADATA table to the appropriate assets.
    • NOTE: I had a difficult time figuring out how make this join without losing some of the data in the output. Aliases were used to make the appropriate joins with no data loss. This resulted with the creation of the two columns with similar names
    • zCldMastMedData-zAddAssetAttr – based on testing appears to contain the row ID corresponding to the ZADDITIONALASSETATTRIBUTES table Z_PK value for assets that have been adjusted / mutated. This is only populated after iCloud Photos was turned ON. While iCloud Photos was turned OFF this data was not present.
    • zCldMastMedData-CldMast – based on testing, it appears to contain the ZCLOUDMASTER table Z_PK value for assets that have been synced with iCloud Photos but does not include Photo Cloud Sharing Data assets.
  • In Figure #18, the example shown, does not have a lot of metadata because this file was created with Windows Paint and not an actual camera.
  • zAddAssetAttr-Imported By indicates how the asset was imported onto the device, notice in this instance the column data indicated Cloud\Other-0                 
Figure #18

iCloud and Sharing Albums turned on

Figure #19 is of the iPhone X test device, navigating and documenting when iCloud Photos and Shared Albums was turned on. Then analyzing the property lists that contains the current iCloud Photos and Shared Albums on or off settings and the settings history.

  • iCloud Photos settings can be accessed via the device by using the following settings path:
    • Settings > Apple ID > iCloud
      • On & Off settings for iCloud Photos – Cloud Photo Library (CPL)
    • \private\var\mobile\Media\PhotoData\private\com.apple.assetsd
      • cloudServiceEnableLog.plist
        • If the settings have been turned on and off, there will be a history of the settings that include timestamps for when they were turned on or off
        • In this example, number 1 in the plist indicates CPL is not enabled, indicated by the False value
  • iCloud Photos and Shared Albums settings can be accessed via the device by using the following settings path:
    • Settings > Apple ID > iCloud > Photos
      • On & Off Settings for iCloud Photos – Cloud Photo Library (CPL) & Shared Albums
    • \private\var\mobile\Media\PhotoData\private\com.apple.accountsd
      • cloudServiceEnableLog.plist
        • If the settings have been turned on and off, there will be a history of the settings that include timestamps for when they were turned on or off.
        • In this example, number 2 in the plist indicates CPL is not enabled, indicated by the False value
        • In this example, number 3 in the plist indicates SharedAlbum is not enabled, indicated by the False value
  • At 2:07 PM, I turned on iCloud Photos via the sub-iCloud Photos settings menu.
    • Notice by turning on the setting in the sub-menu it also turns on the iCloud photos in the main menu
    • When iCloud Photos was turned on, the default storage option was Optimize iPhone Storage
  • At 2:19 PM, Shared Albums was turned on via the sub-iCloud Photos settings menu.
    • The test device data was refreshed in ArtEx
    • The cloudServiceEnableLog.plist in com.apple.accountsd has a new entry (#5), which indicates SharedAlbum is enabled with a True value, and the timestamp matches the time the Shared Albums setting was turned on
Figure #19

Changes in Photos.sqlite because iCloud Photos & Shared Albums was turned on

Figure #20 is reviewing what new files/assets exist on the test device and what changes have been made within the Photos.sqlite as the result of iCloud Photos and Shared Albums being turned on.

  • The ZASSET table only has 48 records, but the query output now has 214 Rows
  • AT 0:00:54 in Figure #20, you will notice the zAsset-Saved Asset Type data is indicating several assets are CPLAssets-6. This is an indication the asset stored on the device has come from Apple ID’s iCloud Photos library
  • Assets (IMG_0001.HEIC, IMG_0002.HEIC and IMG_0003.HEIC) that were captured with the device while iCloud Photos was turned off indicate DCIM-APPLE-3
  • The asset (Windows_Loaded_iCloud_Test_Photo_#5.jpg) which was shared via iCloud Share Link, is listed several times in the query results
    • zAddAssetAttr-Org Filename – Windows_Loaded_iCloud_Test_Photo_#235.jpg
  • zAsset-Filename indicates the filenames you will find when examining the assets stored in the Photo Library
    • Assets that have the same zAsset-Filename and can be listed multiple times in the Photos.sqlite query output, because of their saved asset types. In this example the asset has two asset types (CPLAssets-6 & CMMAssets-8). The asset types can provide insights about how the asset was imported / saved to the device Photos Application and if the asset has been shared
    • Based on testing, if an asset has come from a cloud source, it’s very likely, the zAsset-Filename and the zAsset-Cloud Asset GUID will match
  • zIntResou-Cloud Last Prefetch Date and zIntResou Cloud Prefetch Count will indicate the last time an asset stored in iCloud Photos was fetched by the device to store the CPLAsset. At this point during testing, I haven’t been able to increase the count from 1 prefetch, further testing is needed
  • I debated whether to remove this portion of the video or to include it, but I decided to include it. In Figure #20 between 0:01:05 and 0:02:13, you will notice some Photo Cloud Sharing Data exists within the test device. At this portion of the testing, it was puzzling why this Photo Cloud Sharing Data existed? It was believed all shared albums and corresponding assets were permanently deleted prior to the test device factory reset. Photo Cloud Sharing Data and Shared Albums have yet to be discussed in detail, but you will notice some data already exists and just want to point out something that occurred when Sharing Albums was turned on
    • It appears I missed a Shared Album and its assets when I was preparing for this round of research. The assets are located at:
      • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\16266678879\AF9E19A6-1D51-4C35-9199-552DBF9A7249\100CLOUD\
    • When Shared Albums was turned on, the previously shared album containing assets (photos and videos) synced with the test device
    • The Shared Album title is Cmm #3 shared album
    • If you remember at the beginning of the write-up when discussing the first asset that was captured after the factory reset, the first primary key listed in the Photos.sqlite was 14. Notice the last asset filename in the PhotoCloudSharingData, IMG_0013.JPG and the number of assets stored in the shared album on the device
    • The created date for these assets was 02/05/2022, which was prior to the factory reset.
    • zAsset-Directory column data will provide you with the file path for the Photo Cloud Sharing Data assets. Using this file path, we can navigate to the asset in the file system
    • On the device we can see the Shared Album name and it has the initials of BS in the lower right corner of the album preview window. These are the initials for the Apple ID username whom this album was shared with Bandit Scooter
  • Remember when I uploaded six (6) files to iCloud Photos, prior to iCloud Photos and Shared Albums being turned on? Previously, only the file shared via the iCloud Copy Link (Cloud Master Moment Assets) was visible on the device, now we can see all the files via CPLAssets (Cloud Photo Library Assets) storage location
  • zAsset-Directory will provide you with a file path for the asset in the file system. The following are locations I found which contained assets related to CPLAssets-6:
    • \private\var\mobile\Media\PhotoData\CPLAssets\<group*>
      • Unsure of why there are different groups created.
    • \private\var\mobile\Media\PhotoData\Metadata\CPLAssets\<group*>
      • These locations were empty at the time this video was made.
    • \private\var\mobile\Media\PhotoData\Thumbnails\V2\PhotoData\CPLAssets\<group*>
      • Notice the thumbnail group folder names correspond to the group folder names for the full asset
      • As you might have suspected, the thumbnail asset filename is 5005.JPG. It will be in a folder, whose name will match the full assets filename (zAsset – Filename). This might be good area to analyze if you are in search for evidence of deleted assets.
Figure #20

Deleting CPLAssets

Someone posed a question on DFIR Discord about deleting assets from the following location. In Figure #22 I attempted to test the question and observe the results.

Figure #21
  • \private\var\mobile\Media\PhotoData\Metadata\CPLAssets\group<*>

Figure #22 is of the changes that occurred when a Cloud Photo Library Asset (Windows_Loaded_iCloud_Test_Photo_#4.jpg) was deleted from both the iPhone X and from iCloud Photos

  • First, we will review the asset data stored in Photos.sqlite prior to deletion. The following are some data columns which you will want to analyze if deleted CPLAssets is important in your analysis
    • zAsset-zPK will be present even after the asset has been marked as recently deleted and all entries for the asset still exist in the database. After the asset is permanently deleted from the device, the asset will not be listed in the database and the PK will be missing. Depending on the amount of time that has lapsed since the deletion, you might be able to recover the database entry via the Write Ahead Log (WAL) file or free pages
    • zAsset-Modification Date will be updated when the asset is marked as recently deleted
    • zAsset-Trash State will be updated to reflect recently deleted and zAsset-Trashed Date will be populated with timestamp with when the asset was marked as recently deleted.
    • During testing, I did not notice a change to the zAsset-Cloud Delete State data
    • zAddAssetAttr-Allowed for Analysis column will be updated and the asset will not be allowed for analysis
  • The asset on the device indicates it has 30 days left to recover and the asset in iCloud Photos has 29 days left to recover. Don’t know why there is a difference but wanted to note it
  • When the asset was permanently deleted on the device the asset in iCloud Photos is automatedly deleted
  • Indication of Deletion – After the asset was permanently deleted, the locations mentioned previously were checked and the assets no longer existed, but the folders which held the files remained
  • Store.cloudphotodb can provide some insights into when, why, and how and asset was synced with iCloud Photos. I will discuss this in more detail later in this write-up
Figure #22

Live Photo Captured with iPhone X iCloud Photos on and Time Zone Mismatch

In Figure #23 the time zone setting on the iPhone X test device is changed from Cupertino, U.S.A (UTC-7) to New York, U.S.A (UTC-4). This is a test example of what you might encounter if the device time zone setting it not set properly or if an asset is captured in a different time zone.

  • Testing was performed during daylight saving time
  • The assets listed Photos.sqlite indicate a time zone setting of GMT-0800 and -28800
    • zAddAssetAttr-Time Zone Name – Relative to UTC/GMT
    • zAddAssetAttr-Time Zone Offset – Offset in seconds
    • zAddAssetAttr- Inferred Time Zone Offset – Offset in seconds
  • Live Photo captured (IMG_0035.HEIC) with iCloud Photos and Shared Albums turned on at 5:38 PM (UTC-7)
  • Pay special attention to the test device while the data is refreshing in ArtEx:
    • The Photo library is accessed and at the bottom of the screen where there is an indication the assets have updated to the cloud storage at 8:41 PM (UTC-7)
  • zAsset-Visibility State indicates the asset is visible in the Photo Library, as displayed on the device
  • zAddAssetAttr-Org Filename matches zAsset-Filename
  • zAsset-Date Created indicates the asset was created at 00:38:08 (UTC-0) or 5:38:08 (UTC-7)
  • zAddAssetAttr-EXIF Timestamp String indicates the asset was captured on 3-20-2022 at 8:38:09 (UTC-4), which matches the zAddAssetAttr-Time Zone Name
  • zAsset-Add Date matches the EXIF timestamp string and is listed in UTC-0
  • zCldMast-Org Filename indicates the filename for the asset that is stored in iCloud Photos
  • zAsset-Modification Date indicates a change at 3/21/2022 at 00:41:22 (UTC-0) which is the time the assets were uploaded to iCloud Photos zCldMast-Import Date is a better column of data to reference for this purpose but wanted to point out yet another reason for the modification date could change
  • zCldMast-Creation Date indicates when the cloud master asset was created
  • zIntResou-Cloud Master Date Created indicates when the internal resource was created for the cloud master asset
  • zCldMast-Import Date indicates when the cloud master asset was imported to iCloud Photos. Notice the asset was created on this device the import date matches the created date. Notice the other assets on the screen IMG_0030.MOV and IMG_0034.HEIC they have import dates that match the cloud master creation date.
  • zIntResou-Last On-Demand Download date, zIntResou-Cloud Last Prefetch Date, and zIntResou-Cloud Prefetch count are all 0 or empty, these columns will be discussed again later, but these are the columns you will want to analyze if you want to know if an asset was synced to the device from iCloud Photos or Shared Albums 
  • The time zone on the test device is set back to the correct time zone Cupertino, U.S.A (UTC-7)

Still Photo captured iCloud Photos and Sharing Albums on

  • After the still photo was captured, the Photos.sqlite database was updated, the device data was not remapped
  • There is only one row of data for the new asset (IMG_0036.HEIC)
  • CMzCldMasterMedData-Data is not populated yet because the cloud master asset has not been analyzed
  • zAsset-Cloud Asset GUID is not populated yet
  • Again, observe the test device and the assets are uploading to iCloud Photos at 6:11 PM (UTC-7)
  • zAddAssetAttr-Time Zone Name is not indicating the correct time zone the device is currently in
  • and other cloud master data columns are not yet populated
  • zIntResou-Data Store Sub Type and zIntResou-Uniform Type ID can provide insights into the different rows of data for the asset
  • zAsset-Cloud Local State indicates if the asset has been synced with iCloud Photos notice all other assets other than IMG_0036.HEIC as a value of Synced with Cloud-1, but IMG_0036.HEIC has a value of Cloud Shared Album Asset/Not Synced-0
    • Most of us will never encounter this during an analysis because we do not get devices within a few seconds of an asset being captured, but I guess it’s possible  
    • As I mentioned previously, this decoding is incorrect. The correct decoding you will see in the future is the following:
      • iCldPhotos ON=Asset in Shared Album/iCldPhotos OFF=Not Synced-0
      • iCldPhotos ON=Asset Synced with iCloud-1
  • zIntResou-Cloud Local State indicates if the cloud internal resource is available
  • In Figure #23 at 0:06:00 the Photos.sqlite data is refreshed and additional data will be populated and/or changed
    • IMG_0036.HEIC has additional rows in the query output
    • CMzCldMasterMedData-Data now is populated
    • zAsset-Cloud Asset GUID now populated
    • zCldMast-Org Filename now populated
    • zCldMast-Creation Date now populated
    • zIntResou-Cloud Master Date Created now populated
    • zCldMast-Import Date now populated
Figure #23

iCloud Photos and Shared Albums on some notable artifacts

During testing, I located the following plist which provided accurate details and timestamps about iCloud Photos, device sync, and update status. In Figure #24, I also review some file system locations to identify files and assets that have been stored as the result of an asset being uploaded to iCloud Photos.

  • \private\var\mobile\Media\PhotoData\CPL\
    • syncstatus.plist
      • cloudAssetCountPerType
        • public.image indicates the number of Photos synced with iCloud Photos
        • public.movie indicates the number of Videos synced with iCloud Photos
      • iCloudLibraryExists (True/False) think it’s clear what this indicates
      • inAirplaneMode (True/False) indicates if the device is in Airplane mode
      • initialSyncDate indicates timestamp (UTC) when the device was initially synced with iCloud Photos this timestamp might be a few seconds after the timestamps located in cloudServiceEnableLog.plist because it will take additional time to upload the assets
      • lastCompletePrefetchDate indicates a timestamp (UTC) when the device last prefetched assets from iCloud Photos. In this example, notice the timestamp indicates 2022-3-21T01:11:24z, which matches the last time (6:11 PM UTC-7) the device Photo Library was uploading data mentioned in the previous section and can been seen in Figure #23
      • lastSyncDate indicates the timestamp (UTC) of the last sync / update. In this example, Figure #24, when the device was unlocked, the Photos Application was already running and viewing the Photo Library. At the bottom of the screen, you will notice it is indicating Updating. Notice the time being displayed (3/20/2022 at 6:48 PM UTC-7), it’s the same time listed in the lastSyncDate key
  • \private\var\mobile\Media\DCIM\<*>APPLE
    • This location will contain assets being stored in the Photos Application – Photo Library this includes assets when iCloud Photos is turned on or off
    • At this time in the example, the data within ArtEx has not been updated and remapped since IMG_0036.HEIC was captured. After the remapping of the device data, we can see the newly captured asset
  • In Figure #24 additional file system locations are checked for any related files.
    • \private\var\mobile\Media\PhotoData\MISC\
      • DCIM_APPLE.plist
        • DCIMLastFileNumber key has been updated and now indicates 36
  • \private\var\mobile\Media\PhotoData\Thumbnails\V2\DCIM\<*>APPLE\
    • As stated previously, this location will store the thumbnails for assets stored in the Photo Library
  • \private\var\mobile\Medai\PhotoData\Thumbnails\VideoKeyFrames\DCIM\<*>APPLE\
    • This file location has not been discussed previously and wanted to point some key points about this location:
      • This location will store asset named folders. During my testing, only video assets (live photos are not considered videos in this instance) will have a folder in this location
      • Within the asset named folder, I found files named LocalVideoKeyFrame.jpg which will be the key frame from the video this was something the OS selected. During testing, I did not select or choose these key frames
  • When iCloud Photos are viewed from a web browser, we can see the asset counts in iCloud Photos and on the device match. We can also see both were recently updated
  • Reviewing the Photos.sqlite output we can see the zAsset-Directory indicates DCIM/100APPLE and zAsset-Saved Asset Type indicates DCIM-APPLE-3
    • These are indications an asset originated from the device and not iCloud Photos (CPLAsset)
    • zAsset-Cloud Placeholder Kind will also provide some insight into how the assets were originated; in this example we can see a value of Local&CloudMaster Asset-0. Decoded values include:
      • Local&CloudMaster Asset-0
      • Cloud 5005JPG Thumbnail Asset Only-3
      • Local Asset JPEG-Cloud Asset HEIC-JPG-JPEG-MOV-PNG-4
      • Asset_synced_via_iCloud_Photos_2_Device-5
      • Cloud MP4 7
      • Local Asset JPG-Cloud Asset Live Photo MOV-8
      • Cloud MP4-Saved Local Device-9
      • Additional values were observed during testing, but I have yet to confidently decoded them
    • zAsset-Cloud Local State, zIntResou-Cloud Local State, zCldMast-Cloud Local State will indicate if an asset was synced with iCloud Photos. In Figure #24, this is indicated by Synced with Cloud-1 and Synced with Cloud-3
Figure #24

Automatic timer setting tested based on DFIR Discord community question

Someone recently asked if there was a way to determine if the automatic timer was set prior to the capturing of an asset. I located a property list that held the setting, but it only holds the last setting used before the camera application was closed. The plist was found in iOS 14.7 and iOS 15.1 and is related to the Camera Application (com.apple.camera).

  • \private\var\mobile\Library\Preferences\
    • com.apple.camera.plist
      • CAMUserPreferenceTimerDuration key/node
        • 0 = off or no timer
        • 1 = 3 seconds
        • 2 = 10 seconds
  • In Figure #25, when the plist is opened, the setting is set as 2 (10 seconds), which was the setting when the camera application was last suspended or closed
  • I changed the automatic timer setting to 3 seconds, suspended the app, and reopened the plist
    • CAMUserPreferenceTimerDuration now indicates 1 (3 seconds), which was the setting when the application was suspended
    • This process was completed again when the setting was changed to timer off, the CAMUserPreferenceTimerDuration now indicates 0 (timer off)
  • This process was also completed for the CAMUserPreferenceDesiredFlashMode key
    • 0 = Flash Off
    • 1 = Flash On
    • 2 = Flash Auto
Figure #25

Screenshot previously created

Figure #26 is a review of the data for a screenshot (IMG_0092.PNG) that was previously created.

  • Like other assets previously discussed a screenshot created with a device will have artifacts stored at:
    • \private\var\mobile\Media\DCIM\<*APPLE>
    • \private\var\mobile\Media\PhotoData\Thumbnails\V2\DCIM\<*APPLE>\<AssetFileName>\5005.JPG
  • Notable items from Photos.sqlite for a screenshot:
    • zAsset-Cloud Local State – indicates iCldPhotos ON=Asset Synced with iCloud-1 which means iCloud Photos is turned on and the asset has been synced with the Apple ID iCloud account logged into the analyzed device
    • zAddAssetAttr-Creator Bundle ID and zCldMast-Imported by Bundle ID – indicates com.apple.springboard meaning the screenshot was created via the SpringBoard application, even if a third-party application is in focus when a screenshot is captured the creator bundle ID and imported display name will be the springboard
    • zAddAssetAttr-Imported by Display Name and zCldMast-Imported by Display Name – indicates SpringBoard which corresponds with the bundle ID
    • zAddAssetAttr-Imported By and zCldMast-Imported By – indicates Native-App-8 which means the screenshot was created with a native application
    • Orientation – the orientations for screenshots will be listed as Video-Default-Adjustment-Horizontal-Camera-(left)-1 because they were not captured with a camera and (1) is the default value
    • zAsset-Kind Sub Type – indicates SpringBoard-Screenshot-10
    • zAsset-Uniform Type ID – indicates public.png
Figure #26

Review of asset Moment, Photos Highlight, and Suggestion table data

Figure #27 is a review of the current asset data coming from ZMEMORY, ZMOMENT, ZMOMENTLIST, ZPHOTOSHIGHLIGHT, and ZSUGGESTION tables.

  • Moments and Photos Highlights can be viewed via the device within the Photos Application > For Me area and can be searched, as demonstrated in Figure #27
  • ZMEMORY table
    • At this time the ZMEMORY table does not contain data, I will revisit this area later in this write-up
  • ZMOMENT and ZMOMENTLIST table data
    • Based on the data being stored in the ZASSETZADDITIONALASSETATTRIBUTES, ZMOMENT, and ZMOMENTLIST tables, I was able to decode and parse moments in to two categories, Year Moments & Year and Month Moments
    • The column title will indicate which moment data you are viewing
      • YearzMomentList – indicates Year Moment
      • MegaYMzMomentList – indicates Year and Month Moment
      • zMoment – will indicate data for the moment for that specific asset
    • Year Moment
      • YearzMomentList-Sort Index – indicates the year for the moment
      • YearzMomentList-Granularity Level – indicates the moment is a year moment by Year Moment-20
      • YearzMomentList-Start Date – is based on the asset in the moment with the earliest creation date
      • YearzMomentList-End Date – is based on the asset in the moment with the latest creation date
        • In this example for the 2022 Moment the created date for the first asset/start date in the Year Moment is 2/26/2022 at 01:40:21 UTC and the last asset created date/end date for the Year Moment is 4/05/2022 at 19:10:39 UTC
        • You may have noticed some assets with a created dates prior to 2/26/2022 at 01:40:21 UTC, these assets were from a Shared Album and are not included in Moments
    • Year and Month Moment
      • MegaYMzMomentList-Sort Index – indicates the year and the month for the moment, in this example we can see 202204
      • MegaYMzMomentList-Granularity Level – indicates the type of moment, in this example we can see Year and Month Moment-10
      • MegaYMzMomentList-Start Date – is based on the asset in the moment with the earliest creation date
      • MegaYMzMomentList-End Date – is based on the asset in the moment with the latest creation date
        • In this example for the 202203 Moment the created date for the first asset/start date in the Year and Month Moment is 3/3/2022 at 06:24:14 UTC and the last asset created date/end date for the Year and Month Moment is 3/21/2022 at 01:10:19 UTC
    • Moment
      • zMoment-Start Date – is based on the asset in the moment with the earliest creation date
      • zMoment-Modification Date – is based on any modifications made to any of the assets in the moment
      • zMoment-End Date – is based on the asset in the moment with the latest creation date
        • In this example, the Las Vegas Moment, the created date for the first asset/start date in the Moment is 4/1/2022 at 18:37:17 UTC and the last asset created date/end date for the Moment is 4/2/2022 at 00:11:31 UTC
      • zMoment-Title – indicates the title for the moment
      • zMoment-Localized Location Names – contains data, a plist during testing, which will contain the Moment Title
      • zMoment-Processed Location, zMoment-Approx Latitude, zMoment-Approx Longitude – will provide information about the Moment location
      • zMoment-Cache Count, zMoment-Cached Photos Count, and zMoment-Cached Videos Count – like other areas with similar names, will indicate how many assets are included in the Moment
  • ZPHOTOSHIGHLIGHT table data
    • Based on the data being stored in the ZASSETZADDITIONALASSETATTRIBUTES, and ZPHOTOSHIGHLIGHT tables I was able to decode and parse several different photos highlights (These table and column names may change between iOS Versions)
      • HBAzPhotosHigh = ZHIGHLIGHTBEINGASSETS
      • HBEAzPhotosHigh = ZHIGHLIGHTBEINGEXTENDEDASSETS
      • HBKAzPhotosHigh = ZHIGHLIGHTBEINGKEYASSET
      • HBSAzPhotosHigh = ZHIGHLIGHTBEINGSUMMARYASSETS
      • YHBKAzPhotosHigh = ZYEARHIGHLIGHTBEINGKEYASSET
      • MHBFAzPhotosHigh = ZMONTHHIGHLIGHTBEINGFIRSTASSET
      • MHBKAzPhotosHigh = ZMONTHHIGHLIGHTBEINGKEYASSET
      • DGHBAzPhotosHigh = ZDAYGROUPHIGHLIGHTBEINGASSETS
      • DGHBEAzPhotosHigh = ZDAYGROUPHIGHLIGHTBEINGEXTENDEDASSETS
      • DGHBKAzPhotosHigh = ZDAYGROUPHIGHLIGHTBEINGKEYASSET
      • DGHBSAzPhotosHigh = ZDAYGROUPHIGHLIGHTBEINGSUMMARYASSETS
      • YMParzPhotosHigh = Year Month ZPARENTPHOTOSHIGHLIGHT
      • YearParzPhotosHigh = Year ZPARENTPHOTOSHIGHLIGHT
      • DGParzPhotosHigh = ZPARENTDAYGROUPPHOTOSHIGHLIGHT
    • In Figure #27, you will notice lots of examples of the photo highlights data contained in Photos.sqlite and how those highlights appear on the test device
    • NOTE: There is still a lot of data within the photos highlight table that I haven’t confidently decoded and should be noted it’s still a work in progress
  • ZSUGGESTION table data
    • Based on the data being stored in the ZASSETZADDITIONALASSETATTRIBUTES, and ZSUGGESTION tables I was able to decode and parse several different suggestions (These table and column names may change between iOS Versions)
      • SBKAzSugg = Z_55SUGGESTIONSBEINGKEYASSETS
      • 3SuggBRA = Z_3REPRESENTATIVEASSETS1
      • SBRAzSugg = Z_55SUGGESTIONSBEINGREPRESENTATIVEASSETS
    • SBKAzSugg-Feature Data – contains data, during testing the data was a plist, which in some cases contained device information for the asset
    • At this time during testing, there are only a few assets listed in the suggestions
Figure #27

Capturing assets with native Messenger (com.apple.MobileSMS) (iCloud Photos is turned on)

In Figure #28 an asset was captured with the device camera (com.apple.camera.CameraMessagesApp) via the native Messenger application (com.apple.MobileSMS) and later deleted. Additional assets were received as attachments via the messenger application and then saved to the Photo Library. Figure #28 will review some of the artifacts that might be discovered if these actions take place on a device.

  • When the attachment message is sent, you will notice a notification at the top of the application screen “Add and share your name and photo” I dismissed the setting window without setting it up
    • This setting can be accessed on the device via
      • Settings > Messages > Share Name and Photo
    • \private\var\mobile\Library\Preferences\
      • com.apple.messages.nicknames.plist located at: 
        • MeCardSharingEnabled
          • False = not set up
          • True = set up
        • There are additional settings in this plist, but I did not test them
  • CMzCldMastMedData-Data – is populated because iCloud Photos is turned on and the asset was synced with iCloud Photos. Within the plist we can identify lots of metadata about the captured asset
    • HostComputer – indicates the where the asset came from
    • Software – indicates the software used by the device which captured the asset
    • DateTime – indicates the timestamp in device local time that the asset was captured
    • Model – indicates model name of the capturing device
    • Make – indicates the make of the capturing device
    • Flash – indicates the flash setting at the time of capture, still working on decoding the values
    • LensModel – indicates the lens model, which can include the device model
    • OffsetTimeDigitized – indicates the device time zone offset when the asset was captured, this might be critical if the device time zone, time, and date has been manually set
    • DateTimeOriginal – in this example matches the DateTime and zAsset-Date Created
    • There are lots of other values in this plist, but I haven’t been able to decode them all. Additional testing is required
  • zAsset-Cloud Local State – indicates iCldPhotos ON=Asset Synced with iCloud-1 because the asset has been synced with iCloud Photos
  • zAsset-Filename and zAddAssetAttr-Org Filename – comparing these two columns we can see the file names are different. Are we able to find the original asset within the device? Answer: Yes, we can!
  • zAddAssetAttr-Creator Bundle ID – indicates the bundle id used to create the asset
  • zAddAssetAttr-Imported by Display Name – indicates the application display name
  • zAsset-Directory – indicates a partial file path for the asset
    • \private\var\mobile\Media\DCIM\100APPLE
      • Contains IMG_0095.HEIC and IMG_0095.MOV because the asset captured was a Live Photo, but where is the original asset using the original filename?
      • IMG_0095.MOV is not listed in the Photos.sqlite query output. MOV files from Live Photos will never be listed in the Photos.sqlite output
  • Using the zAddAssetAttr-Org Filename value we can search and locate the following file paths which contain the original assets (HEIC and MOV):
  • \private\var\mobile\Containers\Data\PluginKitPlugin\<GUID for com.apple.camera.CameraMessagesApp>\tmp\<Unknown-Timestamp__GUID.ext>
    • \private\var\mobile\Containers\Data\PluginKitPlugin\B0CEF1A4-7723-4CAE-A9A5-16B6F1E7D0D2\tmp\67132002870__8BA4A51C-39D7-4D82-AFEC-F36B02FD5515.HEIC
    • \private\var\mobile\Containers\Data\PluginKitPlugin\B0CEF1A4-7723-4CAE-A9A5-16B6F1E7D0D2\tmp\67132002773__9DA3456E-857E-4ED5-87DA-52D4DB769D2E.MOV
  • \private\var\mobile\Library\SMS\Attachments\<FolderName>\<FoldeName>\<sms.db-attachment_table-GUID>\<Unknown-Timestamp_GUID.ext>
    • \private\var\mobile\Library\SMS\Attachments\1d\13\0BE884CC-7E2A-47E9-9E8D-30BE2DECA270\67132002870__8BA4A51C-39D7-4D82-AFEC-F36B02FD5515.MOV
  • \private\var\tmp\com.apple.messages\com.apple.MobileSMS\Media\<Folder_GUID>\<Unknown-Timestamp_UUID.ext>
    • \private\var\tmp\com.apple.messages\com.apple.MobileSMS\Media\6AFF0ECB-B63B-456A-924F-D224C4520991\67132002773__9DA3456E-857E-4ED5-87DA-52D4DB769D2E.MOV
    • \private\var\tmp\com.apple.messages\com.apple.MobileSMS\LinkedFiles\2D944B52-AADC-4A74-8004-66EA9C441EAB\67132002870__8BA4A51C-39D7-4D82-AFEC-F36B02FD5515.MOV
  • \private\var\tmp\com.apple.Photos\Neutrino\<Temporary-FileName.ext>
    • \private\var\tmp\com.apple.Photos\Neutrino\065841adc370b423765a7cdf49c80b80.lexp.jpg
  • Notice, some of the file paths are related to tmp storage. I have not been able to determine how long these assets will be temporarily stored, but it’s worth noting these locations could contain assets deleted from the Photo Library. For more information this and some similar earlier testing please review a previous write-up which can be found here:
  • Store.cloudphotodb – is a newly discovered database that I was not aware of until recently. It contains lots of data about Cloud Photo Library Asset transactions and changes. I will be writing a query for this database soon. In Figure #28 you will notice some of the data I was able to correlate to data stored in the Photos.sqlite database
    • \private\var\mobile\Media\PhotoData\CPL\
    • and
    • \System\Library\PrivateFrameworks\CloudPhotoLibrary.framework\
      • cloudphotos-1.0.plist
        • max.days.inRecentlyDeleted
        • max.num.photosToImport.daily
        • max.num.photosToImport.hourly
        • max.num.photoToUploadPerBatch
        • max.num.resourcesToDownloadPerPush
        • max.num.resourcesToUploadPerBatch
        • photo.derivatives and video.derivatives
        • Additional keys available in the plist, but haven’t decoded them
    • \private\var\mobile\Media\PhotoData\CPL\
      • DownloadCounts.plist
        • CountKeyImages
        • CountKeyVideos
        • At this time, there are no downloads recorded in this plist, we will revisit this later after its populated with data
      • Initalsync_marker – indicates a timestamp when the last initial sync was completed. Given the recent questions on the listservs, I am not aware if this timestamp will change if a different Apple ID is used to log into iCloud Photos and a new sync is completed. Further testing is needed that will not be covered in this write-up
Figure #28

Asset last shared timestamp

Something was recently brought to my attention that I was not unaware of, nor had I tested it. James (Twitter: @Mr_EVFA), and I were discussing some artifacts and he mentioned if the zAsset-Last Shared Date was populated, it did not guarantee the asset was in fact shared. He stated he has observed where an asset was placed into an iMessage draft, then deleted that attachment from the drafted message, and the Photos.sqlite would record a zAsset-Last Shared Date for the asset, but the message was not sent, and the asset was not shared. This was a fantastic catch, thanks for letting me know about what you observed!

Based on the testing, my conclusion for this section would be to recognize that an asset can be shared to others and there might not be a zAsset-Last Shared Date documenting the share

  • In Figure #29.1 and Figure #29.2, I tested several different applications and documented if anything was recorded when those assets were shared.
  • NOTE: During this portion of testing, I am using a beta version of ArtEx (v2.2.2.0) and Ian Whiffin’s Advance SQL tool, which can process the database, WAL and recover data from free pages. You may see duplicates, but we will also be able to review data that has not been shown in previously examples.

Figure #29.1

  • IMG_0136.MOV
  • Asset was shared via native Messages Application (com.apple.MobileSMS). Photos.sqlite recorded a shared timestamp
    • The asset can be found in Scooters iPhone X Photo Library
  • I opened the messaging application and opened the chat thread with Dexter
    • I pressed on the Photos Application icon in the lower left corner of the screen. By doing so brought up the Recent Photos from the Photo Library
    • But I wanted to view more of the assets being stored in the Photo Library and then pressed on the All Photos text. Doing so, brough up the Photo-Picker UI
    • Using the Photo-Picker UI, I selected the asset (IMG_0136.MOV) that I wanted to share with Dexter
    • The attachment and a friendly message reminding Dexter why I was sharing the asset was sent on 2022-04-23 10:03 UTC-7
    • A review of the Photos.sqlite data now indicates the asset was shared at 2022-04-23 17:02:41 UTC
  • NOTE: @Mr_EVFA has been working on some fantastic research into the Photo-Picker application and wanted to share his recently published blog:
    • Based on his research, I checked the Photo-Picker tmp location mentioned in his blog and the asset I just shared did not have a trim file. Indicating in this instance, the messaging application did not save an additional asset / file because the Photo-Picker UI was used
  • IMG_0138.MOV
    • I used the Messages Application to draft a message and add an attachment that was not sent / shared. As a result of the drafted message and attachment, Photos.sqlite recorded an asset shared timestamp
  • The native messaging application is opened, and the Photo-Picker UI is again used to add the asset as an attachment to a message that was only drafted and not sent
  • See Figure #32 and associated documentation for information on drafted messages and attachments
    • Text is added to the drafted message indicating the message and the attachment will only be drafted
    • A review of the Photos.sqlite data now indicates the asset was shared at 2022-04-23 17:14:31 UTC
    • But again, notice the attachment was not sent / shared with anyone, it was only drafted
    • I then deleted the drafted message & attachment. Then reloaded the database to see if the shared timestamp was removed and it was not. The database still indicated the asset was shared, but that is not true
  • IMG_0141.HEIC
    • Asset shared via Instagram post which had a tagged person in the post. Photos.sqlite did not record a shared timestamp
      • Reviewed the Photos.sqlite data recorded for the asset
        • Created Date: 2022-04-19 02:45:04 UTC
        • Local Created Date: 2022-04-18 19:45:04 UTC-7
        • EXIF Timestamp: 2022-04-18 19:45:04 UTC-7
        • zAsset-Last Shared Date: blank / empty
      • Once the Instagram application is set up on the device, I posted the asset and tagged Dexter in the post
      • After the review of Photos.sqlite sharing an asset via an Instagram post, does not trigger a share timestamp
  • NOTE: After further testing, if an asset is shared via Instagram instant message, the Photos.sqlite will have an accurate shared timestamp. It appears a post is not considered a share.
  • IMG_0147.PNG
  • Asset shared via Snapchat. It should be noted the asset shared was the asset being stored in the Mutations location, and not the asset being stored in the DCIM location
  • The asset being stored in the Mutations file location is not listed in the Photos.sqlite database
    • Reviewed the Photos.sqlite data recorded for the asset
      • Created Date: 2022-04-19 03:01:13 UTC
      • Local Created Date: 2022-04-18 20:01:13 UTC-7
      • EXIF Timestamp String: 2022-04-18 20:01:13 UTC-7
      • zAsset-Last Shared Date: blank / empty
    • At 6:17 of Figure #29.1, we can see the asset being accessed from the file system at:
      • \private\var\mobile\Media\DCIM\<*APPLE>\
    • NOTE: The asset in the above file system location does not have the yellow writing (mark-up) as seen in the Photo Library. This is because the asset being displayed in the Photo Library is being displayed from the Mutations file system location:
      • \private\var\mobile\Media\PhotoData\Mutations\DCIM\<*APPLE>\
        • FullSizeRender.jpg
  • NOTE: After further testing, I was able to populate the Photos.sqlite zAsset-Last Shared Date for assets shared within Snapchat, but it would not populate for every asset shared. Use caution when analyzing assets shared within the Snapchat application.
  • IMG_0157.HEIC
  • Asset was Shared via Snapchat message and via Kik message.
    • When the asset was shared via Snapchat message, it did not record a shared timestamp in Photos.sqlite
    • When the asset was shared via Kik it did record a shared timestamp in Photos.sqlite
Figure #29.1

Figure #29.2

  • IMG_0158.HEIC
  • Asset was Air dropped to Dexter and Bandit via contacts only and it did not record a shared timestamp in Photos.sqlite
  • IMG_0158.HEIC
  • Asset added to new shared album
    • zGenAlbum-TitleNew shared album for IMG_0158.heic shared 4-23-22 @835PM
    • Shared to Bandit iPhone 6s Plus
    • A new asset (IMG_0001.HEIC) was created on Scooters iPhone X because of the asset (IMG_0158.HEIC) being shared via Shared Album
  • Original asset shared: IMG_0158.HEIC
    • zAsset-Date Created: 2022-04-24 02:48:30 UTC
    • zAddAssetAttr-EXIF Timestamp String: 2022-04-24 19:48:31 UTC-7
    • zAsset-Add Date: 2022-04-24 02:48:31 UTC
    • zAsset-Modification Date: 2022-04-24 10:06:26 UTC
      • This is only one second off from the new asset zAsset-Add Date
    • zAsset-Last Shared Date: 2022-04-24 03:35:26 UTC
      • This is the timestamp related to this asset being shared via shared album
    • zAddAssetAttr-Imported by: Native-Back_Camera-1
    • zGenAlbum-Title: None listed
Using file names to back track to original file name
  • New asset created: IMG_0001.HEIC stored in PhotoCloudSharingData file path
    • zAsset-Date Created: 2022-04-24 02:48:31 UTC
      • This is only one second after the original file created date, but this asset was just created at 2022-04-24 03:35 UTC as seen in Figure #29.2
    • zAddAssetAttr-EXIF Timestamp String: 2022-04-23 19:48:31 UTC-7
    • zAsset-Add Date: 2022-04-24 03:35:25 UTC
      • This is a more accurate documented time to when the asset was created, not the recorded zAsset-Date Created
      • zAsset-Modification Date: None listed
    • zAsset-Last Shared Date: None listed
      • This is the asset in the PhotoCloudSharingData, and we can see has a zAsset-Cloud is My Asset indicates the shared asset is my asset
    • zAddAssetAttr-Imported by: Cloud-Other-0
    • zGenAlbum-Title: New shared album for IMG_0158.heic shared 4-23-22 @835pm
Figure #29.2

Data indicating IMG_0095.HEIC was captured with camera message’s application

Figure #30 will review data indicating that IMG_0095.HEIC (IMG_0093 in the video) was captured with the camera messages application and the file system locations relevant data could be stored.   

  • One of the first things highlighted in Figure #30 is that fact that even when a Live Photo is captured with the camera messages application, you will have both a HEIC asset and a MOV asset
    • 67132002773__9DA3456E-857E-4ED5-87DA-52D4DB769D2E.MOV
    • 67132002870__8BA4A51C-39D7-4D82-AFEC-F36B02FD5515.HEIC
  • Some of the file system locations that we viewed previously in Figure #28 are again reviewed in Figure #30

iMessage attachment received and notable data in the device

  • The iMessage attachment was sent from another test device (iPhone 7) using the name/Apple ID for Dexter
  • When we are reviewing the file locations we mentioned in Figure #28 section, we can now see the new assets for the iMessage attachment with IP7 markup
  • Notice, the iMessage attachment is a Live Photo, thus we again have both the HEIC asset and the mov asset
  • The following are the file system locations where these assets were initially stored:
    • \private\var\mobile\Library\Caches\com.apple.MobileSMS\Previews\Attachments\99\09\B65A452D-439D-4F66-8A04-64DF2AAA8426\RenderedImage-preview.pvt\RenderedImage.HEIC
    • \private\var\mobile\Library\SMS\Attachments\99\09\B65A452D-439D-4F66-8A04-64DF2AAA8426\RenderedImage.HEIC
    • \private\var\mobile\Library\SMS\Attachments\99\09\B65A452D-439D-4F66-8A04-64DF2AAA8426\B65A452D-439D-4F66-8A04-64DF2AAA8426.pvt\RenderedImage.HEIC
  • Notice the mov associated file, does not have the markup writing of IP7
  • We then review the device Apple ID iCloud Photos account and notice the iMessage attachment is not yet synced
  • The iMessage attachment with the IP7 markup is saved to the Photo Library on the device
    • iCloud Photos is turned on, so there should be iCloud Photos Sync for this asset
    • We reviewed the data stored in Photos.sqlite for the newly saved asset IMG_0096.HEIC
    • Notice zAddAssetAttr-Org Filename matches the file name we observed in the above file system storage locations
    • Notice we can observe the asset saved to the device has EXIF timestamp string and the time zone offset
    • Again, the CMzCldMastMedData-Data column data contains some good metadata about the original asset
    • At 08:47 in Figure #30 we can see the difference between an asset that was created with the camera messages application (com.apple.messages.CameraMessagesApp) versus the asset saved from the message’s application (com.apple.MobileSMS)
    • Even though the iMessage attachment was adjusted via a markup when it was captured on the iPhone 7, after it was sent to the iPhone X and an entry was made in the Photos.sqlite, the asset does not have any adjustments listed
    • zIntResou-Resource Type – indicates that there are two different internal resources for the asset, one is a Photo-0 and the other is the Live-Photo-3
    • Now, that the asset was saved to the device Photo Library, the assets will be stored in the typical storage locations mentioned previously
  • When the newly saved asset, IMG_0096.HEIC, was saved to the device and synced with the iCloud Photo Library, there was entries made in the store.cloudphotodb that indicates the direction of the sync. I will discuss this in detail late in this write-up and describe the query that can be used to decode the data in the database
Figure #30

Asset Captured with Photobooth on a MacBook using the same Apple ID

In Figure #31 an asset was captured with Photobooth on a MacBook which was using the same Apple ID as Scooters iPhone X, then that asset was sent to Bandits iPhone 6S Plus via iMessages on the MacBook. We will review the data contained on the device as the result of those actions.

  • After reviewing the previously mentioned file system locations, we can find the new asset in messages with a file name of:
    • Photo on 4-10-22 at 5.01 PM.heic
  •  The message attachment is then saved to Scooters iPhone X Photo Library with the file name of:
    • IMG_0097.HEIC
  • Notice the zAsset-Filename and the zAddAssetAttr-Org Filename are the same, this is different than what we have seen in the past. We have already established that the attachment has a file name listed above
  • IMG_0095.HEIC (IMG_0093 in the video) was deleted from the device, which automatically deleted the asset from iCloud Photos. Then the messages attachment file system locations, listed and depicted in Figure #28, were checked to determine if deleting the Photo Library asset also deletes the assets in the attachment locations?
  • The attachment assets were still present
  • NOTE: All the above tmp and attachment files still existed the next day even after the Photo Library asset (IMG_0095.HEIC (IMG_0093 in the video)) was deleted, which you see Figure #28
  • NOTE: All the above attachment files still existed on 4/30/2022 even after the Photo Library asset (IMG_0095.HEIC (written on paper IMG_0093 in video)) was deleted
Figure #31

A message is drafted, and a previously created screenshot was attached to the message draft

During testing, I found an interesting artifact while testing attached media in the message’s application. In Figure #32 you will notice a screenshot has been attached to a message and a message was typed into the app, Test to get syndication. Let’s look if we can find that drafted message content. I was not able to generate syndication, which I was initially testing for, but found this draft message data.

  • \private\var\mobile\Library\SMS\Drafts\<contact information for the draft message>
    • composition.plist
      • shelfPluginPayload – is a base 64 encoded bplist that might contain the media attachment and / or data about the media attachment to include a tmp file system location the attachment is located with a file name
      • text – is a base 64 encoded bplist that contains the message content
  • \private\var\tmp\com.apple.messages\Drafts\<contact information>
    • recipients.plist
      • If the draft message was sent to a phone number, the following path with contain the composition.plist and the recipients.plist
Figure #32

store.cloudphotodb review of past assets already synced with iCloud Photos

Have you ever wanted to know if an asset was on the device, then synced with iCloud Photos or if the iCloud Photos asset was synced to the device? In Figure #33 we will review the data for some previously created assets that have been synced with iCloud photos and review the data stored in store.cloudphotodb to determine if there are any notable artifacts.

  • \private\var\mobile\Media\PhotoData\CPL\storage\
    • store.cloudphotodb
  • First, we will review the Photos.sqlite database data for an asset (IMG_0100.PNG) that was previously created and synced
    •  zCldMast-Import Date – in Photos.sqlite indicates the asset was imported on 2022-04-13 05:38:56 UTC
      • NOTE: If you watch closely when the store.cloudphotodb database data is being reviewed you will notice the zCldMast-Import Date matches a value in the idMapping-addTimestamp
    • zAsset-UUID – 594DDAB8-8615-42AC-B0A1-E4D58D9CA833
    • zAsset-Cloud_Asset_GUID – 594DDAB8-8615-42AC-B0A1-E4D58D9CA833
    • zCldMast-Cloud_Master_GUID – ATCPYyT8bRFIIRp7aoKPUVYdPFKe
  • Let’s review the data in store.cloudphotodb database and determine if we can learn anything about IMG_0100.PNG
    • Based on what I have decoded so far, I’ve written a small SQLite query for the database, which can be found on GitHub. The query only includes a small dataset compared to the data being stored. Additional testing and validation are needed for this database, but the data appeared too valuable to skip and not mention. The following is some notable data that should be reviewed:
      • Scopes-creationDate – this timestamp indicates when the corresponding scope identifier was created, in this example, the PrimarySync scope identifier was created shortly after the test device was factory reset (see Figures #1.1 and Figure #1.2 for additional details)
      • scopes-scopeIdentifier – I have only encountered two values listed as identifiers (Primary Sync & Cloud Master Moment Identifier). The Cloud Master Moment Identifier can be matched to the data stored at Photos.sqlite – zShare-ScopeID
      • scopes-initialSyncDate – I believe this timestamp indicates when the corresponding scope identifier was initially synced with iCloud Photos, in this example the following timestamps are recorded:
        • PrimarySync – indicates the initial sync date was on 3/06/2022 at 10:12:50 PM (UTC-0), if you review Figure #19, this occurred after iCloud Photos was turned on
        • CMM-EB552B46-B954-4D72-9915-0B2B3480931C – indicates the initial sync date was on 3/26/2022 at 3:53:41 PM (UTC-0), we can check the Photos.sqlitezIntResou-cloud Last Prefetch Date. Notice the timestamps match          
      • cloudCache-class – indicates the type of class or change that occurred for the asset, the following are a list of classes encountered during testing:
        • CPLAssetChange
        • CPLAlbumChange
        • CPLContainerRelationChange
        • CPLFaceCropChange
        • CPLMasterChange
        • CPLMemoryChange
        • CPLPersonChange
        • More to come I’m sure…
      • cloudCache-serializedRecord – may contain a bplist that stores metadata about the asset. This might include type of asset, asset EXIF data, asset file name, owner name, the devices name, asset creator bundle ID, and lots of other metadata about the asset 
      • idMapping-addDirection – during testing, I was only able to produce two values (1 and 2), which based on testing I believe indicates the following:
        • Value of 1 = asset was synced between from the device to iCloud Photos
        • Value of 2 = asset was synced between from iCloud Photos to the device
          • Decoded values include:
            • Device2iCloudPhotos-1
            • iCloudPhotos2Devcie-2
      • downloadQueue-resourceType – indicates the download resource. Decoded values include:
        • iCloud Photos-5
      • downloadQueue-status – indicates the download status. Decoded values include:
        • Synced with Device-2
      • idMapping-mappingState – indicates if the mapping was successful. Decoded values include:
        • Successful-1
      • idMapping-addTimestamp – indicates the last time the sync took place. In this example you will notice this timestamp matches the timestamp found in Photos.sqlite – zIntResou-Cloud Last Prefetch Date
      • cloudCache.confirmed – indicates if confirmed
      • cloudCache-state – indicates if the state is active
      • scopes-disabledDate – was not able to populate during testing
      • scopes-deleteDate – indicates a timestamp when the iCloud Share Link will expire, this data will match the data in Photos.sqlite – zShare-ExpiryDate
      • idMapping-deleteTimestamp – indicates the timestamp when an asset was deleted from Photo Library
        • If you are in possession of a device backup, you might be able to use this data and the data in the device backup to determine exactly which Photo Library / iCloud Photos asset was deleted and exactly when that asset was deleted
        • It should be noted, during testing, I completed a batch delete, meaning several assets were deleted from recently deleted at the same time and for some reason the delete dates did not record in this database.
      • idMapping-deleteDirection – during testing I could not determine what caused data to be populated here, but initial indications are when an asset was deleted from the device Photo Library > Recently Deleted area, then a value of 1 is recorded which I believe indicates:
        • Deleted via Recently Deleted via Device Photos App-1  
      • cloudCache-identifier – indicates a unique identifier that can be matched up with the unique identifier located in Photos.sqlite zAssetUUID-CldMstGUID
      • idMapping-cloudIdentifier – indicates a unique identifier that can be matched up with the unique identifier located in Photos.sqlite – zAssetUUID-CldMstGUID
      • idMapping-localIdentifier – indicates a unique identifier that can be matched up with the unique identifier located in Photos.sqlite – zAssetUUID-CldMstGUID
      • cloudCache-relatedIdentifier – indicates a unique identifier that can be matched up with the unique identifier located in Photos.sqlite – zAssetUUID-CldMstGUID
      • cloudCache-secondaryIdentifier – indicates a unique identifier that can be matched up with the unique identifier located in Photos.sqlite – zGenAlbGUID
  • During the last portion of Figure #33, I review some of the data in store.cloudphotodb that you may want to review if an asset was deleted. You will notice the serialized record is missing for those assets that have been deleted.
Figure #33

Asset deleted from Photo Library > Recently Deleted and the data left over in store.cloudphotodb

In Figure #34 we will see an example of an asset being deleted from the Scooters iPhone X, then review the data that remains in the store.cloudphotodb. Using the unique identifiers from Photos.sqlite we can match them up with the data from store.cloudphotodb to find the timestamp the asset was deleted.

Figure #34

Assets uploaded to web iCloud Photos; assets synced with device & store.cloudphotodb data review

In Figure #35 we will see an example of assets being uploaded to web iCloud Photos then being synced with Scooters iPhone X, then we can review the data stored in Photos.sqlite and store.cloudphotodb that should indicate how the asset made it to the device.

  • Five (5) JPG assets were previously created using a desktop camera, then saved to external media. Those assets were copied and moved from several different storage devices since their creation. On 4/16/2022 those five assets were uploaded to iCloud Photos for Scooters Apple ID
  • The assets are viewable from both iCloud Photos and the device
  • When Photos.sqlite data is reloaded we can see the assets at the top of the query output. This is due to their created date being the eldest listed in Photos.sqlite
  • Take note of the unique identifiers listed for the assets. We can use these to find the corresponding data in the store.cloudphotodb
    • Notice the zAsset-Cloud_Asset_GUID matches the zAsset-Filename for these assets
  • These assets have a zAsset-Saved Asset Type of CPLAssets-6
  • As expected, there is difference between the original file name and the zAsset-Filename for the assets
  • Notice a few things about the timestamps:
    • zAsset-Date Created – indicates the assets were created on 2021-09-22 at different times
    • zAddAssetAttr-EXIF Timestamp String – doesn’t exist
    • zAsset-Add Date – indicates all assets were added at 2022-4-16 15:56:25, if you go back to the start of Figure #34, notice this time matches the date and time on the local machine I was using when these assets were uploaded to iCloud Photos
    • Review of iCloudPhotosWebUploaded4-16-22_1.jpg indicates a last modified date of 2021-09-22 at 1:59:35 PM.
    • zCldMast-Creation Date – indicates the same date as zAsset-Date Created, this is the first time these assets / files have ever been uploaded to an iCloud Photos account. They were not created in iCloud Photos, but this timestamp indicates it was carried over from the last modified date.
    • zIntResou-Cloud Last Prefetch Date occurred before the zIntResou-Last OnDemand Download Date. The download timestamps are out of order based on the file name, this is due to the downloadQueue-position
  • zAsset-Cloud Placeholder Kind decoding was updated after these recent tests because I determined a value of 5 indicates an asset was synced via iCloud Photos to the device.
  • zCldMast-Full Size JPEG Source decoding was updated after these recent tests because I determined the value of 0 indicates the asset was a Cloud Master Asset which originated from iCloud Photos
  • At 5:49 in Figure #35, you will observe how I was able to use those unique identifiers to determine how the assets were synced between iCloud Photos and the device

Store.cloudphotodb downloadQueue table added

During testing, additional data was populated in the downloadQueue table. Limited testing was performed, but again I believe this data was too valuable not to mention. Additional testing and validation are required. Additional note about downloadQueue table data, is that during testing, this data lasted only a few hours. The data was purged the next day from the table. The following are notable data sources from the downloadQueue table.   

  • downloadQueue-itemIdentifier – indicates a unique identifier for the downloaded asset which can be matched up with the value listed in zCldMast-Cloud_Master_GUID and idMapping-cloudIdentifier
  • downloadQueue-fingerPrint – indicates a unique identifier for the downloaded asset which can be matched up with the value listed in zIntResou-Fingerprint
  • downloadQueue-resourceType – the only value populated during testing at this point was five (5), which I believe indicates a resource type of iCloud Photos, indicating this is where the asset was synced from
  • downloadQueue-status – the only value populated during testing at this point was two (2), which I believe indicates the status of asset was synced with the device
  • downloadQueue-position and downloadQueue-taskIdentifier – contains a value, which indicates the asset download position.
    • NOTE: This data might be very useful indicating why some assets might be in a database might be out of order according to primary keys/row IDs, filenames, and/or timestamps. In this case if you check Figure #34 at 4:19 you will notice the zAsset-Add Date timestamps are not in sequential order
Figure #35

Reviewing PhotoCloudSharingData albums and metadata prior to creating new shared albums

In Figure #36 we will review data which highlights file system locations that contains different files that have notable data for Generic Albums and previously existing Share Albums. You may notice several albums and folders that do not contain assets. These albums and folders were used in previous testing prior to Scooters iPhone X was factory reset. Not exactly sure why or how these items survived the factory reset but it’s something you may encounter during an analysis, so it was left in the video examples.

  • \private\var\mobile\Media\PhotoData\AlbumsMetadata\
    • <album UUID>.albummetadata – files contain a bplist containing metadata for the album
    • <folder UUID>.foldermetadata – files contain a bplist containing metadata for the folder
      • The following are a list of notable keys in the plist:
        • importedByBundleIdentifier
        • assetUUIDs
        • cloudGUID
        • title
        • uuid
        • kind
    • As mentioned previously, there are several albums and folders that previously contained assets used to determine the values for ZPERSON and ZFACEDETECTION tables
  • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\
    • <personID> folder – in this example the personID is (1926678879) which is also located in the personID file stored in the root of the above location and contains this personID in HEX
    • cloudSharedEmails.plist – contains emails of the Apple IDs related to the shared albums
    • cloudSharedPersonInfos.plist – contains person information for the Apple IDs related to the shared albums
  • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\<personID>\
    • Within the personID folder you will find additional folders that have UUID names for the shared folders, these UUIDs can be used to find additional data for the albums within Photos.sqlite and store.cloudphotodb
  • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\<personID>\<zGenAlbum-Cloud_GUID>\
    • <*Cloud> folder – which contains the assets that are assigned to the album
    • DCIM_CLOUD.plist – contains the following keys:
      • DCIMLastDirectoryNumber – which will match the last <*Cloud> folder used for that album
      • DCIMLastFileNumber – is the last asset file name / file number for this album. Example: IMG_0013
Figure #36

Creating and sharing new Shared Albums

In Figure #37, I used a combination of assets to create new shared albums, then shared those albums and assets with Bandit (iPhone 6S Plus) and Dexter (iPhone 7) to provide some examples of the data you may encounter when investigating shared albums and assets.

  • A shared album is created and titled: ShareAlbum #1created4-17-22 from multiple assets
    • The album is shared with Dexter and Bandit, notice the emails associated with DexterandBandit that are saved in contacts
    • Three (3) assets were added to the shared album and a Comment was made: Assets from multiple sources including old Snapchat assets Dexter will accept the shared invitation but bandit will deny the invite  
  • The following are some columns of data that may have notable items:
    • zGenAlbum-Album Kind – indicates if the album is a located on the local device/iCloud Photos or if the album is a Shared Album
    • zGenAlbum-title – indicates the title of the album
    • zGenAlbum-Creation Date – indicates the created timestamp for the album
    • zGenAlbum-Cloud Creation Date – indicates a timestamp if the album is created in the cloud share
    • zGenAlbum-Start Date – is based on the asset in the album with the earliest creation date
    • zGenAlbum-End Date – is based on the asset in the album with the latest creation date
    • zGenAlbum-Cloud Subscription Date – indicates the timestamp the shared album is subscribed to / the invitation was accepted
    • zGenAlbum-Cloud Metadata – contains a plist which contains metadata for the album
    • Album Asset Counts – indicates the number of assets associated with the album
    • Different album UUIDs – knowing the different UUIDs associated with the album will allow you to find additional data about the albums that is stored in other locations and files (plists)
    •  zGenAlbum-is Owned – will indicate if the album is owned by the Apple ID logged into the device being analyzed
    • zGenAlbum-Cloud Person ID – indicates the person ID which can also be found at:
    • zGenAlbum-Cloud Owner Hashed Person ID – will also allow you to associate data stored in other locations and files back to a person
    • zGenAlbum-Cloud Owner Mail Key – will indicate the email address associated with the owner of the album and can be found in the following plist:
      • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\
        • cloudSharedEmails.plist
    • zGenAlbum-Cloud Contribution Date – indicates the most recent timestamp the analyzed device Apple ID account holder has contributed an asset to the album
    • zGenAlbum-Cloud Last Interesting Change Date – indicates the most recent timestamp someone other than the analyzed device Apple ID account holder has contributed an asset to the album. This could be updated or changed due to other items not tested, additional testing would be required
  • The following are some columns of data that you might want to analyze to determine who was invited to participate with the shared album and if they accepted the invitation:
    • zCldShareAlbumInvRec-Is My Invitation to Shared Album – indicates if the invitation was sent to the Apple ID of the analyzed device
    • zCldShareAlbumInvRec-Invitation State – indicates if the invitation is pending, accepted, or declined. Decoded values include:
      • Shared Album Invitation Pending-1
      • Shared Album Invitation Accepted-2
      • Shared Album Invitation Declined-3
      • Additional values were observed during testing, but I have yet to confidently decoded them
    • zCldShareAlbumInvRec-Subscription Date – will indicate a timestamp when the invitee accepted the invitation.
    • Invitee Name – First, Last, Full Name
    • zAsset-Visibility State – indicates if the asset is visible or not on the device via the Photo Library. In this example, the assets are Photo Cloud Sharing Data, and these assets must be viewed via the shard albums area of the Photos Application. They are not visible in the Photo Library
    • zAddAssetAttr-Imported by – indicates the asset was imported via the Cloud-other-0
    • At 7:06 in Figure #37, notice we can use the zAsset-Filename (IMG_0003.MP4) and zAddAssetAttr-Org Filename (771F16E9-1BF7-4437-BBC4-30E9D359B857.MP4) to locate the original asset (IMG_0120.MP4) that was used in the cloud sharing album
Using file names to back track to original file name #2
  • zIntResou-Cloud Local State – is decoded incorrectly in the video, Cloud Int Resource Not Avil Local-0  should state Int Resource Not Available in Cloud-0 indicates that particular asset is not available in iCloud Photos, there might be a matching or duplicate that is stored on the device and in iCloud Photos, but the assets that are accessible via the shared album are stored on the owner’s device or iCloud Photos account
    • zCldFeedEnt-Entry Date – indicates the timestamp when the comment or like was made for the asset
    • zCldFeedEnt-Album-GUID – is a unique identifier that matches zGenAlbum-zCloudGUID
    • zCldFeedEnt-Entry Type Number – indicates the type of entry, in this example, it is indicating the asset Is My Shared Asset-1 meaning the asset in the shared album belongs to the Apple ID associated with the analyzed device
  • At 9:30 in Figure #37, I use the file names to locate the assets listed in Photos.sqlite within the device file system
  • At 14:05 in Figure #37, I used the unique identifiers located in Photos.sqlite and store.cloudphotodb to determine if an asset was synced from the device to iCloud Photos
    • cloudCache-identifier – in this example matches the Photos.sqlite – zAsset-UUID for the original asset that was added to the shared album which resulted in the creation of the Photo Cloud Shared Data asset
      • cloudCache-identifier is listed as:
      • D8ABFE8-5D9B-483F-B83F-B647-E548D478EA19-IN-877CFDCF-1218-4142-B525-E75145601F08
        • cloudCache-identifier breakdown: <zAsset-UUID-IN-zGenAlbum-UUID>
        • Which indicates the zAsset-UUID asset is being stored in the zGenAlbum-UUID album. In plain language this means:
          • IMG_0120.MP4 is in the Snapchat Album
      • NOTE: We can also review the cloudCache-serializedRecord plist to learn additional metadata about the asset
  • \private\var\mobile\Media\PhotoData\PhotoCloudSharingData\<personID>\<zGenAlbum-Cloud_GUID>
    • Info.plist indicates the Shared Album owners’ email and identifiers
    • DCIM_CLOUD.plist indicates the number of assets in the shared album
  • \private\var\mobile\Meida\PhotoData\PhotoCloudSharingData\<personID>\<zGenAlbum-Cloud_GUID>\<*CLOUD>\
    • When reviewing the assets located in the shared album and the files being stored on the physical device, we can see three (3) file names:
      • IMG_0001.JPG – in the shared album the asset is a JPG
      • IMG_0002.poster.JPG – in the shared album the asset is a MP4
      • IMG_0003.poster.JPG – in the shared album the asset is a MP4
        • The screen mirroring tool that I was using would not allow videos to play on the device while the screen was mirrored.
  • \private\var\mobile\Media\PhotoData\Thumbnails\V2\PhotoData\PhotoCloudSharingData\<personID>\
    • When reviewing the assets located in the thumbnail location for the shared album there are three (3) folders indicating the file names and extension for the files being stored in the album:
      • IMG_0001.JPG – in the shared album the asset is a JPG
      • IMG_0002.MP4 – in the shared album the asset is a MP4
      • IMG_0003.MP4 – in the shared album the asset is a MP4
  • PhotoCloudSharingData will not have data from the Photos.sqlite ZCLOUDMASTER table which can be seen in both Figure #37 and Figure #38 
Figure #37

Determining if Photo Cloud Sharing Data assets and Albums are stored on devices or in the Cloud?

In Figure #38, I isolated only those assets using a WHERE statement that have a zAsset-Saved Asset Type number four (4), which indicates the asset is a Photo Cloud Sharing Data. Before we dive into the Photo Cloud Sharing Data assets that are being shared, we will review some of the Photos.sqlite data.

  • Notice that all the assets are marked as Not-Visible-Photo_Library-2, indicating these assets are not visible in the Photo Library.
    • This might be a little confusing at times. If an asset is shared from a user’s Photo Library additional files are created and placed into a PhotoCloudSharingData storage location that resemble (duplicates) of the original assets on the sharing owner’s device or iCloud photos, we will see an example of that later
  • Again, notice there isn’t any data from the cloud master table
  • zAsset-Cloud is My Asset – indicates if the cloud asset belongs to the Apple ID logged into the device
  • zAsset-Cloud Asset is Deletable – indicates if the Apple ID signed into the device is authorized to delete the asset. Here are a few examples and what to look for on the device to determine if the user could delete the cloud asset
  • Different Photo Cloud Sharing Data / Shared Album assets can have the same file name, but they are being stored in different file paths. These different files paths are different albums, as discussed in previous sections and in Figure #36
  • zCldSharedComment-Comment Text – will contain the text if a comment was made on the shared asset. In this example, a comment Chance ok 4/18/2022, was made on the asset. When we view the asset on Scooters iPhone X, which is not the owner of the asset, we can swipe up on the asset to view the comment. In this video the screen mirror would not display the text, but it’s there
  • We can also see that the asset is liked, by the filled in thumbs up in the lower right corner of the screen
  • After a review of both, the device Photo Library and iCloud Photos, we can see that the Photo Cloud Sharing Data assets and Shared albums are not present in either location
    • Untested and needs validation, but keep this in mind if analyzing iCloud warrant return data, Photo Cloud Sharing Data assets and Shared Albums might not be present in the return data
Figure #38

Analysis of Photos.sqlite zAddAssetAttr-EXIF Timestamp String after an adjustment

During testing, I discovered something I have relied upon at face value for many years as not true. In the past, I have taken the EXIF timestamp string as the date and time when a media file was captured. Majority of the time, with very little validation, I was able conclude the EXIF timestamp string as the capture date and time. Recently, I have discovered there are instances when a timestamp might be labeled as an EXIF timestamp string, and it was not the captured date and time. I believe this could be a bias that I have built up over the years of very quickly reviewing EXIF timestamp strings, then reviewing camera make and model, then determining yep good to go…

In Figure #39, we will look at an asset you might encounter when someone has adjusted the EXIF timestamp string, then shared that asset with you. Reminder this is an analysis of an iPhone X with iOS 14.7. If you find any of these indicators, you might want to take some extra time to perform additional validation.

Please take the time to review Ian Whiffin’s research on iOS Media Adjustments found here:

https://www.doubleblak.com/blogPosts.php?id=23

  • Notice, we are looking at Dexter’s iPhone 7, which is connected to my local Wi-Fi network. It indicates the date and time as Friday, April 29, 2022
    • I have IMG_0005 pulled up on the device and have swiped up to access the metadata for the asset. I then clicked adjust and changed the EXIF timestamp string:
      • Original timestamp: Tuesday, December 14, 2021, at 7:28:19 AM (UTC-6)
      • Adjusted timestamp: Friday, January 14, 2022, at 10:30:00 PM (UTC-6)
        • Notice the message at the bottom of the screen stating: The original photo will be adjusted by 1 month, 15 hours, 1 minute, 40 seconds
          • I will be conducting additional research and testing to determine if this message is stored on the iOS 15 device
    • The adjustments are saved, and the Messaging application is brought into focus
    • The send button in the message’s application was pressed at 7:33 PM (UTC-7), but you might notice the device is not connected to Wi-Fi when the button is pressed
    • At 7:34 PM (UTC-7), once connected to Wi-Fi Scooters iPhone X receives the notification that a message was received
    • The received asset is saved to Scooters iPhone X Photo Library, which is viewed within the Recents
    • Notice when swiping up on the recents list, it will present the uploading indicator that the assets are being synced with iCloud Photos
    • The time listed, is the adjusted timestamp: Friday, January 14, 2022, at 10:30:00 PM (UTC-6)
    • Photos.sqlite data is reviewed and here are some notable items:
      • The time zone information (UTC-6) matches the original asset on Dexter’s iPhone 7
      • zAsset-Date Created – 2022-01-15 04:30:00 (UTC-0) or 2022-01-14 20:30:00 (UTC-6)
        • This is the adjusted timestamp
      • zAddAssetAttr-Org Filename – IMG_0005.HEIC, which is the file name observed on Dexter’s iPhone
      • zAsset-Filename – IMG_0172.HEIC, is the new asset file name that will be observed from the *\DCIM\100APPLE\ file location
        • As stated in previous write-ups, the file name and original file name relationship can be analyzed to determine if the original asset came from another device. During testing, I was able to use the original file name list as a keyword search list to find original evidence files. Keep this in mind when you are analyzing a victim and suspects device or multiple suspect devices and want to articulate a relationship between media files
      •  zAsset-Add Date – indicates when the asset was added to the Photo Library
      • zAddAssetAttr-Creator Bundle ID and zAddAssetAttr-Imported by Display Name – indicate the asset was created by com.apple.MobileSMS and the imported display name is Messages. The asset was received by and saved from the Messages application
      • CMzCldMastMedData-Data – contains a plist with metadata. When reviewing the timestamps, they are all indicating the adjusted timestamp. I found something interesting and not sure why the timestamp is being recorded in this manner
        • HostComputer – iPhone 7 so we know this didn’t originate from Scooters iPhone X
        • Software – Dexter’s iPhone 7 is running iOS 15.1
        • DateTime key indicates 2022:01:14 22:30:00
          • Why is it? This is local device time
          • The created date is in UTC-6, but the device is now in UTC-8, thus the device local time for 20:30:00 PM, would be 22:30:00 PM
          • This is an example of mobile device UTC analysis headache
            • Perform analysis in UTC!!!
        • EXIF – DateTimeOriginal – 2022:01:14 22:30:00
        • DateTimeDigitized – 2022:01:14 22:30:00
          • Normally, a digitized timestamp would indicate when the media file was captured, but not in this instance
        • The location coordinates have been blocked out, but the location was accurate for where the original asset was captured with Dexter’s iPhone 7
  • To review some previously covered material, we will analyze when this saved asset was synced from the device to Scooters iCloud Photos:
    • We will use the store.cloudphotodb database and a SQLite query to decode the data
    • Use the zAsset-UUID and zAsset-Cloud_Asset_GUID to locate your asset in the store.cloudphotodb
    • idMapping-addDirection – indicates Device2iCloudPhotos-1 which means the asset was synced from the device to iCloud Photos
    • During this portion of the video, I was attempting to locate the original timestamp that was adjusted, but I was not able to find it
Figure #39

Analysis of another asset with adjusted EXIF and Synced with iCloud status

In Figure #40, I conducted another test, where I adjusted the timestamp of an asset captured with Dexter’s iPhone 7.

  • Original Asset:
    • File Name: IMG_0055.MOV
    • Capture Timestamp: Sunday, April 24, 2022, at 9:33 PM (UTC-7)
    • Location: T-Mobile Arena, Las Vegas, Nevada
  • Adjusted asset on Scooters iPhone X:
    • File Name: IMG_0173.MOV
    • Adjusted Timestamp: Thursday, April 28, 2022, at 1:00 PM (UTC-7)
    • Location: T-Mobile Arena, Las Vegas, Nevada
  • Asset was shared with Scooter’s iPhone X, via Messages application
  • Dexter’s iPhone was not connected to a network, once the device was connected to a network Dexter’s device sent the asset to Scooters device
  • On 4/29/2022 at 8:29 PM (UTC-7), the asset was received and saved to the Photo Library
  • At 8:29 PM, we can also see a sync with iCloud Photos is taking place on Scooters iPhone
  • The device data within ArtEx was remapped and using the new ArtEx feature, the processing bar is minimized to the lower left corner of the screen

While the device data is being remapped, we’ll analyze Photos.sqlite data for IMG_0173.MOV

  • zAsset-Filename and zAddAssetAttr-Org Filename match (IMG_0173.MOV)
  • zAsset-Saved Asset Type indicates Saved-via-other-source-0
    • NOTE: this asset has a MOV extension, was saved via the native Messaging application, and has an adjusted timestamp
  • zAsset-Date Created is 2022-04-28 8:00 PM (UTC) or 1:00 PM (UTC-7)
  • The time zone offset data will provide insight into what time zone the device was set
  • zAddAssetAttr-EXIF Timestamp String is 2022-04-28 1:00:00 PM (UTC-7)
  • zCldMast columns are populated indicating the asset is available in iCloud Photos
  • zCldMast-Import Date is the timestamp the asset was synced with iCloud Photos
  • zIntResou-Last OnDemand Download Date timestamp indicates 2022-04-30 03:29:36 (UTC), this appears related to MOV files that are saved
  • zAddAssetAttr-Created Bundle ID and zAddAssetAttr-Imported by Display Name indicates com.apple.MobileSMS and Messages, this is indication the asset was imported to the Photo Library via the Messages application,
  • Normally in this instance, we would see a zAddAssetAttr-Imported by value of Native-App(8 or 9), but in this instance was see its recorded as Cloud-Other-0
    • Based on testing, this is the value you might see when a MOV is saved from the Native Messages Application to the Photo Library. Based on testing, this doesn’t have anything to do with the timestamp adjustment
  • zAddAssetAttr-Reverse Location Data is present and contains the data for the T-Mobile Arena in Las Vegas, Nevada where the asset was captured
  • zAsset-UUID contains the UUID for the asset, as stated previously, we will use this UUID to find how the asset was synced, Device to iCloud Photos or iCloud Photos to the Device, in the store.cloudphotodb
  • When reviewing the data in store.cloudphotodb, I attempted to find any evidence of the original timestamp and failed
Figure #40

Moment and Moment List data with approximate location

There was a question posted via a DFIR listserv about ZMOMENTS Table location data. In Figure #41, Figure #41.1, and Figure #41.2 we will review data related to moments, specifically the location data.

  • One of the main questions related to the moment’s location data was if the zMoment-Approximate Latitude and zMoment-Approximate Longitude data indicate a location the device was physical at?
  • Is it derived from the assets contained in the Moment?
  • Is it derived from the core location services?
  • What’s does the term approximate stand for in this instance?
  • Would the moment contain approximate location data, if the assets were captured with applications that remove or restrict EXIF data?
  • Before we review the data and attempt to answer some of those questions, let’s do a short review of the data being parsed and decoded by my Photos.sqlite query in Figure #41:
  • To get all the data for the Moment you need to ensure that you are getting the data from both the ZMOMENT and ZMOMENTLIST tables
  • The Moments can be broken down into three major types
    • Year Moment
    • Year and Month Moment
    • Individual Moment
      • These individual moments can be broken down into sub-types, here are some that I have encountered during testing
        • Today
        • Yesterday
        • Dates
        • Places
      • NOTE: When searching and viewing Moments, via the device, you will notice Categories that can be selected and used to easily find related Moments for the categories
      • NOTE: Not all Moments will have Titles
      • NOTE: As I have covered previously, a Moments start and the end date timestamp correlate to the assets created date
Figure #41

In Figure #41.1, the following devices were used to test the Moments location data and was found to behave similarly:

  • Dexter Scooter iPhone 7 iOS 15.1 (Wi-Fi only, was not connected to network during this test)
  • Scooter Scott iPhone X iOS 14.7 (data plan with SIM)
  • On 5/7/2022 during a trip to Ikea, several photos and videos were captured with both devices. These assets were captured with both the camera application and the messages camera application
  • Assets were captured outside of the store prior to entering and then while I walked through the store. Doing so resulted in a Moment being created listed as TODAY
    • You will notice that this TODAY Moment will be continuous and will use the current date as the TODAY moment. After the day is over you can use the date to find the moment
  • I used the search term 5-7 to find any moments that might have been created and found one listed as TODAY
  • As discussed earlier, the TODAY Moment will have parent Moments, a YEAR Moment (2022) and a YEAR & MONTH Moment (202205)
  • TODAY Moment (05/07/2022)
    • There are two moments for TODAY 5/7/2022
      • One has 28 assets assigned to it (24 assets with locations)
      • The other has 2 assets assigned to it (1 asset with location)
    • IMG_0201.HEIC
      • Captured on 5/07/22 at 13:34:00 PM (UTC-7) with the device camera application
      • zAsset-Modification Date indicates the last modification was at 05/08/2022 00:15:27 UTC. This occurred during the device data acquisition. When we review the acquisition image_info.txt we can see the modification timestamp falls between the acquisition start and end timestamp
      • zAddAssetAttr-Imported by indicates the asset was imported via the Native-Back-Camera-1
      • zAsset-Latitude 36.06666667
      • zAsset-Longitude -115.28248667
      • zAddAssetAttr-Horizontal Accuracy 300.131358660657 meters, I wouldn’t consider this to be an accurate location, lets check the CMzCldMastMedData-Data to see if the EXIF location data matches
      • The Moment location for this asset is different than the location data previously viewed, and the accuracy are extremely different
        • zMoment-Approx Latitude 36.068609501052627
        • zMoment-Approx Longitude -115.28121903578946
        • zMoment-GPS Horizontal Accuracy 65 meters
      • NOTE: This is the location coordinates for all assets associated with the Z_PK 36 ZMOMENT
  • The above was a detailed review of just one asset in the moment and the location data. This Moment (Z_PK 36) has 27 other assets which are listed below. In Figure #41.1, I will review each asset, their location data stored in EXIF via Photos.sqlite & the location data being stored in the Moment
  • At 05:25 in Figure #41.1, I have highlighted all the assets within the Z_PK 36 ZMOMENT. You will notice, if the asset has EXIF location data, it will be different than the location being stored in the Moment. All the assets, in this example, will have the same Moment Location. After the assets and the location data are reviewed, you will see these locations plotted on a map and hopefully we can determine what is going on
    • IMG_0202.HEIC – Captured with camera app
    • IMG_0203.PNG – Captured with Native app screenshot
    • IMG_0204.HEIC – Captured with camera app
    • IMG_0205.HEIC – Captured with camera app
    • IMG_0206.HEIC – has Org File Name – Captured with messages camera app
    • IMG_0207.HEIC – has Org File Name – Captured with messages camera app
    • IMG_0208.HEIC – has Org File Name – Captured with messages camera app
    • IMG_0209.HEIC – IMG_0227.HEIC – Captured with camera app
    • IMG_0228.MOV – Captured with camera app
  • At 08:41 in Figure #41.1, another example is shown of what some Moments and the associated data might look like
  • You will notice I’ve highlighted several different assets to illustrate, when viewing moments, these different assets will have the same Moment approximate location, just like the previous example
  • In this example, there are several assets that were imported into the Photo Library from several different applications
Figure #41.1

Figure #41.2 is a video of the next day, 5/8/2022, I reviewed the Moment data on Scooters iPhone X and in the Photos.sqlite. Notice the Moment has changed from TODAY to Yesterday, now has a title, and zMoment-Localized Location Names data. This is due to the data being analyzed by the iOS device. The location coordinates stored in the ZMOMENT table, has not changed

  • I plotted all the assets that contained location coordinates into Google Maps. Notice, there is some distance separation between the two groupings of asset locations
  • I then plotted the location coordinates found in zMoment-Approx Latitude and zMoment-Approx Longitude
  • We can clearly see that none of the media assets that are apart of the LAS VEGAS Moment, which occurred on 5/7/2022 had the same location coordinates as the Moment location coordinates. There are some that were very close, but not exactly matching
  • Based on this testing and information collected from other analysts, I believe the location being populated in the zMoment-Approx Latitude and zMoment-Approx Longitude is an approximate location for that Moment. I believe this location is being derived from the assets being assigned to that Moment. It does not mean the device was at that specific location, only that it may have been in the area. It’s recommended to use other location data (Cache.sqlite ZRTCLLOCATIONMO table) or artifacts to corroborate if the device was in that area at a specific time and date
  • NOTE: A review of the Cache.sqlite ZRTCLLOCATIONMO table was completed and there was not a specific location recorded that matched the Moment start or end date, thus reaffirming my belief that the moment approximate location is derived from the assets in the moment   
    • If this is something important for your analysis, I have included a link here, to a SQLite query that will parse some of the Moment data from Photos.sqlite
Figure #41.2

Analysis of zAddAssetAttr-EXIF Timestamp String after saving Snap from Snapchat

Coming Soon

Shared assets via email received on MacBook & saved to photos app synced to device

Coming Soon

Conclusion

Within the last two years, I have been taking the extra time needed to validate some of these artifacts as best as I could. This write-up was one of those efforts and even though it has taken me more than two-years to get this data together and written up, I believe it was well worth it. Even though this was not a fully technical document for every file created or modified as the result of items being captured with or stored in the Photos Application, I hope this document will assist just one person who is analyzing iOS media files to find the truth.

If you have any questions or would like to see something tested, please don’t hesitate to contact me. Additionally, if while reviewing this write-up you have validated some of the same items or if you believe I have decoded something incorrectly, I would be very thankful if you would contact me, and we could compare notes.

Future work

I will be continuing my research and updating this document to include some iOS 15 data soon. The work will never be done with this massive database. I would like to find some time to use a file system monitor and reconduct some of these tests.

Resources

https://developer.apple.com/documentation/photokit

plistiOS 14.7 and iOS 15.3.1 LocationArtifact
Accounts3.sqlite\private\var\mobile\Library\Accounts\VerifiedBackup\lots of main account data included my real phone number
cloudphotos-1.0.plist\private\var\mobile\Media\PhotoData\CPL\Cloud Photo Library sync settings
cloudServiceEnableLog.plist\private\var\mobile\Media\PhotoData\private\com.apple.assetsd\Settings > Apple ID > iCloud > (if iCloud Photos is On or Off)
cloudServiceEnableLog.plist\private\var\mobile\Media\PhotoData\private\com.apple.accountsd\Settings > Apple ID > iCloud > Photos > (is iCloud Photos and Shared Albums On or Off)
cloudSharedEmails.plist\private\var\mobile\Media\PhotoData\PhotoCloudSharingData\Shared Assets and Albums owner email list
cloudSharedEmails.plist\private\var\mobile\Media\PhotoData\PhotoCloudSharingData\contains emails of the Apple IDs related to the shared albums
cloudSharedPersonInfos.plist\private\var\mobile\Media\PhotoData\PhotoCloudSharingData\contains person information for the Apple IDs related to the shared albums
com.apple.assistant.backedup.plist\private\var\mobile\Library\Preferences\Cloud Sync Enabled Modification Date
com.apple.camera.plist\private\var\mobile\Library\Preferences\Camera Application Settings (Flash and Self-Timer)
com.apple.cloudphotod.plist\private\var\mobile\Library\Preferences\CPLLastCPLUpgrade and CPLLastOSUpgrade
com.apple.messages.nicknames.plist\private\var\mobile\Library\Preferences\Settings > Messages > Share Name and Photo
com.apple.migration.plist\private\var\mobile\Library\Preferences\Factory reset and iOS update versions and timestamps
com.apple.mobile.ldbackup.plist\private\var\mobile\Library\Preferences\is iCloud back up enabled
com.apple.MobileBackup.plist\private\var\mobile\Library\Preferences\AccountEnabledDate
com.apple.purplebuddy.plist\private\var\mobile\Library\Preferences\guessedCountry and SetupLastExit
Composition.plist\private\var\mobile\Library\SMS\Drafts\Draft information for com.apple.MobileSMS
composition.plist\private\var\mobile\Library\SMS\Drafts\base 64 encoded bplist that contains the message content
DCIM_APPLE.plist\private\var\mobile\Media\PhotoData\MISC\Photo Library last folder last file name (\private\var\mobile\Media\DCIM)
DownloadCounts.plist\private\var\mobile\Media\PhotoData\CPL\downloaded key images and video’s counts
initialsync_marker\private\var\mobile\Media\PhotoData\CPL\initial iCloud Photos sync timestamp
MigrationInfo.plist\private\var\installd\Library\MobileInstallation\Factory reset and iOS update versions and timestamps
ModuleConfiguration.plist\private\var\mobile\Library\ControlCenter\Control Center configuration plist has a list of apps on the control center
PLDataMigrationInfo.plist\private\var\mobile\Media\PhotoData\Caches\Factory reset and iOS update versions and timestamps
recipients.plist\private\var\mobile\Library\SMS\Drafts\PendingIf message was sent to a contact or phone number this plist will contain the phone number
recipients.plist\private\var\tmp\com.apple.messages\Drafts\If the draft message was sent to a phone number, the following path with contain the plists
RoleUserMigration.plist\private\var\installd\Library\MobileInstallation\Factory reset and iOS update versions and timestamps
store.cloudphotodb\private\var\mobile\Media\PhotoData\CPL\storage\CPLAsset change and transaction data deleted dates with unique identifiers
syncstatus.plist\private\var\mobile\Media\PhotoData\CPL\iCloud Photos Update and Sync timestamps
plist reference guide

2 thoughts on “Photos.sqlite Query Documentation & Notable Artifacts

  1. Hello Scott! Great article! Question for you. Is there any way you’d be interested in contributing the sample database from each version of iOS to the DFIR Artifact Museum?

    https://github.com/AndrewRathbun/DFIRArtifactMuseum

    Additionally, I’d like to request permission to use your queries, with proper attribution of course, to make Maps for SQLECmd for those respective databases.

    https://github.com/EricZimmerman/SQLECmd/tree/master/SQLMap%2FMaps

    Liked by 2 people

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: