If you don’t have time to research but would like to hear more about it, tune in to my talk at the SANS DFIR Summit on July 16th! If you do, try out this query I used for this blog post to pry around at this one table from this database and let me know how it works on your data via Twitter @bizzybarney.
select loc_records.id, sources.bundle_id as "Bundle ID", sources.group_id as "Group ID", datetime(sources.seconds_from_1970, 'unixepoch') as "Source Time", loc_records.cll_latitude_degrees || ", "|| loc_records.cll_longitude_degrees as "Coordinates", loc_records.clp_name as "Name", loc_records.clp_thoroughfare as "Road", loc_records.clp_subThoroughfare as "Address #", loc_records.clp_locality as "City", loc_records.clp_subLocality as "Sub-locality", loc_records.clp_administrativeArea as "Admin Area", loc_records.clp_subAdministrativeArea as "Sub Admin Area", loc_records.clp_postalCode as "Postal Code", loc_records.clp_ISOcountryCode as "Country Code", loc_records.clp_country as "Counrty", hex (loc_records.clp_location) as "Location BLOB (hex)", loc_records.extraction_os_build as "iOS Build Version", loc_records.category as "Category", loc_records.algorithm as "Algorithm", loc_records.initial_score as "Initial Score", CASE WHEN loc_records.is_sync_eligible = 1 then "Yes" WHEN loc_records.is_sync_eligible = 0 then "No" end as "Sync Eligible"from loc_records left join sources on loc_records.source_id=sources.id
