{"id":101950,"date":"2025-02-21T18:31:05","date_gmt":"2025-02-21T18:31:05","guid":{"rendered":"https:\/\/peraltafinancing.com\/apple-2\/guest-post-by-bizzybarney-a-peek-inside-the-ppsqldatabase-db-personalization-portrait-database-mac4n6-com\/"},"modified":"2025-02-21T18:31:05","modified_gmt":"2025-02-21T18:31:05","slug":"guest-post-by-bizzybarney-a-peek-inside-the-ppsqldatabase-db-personalization-portrait-database-mac4n6-com","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=101950","title":{"rendered":"Guest Post by @bizzybarney! A Peek Inside the PPSQLDatabase.db Personalization Portrait Database \u2014 mac4n6.com"},"content":{"rendered":"


\n
<\/p>\n

\n

If you don\u2019t 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<\/a>.\u00a0<\/p>\n

select<\/code><\/pre>\n
 loc_records.id,<\/code><\/pre>\n
 \tsources.bundle_id as \"Bundle ID\",<\/code><\/pre>\n
 \tsources.group_id as \"Group ID\",<\/code><\/pre>\n
 \tdatetime(sources.seconds_from_1970, 'unixepoch') as \"Source Time\",<\/code><\/pre>\n
 \tloc_records.cll_latitude_degrees || \", \"|| loc_records.cll_longitude_degrees as \"Coordinates\",<\/code><\/pre>\n
 \tloc_records.clp_name as \"Name\",<\/code><\/pre>\n
 \tloc_records.clp_thoroughfare as \"Road\",<\/code><\/pre>\n
 \tloc_records.clp_subThoroughfare as \"Address #\",<\/code><\/pre>\n
 \tloc_records.clp_locality as \"City\",<\/code><\/pre>\n
 \tloc_records.clp_subLocality as \"Sub-locality\",<\/code><\/pre>\n
 \tloc_records.clp_administrativeArea as \"Admin Area\",<\/code><\/pre>\n
 \tloc_records.clp_subAdministrativeArea as \"Sub Admin Area\",<\/code><\/pre>\n
 \tloc_records.clp_postalCode as \"Postal Code\",<\/code><\/pre>\n
 \tloc_records.clp_ISOcountryCode as \"Country Code\",<\/code><\/pre>\n
 \tloc_records.clp_country as \"Counrty\",<\/code><\/pre>\n
 \thex (loc_records.clp_location) as \"Location BLOB (hex)\",<\/code><\/pre>\n
 \tloc_records.extraction_os_build as \"iOS Build Version\",<\/code><\/pre>\n
 \tloc_records.category as \"Category\",<\/code><\/pre>\n
 \tloc_records.algorithm as \"Algorithm\",<\/code><\/pre>\n
 \tloc_records.initial_score as \"Initial Score\",<\/code><\/pre>\n
 \tCASE<\/code><\/pre>\n
 \tWHEN loc_records.is_sync_eligible = 1 then \"Yes\"<\/code><\/pre>\n
\tWHEN loc_records.is_sync_eligible = 0 then \"No\"<\/code><\/pre>\n
\tend as \"Sync Eligible\"<\/code><\/pre>\n
from loc_records\u00a0 <\/code><\/pre>\n
left join sources on loc_records.source_id=sources.id<\/code><\/pre>\n<\/div>\n