def get_cacheRoutesGmap(files_found, report_folder, seeker): data_list = [] for file_found in files_found: file_found = str(file_found) filename = os.path.basename(file_found) noext = os.path.splitext(filename)[0] noext = int(noext) datetime_time = datetime.datetime.fromtimestamp(noext/1000) datetime_time = str(datetime_time) with open(file_found, 'rb') as f: deserialized = plistlib.load(f) length = len(deserialized['$objects']) for x in range(length): try: lat = deserialized['$objects'][x]['_coordinateLat'] lon = deserialized['$objects'][x]['_coordinateLong'] #lat longs data_list.append((datetime_time, lat, lon, file_found)) except: pass if len(data_list) > 0: description = 'Google Maps Cache Routes' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'Google Maps Cache Routes', description) report.add_script() data_headers = ('Timestamp','Latitude','Longitude','Source File') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Google Maps Cache Routes' tsv(report_folder, data_headers, data_list, tsvname) kmlactivity = 'Google Maps Cache Routes' kmlgen(report_folder, kmlactivity, data_list, data_headers)
def get_locationDwifilocB(files_found, report_folder, seeker): file_found = str(files_found[0]) #os.chmod(file_found, 0o0777) db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_wifilocation.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", LATITUDE || ", " || LONGITUDE AS "COORDINATES", MAC AS "MAC", CHANNEL AS "CHANNEL", INFOMASK AS "INFOMASK", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE", SCORE AS "SCORE", REACH AS "REACH", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE" FROM WIFILOCATION """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = '' report = ArtifactHtmlReport('LocationD WiFi Location') report.start_artifact_report(report_folder, 'WiFi Location', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'MAC', 'Channel', 'Infomask', 'Speed', 'Course', 'Confidence', 'Score', 'Reach', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD WiFi Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD WiFi Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD WiFi Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD WiFi Location') db.close() return
def get_locationDparkedhistorical(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("11"): logfunc("Unsupported version for RoutineD Parked Historical " + iOSversion) return () file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_vehicle_parked.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(ZRTVEHICLEEVENTHISTORYMO.ZDATE + 978307200, 'UNIXEPOCH') AS "DATE", DATETIME(ZRTVEHICLEEVENTHISTORYMO.ZLOCDATE + 978307200, 'UNIXEPOCH') AS "LOCATION DATE", ZLOCLATITUDE || ", " || ZLOCLONGITUDE AS "COORDINATES", ZLOCUNCERTAINTY AS "LOCATION UNCERTAINTY", ZIDENTIFIER AS "IDENTIFIER", ZLOCLATITUDE AS "LATITUDE", ZLOCLONGITUDE AS "LONGITUDE", ZRTVEHICLEEVENTHISTORYMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTVEHICLEEVENTHISTORYMO """) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])) description = '' report = ArtifactHtmlReport('RoutineD Parked Vehicle Historical') report.start_artifact_report(report_folder, 'Parked Vehicle Historical', description) report.add_script() data_headers = ('Timestamp', 'Location Date', 'Coordinates', 'Location Uncertainty', 'Identifier', 'Latitude', 'Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Parked Vehicle Historical' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Parked Vehicle Historical' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Parked Vehicle Historical' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available in Routine Parked Vehicle Historical') db.close() return
def get_airGuard(files_found, report_folder, seeker, wrap_text): file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT device.lastSeen AS "Last Time Device Seen", beacon.receivedAt AS "Time (Local)", beacon.deviceAddress AS "Device MAC Address", beacon.longitude AS "Latitude", beacon.latitude as "Longitude", beacon.rssi AS "Signal Strength (RSSI)", device.firstDiscovery AS "First Time Device Seen", device.lastNotificationSent as "Last Time User Notified" FROM beacon LEFT JOIN device on device.address=beacon.deviceAddress ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('AirGuard AirTag Tracker') report.start_artifact_report(report_folder, 'AirGuard AirTag Tracker') report.add_script() data_headers = ('Last Time Device Seen', 'Time (Local)', 'Device MAC Address', 'Latitude', 'Longitude', 'Signal Strength (RSSI)', 'First Time Device Seen', 'Last Time User Notified') data_headers_kml = ('Timestamp', 'Time (Local)', 'Device MAC Address', 'Latitude', 'Longitude', 'Signal Strength (RSSI)', 'First Time Device Seen', 'Last Time User Notified') data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'AirGuard AirTag Tracker' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'AirGuard AirTag Tracker' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'AirGuard AirTag Tracker' kmlgen(report_folder, kmlactivity, data_list, data_headers_kml) else: logfunc('No AirGuard AirTag Tracker data available') db.close()
def get_tileAppDb(files_found, report_folder, seeker): for file_found in files_found: file_found = str(file_found) if file_found.endswith('tile-TileNetworkDB.sqlite'): break db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(ZTIMESTAMP,'unixepoch','31 years'), ZNAME, datetime(ZACTIVATION_TIMESTAMP,'unixepoch','31 years'), datetime(ZREGISTRATION_TIMESTAMP,'unixepoch','31 years'), ZALTITUDE, ZLATITUDE, ZLONGITUDE, ZID, ZNODE_TYPE, ZSTATUS, ZIS_LOST, datetime(ZLAST_LOST_TILE_COMMUNITY_CONNECTION,'unixepoch','31 years') FROM ZTILENTITY_NODE INNER JOIN ZTILENTITY_TILESTATE ON ZTILENTITY_NODE.ZTILE_STATE = ZTILENTITY_TILESTATE.Z_PK ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11])) description = '' report = ArtifactHtmlReport('Tile App - Tile Information & Geolocation') report.start_artifact_report(report_folder, 'Tile App DB Info & Geolocation', description) report.add_script() data_headers = ('Timestamp','Tile Name','Activation Timestamp','Registration Timestamp','Altitude','Latitude','Longitude','Tile ID','Tile Type','Status','Is Lost?','Last Community Connection' ) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Tile App DB Info Geolocation' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Tile App DB Info Geolocation' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Tile App DB Info Geolocation' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Tile App DB data available') db.close() return
def get_locationDparkedhistorical(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("11"): logfunc("Unsupported version for RoutineD Parked Historical " + iOSversion) return () file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(""" select datetime(zrtvehicleeventhistorymo.zdate + 978307200, 'unixepoch'), datetime(zrtvehicleeventhistorymo.zlocdate + 978307200, 'unixepoch'), zlocuncertainty, zidentifier, zloclatitude, zloclongitude from zrtvehicleeventhistorymo """) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5])) description = '' report = ArtifactHtmlReport('RoutineD Parked Vehicle Historical') report.start_artifact_report(report_folder, 'Parked Vehicle Historical', description) report.add_script() data_headers = ('Timestamp', 'Location Date', 'Location Uncertainty', 'Identifier', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Parked Vehicle Historical' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Parked Vehicle Historical' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Parked Vehicle Historical' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available in Routine Parked Vehicle Historical') db.close() return
def get_weatherAppLocations(files_found, report_folder, seeker): data_list = [] for file_found in files_found: file_found = str(file_found) with open(file_found, "rb") as plist_file: plist_content = plistlib.load(plist_file) for city in plist_content['Cities']: update_time = city['UpateTime'] update_time_formatted = update_time.strftime( '%Y-%m-%d %H:%M:%S') data_list.append((update_time_formatted, 'Added from User', '', city['Lat'], city['Lon'], city['Name'], city['Country'], city['SecondsFromGMT'])) local_weather = plist_content['LocalWeather'] local_update_time = local_weather['UpateTime'] local_update_time_formatted = local_update_time.strftime( '%Y-%m-%d %H:%M:%S') last_location_update = time.strftime( "%Y-%m-%d %H:%M:%S", time.gmtime(plist_content.get('LastLocationUpdateTime'))) data_list.append( (local_update_time_formatted, 'Local', last_location_update, local_weather['Lat'], local_weather['Lon'], local_weather['Name'], local_weather['Country'], local_weather['SecondsFromGMT'])) if len(data_list) > 0: report = ArtifactHtmlReport('Weather App Locations') report.start_artifact_report(report_folder, 'Weather App Locations') report.add_script() data_headers = ("Update Time", "Type", "Last Location Update", "Latitude", "Longitude", "City", "Country", "Seconds from GMT") report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Weather App Locations' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Weather App Locations' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Weather App Locations' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for Weather App Locations')
def get_cmh(files_found, report_folder, seeker, wrap_text): file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(images.datetaken /1000, "unixepoch") as datetaken, datetime(images.date_added, "unixepoch") as dateadded, datetime(images.date_modified, "unixepoch") as datemodified, images.title, images.bucket_display_name, images.latitude, images.longitude, location_view.address_text, location_view.uri, images._data, images.isprivate FROM images left join location_view on location_view._id = images._id ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Samsung CMH') report.start_artifact_report(report_folder, f'Geodata') report.add_script() data_headers = ('Timestamp', 'Date Added', 'Date Modified', 'Title', 'Bucket Name', 'Latitude', 'Longitude', 'Address', 'URI', 'Data Location', 'Is Private') data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Samsung CMH Geodata' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Samsung CMH Geodata' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Samsung CMH Geodata' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc(f'No Samsung_CMH_GeoData available') db.close() return
def get_waze(files_found, report_folder, seeker, wrap_text): file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' select datetime(PLACES.created_time, 'unixepoch'), datetime(RECENTS.access_time, 'unixepoch'), RECENTS.name, PLACES.name as "Address", round(PLACES.latitude*.000001,6), round(PLACES.longitude*.000001,6) from PLACES join RECENTS on PLACES.id = RECENTS.id ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Waze - Recently Searched Locations') report.start_artifact_report(report_folder, 'Waze - Recently Searched Locations') report.add_script() data_headers = ('Created Timestamp','Accessed Timestamp','Location Name','Address','Latitude','Longitude') data_headers_kml = ('Timestamp','Accessed Timestamp','Location Name','Address','Latitude','Longitude') data_list = [] for row in all_rows: data_list.append((row[0],row[1],row[2],row[3],row[4],row[5])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Waze - Recently Searched Locations' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Waze - Recently Searched Locations' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Waze - Recently Searched Locations' kmlgen(report_folder, kmlactivity, data_list, data_headers_kml) else: logfunc('No Waze - Recently Searched Locations data available') db.close()
def get_ooklaSpeedtestData(files_found, report_folder, seeker): for file_found in files_found: file_found = str(file_found) if file_found.endswith('speedtest.sqlite'): break db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(("ZDATE")+strftime('%s', '2001-01-01 00:00:00'), 'unixepoch') as 'Date', "ZEXTERNALIP" as 'External IP Address', "ZINTERNALIP" as 'Internal IP Address', "ZCARRIERNAME" as 'Carrier Name', "ZISP" as 'ISP', "ZWIFISSID" as 'Wifi SSID', "ZWANTYPE" as 'WAN Type', CASE "ZDEVICEMODEL" WHEN "iPad3,1" THEN "iPad 3rd Gen (Wi-Fi Only)" WHEN "iPad3,2" THEN "iPad 3rd Gen (Wi-Fi/Cellular Verizon/GPS)" WHEN "iPad3,3" THEN "iPad 3rd Gen (Wi-Fi/Cellular AT&T/GPS)" WHEN "iPad3,4" THEN "iPad 4th Gen (Wi-Fi Only)" WHEN "iPad3,5" THEN "iPad 4th Gen (Wi-Fi/AT&T/GPS)" WHEN "iPad3,6" THEN "iPad 4th Gen (Wi-Fi/Verizon & Sprint/GPS)" WHEN "iPad6,11" THEN "iPad 9.7 5th Gen (Wi-Fi Only)" WHEN "iPad6,12" THEN "iPad 9.7 5th Gen (Wi-Fi/Cellular)" WHEN "iPad7,5" THEN "iPad 9.7 6th Gen (Wi-Fi Only)" WHEN "iPad7,6" THEN "iPad 9.7 6th Gen (Wi-Fi/Cellular)" WHEN "iPad7,11" THEN "iPad 10.2 7th Gen (Wi-Fi Only)" WHEN "iPad7,12" THEN "iPad 10.2 7th Gen (Wi-Fi/Cellular Global)" WHEN "iPad11,3" THEN "iPad Air 3rd Gen (Wi-Fi Only)" WHEN "iPad11,4" THEN "iPad Air 3rd Gen (Wi-Fi+Cell)" WHEN "iPad2,5" THEN "iPad mini Wi-Fi Only/1st Gen" WHEN "iPad2,6" THEN "iPad mini Wi-Fi/AT&T/GPS - 1st Gen" WHEN "iPad2,7" THEN "iPad mini Wi-Fi/VZ & Sprint/GPS - 1st Gen" WHEN "iPad4,4" THEN "iPad mini 2 (Retina/2nd Gen Wi-Fi Only)" WHEN "iPad4,5" THEN "iPad mini 2 (Retina/2nd Gen Wi-Fi/Cellular)" WHEN "iPad4,7" THEN "iPad mini 3 (Wi-Fi Only)" WHEN "iPad4,8" THEN "iPad mini 3 (Wi-Fi/Cellular)" WHEN "iPad5,1" THEN "iPad mini 4 (Wi-Fi Only)" WHEN "iPad5,2" THEN "iPad mini 4 (Wi-Fi/Cellular)" WHEN "iPad11,1" THEN "iPad mini 5th Gen (Wi-Fi Only)" WHEN "iPad11,2" THEN "iPad mini 5th Gen (Wi-Fi+Cell)" WHEN "iPad6,7" THEN "iPad Pro 12.9 (Wi-Fi Only)" WHEN "iPad6,8" THEN "iPad Pro 12.9 (Wi-Fi/Cellular)" WHEN "iPad6,3" THEN "iPad Pro 9.7 (Wi-Fi Only)" WHEN "iPad6,4" THEN "iPad Pro 9.7 (Wi-Fi/Cellular)" WHEN "iPad7,3" THEN "iPad Pro 10.5 (Wi-Fi Only)" WHEN "iPad7,4" THEN "iPad Pro 10.5 (Wi-Fi/Cellular)" WHEN "iPad7,1" THEN "iPad Pro 12.9 (Wi-Fi Only - 2nd Gen)" WHEN "iPad7,2" THEN "iPad Pro 12.9 (Wi-Fi/Cell - 2nd Gen)" WHEN "iPad8,9" THEN "iPad Pro 11 (Wi-Fi Only - 2nd Gen)" WHEN "iPad8,10" THEN "iPad Pro 11 (Wi-Fi/Cell - 2nd Gen)" WHEN "iPad8,11" THEN "iPad Pro 12.9 (Wi-Fi Only - 4th Gen)" WHEN "iPad8,12" THEN "iPad Pro 12.9 (Wi-Fi/Cell - 4th Gen)" WHEN "iPhone8,4" THEN "iPhone SE (United States/A1662)" WHEN "iPhone9,1" THEN "iPhone 7 (Verizon/Sprint/China/A1660)" WHEN "iPhone9,3" THEN "iPhone 7 (AT&T/T-Mobile/A1778)" WHEN "iPhone9,2" THEN "iPhone 7 Plus (Verizon/Sprint/China/A1661)" WHEN "iPhone9,4" THEN "iPhone 7 Plus (AT&T/T-Mobile/A1784)" WHEN "iPhone10,1" THEN "iPhone 8 (Verizon/Sprint/China/A1863)" WHEN "iPhone10,4" THEN "iPhone 8 (AT&T/T-Mobile/Global/A1905)" WHEN "iPhone10,2" THEN "iPhone 8 Plus (Verizon/Sprint/China/A1864)" WHEN "iPhone10,5" THEN "iPhone 8 Plus (AT&T/T-Mobile/Global/A1897)" WHEN "iPhone10,3" THEN "iPhone X (Verizon/Sprint/China/A1865)" WHEN "iPhone10,6" THEN "iPhone X (AT&T/T-Mobile/Global/A1901)" WHEN "iPhone11,2" THEN "iPhone Xs (A1920/A2097/A2098/A2100)" WHEN "iPhone11,6" THEN "iPhone Xs Max (A1921/A2101/A2101/A2104)" WHEN "iPhone11,8" THEN "iPhone XR (A1984/A2105/A2106/A2108)" WHEN "iPhone12,1" THEN "iPhone 11 (A2111/A2221/A2223)" WHEN "iPhone12,3" THEN "iPhone 11 Pro (A2160/A2215/A2217)" WHEN "iPhone12,5" THEN "iPhone 11 Pro Max (A2161/A2218/A2220)" ELSE "ZDEVICEMODEL" END 'Device Model', "ZLATITUDE" as 'Latitude', "ZLONGITUDE" as 'Longitude', "ZHORIZONTALACCURACY" as 'Accuracy in Meters' FROM ZSPEEDTESTRESULT ORDER BY "ZDATE" DESC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])) description = '' report = ArtifactHtmlReport('Applications') report.start_artifact_report(report_folder, 'Ookla Speedtest', description) report.add_script() data_headers = ('Timestamp', 'External IP Address', 'Internal IP Address', 'Carrier Name', 'ISP', 'Wifi SSID', 'WAN Type', 'Device Model', 'Latitude', 'Longitude', 'Accuracy in Meters') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Ookla Speedtest Data' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Ookla Speedtest Data' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Ookla Speedtest Data' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Ookla Speedtest Application data available') db.close() return
def get_whatsappMessages(files_found, report_folder, seeker): for file_found in files_found: file_found = str(file_found) if file_found.endswith('.sqlite'): break data_list = [] db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' select datetime(ZMESSAGEDATE+978307200, 'UNIXEPOCH'), ZISFROMME, ZPARTNERNAME, ZFROMJID, ZTOJID, ZWAMESSAGE.ZMEDIAITEM, ZTEXT, ZSTARRED, ZMESSAGETYPE, ZLONGITUDE, ZLATITUDE, ZMEDIALOCALPATH, ZXMPPTHUMBPATH FROM ZWAMESSAGE left JOIN ZWAMEDIAITEM on ZWAMESSAGE.Z_PK = ZWAMEDIAITEM.ZMESSAGE left JOIN ZWACHATSESSION on ZWACHATSESSION.Z_PK = ZWAMESSAGE.ZCHATSESSION ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: for row in all_rows: if row[1] == 1: sender = 'Local User' receiver = row[2] else: sender = row[2] receiver = 'Local User' if row[8] == 5: lon = row[9] lat = row[10] else: lat = '' lon = '' attfile = row[11] attachment = row[12] localpath = row[11] if attachment is not None: for match in files_found: if attachment in match: shutil.copy2(match, report_folder) data_file_name = os.path.basename(match) thumb = f'<img src="{report_folder}/{data_file_name}"></img>' else: thumb = '' if attfile is not None: for matchf in files_found: if attfile in matchf: shutil.copy2(matchf, report_folder) data_file_namef = os.path.basename(matchf) attfile = f'<img src="{report_folder}/{data_file_namef}" width="300"></img>' else: attfile = '' data_list.append(( row[0], sender, row[3], receiver, row[4], row[6], attfile, thumb, localpath, row[7], lat, lon, )) description = 'Whatsapp - Messages' report = ArtifactHtmlReport('Whatsapp - Messages') report.start_artifact_report(report_folder, 'Whatsapp - Messages') report.add_script() data_headers = ( 'Timestamp', 'Sender Name', 'From ID', 'Receiver', 'To ID', 'Message', 'Attachment File', 'Thumb', 'Attachment Local Path', 'Starred?', 'Latitude', 'Longitude', ) # Don't remove the comma, that is required to make this a tuple as there is only 1 element report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() tsvname = f'Whatsapp - Messages' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Whatsapp - Messages' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Whatsapp - Messages' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('Whatsapp - Messages data available')
def get_airtags(files_found, report_folder, seeker): data_list_info = [] data_list_safeloc = [] data_list_location = [] data_list_crowdloc = [] for file_found in files_found: file_found = str(file_found) with open(file_found, 'r') as f: deserialized = json.load(f) for x in deserialized: name = (x['name']) ptype = (x['productType'].get('type')) maname = (x['productType']['productInformation'].get('manufacturerName')) pid = (x['productType']['productInformation'].get('productIdentifier')) vid = (x['productType']['productInformation'].get('vendorIdentifier')) ap = (x['productType']['productInformation'].get('antennaPower')) gid = (x['groupIdentifier']) owner = (x.get('owner')) batstat = (x['batteryStatus']) serial = (x['serialNumber']) lostmode = (x['lostModeMetadata']) cap = (x['capabilities']) id = (x['identifier']) asubAdministrativeArea = (x['address'].get('subAdministrativeArea')) aslabel = (x['address'].get('label')) astreetAddress = (x['address'].get('streetAddress')) acountryCode = (x['address'].get('countryCode')) astateCode = (x['address'].get('stateCode')) administrativeArea = (x['address'].get('administrativeArea')) astreetName = (x['address'].get('streetName')) aformattedAddressLines = (x['address'].get('formattedAddressLines')) amapItemFullAddress = (x['address'].get('mapItemFullAddress')) afullThroroughfare = (x['address'].get('fullThroroughfare')) areaOfInterest = (x['address'].get('areaOfInterest')) alocality = (x['address'].get('locality')) acountry = (x['address'].get('country')) lpostype = (x['location'].get('positionType')) lverticalAccuracy = (x['location'].get('verticalAccuracy')) llong = (x['location'].get('longitude')) lfloor = (x['location'].get('floorLevel')) lisin = (x['location'].get('isInaccurate')) lisold = (x['location'].get('isOld')) lhorz = (x['location'].get('horizontalAccuracy')) llat = (x['location'].get('latitude')) ltimestamp = (x['location'].get('timeStamp')) ltimestamp = timestampcalc(ltimestamp) lalt = (x['location'].get('altitude')) lloc = (x['location'].get('locationFinished')) sysver = (x['systemVersion']) crowdloc = (x['crowdSourcedLocation']) crowdpostype = (x['crowdSourcedLocation'].get('positionType')) crowdvert = (x['crowdSourcedLocation'].get('verticalAccuracy')) crowdlong = (x['crowdSourcedLocation'].get('longitude')) crowdfloor = (x['crowdSourcedLocation'].get('floorLevel')) crowdisacc = (x['crowdSourcedLocation'].get('isInaccurate')) crowdisold = (x['crowdSourcedLocation'].get('isOld')) crowdhorzcc = (x['crowdSourcedLocation'].get('horizontalAccuracy')) crowdlat = (x['crowdSourcedLocation'].get('latitude')) crowdtimestamp= (x['crowdSourcedLocation'].get('timeStamp')) crowdtimestamp = timestampcalc(crowdtimestamp) crowdalt = (x['crowdSourcedLocation'].get('altitude')) crowdlocfin = (x['crowdSourcedLocation'].get('locationFinished')) rname = (x['role'].get('name')) remoji = (x['role'].get('emoji')) ris = (x['role'].get('identifier')) for safeloc in x.get('safeLocations'): sname = (safeloc.get('name')) stype = (safeloc.get('type')) sid = (safeloc.get('identifier')) sva = (safeloc['location'].get('verticalAccuracy')) sha = (safeloc['location'].get('horizontalAccuracy')) slong = (safeloc['location'].get('longitude')) slat = (safeloc['location'].get('latitude')) sfloor = (safeloc['location'].get('floorLevel')) sisina = (safeloc['location'].get('isInaccurate')) sisold = (safeloc['location'].get('isOld')) stimestamp = (safeloc['location'].get('timeStamp')) stimestamp = timestampcalc(stimestamp) salt = (safeloc['location'].get('altitude')) ssub = (safeloc['address'].get('subAdministrativeArea')) slabel = (safeloc['address'].get('label')) sstreet = (safeloc['address'].get('streetAddres')) scountry = (safeloc['address'].get('countryCode')) sstate = (safeloc['address'].get('stateCode')) sadmin = (safeloc['address'].get('administrativeArea')) pstreetn = (safeloc['address'].get('streetName')) sformated = (safeloc['address'].get('formattedAddressLines')) smapfull = (safeloc['address'].get('mapItemFullAddress')) sthro = (safeloc['address'].get('fullThroroughfare')) saoi = (safeloc['address'].get('areaOfInterest')) sloc = (safeloc['address'].get('locality')) scount = (safeloc['address'].get('country')) data_list_safeloc.append((stimestamp, name, serial, id, rname, remoji, ris, sname, stype, sid, sva, sha, slong, slat, sfloor, sisina, sisold, salt, ssub, slabel, sstreet, scountry, sstate, sadmin, pstreetn, sformated, smapfull, sthro, saoi, sloc, scount)) data_list_info.append((name, serial, id, rname, remoji, ris, ptype, maname, pid, vid, ap, gid, owner, batstat, lostmode, cap, sysver,)) data_list_location.append((ltimestamp, name, serial, id, rname, remoji, ris, ptype, maname, pid, vid, ap, gid, owner, batstat, lostmode, cap, sysver, asubAdministrativeArea, aslabel, astreetAddress, acountryCode, astateCode, administrativeArea, astreetName, aformattedAddressLines, amapItemFullAddress, afullThroroughfare, areaOfInterest, alocality, lpostype, lverticalAccuracy, llong, lisin, lisold, lhorz, llat, lalt, lloc, acountry)) data_list_crowdloc.append((crowdtimestamp, name, serial, id, rname, remoji, ris, ptype, maname, pid, vid, ap, gid, owner, batstat, lostmode, cap, sysver, crowdpostype, crowdvert, crowdlong, crowdlat, crowdalt, crowdfloor, crowdisacc, crowdisold, crowdhorzcc, crowdlocfin )) if data_list_safeloc: report = ArtifactHtmlReport('Safe Locations') report.start_artifact_report(report_folder, 'Safe Locations') report.add_script() data_list_safeloc_headers = ('Timestamp', 'Name', 'Serial', 'ID', 'Role Name', 'Emoji', 'Role ID', 'Name', 'Type', 'Identifier', 'Vertical Accuracy', 'Horizontal Accuracy', 'Longitude', 'Latitude', 'Floor Level', 'Is Inaccurate', 'Is Old', 'Altitude', 'Sub-Administrative Area', 'Label', 'Street Address', 'Country Code', 'State Code', 'Administrative Area', 'Street Name', 'Formatted Address Line', 'Map Item Full Address', 'Throroughfare', 'Area of Interest', 'Locality', 'Country' ) report.write_artifact_data_table(data_list_safeloc_headers, data_list_safeloc, file_found) report.end_artifact_report() tsvname = f'Airtags Safe Locations' tsv(report_folder, data_list_safeloc_headers, data_list_safeloc, tsvname) tlactivity = f'Airtags Safe Locations' timeline(report_folder, tlactivity, data_list_safeloc, data_list_safeloc_headers) kmlactivity = 'Airtags Safe Locations' kmlgen(report_folder, kmlactivity, data_list_safeloc, data_list_safeloc_headers) else: logfunc('No Airtags Safe Locations data available') if data_list_location: report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'Locations') report.add_script() data_list_location_headers = ('Timestamp','Name', 'Serial', 'ID', 'Role Name', 'Emoji', 'Role ID', 'Product Type', 'Manufacturer', 'Product ID', 'Vendor ID', 'Antenna Power', 'Group ID', 'Owner', 'Battery Status', 'Lost Mode', 'Capabilities', 'System Version', 'Sub-administrative Area', 'Label', 'Street Address', 'Country Code', 'State Code', 'Administrative Area', 'Street Name', 'Formatted Address Line', 'Item Full Address', 'Throroughfare', 'Area of Interest', 'Locality', 'Type', 'Vertical Accuracy', 'Longitude', 'Is Inaccurate', 'Is Old', 'Horizontal Accuracy', 'Latitude', 'Altitude', 'Location Finished', 'Country' ) report.write_artifact_data_table(data_list_location_headers, data_list_location, file_found) report.end_artifact_report() tsvname = f'Airtags Locations' tsv(report_folder, data_list_location_headers, data_list_location, tsvname) tlactivity = f'Airtags Locations' timeline(report_folder, tlactivity, data_list_location, data_list_location_headers) kmlactivity = 'Airtags Locations' kmlgen(report_folder, kmlactivity, data_list_location, data_list_location_headers) else: logfunc('No Airtags Locations data available') if data_list_info: report = ArtifactHtmlReport('Airtags Info') report.start_artifact_report(report_folder, 'Airtags Info') report.add_script() data_list_info_headers = ('Name', 'Serial', 'ID', 'Role Name', 'Emoji', 'Role ID', 'Product Type', 'Manufacturer', 'Product ID', 'Vendor ID', 'Antenna Power', 'Group ID', 'Owner', 'Battery Status', 'Lost Mode', 'Capabilities', 'System Version' ) report.write_artifact_data_table(data_list_info_headers, data_list_info, file_found) report.end_artifact_report() tsvname = f'Airtags Info' tsv(report_folder, data_list_info_headers, data_list_info, tsvname) tlactivity = f'Airtags Info' timeline(report_folder, tlactivity, data_list_info, data_list_info_headers) else: logfunc('No Airtags Info data available') if data_list_crowdloc: report = ArtifactHtmlReport('Crowdsourced Locations') report.start_artifact_report(report_folder, 'Crowdsourced Locations') report.add_script() data_headers_crowdloc = ('Timestamp', 'Name', 'Serial', 'ID', 'Role Name', 'Emoji', 'Role ID', 'Product Type', 'Manufacturer', 'Product ID', 'Vendor ID', 'Antenna Power', 'Group ID', 'Owner', 'Battery Status', 'Lost Mode', 'Capabilities', 'System Version', 'Position Type', 'Vertical Accuracy', 'Longitude', 'Latitude', 'Altitude', 'Floor Level', 'Is Inaccurate', 'Is Old', 'Horizontal Accuracy', 'Location Finished') report.write_artifact_data_table(data_headers_crowdloc, data_list_crowdloc, file_found) report.end_artifact_report() tsvname = f'Airtgas Crowdsourced Locations' tsv(report_folder, data_headers_crowdloc, data_list_crowdloc, tsvname) tlactivity = f'Airtags Crowdsourced Locations' timeline(report_folder, tlactivity, data_list_crowdloc, data_headers_crowdloc) kmlactivity = 'Airtags Crowdsourced Locations' kmlgen(report_folder, kmlactivity, data_list_crowdloc, data_headers_crowdloc) else: logfunc('No Airtags Crowdsourced Locations data available')
def get_routineDlocations(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("10"): logfunc( "Unsupported version for RoutineD Locations Cache.sqlite on iOS " + iOSversion) else: for file_found in files_found: file_found = str(file_found) if file_found.endswith('Cache.sqlite'): break db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZTIMESTAMP + 978307200, 'UNIXEPOCH') AS "TIMESTAMP", ZLATITUDE || ", " || ZLONGITUDE AS "COORDINATES", ZALTITUDE AS "ALTITUDE", ZCOURSE AS "COURSE", ZSPEED AS "SPEED (M/S)", ZSPEED*2.23694 AS "SPEED (MPH)", ZSPEED*3.6 AS "SPEED (KMPH)", ZHORIZONTALACCURACY AS "HORIZONTAL ACCURACY", ZVERTICALACCURACY AS "VERTICAL ACCURACY", ZLATITUDE AS "LATITUDE", ZLONGITUDE AS "LONGITUDE", ZRTCLLOCATIONMO.Z_PK AS "ZRTCLLOCATIONMO TABLE ID" FROM ZRTCLLOCATIONMO ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11])) description = 'Granular location data (~ 1 week)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTCLLOCATIONMO', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'Altitude', 'Course', 'Speed (M/S)', 'Speed (MPH)', 'Speed (KMPH)', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTCLLOCATIONMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTCLLOCATIONMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTCLLOCATIONMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTCLLOCATIONMO data available') # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZDATE + 978307200, 'UNIXEPOCH') AS "TIMESTAMP", ZLATITUDE || ", " || ZLONGITUDE AS "COORDINATES", ZSOURCE AS "SOURCE", ZLATITUDE AS "LATITUDE", ZLONGITUDE AS "LONGITUDE", ZRTHINTMO.Z_PK AS "ZRTHINTMO TABLE ID" FROM ZRTHINTMO ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5])) description = 'Semi-granular location data (~ 1 week)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTHINTMO', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'Source', 'Latitude', 'Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTHINTMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTHINTMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTHINTMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTHINTMO data available') # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY TIMESTAMP", DATETIME(ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT TIMESTAMP", ZLOCATIONLATITUDE || ", " || ZLOCATIONLONGITUDE AS "COORDINATES", DATETIME(ZDETECTIONDATE + 978307200, 'UNIXEPOCH') AS "DETECTION TIMESTAMP", (ZEXITDATE-ZENTRYDATE)/60.00 AS "VISIT TIME (MINUTES)", ZTYPE AS "TYPE", ZLOCATIONLATITUDE AS "LATITUDE", ZLOCATIONLONGITUDE AS "LONGITUDE", ZLOCATIONUNCERTAINTY AS "UNCERTAINTY", ZDATAPOINTCOUNT AS "DATA POINT COUNT", Z_PK AS "ZRTVISITMO TABLE ID" FROM ZRTVISITMO ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])) description = 'Visit locations' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTVISITMO', description) report.add_script() data_headers = ('Timestamp', 'Exit Timestamp', 'Coordinates', 'Detection Timestamp', 'Visit Time (Minutes)', 'Type', 'Latitude', 'Longitude', 'Uncertainty', 'Data Point Count', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTVISITMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTVISITMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTVISITMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTVISITMO data available')
def get_routineDlocations(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("10"): logfunc( "Unsupported version for RoutineD Locations Cache.sqlite on iOS " + iOSversion) else: for file_found in files_found: file_found = str(file_found) if file_found.endswith('Cache.sqlite'): break db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' select datetime(ztimestamp + 978307200, 'unixepoch'), zaltitude, zcourse, zspeed, zspeed*2.23694, zspeed*3.6, zhorizontalaccuracy, zverticalaccuracy, zlatitude, zlongitude from zrtcllocationmo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9])) description = 'Granular location data (~ 1 week)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTCLLOCATIONMO', description) report.add_script() data_headers = ('Timestamp', 'Altitude', 'Course', 'Speed (M/S)', 'Speed (MPH)', 'Speed (KMPH)', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTCLLOCATIONMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTCLLOCATIONMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTCLLOCATIONMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTCLLOCATIONMO data available') cursor.execute(''' select datetime(zdate + 978307200, 'unixepoch'), zsource, zlatitude, zlongitude from zrthintmo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3])) description = 'Semi-granular location data (~ 1 week)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTHINTMO', description) report.add_script() data_headers = ('Timestamp', 'Source', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTHINTMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTHINTMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTHINTMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTHINTMO data available') cursor.execute(''' select datetime(zentrydate + 978307200, 'unixepoch'), datetime(zexitdate + 978307200, 'unixepoch'), datetime(zdetectiondate + 978307200, 'unixepoch'), (zexitdate-zentrydate)/60.00, ztype, zlocationlatitude, zlocationlongitude, zlocationuncertainty from zrtvisitmo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])) description = 'Visit locations' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD ZRTVISITMO', description) report.add_script() data_headers = ('Timestamp', 'Exit Timestamp', 'Detection Timestamp', 'Visit Time (Minutes)', 'Type', 'Latitude', 'Longitude', 'Uncertainty') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD ZRTVISITMO' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD ZRTVISITMO' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD ZRTVISITMO' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD ZRTVISITMO data available')
def get_photosMetadata(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc("Unsupported version for Photos.sqlite metadata on iOS " + iOSversion) elif (version.parse(iOSversion) >= version.parse("13")) & ( version.parse(iOSversion) < version.parse("14")): file_found = str(files_found[0]) #os.chmod(file_found, 0o0777) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DateTime( ZGENERICASSET.ZDATECREATED + 978307200, 'UNIXEPOCH' ) AS 'DateCreated', ZGENERICASSET.Z_PK AS 'GenericAsset_zpk', ZGENERICASSET.ZADDITIONALATTRIBUTES AS 'AddAttributes_Key', ZDETECTEDFACE.ZASSET AS 'DetectedFaceAsset', CASE ZGENERICASSET.ZKIND WHEN 0 THEN 'Photo' WHEN 1 THEN 'Video' END AS 'Kind', ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRING AS 'EXIFtimestamp', DateTime( ZADDITIONALASSETATTRIBUTES.ZSCENEANALYSISTIMESTAMP + 978307200, 'UNIXEPOCH' ) AS 'SceneAnalysisTimeStamp', DateTime( ZGENERICASSET.ZANALYSISSTATEMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'AnalysisStateModificationDate', DateTime( ZGENERICASSET.ZADDEDDATE + 978307200, 'UNIXEPOCH' ) AS 'AddDate', ZGENERICASSET.ZFILENAME AS 'FileName', ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAME AS 'OriginalFilename', ZGENERICALBUM.ZTITLE AS 'AlbumTitle', ZADDITIONALASSETATTRIBUTES.ZCREATORBUNDLEID AS 'CreatorBundleID', ZADDITIONALASSETATTRIBUTES.ZEDITORBUNDLEID AS 'EditorBundleID', ZGENERICASSET.ZDIRECTORY AS 'Directory', ZGENERICASSET.ZUNIFORMTYPEIDENTIFIER AS 'UniformID', CASE ZGENERICASSET.ZSAVEDASSETTYPE WHEN 0 THEN 'Saved from other source' WHEN 2 THEN 'Photo Streams Data' WHEN 3 THEN 'Made/saved with this device' WHEN 4 THEN 'Default row' WHEN 7 THEN 'Deleted' ELSE ZGENERICASSET.ZSAVEDASSETTYPE END AS 'SavedAssetType', CASE WHEN ZGENERICASSET.ZFACEAREAPOINTS > 0 THEN 'Yes' ELSE 'NA' END AS 'FaceDetectedinPhoto', ZPERSON.ZDISPLAYNAME AS 'DisplayName', ZPERSON.ZFULLNAME AS 'FullName', ZPERSON.ZFACECOUNT AS 'FaceCount', ZDETECTEDFACE.ZPERSON AS 'Person', ZPERSON.ZCONTACTMATCHINGDICTIONARY AS 'ContactBlob', ZPERSON.ZPERSONUUID as 'PersonUUID', ZDETECTEDFACE.ZQUALITYMEASURE AS 'DetectedFaceQuality', CASE ZDETECTEDFACE.ZAGETYPE WHEN 1 THEN 'Baby/Toddler' WHEN 2 THEN 'Baby/Toddler' WHEN 3 THEN 'Child/YoungAdult' WHEN 4 THEN 'YoungAdult/Adult' WHEN 5 THEN 'Adult' ELSE ZDETECTEDFACE.ZAGETYPE END AS 'AgeTypeEstimate', CASE ZDETECTEDFACE.ZGENDERTYPE WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' ELSE ZDETECTEDFACE.ZGENDERTYPE END AS 'Gender', CASE ZDETECTEDFACE.ZGLASSESTYPE WHEN 3 THEN 'None' WHEN 2 THEN 'Sun' WHEN 1 THEN 'Eye' ELSE ZDETECTEDFACE.ZGLASSESTYPE END AS 'GlassesType', CASE ZDETECTEDFACE.ZFACIALHAIRTYPE WHEN 1 THEN 'None' WHEN 2 THEN 'Beard/Mustache' WHEN 3 THEN 'Goatee' WHEN 5 THEN 'Stubble' ELSE ZDETECTEDFACE.ZFACIALHAIRTYPE END AS 'FacialHairType', CASE ZDETECTEDFACE.ZBALDTYPE WHEN 2 THEN 'Bald' WHEN 3 THEN 'NotBald' ELSE ZDETECTEDFACE.ZBALDTYPE END AS 'Baldness', ZGENERICASSET.ZORIGINALCOLORSPACE AS 'ColorSpace', ZGENERICASSET.Zduration AS 'Duration', ZGENERICASSET.Zvideocpdurationvalue AS 'VideoDuration', CASE ZGENERICASSET.ZCOMPLETE WHEN 1 THEN 'Yes' END AS 'Complete', CASE ZGENERICASSET.ZVISIBILITYSTATE WHEN 0 THEN 'Visible' WHEN 1 THEN 'Photo Streams Data' WHEN 2 THEN 'Burst' ELSE ZVISIBILITYSTATE END AS 'VisibilityState', CASE ZGENERICASSET.ZFAVORITE WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'Favorite', CASE ZGENERICASSET.zhidden WHEN 0 THEN 'Not_Hidden' WHEN 1 THEN 'File_Hidden' ELSE ZGENERICASSET.zhidden END AS 'Hidden_File', CASE ZGENERICASSET.ZTRASHEDSTATE WHEN 1 THEN 'In_Trash' WHEN 0 THEN 'Not_In_Trash' ELSE ZGENERICASSET.ZTRASHEDSTATE END AS 'TrashState', DateTime( ZGENERICASSET.ZTRASHEDDATE + 978307200, 'UNIXEPOCH' ) AS 'FileTrashDate', ZADDITIONALASSETATTRIBUTES.ZVIEWCOUNT AS 'ViewCount', ZADDITIONALASSETATTRIBUTES.ZPLAYCOUNT AS 'PlayCount', ZADDITIONALASSETATTRIBUTES.ZSHARECOUNT AS 'ShareCount', DateTime( ZGENERICASSET.ZLASTSHAREDDATE + 978307200, 'UNIXEPOCH' ) AS 'LastSharedDate', DateTime( ZGENERICASSET.ZMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'FileModificationDate', CASE ZGENERICASSET.ZHASADJUSTMENTS WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'Has_Adjustments', DateTime( ZGENERICASSET.ZADJUSTMENTTIMESTAMP + 978307200, 'UNIXEPOCH' ) AS 'AdjustmentsTimeStamp', ZADDITIONALASSETATTRIBUTES.ZORIGINALFILESIZE AS 'OriginalFileSize', ZGENERICASSET.ZHEIGHT AS 'File_Height', ZADDITIONALASSETATTRIBUTES.ZORIGINALHEIGHT AS 'OrgFileHeight', ZGENERICASSET.ZWIDTH AS 'File_Width', ZADDITIONALASSETATTRIBUTES.ZORIGINALWIDTH AS 'OrgFileWidth', CASE ZGENERICASSET.ZORIENTATION WHEN 1 THEN 'Horizontal (left)' WHEN 3 THEN 'Horizontal (right)' WHEN 6 THEN 'Vertical (up)' WHEN 8 THEN 'Vertical (down)' ELSE ZORIENTATION END AS 'Orientation', CASE ZADDITIONALASSETATTRIBUTES.ZORIGINALORIENTATION WHEN 1 THEN 'Horizontal (left)' WHEN 3 THEN 'Horizontal (right)' WHEN 6 THEN 'Vertical (up)' WHEN 8 THEN 'Vertical (down)' ELSE ZORIENTATION END AS 'Org_Orientation', ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME AS 'TimeZoneName', ZADDITIONALASSETATTRIBUTES.ZTIMEZONEOFFSET AS 'TimeZoneOffset', ZADDITIONALASSETATTRIBUTES.ZINFERREDTIMEZONEOFFSET AS 'InferredTimeZoneOffset', ZGENERICASSET.ZLOCATIONDATA AS 'FileLocationData', CASE ZGENERICASSET.ZLATITUDE WHEN - 180.0 THEN '' ELSE ZGENERICASSET.ZLATITUDE END AS 'Latitude', CASE ZGENERICASSET.ZLONGITUDE WHEN - 180.0 THEN '' ELSE ZGENERICASSET.ZLONGITUDE END AS 'Longitude', CASE ZADDITIONALASSETATTRIBUTES.ZSHIFTEDLOCATIONISVALID WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'ShiftedLocationValid', CASE ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATAISVALID WHEN 0 THEN 'No_Check_SceneAnalysis' WHEN 1 THEN 'Yes_Check_SceneAnalysis' END AS 'ReverseLocationDataIsValid', ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATA AS 'OrgFileReverseLocationData', ZGENERICASSET.Zthumbnailindex AS 'ThumbnailIndex', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILWIDTH AS 'EmbeddedThumbnailWidth', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILHEIGHT AS 'EmbeddedThumbnailHeight', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILOFFSET AS 'EmbeddedThumbnailOffset', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILLENGTH AS 'EmbeddedThumbnailLenght', ZGENERICASSET.ZMOMENT AS 'MomentPK', DateTime( ZMOMENT.ZSTARTDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentStartDate', DateTime( ZMOMENT.Zrepresentativedate + 978307200, 'UNIXEPOCH' ) AS 'MomentRepresentativeDate', DateTime( ZMOMENT.ZMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentModificationDate', DateTime( ZMOMENT.ZENDDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentEndDate', ZMOMENT.ZTITLE AS 'MomentTitle', CASE ZMOMENT.Zapproximatelatitude WHEN - 180.0 THEN '' ELSE ZMOMENT.Zapproximatelatitude END AS 'MomentApproxLatitude', CASE ZMOMENT.Zapproximatelongitude WHEN - 180.0 THEN '' ELSE ZMOMENT.Zapproximatelongitude END AS 'MomentApproxLongitude', ZGENERICASSET.ZUUID AS 'UUID', ZGENERICASSET.ZMEDIAGROUPUUID AS 'MediaGroupUUID', ZGENERICASSET.ZCLOUDASSETGUID AS 'CloudAssetGUID', ZADDITIONALASSETATTRIBUTES.ZPUBLICGLOBALUUID AS 'PublicGlobalUUID', ZADDITIONALASSETATTRIBUTES.ZMASTERFINGERPRINT AS 'MasterFingerprint', ZADDITIONALASSETATTRIBUTES.ZADJUSTEDFINGERPRINT AS 'AdjustedFingerprint' FROM ZGENERICASSET JOIN Z_PRIMARYKEY ON ZGENERICASSET.z_ent = Z_PRIMARYKEY.z_ent LEFT JOIN ZMOMENT ON ZGENERICASSET.ZMOMENT = ZMOMENT.Z_PK JOIN ZADDITIONALASSETATTRIBUTES ON ZGENERICASSET.ZADDITIONALATTRIBUTES = ZADDITIONALASSETATTRIBUTES.Z_PK LEFT JOIN ZDETECTEDFACE ON ZADDITIONALASSETATTRIBUTES.ZASSET = ZDETECTEDFACE.ZASSET LEFT JOIN ZPERSON ON ZPERSON.Z_PK = ZDETECTEDFACE.ZPERSON LEFT JOIN Z_26ASSETS ON ZGENERICASSET.Z_PK = Z_26ASSETS.Z_34ASSETS LEFT JOIN ZGENERICALBUM ON ZGENERICALBUM.Z_PK = Z_26ASSETS.Z_26ALBUMS """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] counter = 0 if usageentries > 0: for row in all_rows: postal_address = '' postal_address_subadminarea = '' postal_address_sublocality = '' if row[61] is not None: pathto = os.path.join( report_folder, 'ReverseLocationData' + str(counter) + '.bplist') with open(pathto, 'ab') as wf: wf.write(row[61]) with open(pathto, 'rb') as f: try: deserialized_plist = nd.deserialize_plist(f) postal_address = deserialized_plist[ 'postalAddress']['_formattedAddress'] postal_address_subadminarea = deserialized_plist[ 'postalAddress']['_subAdministrativeArea'] postal_address_sublocality = deserialized_plist[ 'postalAddress']['_subLocality'] except: logfunc( 'Error reading exported bplist from Asset PK' + row[0]) deserialized_plist = None data_list.append( (row[0], row[0], postal_address, postal_address_subadminarea, postal_address_sublocality, row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27], row[28], row[29], row[30], row[31], row[32], row[33], row[34], row[35], row[36], row[37], row[38], row[39], row[40], row[41], row[42], row[43], row[44], row[45], row[46], row[47], row[48], row[49], row[50], row[51], row[52], row[53], row[54], row[55], row[56], row[57], row[58], row[59], row[60], row[61], row[62], row[63], row[64], row[65], row[66], row[67], row[68], row[69], row[70], row[71], row[72], row[73], row[74], row[75], row[76], row[77], row[78], row[79], row[80])) counter += 1 description = '' report = ArtifactHtmlReport('Photos.sqlite') report.start_artifact_report(report_folder, 'Metadata', description) report.add_script() data_headers = ( 'Timestamp', 'Date Created', 'Postal Address', 'Postal Subadmin Area', 'Postal Sublocality', 'Generic Asset ZPK', 'Add Attributes Key', 'Detected Face Asset', 'Kind', 'EXIF Timestamp', 'Scene Analysis Timestamp', 'Analysis State Modified Date', 'Add Date', 'Filename', 'Original Filename', 'Album Title', 'Creator Bundle ID', 'Editor Bundle ID', 'Directory', 'Uniform ID', 'Saved Asset Type', 'Face Detected in Photo', 'Display Name', 'Full Name', 'Face Count', 'Person', 'Contact Blob', 'Person UUID', 'Detected Face Quality', 'Age Type Estimate', 'Gender', 'Glasses Type', 'Facial Hair Type', 'Baldness', 'Color Space', 'Duration', 'Video Duration', 'Complete', 'Visibility State', 'Favorite', 'Hidden Fie?', 'Trash State', 'File Trash Date', 'View Count', 'Play Count', 'Share Count', 'Last Shared Date', 'File Modification Date', 'Has Adjustments?', 'Adjustment Timestamp', 'Original File Size', 'File Height', 'Org File Height', 'File Width', 'Org File Width', 'Orientation', 'Org Orientation', 'Timezone Name', 'Timezone Offset', 'Infered Timezone Offset', 'File Location Data', 'Latitude', 'Longitude', 'Shifted Location Valid', 'Reverse Lcoation Data is Valid', 'Org File Reverse Location Data', 'Thumbnail Index', 'Embedded Thumbnail Width', 'Embedded Thumbnail Height', 'Embedded Thumbnail Offset', 'Embedded Thumbnail Lenght', 'Moment PK', 'Moment Start Date', 'Moment Representative Date', 'Moment Modification Date', 'Moment End Date', 'Moment Title', 'Moment Approx Latitude', 'Moment Approx Longitude', 'UUID', 'Media Group UUID', 'Cloud Assest GUID', 'Public Global UUID', 'Master Fingetprint', 'Adjusted Fingerprint') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Photos-sqlite Metadata' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Photos-sqlite Metadata' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Photos-sqlite Metadata' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for Photos.sqlite metadata') db.close() return elif (version.parse(iOSversion) >= version.parse("14")): file_found = str(files_found[0]) #os.chmod(file_found, 0o0777) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DateTime( ZASSET.ZDATECREATED + 978307200, 'UNIXEPOCH' ) AS 'DateCreated', ZASSET.Z_PK AS 'Asset_zpk', ZASSET.ZADDITIONALATTRIBUTES AS 'AddAttributes_Key', ZDETECTEDFACE.ZASSET AS 'DetectedFaceAsset', CASE ZASSET.ZKIND WHEN 0 THEN 'Photo' WHEN 1 THEN 'Video' END AS 'Kind', ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRING AS 'EXIFtimestamp', DateTime( ZADDITIONALASSETATTRIBUTES.ZSCENEANALYSISTIMESTAMP + 978307200, 'UNIXEPOCH' ) AS 'SceneAnalysisTimeStamp', DateTime( ZASSET.ZANALYSISSTATEMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'AnalysisStateModificationDate', DateTime( ZASSET.ZADDEDDATE + 978307200, 'UNIXEPOCH' ) AS 'AddDate', ZASSET.ZFILENAME AS 'FileName', ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAME AS 'OriginalFilename', ZGENERICALBUM.ZTITLE AS 'AlbumTitle', ZADDITIONALASSETATTRIBUTES.ZCREATORBUNDLEID AS 'CreatorBundleID', ZADDITIONALASSETATTRIBUTES.ZEDITORBUNDLEID AS 'EditorBundleID', ZASSET.ZDIRECTORY AS 'Directory', ZASSET.ZUNIFORMTYPEIDENTIFIER AS 'UniformID', CASE ZASSET.ZSAVEDASSETTYPE WHEN 0 THEN 'Saved from other source' WHEN 2 THEN 'Photo Streams Data' WHEN 3 THEN 'Made/saved with this device' WHEN 4 THEN 'Default row' WHEN 7 THEN 'Deleted' ELSE ZASSET.ZSAVEDASSETTYPE END AS 'SavedAssetType', CASE WHEN ZASSET.ZFACEAREAPOINTS > 0 THEN 'Yes' ELSE 'NA' END AS 'FaceDetectedinPhoto', ZPERSON.ZDISPLAYNAME AS 'DisplayName', ZPERSON.ZFULLNAME AS 'FullName', ZPERSON.ZFACECOUNT AS 'FaceCount', ZDETECTEDFACE.ZPERSON AS 'Person', ZPERSON.ZCONTACTMATCHINGDICTIONARY AS 'ContactBlob', ZPERSON.ZPERSONUUID as 'PersonUUID', ZDETECTEDFACE.ZQUALITYMEASURE AS 'DetectedFaceQuality', CASE ZDETECTEDFACE.ZAGETYPE WHEN 1 THEN 'Baby/Toddler' WHEN 2 THEN 'Baby/Toddler' WHEN 3 THEN 'Child/YoungAdult' WHEN 4 THEN 'YoungAdult/Adult' WHEN 5 THEN 'Adult' ELSE ZDETECTEDFACE.ZAGETYPE END AS 'AgeTypeEstimate', CASE ZDETECTEDFACE.ZGENDERTYPE WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' ELSE ZDETECTEDFACE.ZGENDERTYPE END AS 'Gender', CASE ZDETECTEDFACE.ZGLASSESTYPE WHEN 3 THEN 'None' WHEN 2 THEN 'Sun' WHEN 1 THEN 'Eye' ELSE ZDETECTEDFACE.ZGLASSESTYPE END AS 'GlassesType', CASE ZDETECTEDFACE.ZFACIALHAIRTYPE WHEN 1 THEN 'None' WHEN 2 THEN 'Beard/Mustache' WHEN 3 THEN 'Goatee' WHEN 5 THEN 'Stubble' ELSE ZDETECTEDFACE.ZFACIALHAIRTYPE END AS 'FacialHairType', CASE ZDETECTEDFACE.ZBALDTYPE WHEN 2 THEN 'Bald' WHEN 3 THEN 'NotBald' ELSE ZDETECTEDFACE.ZBALDTYPE END AS 'Baldness', ZASSET.ZORIGINALCOLORSPACE AS 'ColorSpace', ZASSET.Zduration AS 'Duration', ZASSET.Zvideocpdurationvalue AS 'VideoDuration', CASE ZASSET.ZCOMPLETE WHEN 1 THEN 'Yes' END AS 'Complete', CASE ZASSET.ZVISIBILITYSTATE WHEN 0 THEN 'Visible' WHEN 1 THEN 'Photo Streams Data' WHEN 2 THEN 'Burst' ELSE ZVISIBILITYSTATE END AS 'VisibilityState', CASE ZASSET.ZFAVORITE WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'Favorite', CASE ZASSET.zhidden WHEN 0 THEN 'Not_Hidden' WHEN 1 THEN 'File_Hidden' ELSE ZASSET.zhidden END AS 'Hidden_File', CASE ZASSET.ZTRASHEDSTATE WHEN 1 THEN 'In_Trash' WHEN 0 THEN 'Not_In_Trash' ELSE ZASSET.ZTRASHEDSTATE END AS 'TrashState', DateTime( ZASSET.ZTRASHEDDATE + 978307200, 'UNIXEPOCH' ) AS 'FileTrashDate', ZADDITIONALASSETATTRIBUTES.ZVIEWCOUNT AS 'ViewCount', ZADDITIONALASSETATTRIBUTES.ZPLAYCOUNT AS 'PlayCount', ZADDITIONALASSETATTRIBUTES.ZSHARECOUNT AS 'ShareCount', DateTime( ZASSET.ZLASTSHAREDDATE + 978307200, 'UNIXEPOCH' ) AS 'LastSharedDate', DateTime( ZASSET.ZMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'FileModificationDate', CASE ZASSET.ZHASADJUSTMENTS WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'Has_Adjustments', DateTime( ZASSET.ZADJUSTMENTTIMESTAMP + 978307200, 'UNIXEPOCH' ) AS 'AdjustmentsTimeStamp', ZADDITIONALASSETATTRIBUTES.ZORIGINALFILESIZE AS 'OriginalFileSize', ZASSET.ZHEIGHT AS 'File_Height', ZADDITIONALASSETATTRIBUTES.ZORIGINALHEIGHT AS 'OrgFileHeight', ZASSET.ZWIDTH AS 'File_Width', ZADDITIONALASSETATTRIBUTES.ZORIGINALWIDTH AS 'OrgFileWidth', CASE ZASSET.ZORIENTATION WHEN 1 THEN 'Horizontal (left)' WHEN 3 THEN 'Horizontal (right)' WHEN 6 THEN 'Vertical (up)' WHEN 8 THEN 'Vertical (down)' ELSE ZORIENTATION END AS 'Orientation', CASE ZADDITIONALASSETATTRIBUTES.ZORIGINALORIENTATION WHEN 1 THEN 'Horizontal (left)' WHEN 3 THEN 'Horizontal (right)' WHEN 6 THEN 'Vertical (up)' WHEN 8 THEN 'Vertical (down)' ELSE ZORIENTATION END AS 'Org_Orientation', ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME AS 'TimeZoneName', ZADDITIONALASSETATTRIBUTES.ZTIMEZONEOFFSET AS 'TimeZoneOffset', ZADDITIONALASSETATTRIBUTES.ZINFERREDTIMEZONEOFFSET AS 'InferredTimeZoneOffset', ZASSET.ZLOCATIONDATA AS 'FileLocationData', CASE ZASSET.ZLATITUDE WHEN - 180.0 THEN '' ELSE ZASSET.ZLATITUDE END AS 'Latitude', CASE ZASSET.ZLONGITUDE WHEN - 180.0 THEN '' ELSE ZASSET.ZLONGITUDE END AS 'Longitude', CASE ZADDITIONALASSETATTRIBUTES.ZSHIFTEDLOCATIONISVALID WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS 'ShiftedLocationValid', CASE ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATAISVALID WHEN 0 THEN 'No_Check_SceneAnalysis' WHEN 1 THEN 'Yes_Check_SceneAnalysis' END AS 'ReverseLocationDataIsValid', ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATA AS 'OrgFileReverseLocationData', ZASSET.Zthumbnailindex AS 'ThumbnailIndex', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILWIDTH AS 'EmbeddedThumbnailWidth', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILHEIGHT AS 'EmbeddedThumbnailHeight', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILOFFSET AS 'EmbeddedThumbnailOffset', ZADDITIONALASSETATTRIBUTES.ZEMBEDDEDTHUMBNAILLENGTH AS 'EmbeddedThumbnailLenght', ZASSET.ZMOMENT AS 'MomentPK', ZMOMENT.ZTITLE AS 'MomentTitle', DateTime( ZMOMENT.ZSTARTDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentStartDate', DateTime( ZMOMENT.Zrepresentativedate + 978307200, 'UNIXEPOCH' ) AS 'MomentRepresentativeDate', DateTime( ZMOMENT.ZMODIFICATIONDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentModificationDate', DateTime( ZMOMENT.ZENDDATE + 978307200, 'UNIXEPOCH' ) AS 'MomentEndDate', CASE ZMOMENT.ZTRASHEDSTATE WHEN 1 THEN 'In_Trash' WHEN 0 THEN 'Not_In_Trash' ELSE ZMOMENT.ZTRASHEDSTATE END AS 'MomentTrashState', CASE ZMOMENT.Zapproximatelatitude WHEN - 180.0 THEN '' ELSE ZMOMENT.Zapproximatelatitude END AS 'MomentApproxLatitude', CASE ZMOMENT.Zapproximatelongitude WHEN - 180.0 THEN '' ELSE ZMOMENT.Zapproximatelongitude END AS 'MomentApproxLongitude', ZASSET.ZUUID AS 'UUID', ZASSET.ZMEDIAGROUPUUID AS 'MediaGroupUUID', ZASSET.ZCLOUDASSETGUID AS 'CloudAssetGUID', ZADDITIONALASSETATTRIBUTES.ZPUBLICGLOBALUUID AS 'PublicGlobalUUID', ZADDITIONALASSETATTRIBUTES.ZMASTERFINGERPRINT AS 'MasterFingerprint', ZADDITIONALASSETATTRIBUTES.ZADJUSTEDFINGERPRINT AS 'AdjustedFingerprint' FROM ZASSET LEFT JOIN ZMOMENT ON ZASSET.ZMOMENT = ZMOMENT.Z_PK JOIN ZADDITIONALASSETATTRIBUTES ON ZASSET.ZADDITIONALATTRIBUTES = ZADDITIONALASSETATTRIBUTES.Z_PK LEFT JOIN ZDETECTEDFACE ON ZADDITIONALASSETATTRIBUTES.ZASSET = ZDETECTEDFACE.ZASSET LEFT JOIN ZPERSON ON ZPERSON.Z_PK = ZDETECTEDFACE.ZPERSON LEFT JOIN Z_26ASSETS ON ZASSET.Z_PK = Z_26ASSETS.Z_3ASSETS LEFT JOIN ZGENERICALBUM ON ZGENERICALBUM.Z_PK = Z_26ASSETS.Z_26ALBUMS """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] counter = 0 if usageentries > 0: for row in all_rows: postal_address = '' postal_address_subadminarea = '' postal_address_sublocality = '' if row[61] is not None: pathto = os.path.join( report_folder, 'ReverseLocationData' + str(counter) + '.bplist') with open(pathto, 'ab') as wf: wf.write(row[61]) with open(pathto, 'rb') as f: try: deserialized_plist = nd.deserialize_plist(f) postal_address = deserialized_plist[ 'postalAddress']['_formattedAddress'] postal_address_subadminarea = deserialized_plist[ 'postalAddress']['_subAdministrativeArea'] postal_address_sublocality = deserialized_plist[ 'postalAddress']['_subLocality'] except: logfunc( 'Error reading exported bplist from Asset PK' + row[0]) deserialized_plist = None data_list.append( (row[0], row[0], postal_address, postal_address_subadminarea, postal_address_sublocality, row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27], row[28], row[29], row[30], row[31], row[32], row[33], row[34], row[35], row[36], row[37], row[38], row[39], row[40], row[41], row[42], row[43], row[44], row[45], row[46], row[47], row[48], row[49], row[50], row[51], row[52], row[53], row[54], row[55], row[56], row[57], row[58], row[59], row[60], row[61], row[62], row[63], row[64], row[65], row[66], row[67], row[68], row[69], row[70], row[71], row[72], row[73], row[74], row[75], row[76], row[77], row[78], row[79], row[80], row[81])) counter += 1 description = '' report = ArtifactHtmlReport('Photos.sqlite') report.start_artifact_report(report_folder, 'Metadata', description) report.add_script() data_headers = ( 'Timestamp', 'Date Created', 'Postal Address', 'Postal Subadmin Area', 'Postal Sublocality', 'Asset ZPK', 'Add Attributes Key', 'Detected Face Asset', 'Kind', 'EXIF Timestamp', 'Scene Analysis Timestamp', 'Analysis State Modified Date', 'Add Date', 'Filename', 'Original Filename', 'Album Title', 'Creator Bundle ID', 'Editor Bundle ID', 'Directory', 'Uniform ID', 'Saved Asset Type', 'Face Detected in Photo', 'Display Name', 'Full Name', 'Face Count', 'Person', 'Contact Blob', 'Person UUID', 'Detected Face Quality', 'Age Type Estimate', 'Gender', 'Glasses Type', 'Facial Hair Type', 'Baldness', 'Color Space', 'Duration', 'Video Duration', 'Complete', 'Visibility State', 'Favorite', 'Hidden Fie?', 'Trash State', 'File Trash Date', 'View Count', 'Play Count', 'Share Count', 'Last Shared Date', 'File Modification Date', 'Has Adjustments?', 'Adjustment Timestamp', 'Original File Size', 'File Height', 'Org File Height', 'File Width', 'Org File Width', 'Orientation', 'Org Orientation', 'Timezone Name', 'Timezone Offset', 'Infered Timezone Offset', 'File Location Data', 'Latitude', 'Longitude', 'Shifted Location Valid', 'Reverse Lcoation Data is Valid', 'Org File Reverse Location Data', 'Thumbnail Index', 'Embedded Thumbnail Width', 'Embedded Thumbnail Height', 'Embedded Thumbnail Offset', 'Embedded Thumbnail Lenght', 'Moment PK', 'Moment Title', 'Moment Start Date', 'Moment Representative Date', 'Moment Modification Date', 'Moment End Date', 'Moment Trash State', 'Moment Approx Latitude', 'Moment Approx Longitude', 'UUID', 'Media Group UUID', 'Cloud Assest GUID', 'Public Global UUID', 'Master Fingetprint', 'Adjusted Fingerprint') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Photos-sqlite Metadata' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Photos-sqlite Metadata' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Photos-sqlite Metadata' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for Photos.sqlite metadata') db.close() return
def get_mapsSync(files_found, report_folder, seeker): for file_found in files_found: file_found = str(file_found) if not file_found.endswith('MapsSync_0.0.1'): continue # Skip all other files db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(ZHISTORYITEM.ZCREATETIME+978307200,'UNIXEPOCH','localtime') AS 'Time Created', datetime(ZHISTORYITEM.ZMODIFICATIONTIME+978307200,'UNIXEPOCH','localtime') AS 'Time Modified', ZHISTORYITEM.z_pk AS 'Item Number', CASE when ZHISTORYITEM.z_ent = 14 then 'coordinates of search' when ZHISTORYITEM.z_ent = 16 then 'location search' when ZHISTORYITEM.z_ent = 12 then 'navigation journey' end AS 'Type', ZHISTORYITEM.ZQUERY AS 'Location Search', ZHISTORYITEM.ZLOCATIONDISPLAY AS 'Location City', ZHISTORYITEM.ZLATITUDE AS 'Latitude', ZHISTORYITEM.ZLONGITUDE AS 'Longitude', ZHISTORYITEM.ZROUTEREQUESTSTORAGE AS 'Journey BLOB', ZMIXINMAPITEM.ZMAPITEMSTORAGE as 'Map Item Storage BLOB' from ZHISTORYITEM left join ZMIXINMAPITEM on ZMIXINMAPITEM.Z_PK=ZHISTORYITEM.ZMAPITEM ''') # Above query courtesy of CheekyForensicsMonkey # https://cheeky4n6monkey.blogspot.com/2020/11/ios14-maps-history-blob-script.html all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] for row in all_rows: #print(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]) directa = '' directb = '' mapitem = '' agg1 = '' if row[8] is None: pass #pp = pprint.PrettyPrinter(indent = 1) #pp.pprint(message) else: message, types = blackboxprotobuf.decode_message(row[8]) for x in message['1']: for y in x['2']['1']['4']: z = y.get('8') if z == None: pass else: if isinstance(z, dict): w = z.get('31') if w == None: pass else: three = get_recursively(w, '3') if three[1] == b'create': #print(f'Three: {three[1]}') if message['1'][1]['1'].get( '2') is not None: for address in ( message['1'][1]['1'] ['2']['6']): directa = directa + ' ' + ( address.decode( 'latin-1')) #print(row[0],directa, 'directa') if agg1 == '': agg1 = directa directa = '' else: agg1 = agg1 + ' <---> ' + directa else: for address in (w['1']['101'] ['2']['11']): directa = directa + ' ' + ( address.decode( 'latin-1')) #print(row[0], directb, 'directb') if agg1 == '': agg1 = directa directa = '' else: agg1 = agg1 + ' <---> ' + directa if row[9] is None: pass else: message, types = blackboxprotobuf.decode_message(row[9]) #pp = pprint.PrettyPrinter(indent = 1) #pp.pprint(message['1']['4'])#[7]['8']['31']['1']['101']['2']['11']) get101 = (get_recursively(message, '101')) for address in (get101[0]['2']['11']): mapitem = mapitem + ' ' + (address.decode('latin-1')) data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], agg1, mapitem)) agg1 = '' if usageentries > 0: description = 'Disclaimer: Entries should be corroborated. Locations and searches from other linked devices might show up here. Travel should be confirmed. Medium confidence.' report = ArtifactHtmlReport('MapsSync') report.start_artifact_report(report_folder, 'MapsSync', description) report.add_script() data_headers = ('Timestamp', 'Modified Time', 'Item Number', 'Type', 'Location Search', 'Location City', 'Latitude', 'Longitude', 'Journey BLOB Item', 'Map Item Storage BLOB item') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'MapsSync' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'MapsSync' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'MapsSync' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No MapsSync data available')
def get_locationDappharvest(files_found, report_folder, seeker): file_found = str(files_found[0]) #os.chmod(file_found, 0o0777) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("11"): logfunc("Unsupported version for LocationD App Harvest on iOS " + iOSversion) return () db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_appharvest.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", BUNDLEID AS "BUNDLE ID", LATITUDE || ", " || LONGITUDE AS "COORDINATES", ALTITUDE AS "ALTITUDE", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", STATE AS "STATE", AGE AS "AGE", ROUTINEMODE AS "ROUTINE MODE", LOCATIONOFINTERESTTYPE AS "LOCATION OF INTEREST TYPE", HEX(SIG) AS "SIG (HEX)", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE" FROM APPHARVEST """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15])) description = '' report = ArtifactHtmlReport('LocationD App Harvest') report.start_artifact_report(report_folder, 'App Harvest', description) report.add_script() data_headers = ('Timestamp', 'Bundle ID', 'Coordinates', 'Altitude', 'Horizontal Accuracy', 'Vertical Accuracy', 'State', 'Age', 'Routine Mode', 'Location of Interest Type', 'Sig (HEX)', 'Latitude', 'Longitude', 'Speed', 'Course', 'Confidence') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD Cell App Harvest' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD Cell App Harvest' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD Cell App Harvest' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD App Harvest') db.close() return
def get_teamsSegment(files_found, report_folder, seeker): data_list_location = [] data_list_motion = [] data_list_timecheck = [] data_list_power = [] data_list_statechange = [] for file_found in files_found: with open(file_found) as file: for line in file: serial = json.loads(line) timestamp = serial[0].replace('T', ' ') #print(serial[1]) if serial[1] == 'location': locationtimestamp = serial[2]['sourceTimestamp'] locationtimestamp = locationtimestamp.replace('T', ' ') longitude = serial[2]['longitude'] latitude = serial[2]['latitude'] speed = serial[2]['speed'] altitude = serial[2]['altitude'] vertacc = serial[2]['verticalAccuracy'] horiacc = serial[2]['horizontalAccuracy'] data_list_location.append( (locationtimestamp, longitude, latitude, speed, altitude, vertacc, horiacc)) if serial[1] == 'motion': motionact = (serial[2]['activityName']) data_list_motion.append((timestamp, motionact)) if serial[1] == 'timeCheck': tczone = serial[2]['timezone'] tcoffset = serial[2]['offset'] tcreason = serial[2]['reason'] data_list_timecheck.append( (timestamp, tczone, tcoffset, tcreason)) if serial[1] == 'power': plugged = serial[2]['isPluggedIn'] batlvl = serial[2]['batteryLevel'] data_list_power.append((timestamp, plugged, batlvl)) if serial[1] == 'stateChange': agg = ' ' for a, b in serial[2].items(): agg = agg + (f'{a}: {b} ') agg = agg.lstrip() data_list_statechange.append((timestamp, agg)) if len(data_list_location) > 0: report = ArtifactHtmlReport('Microsoft Teams Locations') report.start_artifact_report(report_folder, 'Teams Locations') report.add_script() data_headers_location = ('Timestamp', 'Longitude', 'Latitude', 'Speed', 'Altitude', 'Vertical Accuracy', 'Horizontal Accuracy') report.write_artifact_data_table(data_headers_location, data_list_location, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Locations' tsv(report_folder, data_headers_location, data_list_location, tsvname) tlactivity = 'Microsoft Teams Locations' timeline(report_folder, tlactivity, data_list_location, data_headers_location) kmlactivity = 'Microsoft Teams Locations' kmlgen(report_folder, kmlactivity, data_list_location, data_headers_location) else: logfunc('No Microsoft Teams Locations data') if len(data_list_motion) > 0: report = ArtifactHtmlReport('Microsoft Teams Motion') report.start_artifact_report(report_folder, 'Teams Motion') report.add_script() data_headers_motion = ('Timestamp', 'Activity') report.write_artifact_data_table(data_headers_motion, data_list_motion, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Motion' tsv(report_folder, data_headers_motion, data_list_motion, tsvname) tlactivity = 'Microsoft Teams Motion' timeline(report_folder, tlactivity, data_list_motion, data_headers_motion) else: logfunc('No Microsoft Teams Motion data') if len(data_list_timecheck) > 0: report = ArtifactHtmlReport('Microsoft Teams Timezone') report.start_artifact_report(report_folder, 'Teams Timezone') report.add_script() data_headers_timecheck = ('Timestamp', 'Timezone', 'Timezone Offset', 'Timezone reason') report.write_artifact_data_table(data_headers_timecheck, data_list_timecheck, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Timezone' tsv(report_folder, data_headers_timecheck, data_list_timecheck, tsvname) tlactivity = 'Microsoft Teams Timezone' timeline(report_folder, tlactivity, data_list_timecheck, data_headers_timecheck) else: logfunc('No Microsoft Teams Timezone data') if len(data_list_power) > 0: report = ArtifactHtmlReport('Microsoft Teams Power Log') report.start_artifact_report(report_folder, 'Teams Power Log') report.add_script() data_headers_power = ('Timestamp', 'Is plugged in?', 'Battery Level') report.write_artifact_data_table(data_headers_power, data_list_power, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Power Log' tsv(report_folder, data_headers_power, data_list_power, tsvname) tlactivity = 'Microsoft Teams Power Log' timeline(report_folder, tlactivity, data_list_power, data_headers_power) else: logfunc('No Microsoft Teams Power Log data') if len(data_list_statechange) > 0: report = ArtifactHtmlReport('Microsoft Teams State Change') report.start_artifact_report(report_folder, 'Teams State Change') report.add_script() data_headers_statechange = ('Timestamp', 'Change') report.write_artifact_data_table(data_headers_statechange, data_list_statechange, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams State Change' tsv(report_folder, data_headers_statechange, data_list_statechange, tsvname) tlactivity = 'Microsoft Teams State Change' timeline(report_folder, tlactivity, data_list_statechange, data_headers_statechange) else: logfunc('No Microsoft Teams Power State Change')
def get_routineDCloud(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf for file_found in files_found: file_found = str(file_found) if file_found.endswith('Cloud-V2.sqlite'): break db = sqlite3.connect(file_found) cursor = db.cursor() if version.parse(iOSversion) >= version.parse("12"): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_cloud_visit_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTADDRESSMO.ZCREATIONDATE + 978307200, 'unixepoch') AS "ADDRESS CREATION DATE", DATETIME(ZRTADDRESSMO.ZEXPIRATIONDATE + 978307200, 'unixepoch') AS "ADDRESS EXPIRATION DATE", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", ZRTADDRESSMO.ZAREASOFINTEREST AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTADDRESSMO.Z_PK AS "ZRTADDRESSMO TABLE ID" FROM ZRTADDRESSMO ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14])) description = 'Address' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Addresses', description) report.add_script() data_headers = ('Address Creation Date', 'Address Expiration Date', 'Country', 'Country Code', 'Postal Code', 'Locality', 'Sublocality', 'Throroughfare', 'Subthroroughfare', 'Subadministrative Area', 'Area of Interest', 'Ocean', 'Inland Water', 'Island', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Addresses' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Addresses' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No RoutineD Cloud Addresses data available') if version.parse(iOSversion) >= version.parse("13"): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_cloud_visit_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTMAPITEMMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION DATE", DATETIME(ZRTMAPITEMMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM EXPIRATION DATE", ZRTMAPITEMMO.ZLATITUDE || ", " || ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM COORDINATES", ZRTMAPITEMMO.ZNAME AS "MAP ITEM NAME", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", ZRTADDRESSMO.ZAREASOFINTEREST AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTMAPITEMMO.ZLATITUDE AS "LATITUTE", ZRTMAPITEMMO.ZLONGITUDE AS "LONGITUDE", ZRTMAPITEMMO.ZUNCERTAINTY AS "UNCERTAINTY", ZRTMAPITEMMO.ZDISPLAYLANGUAGE AS "MAP ITEM LANGUAGE", ZRTMAPITEMMO.Z_PK AS "ZRTMAPITEMMO TABLE ID" FROM ZRTMAPITEMMO LEFT JOIN ZRTADDRESSMO ON ZRTMAPITEMMO.Z_PK == ZRTADDRESSMO.ZMAPITEM ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20])) description = 'RoutineD Cloud Map Items' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Map Items', description) report.add_script() data_headers = ('Timestamp', 'Map Item Expiration Date', 'Map Item Coordinates', 'Map Item Name', 'Country', 'Country Code', 'Postal Code', 'Locality', 'Sublocality', 'Throroughfare', 'Subthroroughfare', 'Subadministrative Area', 'Area of Interest', 'Ocean', 'Inland Water', 'Island', 'Latitude', 'Longitude', 'Uncertainty', 'Map Item Language', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Map Items' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Map Items' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Cloud Map Items' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD Map Items Cloud-V2.sqlite data available') if (version.parse(iOSversion) >= version.parse("12")) and ( version.parse(iOSversion) < version.parse("13")): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_cloud_visit_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTMAPITEMMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION DATE", DATETIME(ZRTMAPITEMMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM EXPIRATION DATE", ZRTMAPITEMMO.ZLATITUDE || ", " || ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM COORDINATES", ZRTMAPITEMMO.ZNAME AS "MAP ITEM NAME", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", ZRTADDRESSMO.ZAREASOFINTEREST AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTMAPITEMMO.ZLATITUDE AS "LATITUTE", ZRTMAPITEMMO.ZLONGITUDE AS "LONGITUDE", ZRTMAPITEMMO.ZUNCERTAINTY AS "UNCERTAINTY", ZRTMAPITEMMO.Z_PK AS "ZRTMAPITEMMO TABLE ID" FROM ZRTMAPITEMMO LEFT JOIN ZRTADDRESSMO ON ZRTMAPITEMMO.Z_PK == ZRTADDRESSMO.ZMAPITEM ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[14], row[15], row[16], row[17], row[18], row[19])) description = 'RoutineD Cloud Map Items' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Map Items', description) report.add_script() data_headers = ('Timestamp', 'Map Item Expiration Date', 'Map Item Coordinates', 'Map Item Name', 'Country', 'Country Code', 'Postal Code', 'Locality', 'Sublocality', 'Throroughfare', 'Subthroroughfare', 'Subadministrative Area', 'Area of Interest', 'Ocean', 'Inland Water', 'Island', 'Latitude', 'Longitude', 'Uncertainty', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Map Items' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Map Items' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Cloud Map Items' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD Cloud Map Items data available') if (version.parse(iOSversion) >= version.parse("13")): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_cloud_visit_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", (ZEXITDATE-ZENTRYDATE)/60.00 AS "VISIT TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", ZRTADDRESSMO.ZAREASOFINTEREST AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTADDRESSMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "ADDRESS CREATION", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTMAPITEMMO.ZLATITUDE || ", " || ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM COORDINATES", DATETIME(ZRTMAPITEMMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION DATE", DATETIME(ZRTMAPITEMMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM EXPIRATION DATE", ZRTMAPITEMMO.ZLATITUDE AS "MAP ITEM LATITUTE", ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM LONGITUDE", ZRTMAPITEMMO.ZUNCERTAINTY AS "UNCERTAINTY", ZRTMAPITEMMO.ZDISPLAYLANGUAGE AS "MAP ITEM LANGUAGE", ZRTMAPITEMMO.ZNAME AS "MAP ITEM NAME", ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE LEFT JOIN ZRTADDRESSMO ON ZRTADDRESSMO.ZMAPITEM = ZRTLEARNEDPLACEMO.ZMAPITEM LEFT JOIN ZRTMAPITEMMO ON ZRTMAPITEMMO.Z_PK = ZRTLEARNEDPLACEMO.ZMAPITEM ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27], row[28], row[29], row[30], row[31], row[32], row[33], row[34], row[35], row[36], row[37], row[38])) description = 'Significant Locations - Vist Entry & Exit (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Visit Entry Exit', description) report.add_script() data_headers = ('Timestamp', 'Visit Exit', 'Visit Time (Minutes)', 'Coordinates', 'Place ID', 'Data Point Count', 'Country', 'Country Code', 'Postal Code', 'Locality', 'Sublocality', 'Throroughfare', 'Subthroroughfare', 'Subadministrative Area', 'Area of Interest', 'Ocean', 'Inland Water', 'Island', 'Location Uncertainty', 'Confidence', 'Visit Creation', 'Visit Expiration', 'Device Class', 'Device Model', 'Device Name', 'Learned Placed Creation', 'Learned Place Expiration', 'Address Creation', 'Latitude', 'Longitude', 'Map Item Coordinates', 'Map Item Creation Date', 'Map Item Expiration Date', 'Map Item Latitude', 'Map Item Longitude', 'Uncertainty', 'Map Item Language', 'Map Item Name', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Visit Entry Exit' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Visit Entry Exit' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Cloud Visit Entry Exit' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations - Vist Entry & Exit (Historical)' )
def get_viber(files_found, report_folder, seeker): viber_settings = {} for file_found in files_found: file_found = str(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("14"): logfunc("Viber parsing has not be tested on this iOS " + iOSversion + " version. Please contact @theAtropos4n6 for resolving this issue.") if version.parse(iOSversion) >= version.parse("14"): if file_found.endswith('Settings.data'): db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT Data.key,value FROM Data WHERE Data.key IN ( '_myUserName', '_currentEmail', '_myPhoneNumber', '_myCanonizedPhoneNumber', '_myFormattedPhoneNumber', '_myCountryPhoneCode', '_myCountryCode', '_myLanguageCode', '_wasabiLastKnownUserLocation', '_uid', '_appVersion', '_savedDeviceId', '_attemptsToDownloadBackupForRestore', '_backupAttemptsCount', '_hiddenChatsPINData', '_myPhotoLocalID' ) UNION SELECT Data.key, CASE WHEN value LIKE '-%' THEN value ELSE datetime(value,'unixepoch') END FROM Data WHERE Data.key IN ('_registrationDate', '_autoBackupLastRunTime', '_lastBackupStartDate') UNION SELECT Data.key, datetime(value,'unixepoch') -- this value is stored in the user localtime FROM Data WHERE Data.key IS '_birthdate' ORDER BY value ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list =[] for row in all_rows: viber_settings[row[0]] = row[1] temp_list = list(row) if temp_list[0] == '_appVersion': temp_list[0] = 'Application Version' elif temp_list[0] == '_lastBackupStartDate': temp_list[0] = 'Last Backup Start Date - UTC' elif temp_list[0] == '_myUserName': temp_list[0] = 'User Name' elif temp_list[0] == '_currentEmail': temp_list[0] = 'Current Email' elif temp_list[0] == '_birthdate': temp_list[0] = "Birth Date - UTC (apply user's localtime offset)" elif temp_list[0] == '_registrationDate': temp_list[0] = 'Registration Date - UTC' elif temp_list[0] == '_uid': temp_list[0] = 'User ID' elif temp_list[0] == '_myPhoneNumber': temp_list[0] = 'Phone Number' elif temp_list[0] == '_myCanonizedPhoneNumber': temp_list[0] = 'Canonized Phone Number' elif temp_list[0] == '_myFormattedPhoneNumber': temp_list[0] = 'Formatted Phone Number' elif temp_list[0] == '_myCountryPhoneCode': temp_list[0] = 'Country Phone Code' elif temp_list[0] == '_myCountryCode': temp_list[0] = 'Country Code' elif temp_list[0] == '_myLanguageCode': temp_list[0] = 'Language Code' elif temp_list[0] == '_wasabiLastKnownUserLocation': temp_list[0] = 'Last Known User Location' elif temp_list[0] == '_savedDeviceId': temp_list[0] = 'Device ID' elif temp_list[0] == '_myPhotoLocalID': temp_list[0] = 'Profile Picture' try: if temp_list[1] is not None: thumb = media_to_html(temp_list[1], files_found, report_folder) temp_list[1] = thumb else: thumb = '' except: pass elif temp_list[0] == '_attemptsToDownloadBackupForRestore': temp_list[0] = 'Attempts To Download Backup For Restore' try: int.from_bytes(temp_list[1], byteorder='big') #needs further validation about the byteorder except Exception as err: logfunc(f'Viber - Settings "_attemptsToDownloadBackupForRestore" could not be extracted. The error was: {err}' ) elif temp_list[0] == '_backupAttemptsCount': temp_list[0] = 'Backup Attempts Count' try: int.from_bytes(temp_list[1], byteorder='big') #needs further validation about the byteorder except Exception as err: logfunc(f'Viber - Settings "_backupAttemptsCount" could not be extracted. The error was: {err}' ) elif temp_list[0] == '_autoBackupLastRunTime': temp_list[0] = 'Auto Backup Last Run Time - UTC' x = str(temp_list[1]) if x.startswith("-"): temp_list[1] = 'Not Applied' elif temp_list[0] == '_lastBackupStartDate': x = str(temp_list[1]) if x.startswith("-"): temp_list[1] = 'Not Applied' elif temp_list[0] == '_hiddenChatsPINData': temp_list[0] = 'Hidden Chats PIN Data' row = tuple(temp_list) data_list.append((row[0], row[1])) if usageentries > 0: report = ArtifactHtmlReport('Viber - Settings') report.start_artifact_report(report_folder, 'Viber - Settings') report.add_script() data_headers = ('Setting','Value') report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() tsvname = 'Viber - Settings' tsv(report_folder, data_headers, data_list, tsvname) db.close() elif file_found.endswith('Contacts.data'): db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT ZABCONTACT.ZMAINNAME AS 'Main Name', ZABCONTACT.ZPREFIXNAME AS 'Prefix Name', ZABCONTACT.ZSUFFIXNAME AS 'Suffix Name', ZABCONTACTNUMBER.ZPHONE AS 'Phone Number', ZABCONTACTNUMBER.ZCANONIZEDPHONENUM AS 'Canonized Phone Number', ZABCONTACT.ZCONTACTID AS 'Contact ID' FROM ZABCONTACT LEFT JOIN ZABCONTACTNUMBER ON ZABCONTACT.Z_PK = ZABCONTACTNUMBER.ZCONTACT UNION SELECT ZABCONTACT.ZMAINNAME AS 'Main Name', ZABCONTACT.ZPREFIXNAME AS 'Prefix Name', ZABCONTACT.ZSUFFIXNAME AS 'Suffix Name', ZABCONTACTNUMBER.ZPHONE AS 'Phone Number', ZABCONTACTNUMBER.ZCANONIZEDPHONENUM AS 'Canonized Phone Number', ZABCONTACT.ZCONTACTID AS 'Contact ID' FROM ZABCONTACTNUMBER LEFT JOIN ZABCONTACT ON ZABCONTACT.Z_PK = ZABCONTACTNUMBER.ZCONTACT ORDER BY ZMAINNAME ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list =[] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5])) if usageentries > 0: report = ArtifactHtmlReport('Viber - Contacts') report.start_artifact_report(report_folder, 'Viber - Contacts') report.add_script() data_headers = ('Main Name','Prefix Name','Suffix Name','Phone Number','Canonized Phone Number','Contact ID') report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() tsvname = 'Viber - Contacts' tsv(report_folder, data_headers, data_list, tsvname) cursor.execute(''' SELECT datetime(ZRECENT.ZDATE+ 978307200,'unixepoch') AS 'Timestamp - UTC', ZRECENT.ZRECENTSLINE AS 'EMPTY DUMMY COLUMN', CASE WHEN ZRECENT.ZCALLTYPE = 'missed' THEN 'Missed Audio Call' WHEN ZRECENT.ZCALLTYPE = 'missed_with_video' THEN 'Missed Video Call' WHEN ZRECENT.ZCALLTYPE = 'outgoing_viber' THEN 'Outgoing Audio Call' WHEN ZRECENT.ZCALLTYPE = 'outgoing_viber_with_video' THEN 'Outgoing Video Call' WHEN ZRECENT.ZCALLTYPE = 'incoming_with_video' THEN 'Incoming Video Call' WHEN ZRECENT.ZCALLTYPE = 'incoming' THEN 'Incoming Audio Call' ELSE ZRECENT.ZCALLTYPE end AS 'Call Type', ZRECENT.ZDURATION AS 'Duration' FROM ZRECENT WHERE ZRECENT.ZCALLLOGMESSAGE IS NULL AND ZRECENT.ZRECENTSLINE IS NULL ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list =[] for row in all_rows: temp_list = list(row) try: if 'Outgoing' in temp_list[2]: temp_list[1] = str(viber_settings['_myUserName']) + ',' + str(viber_settings['_myPhoneNumber']) except TypeError: pass row = tuple(temp_list) data_list.append((row[0], row[1], row[2], row[3])) if usageentries > 0: report = ArtifactHtmlReport('Viber - Call Remnants') report.start_artifact_report(report_folder, 'Viber - Call Remnants') report.add_script() data_headers = ('Timestamp - UTC','Caller','Call Type','Duration') report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() cursor.execute(''' SELECT CHAT_MEMBER.ZDISPLAYFULLNAME AS 'Sender (Display Full Name)', CHAT_MEMBER.ZDISPLAYSHORTNAME AS 'Sender (Display Short Name)', CHAT_MEMBER.ZPHONE AS 'Sender (Phone)', CHATS.Chat_Name AS 'Chat Name', CHATS.CHAT_MEMBERS AS 'Chat Participant(s)', CHATS.CHAT_PHONES 'Chat Phone(s)', datetime(ZVIBERMESSAGE.ZSTATEDATE+ 978307200,'unixepoch') AS 'Message Creation Date - UTC', datetime(ZVIBERMESSAGE.ZDATE+ 978307200,'unixepoch') AS 'Message Change State Date - UTC', datetime(RECENT.ZRECENTDATE+ 978307200,'unixepoch') AS 'Call Date - UTC', CASE WHEN ZCALLTYPE = 'missed' THEN 'Missed Audio Call' WHEN ZCALLTYPE = 'missed_with_video' THEN 'Missed Video Call' WHEN ZCALLTYPE = 'outgoing_viber' THEN 'Outgoing Audio Call' WHEN ZCALLTYPE = 'outgoing_viber_with_video' THEN 'Outgoing Video Call' WHEN ZCALLTYPE = 'incoming_with_video' THEN 'Incoming Video Call' WHEN ZCALLTYPE = 'incoming' THEN 'Incoming Audio Call' ELSE ZCALLTYPE end AS 'Call Type', CASE WHEN ZVIBERMESSAGE.ZSTATE IN ('send','delivered') THEN 'Outgoing' WHEN ZVIBERMESSAGE.ZSTATE = 'received' THEN 'Incoming' ELSE ZVIBERMESSAGE.ZSTATE END AS 'State', RECENT.ZDURATION AS 'Duration', ZVIBERMESSAGE.ZSYSTEMTYPE 'System Type Description', ZVIBERMESSAGE.ZMETADATA AS 'Message Metadata', ZVIBERMESSAGE.ZTEXT AS 'Message Content', ZATTACHMENT.ZNAME AS 'Attachment Name', ZATTACHMENT.ZTYPE AS 'Attachment Type', ZATTACHMENT.ZFILESIZE AS 'Attachment Size', ZVIBERLOCATION.ZLATITUDE AS 'Latitude', ZVIBERLOCATION.ZLONGITUDE AS 'Longitude', CASE WHEN CHATS.Chat_Deleted = 1 THEN 'True' WHEN CHATS.Chat_Deleted = 0 THEN 'False' ELSE CHATS.Chat_Deleted END AS 'Conversation Deleted', CASE WHEN ZVIBERMESSAGE.ZBEINGDELETED = 1 THEN 'True' WHEN ZVIBERMESSAGE.ZBEINGDELETED = 0 THEN 'False' ELSE ZVIBERMESSAGE.ZBEINGDELETED END AS 'Message Deleted', CHATS.ZTIMEBOMBDURATION AS 'Conversation Time Bomb Duration', ZVIBERMESSAGE.ZTIMEBOMBDURATION AS 'Message Time Bomb Duration', datetime(ZVIBERMESSAGE.ZTIMEBOMBTIMESTAMP+ 978307200,'unixepoch') AS 'Message Time Bomb Timestamp', CASE WHEN CHATS.Chat_Favorite= 1 THEN 'True' WHEN CHATS.Chat_Favorite = 0 THEN 'False' ELSE CHATS.Chat_Favorite END AS 'Conversation Marked Favorite', ZVIBERMESSAGE.ZLIKESCOUNT AS 'Likes Count' FROM ZVIBERMESSAGE LEFT JOIN (SELECT ZVIBERMESSAGE.ZCONVERSATION, ZCONVERSATION.ZNAME AS 'Chat_Name', ZCONVERSATION.ZBEINGDELETED AS 'Chat_Deleted', ZCONVERSATION.ZFAVORITE AS 'Chat_Favorite', ZCONVERSATION.ZTIMEBOMBDURATION, coalesce(ZVIBERMESSAGE.ZPHONENUMINDEX,ZCONVERSATION.ZINTERLOCUTOR) AS 'MEMBER_ID', MEMBER.ZDISPLAYFULLNAME, MEMBER.ZDISPLAYSHORTNAME, MEMBER.ZNAME AS 'Participant_Name', MEMBER.ZCANONIZEDPHONENUM, MEMBER.ZPHONE, group_concat(DISTINCT(MEMBER.ZDISPLAYFULLNAME)) AS 'CHAT_MEMBERS', group_concat(DISTINCT(MEMBER.ZPHONE)) AS 'CHAT_PHONES', group_concat(DISTINCT(MEMBER.ZCANONIZEDPHONENUM)) AS 'CHAT_CANONIZED_PHONES' FROM ZVIBERMESSAGE,ZCONVERSATION LEFT JOIN (SELECT ZMEMBER.ZDISPLAYFULLNAME, ZMEMBER.ZDISPLAYSHORTNAME, ZMEMBER.ZNAME, ZPHONENUMBER.ZCANONIZEDPHONENUM, ZPHONENUMBER.ZPHONE, ZMEMBER.Z_PK FROM ZMEMBER LEFT JOIN ZPHONENUMBER ON ZMEMBER.Z_PK = ZPHONENUMBER.ZMEMBER UNION SELECT ZMEMBER.ZDISPLAYFULLNAME, ZMEMBER.ZDISPLAYSHORTNAME, ZMEMBER.ZNAME, ZPHONENUMBER.ZCANONIZEDPHONENUM, ZPHONENUMBER.ZPHONE, ZMEMBER.Z_PK FROM ZPHONENUMBER LEFT JOIN ZMEMBER ON ZPHONENUMBER.ZMEMBER = ZMEMBER.Z_PK ) AS MEMBER ON MEMBER.Z_PK = MEMBER_ID LEFT JOIN ZPHONENUMBER ON MEMBER_ID = ZPHONENUMBER.ZMEMBER WHERE ZVIBERMESSAGE.ZCONVERSATION = ZCONVERSATION.Z_PK GROUP BY ZVIBERMESSAGE.ZCONVERSATION ) CHATS ON ZVIBERMESSAGE.ZCONVERSATION = CHATS.ZCONVERSATION LEFT JOIN (SELECT ZMEMBER.ZDISPLAYFULLNAME, ZMEMBER.ZDISPLAYSHORTNAME, ZMEMBER.ZNAME, ZPHONENUMBER.ZCANONIZEDPHONENUM, ZPHONENUMBER.ZPHONE, ZMEMBER.Z_PK FROM ZMEMBER LEFT JOIN ZPHONENUMBER ON ZMEMBER.Z_PK = ZPHONENUMBER.ZMEMBER UNION SELECT ZMEMBER.ZDISPLAYFULLNAME, ZMEMBER.ZDISPLAYSHORTNAME, ZMEMBER.ZNAME, ZPHONENUMBER.ZCANONIZEDPHONENUM, ZPHONENUMBER.ZPHONE, ZMEMBER.Z_PK FROM ZPHONENUMBER LEFT JOIN ZMEMBER ON ZPHONENUMBER.ZMEMBER = ZMEMBER.Z_PK ) AS CHAT_MEMBER ON ZVIBERMESSAGE.ZPHONENUMINDEX = CHAT_MEMBER.Z_PK LEFT JOIN (SELECT ZRECENT.ZDURATION, ZRECENT.ZCALLLOGMESSAGE, ZRECENT.ZDATE AS 'ZRECENTDATE', ZRECENTSLINE.ZDATE AS 'ZRECENTSLINEDATE', ZRECENT.ZCALLTYPE AS 'CALL TYPE', ZRECENTSLINE.ZCALLTYPE AS 'CALL TYPE', ZRECENTSLINE.ZPHONENUMBER AS 'PHONE NUMBER' FROM ZRECENT LEFT JOIN ZRECENTSLINE ON ZRECENT.ZRECENTSLINE = ZRECENTSLINE.Z_PK ) AS RECENT ON ZVIBERMESSAGE.Z_PK = RECENT.ZCALLLOGMESSAGE LEFT JOIN ZVIBERLOCATION ON ZVIBERMESSAGE.ZLOCATION = ZVIBERLOCATION.Z_PK LEFT JOIN ZATTACHMENT ON ZVIBERMESSAGE.ZATTACHMENT = ZATTACHMENT.Z_PK ORDER BY ZVIBERMESSAGE.Z_PK ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list =[] for row in all_rows: temp_list = list(row) temp_chats_names = str(temp_list[4]) temp_list[4] = temp_chats_names + ',' + str(viber_settings['_myUserName']) temp_chats_phones = str(temp_list[5]) temp_list[5] = temp_chats_phones + ',' + str(viber_settings['_myPhoneNumber']) if temp_list[13]: y = json.loads(temp_list[13], strict=False) # the key that stores geolocation data is ['pa_message_data']['rich_media']['Buttons'][2]['Map'] #if the 'Map' key is identified successfully it will assign lat,lon to the corresponding columns, otherwise it will continue on (passing any key,index errors) temp_list[13] = '' try: temp_list[18] = y['pa_message_data']['rich_media']['Buttons'][2]['Map']['Latitude'] temp_list[19] = y['pa_message_data']['rich_media']['Buttons'][2]['Map']['Longitude'] except (KeyError,IndexError) as e: pass #What this ugly long list of dict keys simply does is that it extracts only specific fields identified as important from the whole dictionary. #The reason why we extract only specific fields is because the report is much prettier. In order to have a complete picture you will have to go through the whole dictionary #while inspecting the .db itself. Therefore this column is named as 'Message Metadata Fragments' #general values if "Text" in y: try: temp_list[13] += "Text: "+ str(y['Text'])+"," except KeyError: pass if "Title" in y: try: temp_list[13] += "Title: "+ str(y['Title'])+"," except KeyError: pass if "URL" in y: try: temp_list[13] += "URL: "+ str(y['URL'])+"," except KeyError: pass if "ThumbnailURL" in y: try: temp_list[13] += "ThumbnailURL: "+ str(y['ThumbnailURL'])+"," except KeyError: pass if "Type" in y: try: temp_list[13] += "Type: "+ str(y['Type'])+"," except KeyError: pass if "generalFwdInfo" in y: try: temp_list[13] += "Original Chat ID: "+ str(y['generalFwdInfo']['orig_chat_id'])+"," except KeyError: pass if "audio_ptt" in y: try: temp_list[13] += "Audio Duration: "+ str(y['audio_ptt']['duration'])+"," except KeyError: pass #fileInfo values if "fileInfo" in y: try: temp_list[13] += "File Info - Content Type: "+ str(y['fileInfo']['ContentType'])+"," except KeyError: pass try: temp_list[13] += "File Info - Type: "+ str(y['fileInfo']['Type'])+"," except KeyError: pass try: temp_list[13] += "File Info - Hash: "+ str(y['fileInfo']['FileHash'])+"," except KeyError: pass try: temp_list[13] += "File Info - Name: "+ str(y['fileInfo']['FileName'])+"," except KeyError: pass try: temp_list[13] += "File Info - Extension: "+ str(y['fileInfo']['FileExt'])+"," except KeyError: pass try: temp_list[13] += "File Info - Duration: "+ str(y['fileInfo']['Duration'])+"," except KeyError: pass try: temp_list[13] += "File Info - Size: "+ str(y['fileInfo']['FileSize'])+"," except KeyError: pass try: temp_list[13] += "File Info - Original Size: "+ str(y['fileInfo']['OrigSize'])+"," except KeyError: pass try: temp_list[13] += "File|Media Info - iOS Origin: "+ str(y['fileInfo']['mediaInfo']['ios_origin'])+"," except KeyError: pass try: temp_list[13] += "File|Media Info - Width: "+ str(y['fileInfo']['mediaInfo']['Width'])+"," except KeyError: pass try: temp_list[13] += "File|Media Info - Height: "+ str(y['fileInfo']['mediaInfo']['Height'])+"," except KeyError: pass try: temp_list[13] += "File|Media Info - Media Type: "+ str(y['fileInfo']['mediaInfo']['MediaType'])+"," except KeyError: pass #custom_sticker_info values if "custom_sticker_info" in y: try: temp_list[13] += "Custom Sticker Info - Package ID: "+ str(y['custom_sticker_info']['package_id'])+"," except KeyError: pass try: temp_list[13] += "Custom Sticker Info - Sticker ID: "+ str(y['custom_sticker_info']['sticker_id'])+"," except KeyError: pass #groupReferralInfo values if "groupReferralInfo" in y: try: temp_list[13] += "Group ID: "+ str(y['groupReferralInfo']['groupID'])+"," except KeyError: pass try: temp_list[13] += "Group Name: "+ str(y['groupReferralInfo']['groupName'])+"," except KeyError: pass try: temp_list[13] += "Invite Link: "+ str(y['groupReferralInfo']['inviteLink'])+"," except KeyError: pass #pa_message_data values if "pa_message_data" in y: try: temp_list[13] += "Message Data - Text: "+ str(y['pa_message_data']['text'])+"," except KeyError: pass try: temp_list[13] += "Message Data - Sender Name: "+ str(y['pa_message_data']['sender']['name'])+"," except KeyError: pass try: temp_list[13] += "Message Data - Alt Text: "+ str(y['pa_message_data']['alt_text'])+"," except KeyError: pass try: temp_list[13] += "Message Data - Favorites Metadata - URL: "+ str(y['pa_message_data']['rich_media']['FavoritesMetadata']['url'])+"," except KeyError: pass #pin values if "pin" in y: try: temp_list[13] += "Pin - Action: "+ str(y['pin']['action'])+"," except KeyError: pass try: temp_list[13] += "Pin - Text: "+ str(y['pin']['text'])+"," except KeyError: pass try: temp_list[13] += "Pin - Description: "+ str(y['pin']['extended']['descr'])+"," except KeyError: pass #poll values if "poll" in y: try: temp_list[13] += "Poll - Group ID: "+ str(y['poll']['groupID'])+"," except KeyError: pass try: temp_list[13] += "Poll - Type: "+ str(y['poll']['type'])+"," except KeyError: pass try: temp_list[13] += "Poll - Sender ID: "+ str(y['poll']['senderID'])+"," except KeyError: pass try: temp_list[13] += "Poll - Multiple: "+ str(y['poll']['multiple'])+"," except KeyError: pass try: temp_list[13] += "Poll - Quiz Text: "+ str(y['poll']['quiz_text'])+"," except KeyError: pass try: temp_list[13] += "Poll - Description: "+ str(y['poll']['extended']['descr'])+"," except KeyError: pass try: if y['poll']['options']: z = '' for x in y['poll']['options']: try: z = x['count'] temp_list[13] += "Poll - Options - Count: "+ str(z)+"," except (KeyError,IndexError) as e: pass try: z = x['name'] temp_list[13] += "Poll - Options - Name: "+ str(z)+"," except (KeyError,IndexError) as e: pass try: z = x['isLiked'] temp_list[13] += "Poll - Options - Is Liked: "+ str(z)+"," except (KeyError,IndexError) as e: pass except (KeyError,IndexError) as e: pass #quote values if "quote" in y: try: temp_list[13] += "Quote - Text: "+ str(y['quote']['text'])+"," except KeyError: pass try: temp_list[13] += "Quote - Name: "+ str(y['quote']['name'])+"," except KeyError: pass try: temp_list[13] += "Quote - Attachment Name: "+ str(y['quote']['attachmentName'])+"," except KeyError: pass try: temp_list[13] += "Quote - Attachment UID: "+ str(y['quote']['attachmentUID'])+"," except KeyError: pass try: temp_list[13] += "Quote - Attachment Preview Path: "+ str(y['quote']['attachmentPreviewPath'])+"," except KeyError: pass try: temp_list[13] += "Quote - Text Meta Info - Data: "+ y['quote']['textMetaInfo_v2'][0]['data']+"," except (KeyError,IndexError) as e: pass if temp_list[10] == 'Outgoing': temp_list[0] = viber_settings['_myUserName'] temp_list[1] = '' temp_list[2] = viber_settings['_myPhoneNumber'] if row[15] is not None: thumb = media_to_html(row[15], files_found, report_folder) else: thumb = '' row = tuple(temp_list) data_list.append((row[6], row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[14], row[15], thumb, row[8], row[9], row[10], row[11], row[12], row[16], row[17], row[18],row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[13])) if usageentries > 0: report = ArtifactHtmlReport('Viber - Chats') report.start_artifact_report(report_folder, 'Viber - Chats') report.add_script() data_headers = ('Timestamp', 'Sender (Display Full Name)','Sender (Display Short Name)','Sender (Phone)','Chat Name','Chat Participant(s)','Chat Phone(s)', 'Message Creation Date - UTC','Message Change State Date - UTC','Message Content','Attachment Name', 'Attachment','Call Date - UTC','Call Type','State','Duration (Seconds)','System Type Description','Attachment Type','Attachment Size','Latitude','Longitude','Conversation Deleted','Message Deleted', 'Conversation Time Bomb Duration','Message Time Bomb Duration','Message Time Bomb Timestamp - UTC','Conversation Marked Favorite','Likes Count','Message Metadata Fragments') report.write_artifact_data_table(data_headers, data_list, file_found, html_no_escape=['Attachment']) #html_escape=False report.end_artifact_report() kmlactivity = 'Viber - Chats' kmlgen(report_folder, kmlactivity, data_list, data_headers) tsvname = 'Viber - Chats' tsv(report_folder, data_headers, data_list, tsvname) db.close() else: logfunc('No Viber data found.')
def get_teams(files_found, report_folder, seeker): CacheFile = 0 for file_found in files_found: file_found = str(file_found) if file_found.endswith('.sqlite'): databasedata = file_found if file_found.endswith('CacheFile'): CacheFile = file_found if CacheFile != 0: with open(CacheFile, 'rb') as nsfile: nsplist = nd.deserialize_plist(nsfile) db = open_sqlite_db_readonly(databasedata) cursor = db.cursor() cursor.execute(''' SELECT datetime('2001-01-01', "ZARRIVALTIME" || ' seconds'), ZIMDISPLAYNAME, ZCONTENT from ZSMESSAGE ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: thumb = '' if '<div><img src=' in row[2]: matches = re.search('"([^"]+)"', row[2]) imageURL = (matches[0].strip('\"')) if imageURL in nsplist.keys(): data_file_real_path = nsplist[imageURL] for match in files_found: if data_file_real_path in match: shutil.copy2(match, report_folder) data_file_name = os.path.basename(match) thumb = f'<img src="{report_folder}/{data_file_name}"></img>' data_list.append((row[0], row[1], row[2], thumb)) description = 'Teams Messages' report = ArtifactHtmlReport('Teams Messages') report.start_artifact_report(report_folder, 'Teams Messages', description) report.add_script() data_headers = ('Timestamp', 'Name', 'Message', 'Shared Media') report.write_artifact_data_table(data_headers, data_list, file_found, html_no_escape=['Shared Media']) report.end_artifact_report() tsvname = 'Microsoft Teams Messages' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Microsoft Teams Messages' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Microsoft Teams Messages data available') cursor.execute(''' SELECT ZDISPLAYNAME, zemail, ZPHONENUMBER from ZDEVICECONTACTHASH ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2])) description = 'Teams Contact' report = ArtifactHtmlReport('Teams Contact') report.start_artifact_report(report_folder, 'Teams Contact', description) report.add_script() data_headers = ('Display Name', 'Email', 'Phone Number') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Teams Contact' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No Teams Contact data available') cursor.execute(''' SELECT datetime('2001-01-01', "ZTS_LASTSYNCEDAT" || ' seconds'), ZDISPLAYNAME, ZTELEPHONENUMBER from zuser ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2])) description = 'Teams User' report = ArtifactHtmlReport('Teams User') report.start_artifact_report(report_folder, 'Teams User', description) report.add_script() data_headers = ('Timestamp Last Sync', 'Display Name', 'Phone Number') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams User' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Microsoft Teams User' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Teams User data available') cursor.execute(''' SELECT ZCOMPOSETIME, zfrom, ZIMDISPLAYNAME, zcontent, ZPROPERTIES from ZSMESSAGE, ZMESSAGEPROPERTIES where ZSMESSAGE.ZTSID = ZMESSAGEPROPERTIES.ZTSID order by ZCOMPOSETIME ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list_calls = [] data_list_cards = [] data_list_unparsed = [] if usageentries > 0: for row in all_rows: plist = '' composetime = row[0].replace('T', ' ') plist_file_object = io.BytesIO(row[4]) if row[4].find(b'NSKeyedArchiver') == -1: if sys.version_info >= (3, 9): plist = plistlib.load(plist_file_object) else: plist = biplist.readPlist(plist_file_object) else: try: plist = nd.deserialize_plist(plist_file_object) except (nd.DeserializeError, nd.biplist.NotBinaryPlistException, nd.biplist.InvalidPlistException, nd.plistlib.InvalidFileException, nd.ccl_bplist.BplistError, ValueError, TypeError, OSError, OverflowError) as ex: logfunc(f'Failed to read plist for {row[4]}, error was:' + str(ex)) if 'call-log' in plist: datacalls = json.loads(plist['call-log']) callstart = (datacalls.get('startTime')) callstart = callstart.replace('T', ' ') callconnect = (datacalls.get('connectTime')) callconnect = callconnect.replace('T', ' ') callend = (datacalls['endTime']) callend = callend.replace('T', ' ') calldirection = (datacalls['callDirection']) calltype = (datacalls['callType']) callstate = (datacalls['callState']) calloriginator = (datacalls['originator']) calltarget = (datacalls['target']) calloriginatordname = ( datacalls['originatorParticipant']['displayName']) callparticipantdname = ( datacalls['targetParticipant']['displayName']) data_list_calls.append( (composetime, row[1], row[2], row[3], callstart, callconnect, callend, calldirection, calltype, callstate, calloriginator, calltarget, calloriginatordname, callparticipantdname)) elif 'cards' in plist: cards = json.loads(plist['cards']) cardurl = ( cards[0]['content']['body'][0]['selectAction']['url']) cardtitle = ( cards[0]['content']['body'][0]['selectAction']['title']) cardtext = (cards[0]['content']['body'][1]['text']) cardurl2 = (cards[0]['content']['body'][0]['url']) if (cards[0]['content']['body'][0].get('id')) is not None: idcontent = json.loads( cards[0]['content']['body'][0]['id']) cardlat = (idcontent.get('latitude')) cardlong = (idcontent.get('longitude')) cardexpires = (idcontent.get('expiresAt')) cardexpires = datetime.datetime.fromtimestamp(cardexpires / 1000) carddevid = (idcontent.get('deviceId')) data_list_cards.append( (composetime, row[1], row[2], row[3], cardurl, cardtitle, cardtext, cardurl2, cardlat, cardlong, cardexpires, carddevid)) else: data_list_unparsed.append(composetime, row[1], row[2], row[3], plist) description = 'Microsoft Teams Call Logs' report = ArtifactHtmlReport('Microsoft Teams Call Logs') report.start_artifact_report(report_folder, 'Teams Call Logs', description) report.add_script() data_headers = ('Compose Timestamp', 'From', 'Display Name', 'Content', ' Call Start', 'Call Connect', 'Call End', 'Call Direction', 'Call Type', 'Call State', 'Call Originator', 'Call Target', 'Call Originator Name', 'Call Participant Name') report.write_artifact_data_table(data_headers, data_list_calls, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Call Logs' tsv(report_folder, data_headers, data_list_calls, tsvname) tlactivity = 'Microsoft Teams Call Logs' timeline(report_folder, tlactivity, data_list_calls, data_headers) description = 'Microsoft Teams Shared Locations' report = ArtifactHtmlReport('Microsoft Teams Shared Locations') report.start_artifact_report(report_folder, 'Teams Shared Locations', description) report.add_script() data_headers = ('Timestamp', 'From', 'Display Name', 'Content', 'Card URL', 'Card Title', 'Card Text', 'Card URL2', 'Latitude', 'Longitude', 'Card Expires', 'Device ID') report.write_artifact_data_table(data_headers, data_list_cards, file_found) report.end_artifact_report() tsvname = 'Microsoft Teams Shared Locations' tsv(report_folder, data_headers, data_list_cards, tsvname) tlactivity = 'Microsoft Teams Shared Locations' timeline(report_folder, tlactivity, data_list_cards, data_headers) kmlactivity = 'Microsoft Teams Shared Locations' kmlgen(report_folder, kmlactivity, data_list_cards, data_headers) else: logfunc('No Microsoft Teams Call Logs & Cards data available') db.close()
def get_locationDallB(files_found, report_folder, seeker): file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("11"): logfunc("Unsupported version for LocationD App Harvest on iOS " + iOSversion) else: logfunc(iOSversion) cursor = db.cursor() cursor.execute(""" select datetime(timestamp + 978307200,'unixepoch'), bundleid, altitude, horizontalaccuracy, verticalaccuracy, state, age, routinemode, locationofinteresttype, latitude, longitude, speed, course, confidence from appharvest """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = '' report = ArtifactHtmlReport('LocationD App Harvest') report.start_artifact_report(report_folder, 'App Harvest', description) report.add_script() data_headers = ('Timestamp', 'Bundle ID', 'Altitude', 'Horizontal Accuracy', 'Vertical Accuracy', 'State', 'Age', 'Routine Mode', 'Location of Interest Type', 'Latitude', 'Longitude', 'Speed', 'Course', 'Confidence') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD Cell App Harvest' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD Cell App Harvest' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD Cell App Harvest' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD App Harvest') if does_table_exist(db, "cdmacelllocation"): cursor = db.cursor() cursor.execute(""" select datetime(timestamp + 978307200,'unixepoch'), mcc, sid, nid, bsid, zoneid, bandclass, channel, pnoffset, altitude, speed, course, confidence, horizontalaccuracy, verticalaccuracy, latitude, longitude from cdmacelllocation """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16])) description = '' report = ArtifactHtmlReport('LocationD CDMA Location') report.start_artifact_report(report_folder, 'CDMA Location', description) report.add_script() data_headers = ('Timestamp', 'MCC', 'SID', 'NID', 'BSID', 'ZONEID', 'BANDCLASS', 'Channel', 'PNOFFSET', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD CDMA Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD CDMA Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD CDMA Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD CDMA Location') if does_table_exist(db, "celllocation"): cursor = db.cursor() cursor.execute(""" select datetime(timestamp + 978307200,'unixepoch'), mcc, mnc, lac, ci, uarfcn, psc, altitude, speed, course, confidence, horizontalaccuracy, verticalaccuracy, latitude, longitude from celllocation """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14])) description = '' report = ArtifactHtmlReport('LocationD Cell Location') report.start_artifact_report(report_folder, 'Cell Location', description) report.add_script() data_headers = ('Timestamp', 'MCC', 'MNC', 'LAC', 'CI', 'UARFCN', 'PSC', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD Cell Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD Cell Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD Cell Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD Cell Location') if does_table_exist(db, "ltecelllocation"): cursor = db.cursor() cursor.execute(""" select datetime(timestamp + 978307200,'unixepoch'), mcc, mnc, ci, uarfcn, pid, altitude, speed, course, confidence, horizontalaccuracy, verticalaccuracy, latitude, longitude from ltecelllocation """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = '' report = ArtifactHtmlReport('LocationD LTE Location') report.start_artifact_report(report_folder, 'LTE Location', description) report.add_script() data_headers = ('Timestamp', 'MCC', 'MNC', 'CI', 'UARFCN', 'PID', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD LTE Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD LTE Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD LTE Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD LTE Location') cursor = db.cursor() cursor.execute(""" select datetime(timestamp + 978307200,'unixepoch'), mac, channel, infomask, speed, course, confidence, score, reach, horizontalaccuracy, verticalaccuracy, latitude, longitude from wifilocation """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12])) description = '' report = ArtifactHtmlReport('LocationD WiFi Location') report.start_artifact_report(report_folder, 'WiFi Location', description) report.add_script() data_headers = ('Timestamp', 'MAC', 'Channel', 'Infomask', 'Speed', 'Course', 'Confidence', 'Score', 'Reach', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD WiFi Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD WiFi Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD WiFi Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD WiFi Location')
def get_locationDparked(files_found, report_folder, seeker): file_found = str(files_found[0]) db = open_sqlite_db_readonly(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("12"): cursor = db.cursor() cursor.execute(''' select datetime(zrtvehicleeventmo.zdate + 978307200, 'unixepoch'), datetime(zrtvehicleeventmo.zlocdate + 978307200, 'unixepoch'), zvehicleidentifier, zlocuncertainty, zidentifier, zlocationquality, zusersetlocation, zusuallocation, znotes, zphotodata, zloclatitude, zloclongitude from zrtvehicleeventmo ''') else: cursor = db.cursor() cursor.execute(''' select datetime(zrtvehicleeventmo.zdate + 978307200, 'unixepoch'), datetime(zrtvehicleeventmo.zlocdate + 978307200, 'unixepoch'), zvehicleidentifier, zlocuncertainty, zidentifier, zlocationquality, zusersetlocation, zusuallocation, znotes, zgeomapitem, zphotodata, zloclatitude, zloclongitude from zrtvehicleeventmo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("12"): for row in all_rows: data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11])) report = ArtifactHtmlReport('RoutineD Vehicle Location') report.start_artifact_report(report_folder, 'Vehicle Location') report.add_script() data_headers = ('Timestamp','Location Date','Vehicle Identifier', 'Location Identifier', 'Identifier','Location Quality','User Set Location','Usual Location','Notes','Photo Data','Latitude','Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Vehicle Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Vehicle Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Vehicle Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: for row in all_rows: data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12])) report = ArtifactHtmlReport('RoutineD Vehicle Location') report.start_artifact_report(report_folder, 'Vehicle Location') report.add_script() data_headers = ('Timestamp','Location Date','Vehicle Identifier', 'Location Identifier', 'Identifier','Location Quality','User Set Location','Usual Location','Notes','Geo Map Item','Photo Data','Latitude','Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Vehicle Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Vehicle Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Vehicle Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available in RoutineD Vehicle Location') db.close() return
def get_icloudPhotoMeta(files_found, report_folder, seeker): counter = 0 os.makedirs(os.path.join(report_folder, "bplists")) for file_found in files_found: file_found = str(file_found) counter = counter + 1 data_list = [] with open(file_found, "r") as filecontent: for line in filecontent: jsonconv = json.loads(line) #print(jsonconv) if (isinstance(jsonconv, dict)): jsonconv = jsonconv['results'] length = len(jsonconv) for i in range(length): #Initialize variables for when items are not located in a loop. id = '' created_timestamp = '' created_user = '' created_device = '' modified_timestamp = '' modified_user = '' modified_device = '' decoded = '' is_deleted = '' is_expunged = '' org_filesize = '' rec_mod_date = '' import_date = '' f_org_creation_date = '' res_org_filesize = '' added_date = '' timezoneoffse = '' latitude = '' longitude = '' altitude = '' datestamp = '' timestamp = '' vid_name = '' decoded_tz = '' title = '' recordtype = '' tiff = '' exif = '' id = (jsonconv[i].get('id', '')) rowid = str(i) recordtype = (jsonconv[i].get('recordType', '')) if (jsonconv[i].get('created', '')): created_timestamp = (jsonconv[i]['created'].get( 'timestamp', '')) created_timestamp = timefactorconversion( str(created_timestamp)) #created_user = (jsonconv[i]['created'].get('user', '')) #created_device = (jsonconv[i]['created'].get('device', '')) if (jsonconv[i].get('modified', '')): modified_timestamp = (jsonconv[i]['modified'].get( 'timestamp', '')) modified_timestamp = timefactorconversion( str(modified_timestamp)) #modified_user = (jsonconv[i]['modified'].get('user', '')) #modified_device = (jsonconv[i]['modified'].get('device', '')) if (jsonconv[i].get('fields')): coded_string = (jsonconv[i]['fields'].get( 'filenameEnc', '')) #base64 decoded = base64.b64decode(coded_string) decoded = decoded.decode() coded_string_tz = (jsonconv[i]['fields'].get( 'timeZoneNameEnc', '')) #base64 decoded_tz = base64.b64decode(coded_string_tz) decoded_tz = decoded_tz.decode() is_deleted = (jsonconv[i]['fields'].get( 'isDeleted', '')) is_expunged = (jsonconv[i]['fields'].get( 'isExpunged', '')) org_filesize = (jsonconv[i]['fields'].get( 'resOriginalFileSize', '')) res_org_filesize = (jsonconv[i]['fields'].get( 'resOriginalFileSize', '')) if (jsonconv[i]['fields'].get('originalCreationDate', '')): created_timestamp = (jsonconv[i]['fields'].get( 'originalCreationDate', '')) created_timestamp = timefactorconversion( str(created_timestamp)) rec_mod_date = (jsonconv[i]['fields'].get( 'recordModificationDate', '')) if isinstance(rec_mod_date, int): rec_mod_date = timefactorconversion( str(rec_mod_date)) import_date = (jsonconv[i]['fields'].get( 'importDate', '')) if isinstance(import_date, int): import_date = timefactorconversion( str(import_date)) f_org_creation_date = (jsonconv[i]['fields'].get( 'originalCreationDate', '')) if isinstance(f_org_creation_date, int): f_org_creation_date = timefactorconversion( str(f_org_creation_date)) added_date = (jsonconv[i]['fields'].get( 'addedDate', '')) if isinstance(added_date, int): added_date = timefactorconversion(str(added_date)) timezoneoffse = (jsonconv[i]['fields'].get( 'timeZoneOffse', '')) title = (jsonconv[i]['fields'].get('title', '')) decodedt = base64.b64decode(title) title = decodedt.decode() coded_bplist = ( jsonconv[i]['fields'].get('mediaMetaDataEnc') ) #convert from base64 to bplist and process if coded_bplist is not None: decoded_bplist = base64.b64decode(coded_bplist) # If needed send the full bplist to report folder by editing the outputpath below with open( os.path.join(report_folder, "bplists", rowid + ".bplist"), 'wb') as g: g.write(decoded_bplist) pl = plistlib.loads(decoded_bplist) if (pl.get('{TIFF}')): #print('YESS TIFF # '+str(i)) tiff = (pl.get('{TIFF}')) exif = (pl.get('{Exif}')) if (pl.get('{GPS}')) is not None: #print(pl['{GPS}']) latitude = (pl['{GPS}'].get('Latitude')) longitude = (pl['{GPS}'].get('Longitude')) altitude = (pl['{GPS}'].get('Altitude')) datestamp = (pl['{GPS}'].get('DateStamp')) timestamp = (pl['{GPS}'].get('TimeStamp')) #print(latitude) else: if ( pl.get('moop') ): #If needed send binary content to report flder pass #with open(f'{outputpath}/{i}.moop', 'wb') as g: # g.write(pl.get('moop')) data_list.append( (created_timestamp, rowid, recordtype, decoded, title, org_filesize, latitude, longitude, altitude, datestamp, timestamp, added_date, timezoneoffse, decoded_tz, is_deleted, is_expunged, import_date, rec_mod_date, res_org_filesize, id, tiff, exif)) if len(data_list) > 0: report = ArtifactHtmlReport('iCloud - Photos Metadata' + ' ' + str(counter)) report.start_artifact_report( report_folder, 'iCloud - Photos Metadata' + ' ' + str(counter)) report.add_script() data_headers = ('Timestamp', 'Row ID', 'Record Type', 'Decoded', 'Title', 'Filesize', 'Latitude', 'Longitude', 'Altitude', 'GPS Datestamp', 'GPS Time', 'Added Date', 'Timezone Offset', 'Decoded TZ', 'Is Deleted?', 'Is Expunged?', 'Import Date', 'Modification Date', 'Filesize', 'ID', 'TIFF', 'EXIF') report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() tsvname = 'iCloud - Photos Metadata' tsv(report_folder, data_headers, data_list, tsvname) kmlactivity = 'iCloud - Photos Metadata' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available')
def get_fitbitExercise(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not file_found.endswith('exercise_db'): continue # Skip all other files data_list = [] db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' Select DISTINCT(SESSION_ID) from EXERCISE_EVENT ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: for row in all_rows: sessionID = row[0] cursor.execute(f''' Select datetime(TIME/1000,'unixepoch'), LABEL, LATITUDE, LONGITUDE, ACCURACY, ALTITUDE, SPEED, PACE, SESSION_ID from EXERCISE_EVENT where SESSION_ID = "{sessionID}" ''') all_rows_exercise = cursor.fetchall() usageentries_all = len(all_rows_exercise) if usageentries_all > 0: data_list_current = [] data_headers = ('Timestamp', 'Label', 'Latitude', 'Longitude', 'Accuracy', 'Altitude', 'Speed', 'Pace', 'Session_ID') for row_exercise in all_rows_exercise: data_list.append( (row_exercise[0], row_exercise[1], row_exercise[2], row_exercise[3], row_exercise[4], row_exercise[5], row_exercise[6], row_exercise[7], row_exercise[8])) data_list_current.append( (row_exercise[0], row_exercise[1], row_exercise[2], row_exercise[3], row_exercise[4], row_exercise[5], row_exercise[6], row_exercise[7], row_exercise[8])) kmlactivity = f'Fitbit Map - Session ID {sessionID}' kmlgen(report_folder, kmlactivity, data_list_current, data_headers) data_list_current = [] report = ArtifactHtmlReport('Fitbit Exercise') report.start_artifact_report(report_folder, 'Fitbit Exercise') report.add_script() report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Fitbit Exercise' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Fitbit Exercise' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc(f'No Fitbit - Exercise data available')
def get_googlePhotos(files_found, report_folder, seeker, wrap_text): source_file_photos = '' source_file_cache = '' source_file_trash = '' gphotos_photos_db = '' gphotos_cache_db = '' gphotos_trash_db = '' for file_found in files_found: file_name = str(file_found) if file_name.lower().endswith('gphotos0.db'): gphotos_photos_db = str(file_found) source_file_photos = file_found.replace(seeker.directory, '') if file_name.lower().endswith('disk_cache'): gphotos_cache_db = str(file_found) source_file_cache = file_found.replace(seeker.directory, '') if file_name.lower().endswith('local_trash.db'): gphotos_trash_db = str(file_found) source_file_trash = file_found.replace(seeker.directory, '') db = open_sqlite_db_readonly(gphotos_photos_db) cursor = db.cursor() columns = [i[1] for i in cursor.execute('PRAGMA table_info(local_media)')] if 'purge_timestamp' not in columns: cursor.execute(''' select datetime(utc_timestamp/1000, 'unixepoch'), filename, filepath, datetime(capture_timestamp/1000, 'unixepoch'), timezone_offset/3600000, width, height, size_bytes, case duration when 0 then '' else strftime('%H:%M:%S', duration/1000, 'unixepoch') end, latitude, longitude, folder_name, media_store_id, case trash_timestamp when NULL then '' else datetime(trash_timestamp/1000, 'unixepoch') end from local_media ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Local Media') report.start_artifact_report(report_folder, 'Google Photos - Local Media') report.add_script() data_headers = ('Timestamp', 'File Name', 'File Path', 'Captured Timestamp (Local)', 'Timezone Offset', 'Width', 'Height', 'Size', 'Duration', 'Latitude', 'Longitude', 'Folder Name', 'Media Store ID', 'Trashed Timestamp') data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Local Media' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Local Media' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Google Photos - Local Media' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Google Photos - Local Media data available') else: cursor.execute(''' select datetime(utc_timestamp/1000, 'unixepoch'), filename, filepath, datetime(capture_timestamp/1000, 'unixepoch'), timezone_offset/3600000, width, height, size_bytes, case duration when 0 then '' else strftime('%H:%M:%S', duration/1000, 'unixepoch') end, latitude, longitude, folder_name, media_store_id, case trash_timestamp when NULL then '' else datetime(trash_timestamp/1000, 'unixepoch') end, case purge_timestamp when NULL then '' else datetime(purge_timestamp/1000, 'unixepoch') end from local_media ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Local Media') report.start_artifact_report(report_folder, 'Google Photos - Local Media') report.add_script() data_headers = ('Timestamp', 'File Name', 'File Path', 'Captured Timestamp (Local)', 'Timezone Offset', 'Width', 'Height', 'Size', 'Duration', 'Latitude', 'Longitude', 'Folder Name', 'Media Store ID', 'Trashed Timestamp', 'Purge Timestamp') data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Local Media' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Local Media' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Google Photos - Local Media' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Google Photos - Local Media data available') columns2 = [ i[1] for i in cursor.execute('PRAGMA table_info(remote_media)') ] if 'inferred_latitude' not in columns2: cursor.execute(''' select datetime(utc_timestamp/1000, 'unixepoch'), filename, replace(remote_url,'=s0-d',''), datetime(capture_timestamp/1000, 'unixepoch'), timezone_offset/3600000, strftime('%H:%M:%S', duration/1000, 'unixepoch'), latitude, longitude, upload_status from remote_media ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Remote Media') report.start_artifact_report(report_folder, 'Google Photos - Remote Media') report.add_script() data_headers = ('Timestamp', 'File Name', 'Remote URL', 'Captured Timestamp (Local)', 'Timezone Offset', 'Duration', 'Latitude', 'Longitude', 'Upload Status %') data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Remote Media' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Remote Media' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Google Photos - Remote Media' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Google Photos - Remote Media data available') else: cursor.execute(''' select datetime(utc_timestamp/1000, 'unixepoch'), filename, replace(remote_url,'=s0-d',''), datetime(capture_timestamp/1000, 'unixepoch'), timezone_offset/3600000, strftime('%H:%M:%S', duration/1000, 'unixepoch'), latitude, longitude, inferred_latitude, inferred_longitude, upload_status from remote_media ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Remote Media') report.start_artifact_report(report_folder, 'Google Photos - Remote Media') report.add_script() data_headers = ('Timestamp', 'File Name', 'Remote URL', 'Captured Timestamp (Local)', 'Timezone Offset', 'Duration', 'Latitude', 'Longitude', 'Inferred Latitude', 'Inferred Longitude', 'Upload Status %') data_list = [] for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Remote Media' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Remote Media' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'Google Photos - Remote Media' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No Google Photos - Remote Media data available') cursor.execute(''' select datetime(utc_timestamp/1000, 'unixepoch') as "Timestamp (UTC)", filename, replace(remote_url,'=s0-d',''), size_bytes, datetime(capture_timestamp/1000, 'unixepoch') as "Capture Local Timestamp", timezone_offset/3600000, upload_status from shared_media ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Shared Media') report.start_artifact_report(report_folder, 'Google Photos - Shared Media') report.add_script() data_headers = ('Timestamp', 'File Name', 'Remote URL', 'Size', 'Captured Timestamp (Local)', 'Timezone Offset', 'Upload Status %') data_list = [] for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Shared Media' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Shared Media' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Google Photos - Shared Media data available') cursor.execute(''' select DISTINCT(local_media.bucket_id), local_media.folder_name, rtrim(local_media.filepath, replace(local_media.filepath, '/', '')) from local_media, backup_folders where local_media.bucket_id = backup_folders.bucket_id ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Google Photos - Backed Up Folders') report.start_artifact_report(report_folder, 'Google Photos - Backed Up Folders') report.add_script() data_headers = ( 'Bucket ID', 'Backed Up Folder Name', 'Backed Up Folder Path', ) data_list = [] for row in all_rows: data_list.append((row[0], row[1], row[2])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Google Photos - Backed Up Folders' tsv(report_folder, data_headers, data_list, tsvname, source_file_photos) tlactivity = f'Google Photos - Backed Up Folders' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Google Photos - Backed Up Folders data available') db.close() db = open_sqlite_db_readonly(gphotos_cache_db) cursor = db.cursor() cursor.execute(''' select datetime(last_modified_time/1000, 'unixepoch'), key, size, case pending_delete when 0 then '' when 1 then 'Yes' end from journal ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: fileNameKey = row[1] for match in files_found: if fileNameKey in match: mimetype = magic.from_file(match, mime=True) ext = (mimetype.split('/')[1]) newname = os.path.join(report_folder, f'{fileNameKey}.{ext}') shutil.copy2(match, newname) thumb = media_to_html(fileNameKey, files_found, report_folder) data_list.append((row[0], row[1], thumb, row[2], row[3])) report = ArtifactHtmlReport('Google Photos - Cache') report.start_artifact_report(report_folder, 'Google Photos - Cache') report.add_script() data_headers = ('Timestamp', 'Key', 'Image', 'Size', 'Pending Deletion') report.write_artifact_data_table(data_headers, data_list, file_found, html_no_escape=['Image']) report.end_artifact_report() tsvname = f'Google Photos - Cache' tsv(report_folder, data_headers, data_list, tsvname, source_file_cache) tlactivity = f'Google Photos - Cache' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Google Photos - Cache data available') db.close() db = open_sqlite_db_readonly(gphotos_trash_db) cursor = db.cursor() cursor.execute(''' select datetime(deleted_time/1000, 'unixepoch'), local_path, content_uri, trash_file_name, case is_video when 0 then '' when 1 then 'Yes' end, media_store_id from local ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: fileNameKey = row[3] thumb = '' for match in files_found: if fileNameKey in match: mimetype = magic.from_file(match, mime=True) ext = (mimetype.split('/')[1]) newname = os.path.join(report_folder, f'{fileNameKey}.{ext}') shutil.copy2(match, newname) thumb = media_to_html(fileNameKey, files_found, report_folder) data_list.append( (row[0], row[1], row[2], row[3], thumb, row[4], row[5])) report = ArtifactHtmlReport('Google Photos - Local Trash') report.start_artifact_report(report_folder, 'Google Photos - Local Trash') report.add_script() data_headers = ('Timestamp', 'Original Path', 'Content URI', 'File Name', 'Image', 'Is Video', 'Media Store ID') report.write_artifact_data_table(data_headers, data_list, file_found, html_no_escape=['Image']) report.end_artifact_report() tsvname = f'Google Photos - Local Trash' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Google Photos - Local Trash' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Google Photos - Local Trash data available') db.close() return
def get_routineDLocationsLocal(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc( "Unsupported version for RoutineD Locations Local.sqlite on iOS " + iOSversion) else: for file_found in files_found: file_found = str(file_found) if file_found.endswith('Local.sqlite'): break db = open_sqlite_db_readonly(file_found) cursor = db.cursor() if version.parse(iOSversion) >= version.parse("14"): # Tested 14.1 cursor.execute(''' select datetime(zrtlearnedlocationofinterestvisitmo.zentrydate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinterestvisitmo.zexitdate + 978307200, 'unixepoch'), (zrtlearnedlocationofinterestvisitmo.zexitdate-zrtlearnedlocationofinterestvisitmo.zentrydate)/60.00, zrtlearnedlocationofinterestmo.zlocationlatitude, zrtlearnedlocationofinterestmo.zlocationlongitude, zrtlearnedlocationofinterestvisitmo.zconfidence, zrtlearnedlocationofinterestvisitmo.zlocationverticaluncertainty, zrtlearnedlocationofinterestvisitmo.zlocationhorizontaluncertainty, zrtlearnedlocationofinterestvisitmo.zdatapointcount, datetime(zrtlearnedlocationofinterestvisitmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinterestvisitmo.zexpirationdate + 978307200, 'unixepoch'), zrtlearnedlocationofinterestvisitmo.zlocationlatitude, zrtlearnedlocationofinterestvisitmo.zlocationlongitude from zrtlearnedlocationofinterestvisitmo, zrtlearnedlocationofinterestmo where zrtlearnedlocationofinterestmo.z_pk = zrtlearnedlocationofinterestvisitmo.zlocationofinterest ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12])) description = 'Significant Locations - Location of Interest Entry (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Locations Entry', description) report.add_script() data_headers = ('Timestamp', 'Exit', 'Entry Time (Minutes)', 'Latitude', 'Longitude', 'Confidence', 'Location Vertical Uncertainty', 'Location Horizontal Uncertainty', 'Data Point Count', 'Place Creation Date', 'Expiration', 'Visit latitude', 'Visit Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Locations Entry' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Locations Entry' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Locations Entry' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations Entry data available') else: # < ios 14 cursor.execute(''' select datetime(zrtlearnedlocationofinterestvisitmo.zentrydate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinterestvisitmo.zexitdate + 978307200, 'unixepoch'), (zrtlearnedlocationofinterestvisitmo.zexitdate-zrtlearnedlocationofinterestvisitmo.zentrydate)/60.00, zrtlearnedlocationofinterestmo.zlocationlatitude, zrtlearnedlocationofinterestmo.zlocationlongitude, zrtlearnedlocationofinterestvisitmo.zconfidence, zrtlearnedlocationofinterestvisitmo.zlocationuncertainty, zrtlearnedlocationofinterestvisitmo.zdatapointcount, datetime(zrtlearnedlocationofinterestvisitmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinterestvisitmo.zexpirationdate + 978307200, 'unixepoch'), zrtlearnedlocationofinterestvisitmo.zlocationlatitude, zrtlearnedlocationofinterestvisitmo.zlocationlongitude from zrtlearnedlocationofinterestvisitmo, zrtlearnedlocationofinterestmo where zrtlearnedlocationofinterestmo.z_pk = zrtlearnedlocationofinterestvisitmo.zlocationofinterest ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11])) description = 'Significant Locations - Location of Interest Entry (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Locations Entry', description) report.add_script() data_headers = ('Timestamp', 'Exit', 'Entry Time (Minutes)', 'Latitude', 'Longitude', 'Confidence', 'Location Uncertainty', 'Data Point Count', 'Place Creation Date', 'Expiration', 'Visit latitude', 'Visit Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Locations Entry' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Locations Entry' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Locations Entry' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations Entry data available') if version.parse(iOSversion) >= version.parse("12"): cursor.execute(''' select datetime(zrtlearnedlocationofinteresttransitionmo.zstartdate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinteresttransitionmo.zstopdate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinteresttransitionmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtlearnedlocationofinteresttransitionmo.zexpirationdate + 978307200, 'unixepoch'), zrtlearnedlocationofinterestmo.zlocationlatitude, zrtlearnedlocationofinterestmo.zlocationlongitude from zrtlearnedlocationofinteresttransitionmo left join zrtlearnedlocationofinterestmo on zrtlearnedlocationofinterestmo.z_pk = zrtlearnedlocationofinteresttransitionmo.zlocationofinterest ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5])) description = 'Significant Locations - Location of Interest Transition(Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Transition', description) report.add_script() data_headers = ('Start', 'Stop', 'Creation Date', 'Expiration', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Transition' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Transition' timeline(report_folder, tlactivity, data_list, data_headers) tlactivity = 'RoutineD Transition' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations Transition data available' )
def get_routineDCloud(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf for file_found in files_found: file_found = str(file_found) if file_found.endswith('Cloud-V2.sqlite'): break db = open_sqlite_db_readonly(file_found) cursor = db.cursor() if version.parse(iOSversion) >= version.parse("12"): cursor.execute(''' select datetime(zrtaddressmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtaddressmo.zexpirationdate + 978307200, 'unixepoch'), zrtaddressmo.zcountry, zrtaddressmo.zcountrycode, zrtaddressmo.zpostalcode, zrtaddressmo.zlocality, zrtaddressmo.zsublocality, zrtaddressmo.zthoroughfare, zrtaddressmo.zsubthoroughfare, zrtaddressmo.zsubadministrativearea, zrtaddressmo.zareasofinterest from zrtaddressmo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10])) description = 'Address' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Addresses', description) report.add_script() data_headers = ('Address Creation Date','Address Expiration Date','Country','Country Code','Postal Code', 'Locality','Sublocality','Throroughfare','Subthroroughfare','Subadministrative Area','Area of Interest' ) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Addresses' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Addresses' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No RoutineD Cloud Addresses data available') if version.parse(iOSversion) >= version.parse("12"): cursor.execute(''' select datetime(zrtmapitemmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtmapitemmo.zexpirationdate + 978307200, 'unixepoch'), zrtmapitemmo.zname, zrtaddressmo.zcountry, zrtaddressmo.zcountrycode, zrtaddressmo.zpostalcode, zrtaddressmo.zlocality, zrtaddressmo.zsublocality, zrtaddressmo.zthoroughfare, zrtaddressmo.zsubthoroughfare, zrtaddressmo.zsubadministrativearea, zrtaddressmo.zareasofinterest, zrtmapitemmo.zlatitude, zrtmapitemmo.zlongitude, zrtmapitemmo.zuncertainty from zrtmapitemmo, zrtaddressmo where zrtmapitemmo.z_pk == zrtaddressmo.zmapitem ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14])) description = 'RoutineD Cloud Map Items' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Map Items', description) report.add_script() data_headers = ('Timestamp','Map Item Expiration Date','Map Item Name','Country','Country Code','Postal Code', 'Locality','Sublocality','Throroughfare','Subthroroughfare','Subadministrative Area','Area of Interest', 'Latitude','Longitude','Uncertainty' ) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Map Items' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Map Items' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Cloud Map Items' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD Map Items Cloud-V2.sqlite data available') if (version.parse(iOSversion) >= version.parse("13")): cursor.execute(''' select datetime(zrtlearnedvisitmo.zentrydate + 978307200, 'unixepoch'), datetime(zrtlearnedvisitmo.zexitdate + 978307200, 'unixepoch'), (zexitdate-zentrydate)/60.00, zrtlearnedvisitmo.zplace, zrtlearnedvisitmo.zdatapointcount, zrtaddressmo.zcountry, zrtaddressmo.zcountrycode, zrtaddressmo.zpostalcode, zrtaddressmo.zlocality, zrtaddressmo.zsublocality, zrtaddressmo.zthoroughfare, zrtaddressmo.zsubthoroughfare, zrtaddressmo.zsubadministrativearea, zrtaddressmo.zareasofinterest, zrtlearnedvisitmo.zlocationuncertainty, zrtlearnedvisitmo.zconfidence, datetime(zrtlearnedvisitmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtlearnedvisitmo.zexpirationdate + 978307200, 'unixepoch'), zrtdevicemo.zdeviceclass, zrtdevicemo.zdevicemodel, zrtdevicemo.zdevicename, datetime(zrtlearnedplacemo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtlearnedplacemo.zexpirationdate + 978307200, 'unixepoch'), datetime(zrtaddressmo.zcreationdate + 978307200, 'unixepoch'), zrtlearnedvisitmo.zlocationlatitude, zrtlearnedvisitmo.zlocationlongitude, datetime(zrtmapitemmo.zcreationdate + 978307200, 'unixepoch'), datetime(zrtmapitemmo.zexpirationdate + 978307200, 'unixepoch'), zrtmapitemmo.zlatitude, zrtmapitemmo.zlongitude, zrtmapitemmo.zuncertainty, zrtmapitemmo.zdisplaylanguage, zrtmapitemmo.zname from zrtlearnedvisitmo, zrtdevicemo, zrtlearnedplacemo, zrtaddressmo, zrtmapitemmo where zrtlearnedvisitmo.zdevice = zrtdevicemo.z_pk and zrtlearnedplacemo.z_pk = zrtlearnedvisitmo.zplace and zrtaddressmo.zmapitem = zrtlearnedplacemo.zmapitem and zrtmapitemmo.z_pk = zrtlearnedplacemo.zmapitem ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27], row[28], row[29], row[30], row[31], row[32])) description = 'Significant Locations - Vist Entry & Exit (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Cloud Visit Entry Exit', description) report.add_script() data_headers = ('Timestamp','Visit Exit','Visit Time (Minutes)','Place ID','Data Point Count','Country','Country Code','Postal Code', 'Locality','Sublocality','Throroughfare','Subthroroughfare','Subadministrative Area','Area of Interest', 'Location Uncertainty', 'Confidence','Visit Creation','Visit Expiration','Device Class','Device Model','Device Name','Learned Placed Creation', 'Learned Place Expiration','Address Creation', 'Latitude','Longitude','Map Item Creation Date','Map Item Expiration Date','Map Item Latitude','Map Item Longitude','Uncertainty','Map Item Language','Map Item Name' ) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Cloud Visit Entry Exit' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Cloud Visit Entry Exit' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Cloud Visit Entry Exit' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD Significant Locations - Vist Entry & Exit (Historical)')
def get_routineDLocationsLocal(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc( "Unsupported version for RoutineD Locations Local.sqlite on iOS " + iOSversion) else: for file_found in files_found: file_found = str(file_found) if file_found.endswith('Local.sqlite'): break db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT", (ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "ENTRY TIME (MINUTES)", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID" FROM ZRTLEARNEDLOCATIONOFINTERESTVISITMO LEFT JOIN ZRTLEARNEDLOCATIONOFINTERESTMO ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = 'Significant Locations - Location of Interest Entry (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Locations Entry', description) report.add_script() data_headers = ('Timestamp', 'Exit', 'Entry Time (Minutes)', 'Coordinates', 'Latitude', 'Longitude', 'Confidence', 'Location Uncertainty', 'Data Point Count', 'Place Creation Date', 'Expiration', 'Visit latitude', 'Visit Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Locations Entry' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Locations Entry' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'RoutineD Locations Entry' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No RoutineD Significant Locations Entry data available') # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT", (ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "EXIT TIME (MINUTES)", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID" FROM ZRTLEARNEDLOCATIONOFINTERESTVISITMO LEFT JOIN ZRTLEARNEDLOCATIONOFINTERESTMO ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST ''') if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = 'Significant Locations - Location of Interest Exit (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Locations Exit', description) report.add_script() data_headers = ('Timestamp', 'Exit', 'Entry Time (Minutes)', 'Coordinates', 'Latitude', 'Longitude', 'Confidence', 'Location Uncertainty', 'Data Point Count', 'Place Creation Date', 'Expiration', 'Visit latitude', 'Visit Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Locations Exit' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Locations Exit' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No RoutineD Significant Locations Exit data available') if version.parse(iOSversion) >= version.parse("12"): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' SELECT DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT", (ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "EXIT TIME (MINUTES)", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID" FROM ZRTLEARNEDLOCATIONOFINTERESTVISITMO LEFT JOIN ZRTLEARNEDLOCATIONOFINTERESTMO ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])) description = 'Significant Locations - Location of Interest Transition Start (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Transtition Start', description) report.add_script() data_headers = ('Timestamp', 'Stop', 'Transition Time (Minutes)', 'Coordinates', 'Creation Date', 'Expiration', 'Latitude', 'Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Transtition Start' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Transtition Start' timeline(report_folder, tlactivity, data_list, data_headers) tlactivity = 'RoutineD Transtition Start' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations Transtition Start data available' ) if (version.parse(iOSversion) >= version.parse("11")) and ( version.parse(iOSversion) < version.parse("12")): # The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/routined_local_learned_location_of_interest_entry.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(''' ELECT DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "START", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "STOP", (ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "TRANSITION TIME (MINUTES)", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "CREATION DATE", DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO TABLE ID" FROM ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO LEFT JOIN ZRTLEARNEDLOCATIONOFINTERESTMO ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZLOCATIONOFINTEREST ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])) description = 'Significant Locations - Location of Interest Transition Stop (Historical)' report = ArtifactHtmlReport('Locations') report.start_artifact_report(report_folder, 'RoutineD Transtition Stop', description) report.add_script() data_headers = ('Timestamp', 'Stop', 'Transition Time (Minutes)', 'Coordinates', 'Creation Date', 'Expiration', 'Latitude', 'Longitude', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'RoutineD Transtition Stop' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'RoutineD Transtition Stop' timeline(report_folder, tlactivity, data_list, data_headers) tlactivity = 'RoutineD Transtition Stop' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc( 'No RoutineD Significant Locations Transtition Stop data available' )
def get_locationDallB(files_found, report_folder, seeker): file_found = str(files_found[0]) #os.chmod(file_found, 0o0777) db = sqlite3.connect(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("11"): logfunc("Unsupported version for LocationD App Harvest on iOS " + iOSversion) else: logfunc(iOSversion) db = sqlite3.connect(file_found) cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_appharvest.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", BUNDLEID AS "BUNDLE ID", LATITUDE || ", " || LONGITUDE AS "COORDINATES", ALTITUDE AS "ALTITUDE", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", STATE AS "STATE", AGE AS "AGE", ROUTINEMODE AS "ROUTINE MODE", LOCATIONOFINTERESTTYPE AS "LOCATION OF INTEREST TYPE", HEX(SIG) AS "SIG (HEX)", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE" FROM APPHARVEST """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15])) description = '' report = ArtifactHtmlReport('LocationD App Harvest') report.start_artifact_report(report_folder, 'App Harvest', description) report.add_script() data_headers = ('Timestamp', 'Bundle ID', 'Coordinates', 'Altitude', 'Horizontal Accuracy', 'Vertical Accuracy', 'State', 'Age', 'Routine Mode', 'Location of Interest Type', 'Sig (HEX)', 'Latitude', 'Longitude', 'Speed', 'Course', 'Confidence') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD Cell App Harvest' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD Cell App Harvest' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD Cell App Harvest' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD App Harvest') cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_cdmacelllocation.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", LATITUDE || ", " || LONGITUDE AS "COORDINATES", MCC AS "MCC", SID AS "SID", NID AS "NID", BSID AS "BSID", ZONEID AS "ZONEID", BANDCLASS AS "BANDCLASS", CHANNEL AS "CHANNEL", PNOFFSET AS "PNOFFSET", ALTITUDE AS "ALTITUDE", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE" FROM CDMACELLLOCATION """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17])) description = '' report = ArtifactHtmlReport('LocationD CDMA Location') report.start_artifact_report(report_folder, 'CDMA Location', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'MCC', 'SID', 'NID', 'BSID', 'ZONEID', 'BANDCLASS', 'Channel', 'PNOFFSET', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD CDMA Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD CDMA Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD CDMA Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD CDMA Location') cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_celllocation.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", LATITUDE || ", " || LONGITUDE AS "COORDINATES", MCC AS "MCC", MNC AS "MNC", LAC AS "LAC", CI AS "CI", UARFCN AS "UARFCN", PSC AS "PSC", ALTITUDE AS "ALTITUDE", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE" FROM CELLLOCATION """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15])) description = '' report = ArtifactHtmlReport('LocationD Cell Location') report.start_artifact_report(report_folder, 'Cell Location', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'MCC', 'MNC', 'LAC', 'CI', 'UARFCN', 'PSC', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD Cell Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD Cell Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD Cell Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD Cell Location') cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_ltecelllocation.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", LATITUDE || ", " || LONGITUDE AS "COORDINATES", MCC AS "MCC", MNC AS "MNC", CI AS "CI", UARFCN AS "UARFCN", PID AS "PID", ALTITUDE AS "ALTITUDE", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE" FROM LTECELLLOCATION """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14])) description = '' report = ArtifactHtmlReport('LocationD LTE Location') report.start_artifact_report(report_folder, 'LTE Location', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'MCC', 'MNC', 'CI', 'UARFCN', 'PID', 'Altitude', 'Speed', 'Course', 'Confidence', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD LTE Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD LTE Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD LTE Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD LTE Location') cursor = db.cursor() # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_wifilocation.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(TIMESTAMP + 978307200,'UNIXEPOCH') AS "TIMESTAMP", LATITUDE || ", " || LONGITUDE AS "COORDINATES", MAC AS "MAC", CHANNEL AS "CHANNEL", INFOMASK AS "INFOMASK", SPEED AS "SPEED", COURSE AS "COURSE", CONFIDENCE AS "CONFIDENCE", SCORE AS "SCORE", REACH AS "REACH", HORIZONTALACCURACY AS "HORIZONTAL ACCURACY", VERTICALACCURACY AS "VERTICAL ACCURACY", LATITUDE AS "LATITUDE", LONGITUDE AS "LONGITUDE" FROM WIFILOCATION """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13])) description = '' report = ArtifactHtmlReport('LocationD WiFi Location') report.start_artifact_report(report_folder, 'WiFi Location', description) report.add_script() data_headers = ('Timestamp', 'Coordinates', 'MAC', 'Channel', 'Infomask', 'Speed', 'Course', 'Confidence', 'Score', 'Reach', 'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude', 'Longitude') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'LocationD WiFi Location' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'LocationD WiFi Location' timeline(report_folder, tlactivity, data_list, data_headers) kmlactivity = 'LocationD WiFi Location' kmlgen(report_folder, kmlactivity, data_list, data_headers) else: logfunc('No data available for LocationD WiFi Location')