def process_recentactivity(folder, uid, report_folder): slash = '\\' if is_platform_windows() else '/' db = sqlite3.connect( os.path.join(report_folder, 'RecentAct_{}.db'.format(uid))) cursor = db.cursor() #Create table recent. cursor.execute(''' CREATE TABLE recent(task_id TEXT, effective_uid TEXT, affinity TEXT, real_activity TEXT, first_active_time TEXT, last_active_time TEXT, last_time_moved TEXT, calling_package TEXT, user_id TEXT, action TEXT, component TEXT, snap TEXT,recimg TXT, fullat1 TEXT, fullat2 TEXT) ''') db.commit() err = 0 if report_folder[-1] == slash: folder_name = os.path.basename(report_folder[:-1]) else: folder_name = os.path.basename(report_folder) for filename in glob.iglob(os.path.join(folder, 'recent_tasks', '**'), recursive=True): if os.path.isfile(filename): # filter dirs file_name = os.path.basename(filename) #logfunc(filename) #logfunc(file_name) #numid = file_name.split('_')[0] try: ET.parse(filename) except ET.ParseError: logfunc('Parse error - Non XML file? at: ' + filename) err = 1 #print(filename) if err == 1: err = 0 continue else: tree = ET.parse(filename) root = tree.getroot() #print('Processed: '+filename) for child in root: #All attributes. Get them in using json dump thing fullat1 = json.dumps(root.attrib) task_id = (root.attrib.get('task_id')) effective_uid = (root.attrib.get('effective_uid')) affinity = (root.attrib.get('affinity')) real_activity = (root.attrib.get('real_activity')) first_active_time = (root.attrib.get('first_active_time')) last_active_time = (root.attrib.get('last_active_time')) last_time_moved = (root.attrib.get('last_time_moved')) calling_package = (root.attrib.get('calling_package')) user_id = (root.attrib.get('user_id')) #print(root.attrib.get('task_description_icon_filename')) #All attributes. Get them in using json dump thing fullat2 = json.dumps(child.attrib) action = (child.attrib.get('action')) component = (child.attrib.get('component')) icon_image_path = ( root.attrib.get('task_description_icon_filename')) #Snapshot section picture snapshot = task_id + '.jpg' #print(snapshot) #check for image in directories check1 = os.path.join(folder, 'snapshots', snapshot) isit1 = os.path.isfile(check1) if isit1: #copy snaphot image to report folder shutil.copy2(check1, report_folder) #snap = r'./snapshots/' + snapshot snap = snapshot else: snap = 'NO IMAGE' #Recent_images section if icon_image_path is not None: recent_image = os.path.basename(icon_image_path) check2 = os.path.join(folder, 'recent_images', recent_image) isit2 = os.path.isfile(check2) if isit2: shutil.copy2(check2, report_folder) #recimg = r'./recent_images/' + recent_image recimg = recent_image else: recimg = 'NO IMAGE' else: #check for other files not in the XML - all types check3 = glob.glob( os.path.join(folder, 'recent_images', task_id, '*.*')) if check3: check3 = check3[0] isit3 = os.path.isfile(check3) else: isit3 = 0 if isit3: shutil.copy2(check3, report_folder) recimg = os.path.basename(check3) else: recimg = 'NO IMAGE' #else: # recimg = 'NO IMAGE' #insert all items in database cursor = db.cursor() datainsert = ( task_id, effective_uid, affinity, real_activity, first_active_time, last_active_time, last_time_moved, calling_package, user_id, action, component, snap, recimg, fullat1, fullat2, ) cursor.execute( 'INSERT INTO recent (task_id, effective_uid, affinity, real_activity, first_active_time, last_active_time, last_time_moved, calling_package, user_id, action, component, snap, recimg, fullat1, fullat2) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', datainsert) db.commit() #Query to create report db = sqlite3.connect( os.path.join(report_folder, 'RecentAct_{}.db'.format(uid))) cursor = db.cursor() #Query to create report cursor.execute(''' SELECT task_id as Task_ID, effective_uid as Effective_UID, affinity as Affinity, real_activity as Real_Activity, datetime(first_active_time/1000, 'UNIXEPOCH') as First_Active_Time, datetime(last_active_time/1000, 'UNIXEPOCH') as Last_Active_Time, datetime(last_time_moved/1000, 'UNIXEPOCH') as Last_Time_Moved, calling_package as Calling_Package, user_id as User_ID, action as Action, component as Component, snap as Snapshot_Image, recimg as Recent_Image FROM recent ''') all_rows = cursor.fetchall() colnames = cursor.description if len(all_rows) > 0: report = ArtifactHtmlReport('Recent Tasks, Snapshots & Images') location = os.path.join(folder, 'recent_tasks') report.start_artifact_report(report_folder, f'Recent Activity_{uid}', f'Artifacts located at {location}') report.add_script() data_headers = ('Key', 'Value') image_data_headers = ('Snapshot_Image', 'Recent_Image') for row in all_rows: if row[2] is None: row2 = '' #'NO DATA' else: row2 = row[2] report.write_minor_header(f'Application: {row2}') #do loop for headers data_list = [] for x in range(0, 13): if row[x] is None: pass else: data_list.append((colnames[x][0], str(row[x]))) report.write_artifact_data_table(data_headers, data_list, folder, table_id='', write_total=False, write_location=False, cols_repeated_at_bottom=False) image_data_row = [] image_data_list = [image_data_row] if row[11] == 'NO IMAGE': image_data_row.append('No Image') else: image_data_row.append( '<a href="{1}/{0}"><img src="{1}/{0}" class="img-fluid z-depth-2 zoom" style="max-height: 400px" title="{0}"></a>' .format(str(row[11]), folder_name)) if row[12] == 'NO IMAGE': image_data_row.append('No Image') else: image_data_row.append( '<a href="{1}/{0}"><img src="{1}/{0}" class="img-fluid z-depth-2 zoom" style="max-height: 400px" title="{0}"></a>' .format(str(row[12]), folder_name)) report.write_artifact_data_table(image_data_headers, image_data_list, folder, table_id='', table_style="width: auto", write_total=False, write_location=False, html_escape=False, cols_repeated_at_bottom=False) report.write_raw_html('<br />') report.end_artifact_report()
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')
def get_knowCdisksub(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("13"): pass else: logfunc("Unsupported version for KnowledgC Disk Subsystem on iOS " + iOSversion) return () file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZSOURCE.ZBUNDLEID AS "BUNDLE ID", ZOBJECT.ZVALUESTRING, (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME is "/disk/subsystemAccess" """) 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], row[8], row[9], row[10])) description = '' report = ArtifactHtmlReport('KnowledgeC Disk Subsystem Access') report.start_artifact_report(report_folder, 'Disk Subsystem Access', description) report.add_script() data_headers = ('Start', 'End', 'Bundle ID', 'Value String', 'Usage in Seconds', 'Usage in Minutes', 'Day of Week', 'GMT Offset', 'Entry Creation', 'UUID', 'Zobject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Disk Subsystem Access' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in table') db.close() return
def get_knowCwebusage(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc("Unsupported version for KnowledgC Web Usage" + iOSversion) return () file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "APP NAME", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", ZSTRUCTUREDMETADATA .Z_DKDIGITALHEALTHMETADATAKEY__WEBDOMAIN AS "DOMAIN", ZSTRUCTUREDMETADATA .Z_DKDIGITALHEALTHMETADATAKEY__WEBPAGEURL AS "URL", ZSOURCE.ZDEVICEID AS "DEVICE ID (HARDWARE UUID)", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/app/webUsage" """) 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], row[8], row[9], row[10], row[11], row[12])) description = '' report = ArtifactHtmlReport('KnowledgeC Web Usage') report.start_artifact_report(report_folder, 'Web Usage', description) report.add_script() data_headers = ('Start', 'End', 'App Name', 'Usage in Seconds', 'Usage in Minutes', 'Domain', 'URL', 'Device ID', 'Day of the Wekk', 'GMT Offset', 'Entry Creation', 'UUID', 'Zobject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Web Usage' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Web Usage' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in table') db.close() return
def get_tikTok(files_found, report_folder, seeker): data_list = [] data_list1 = [] for file_found in files_found: file_found = str(file_found) if file_found.endswith('db.sqlite'): maindb = file_found if file_found.endswith('AwemeIM.db'): attachdb = file_found db = open_sqlite_db_readonly(maindb) cursor = db.cursor() cursor.execute(f"ATTACH DATABASE '{attachdb}' as AwemeIM;") cursor.execute( "SELECT name FROM AwemeIM.sqlite_master WHERE type='table' LIMIT 1;") tablename = cursor.fetchall() for rown in tablename: tname = rown[0] cursor.execute(f''' select datetime(localcreatedat, 'unixepoch') as Local_Create_Time, sender, customid, nickname, json_extract(content, '$.text') as message, json_extract(content, '$.tips') as localresponse, json_extract(content,'$.display_name') as links_display_name, json_extract(content, '$.url.url_list[0]') as links_gifs_urls, case when servercreatedat > 1 then datetime(servercreatedat, 'unixepoch') else servercreatedat end servercreatedat, url1 as profilepicURL from TIMMessageORM, {tname} where uid = sender order by Local_Create_Time ''') all_rows = cursor.fetchall() if len(all_rows) > 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])) report = ArtifactHtmlReport('TikTok Messages') report.start_artifact_report(report_folder, 'TikTok Messages') report.add_script() data_headers = ('Timestamp', 'Sender', 'Custom ID', 'Nickname', 'Message', 'Local Response', 'Link GIF Name', 'Link GIF URL', 'Server Create Timestamps', 'Profile Pic URL') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Tiktok Messages' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'TikTok Messages' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No TikTok messages available') cursor.execute(f''' select case when latestchattimestamp > 1 then datetime(latestchattimestamp, 'unixepoch') else latestchattimestamp end latestchattimestamp, nickname, uid, customID, url1 from {tname} ''') all_rows1 = cursor.fetchall() if len(all_rows) > 0: description = 'Timestamp corresponds to latest chat if available' for row in all_rows1: data_list1.append((row[0], row[1], row[2], row[3], row[4])) report = ArtifactHtmlReport('TikTok Contacts') report.start_artifact_report(report_folder, 'TikTok Contacts', description) report.add_script() data_headers1 = ('Timestamp', 'Nickname', 'Unique ID', 'Custom ID', 'URL') report.write_artifact_data_table(data_headers1, data_list1, file_found) report.end_artifact_report() tsvname = 'TikTok Contacts' tsv(report_folder, data_headers1, data_list1, tsvname) tlactivity = 'TikTok Last Contact' timeline(report_folder, tlactivity, data_list1, data_headers1) else: logfunc('No TikTok Contacts available') db.close()
def get_protonmailMessages(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not file_found.endswith('MessagesDatabase.db'): continue # Skip all other files db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(messagev3.Time,'unixepoch') AS 'Message Timestamp', messagev3.Subject AS 'Subject', messagev3.Sender_SenderSerialized AS 'Sender', CASE messagev3.Type WHEN 0 THEN 'Incoming' WHEN 2 THEN 'Outgoing' END AS 'Message Direction', CASE messagev3.Unread WHEN 0 THEN 'Read' WHEN 1 THEN 'Unread' END AS 'Status', messagev3.Size AS 'Message Size', CASE messagev3.AccessTime WHEN 0 THEN '' ELSE datetime(messagev3.AccessTime/1000,'unixepoch') END AS 'Accessed Timestamp', CASE messagev3.Location WHEN 0 THEN 'Inbox' WHEN 7 THEN 'Sent' END AS 'Folder', attachmentv3.file_name AS 'Attachment Name', attachmentv3.file_size AS 'Attachment Size', messagev3.ToList AS 'To List', messagev3.ReplyTos AS 'Reply To', messagev3.CCList AS 'CC List', messagev3.BCCList AS 'BCC List', messagev3.Header AS 'Message Header' FROM messagev3 LEFT JOIN attachmentv3 ON attachmentv3.message_id = messagev3.ID ORDER BY messagev3.Time ASC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('ProtonMail - Messages') report.start_artifact_report(report_folder, 'ProtonMail - Messages') report.add_script() data_headers = ('Message Timestamp','Subject','Sender','Message Direction','Status','Message Size','Accessed Timestamp','Folder','Attachment Name','Attachment Size','Message Header') 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[14])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'ProtonMail - Messages' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'ProtonMail - Messages' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No ProtonMail - Messages data available') db.close()
def get_notificationsXII(files_found, report_folder, seeker): bundle_info = get_bundle_info(files_found) data_list = [] exportedbplistcount = 0 pathfound = str(files_found[0]) # logfunc(f'Posix to string is: {pathfound}') for filepath in glob.iglob(pathfound + "/**", recursive=True): # create directory where script is running from if os.path.isfile(filepath): # filter dirs file_name = os.path.splitext(os.path.basename(filepath))[0] # create directory if filepath.endswith('DeliveredNotifications.plist'): bundle_id = os.path.basename(os.path.dirname(filepath)) # open the plist p = open(filepath, "rb") plist = nd.deserialize_plist(p) # Empty plist will be { 'root': None } if isinstance(plist, dict): continue # skip it, it's empty # Good plist will be a list of dicts for item in plist: creation_date = '' title = '' subtitle = '' message = '' other_dict = {} bundle_name = bundle_info.get(bundle_id, bundle_id) #if bundle_name == 'com.apple.ScreenTimeNotifications': # pass # has embedded plist! for k, v in item.items(): if k == 'AppNotificationCreationDate': creation_date = str(v) elif k == 'AppNotificationMessage': message = v elif k == 'AppNotificationTitle': title = v elif k == 'AppNotificationSubtitle': subtitle = v else: if isinstance(v, bytes): logfunc(f'Found binary data, look into this one later k={k}!') elif isinstance(v, dict): pass # recurse look for plists #TODO elif isinstance(v, list): pass # recurse look for plists #TODO other_dict[k] = str(v) if subtitle: title += f'[{subtitle}]' data_list.append((creation_date, bundle_name, title, message, str(other_dict))) p.close() elif "AttachmentsList" in file_name: pass # future development description = 'iOS > 12 Notifications' report = ArtifactHtmlReport('iOS Notificatons') report.start_artifact_report(report_folder, 'iOS Notifications', description) report.add_script() data_headers = ('Creation Time', 'Bundle', 'Title[Subtitle]', 'Message', 'Other Details') report.write_artifact_data_table(data_headers, data_list, filepath) report.end_artifact_report() logfunc("Total notifications processed:" + str(len(data_list))) #logfunc("Total exported bplists from notifications:" + str(exportedbplistcount)) tsvname = 'Notifications' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Notifications' timeline(report_folder, tlactivity, data_list, data_headers) if len(data_list) == 0: logfunc("No notifications found.")
def get_line(files_found, report_folder, seeker, wrap_text): source_file_msg = '' source_file_call = '' line_call_db = '' line_msg_db = '' for file_found in files_found: file_name = str(file_found) if file_name.lower().endswith('naver_line'): line_msg_db = str(file_found) source_file_msg = file_found.replace(seeker.directory, '') if file_name.lower().endswith('call_history'): line_call_db = str(file_found) source_file_call = file_found.replace(seeker.directory, '') db = open_sqlite_db_readonly(line_msg_db) cursor = db.cursor() try: cursor.execute(''' SELECT m_id, server_name FROM contacts ''') all_rows = cursor.fetchall() usageentries = len(all_rows) except: usageentries = 0 if usageentries > 0: report = ArtifactHtmlReport('Line - Contacts') report.start_artifact_report(report_folder, 'Line - Contacts') report.add_script() data_headers = ( 'user_id', 'user_name' ) # Don't remove the comma, that is required to make this a tuple as there is only 1 element data_list = [] for row in all_rows: data_list.append((row[0], row[1])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Line - Contacts' tsv(report_folder, data_headers, data_list, tsvname, source_file_msg) else: logfunc('No Line Contact Logs found') try: cursor.execute(''' SELECT contact_book_w_groups.id, contact_book_w_groups.members, messages.from_mid, messages.content, messages.created_time/1000, messages.attachement_type, messages.attachement_local_uri, case messages.status when 1 then "Incoming" else "Outgoing" end status FROM (SELECT id, Group_concat(M.m_id) AS members FROM membership AS M GROUP BY id UNION SELECT m_id, NULL FROM contacts) AS contact_book_w_groups JOIN chat_history AS messages ON messages.chat_id = contact_book_w_groups.id WHERE attachement_type != 6 ''') all_rows = cursor.fetchall() usageentries = len(all_rows) except: usageentries = 0 if usageentries > 0: report = ArtifactHtmlReport('Line - Messages') report.start_artifact_report(report_folder, 'Line - Messages') report.add_script() data_headers = ( 'Start Time', 'From ID', 'To ID', 'Direction', 'Thread ID', 'Message', 'Attachments' ) # Don't remove the comma, that is required to make this a tuple as there is only 1 element data_list = [] for row in all_rows: thread_id = None if row[1] == None: thread_id = row[0] to_id = None if row[4] == "Outgoing": if ',' in row[1]: to_id = row[1] else: to_id = row[0] attachment = row[6] if row[6] is None: attachment = None elif 'content' in row[6]: attachment = None created_time = datetime.datetime.fromtimestamp(int( row[4])).strftime('%Y-%m-%d %H:%M:%S') data_list.append((created_time, row[2], to_id, row[7], thread_id, row[3], attachment)) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Line - Messages' tsv(report_folder, data_headers, data_list, tsvname, source_file_msg) tlactivity = f'Line - Messages' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Line Messages available') db.close() db = open_sqlite_db_readonly(line_call_db) cursor = db.cursor() cursor.execute('''attach database "''' + line_msg_db + '''" as naver_line ''') try: cursor.execute(''' SELECT case Substr(calls.call_type, -1) when "O" then "Outgoing" else "Incoming" end AS direction, calls.start_time/1000 AS start_time, calls.end_time/1000 AS end_time, case when Substr(calls.call_type, -1) = "O" then contact_book_w_groups.members else null end AS group_members, calls.caller_mid, case calls.voip_type when "V" then "Video" when "A" then "Audio" when "G" then calls.voip_gc_media_type end AS call_type FROM (SELECT id, Group_concat(M.m_id) AS members FROM membership AS M GROUP BY id UNION SELECT m_id, NULL FROM naver_line.contacts) AS contact_book_w_groups JOIN call_history AS calls ON calls.caller_mid = contact_book_w_groups.id ''') all_rows = cursor.fetchall() usageentries = len(all_rows) except: usageentries = 0 if usageentries > 0: report = ArtifactHtmlReport('Line - Call Logs') report.start_artifact_report(report_folder, 'Line - Call Logs') report.add_script() data_headers = ( 'Start Time', 'End Time', 'To ID', 'From ID', 'Direction', 'Call Type' ) # Don't remove the comma, that is required to make this a tuple as there is only 1 element data_list = [] for row in all_rows: start_time = datetime.datetime.fromtimestamp(int( row[1])).strftime('%Y-%m-%d %H:%M:%S') end_time = datetime.datetime.fromtimestamp(int( row[2])).strftime('%Y-%m-%d %H:%M:%S') data_list.append( (start_time, end_time, row[3], row[4], row[0], row[5])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Line - Call Logs' tsv(report_folder, data_headers, data_list, tsvname, source_file_call) tlactivity = f'Line - Call Logs' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Line Call Logs found') db.close return
def get_healthAll(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(samples.start_date + 978307200, 'unixepoch'), datetime(samples.end_date + 978307200, 'unixepoch'), metadata_values.numerical_value, case workouts.activity_type when 63 then "high intensity interval training (hiit)" when 37 then "indoor / outdoor run" when 3000 then "other" when 52 then "indoor / outdoor walk" when 20 then "functional training" when 13 then "indoor cycle" when 16 then "elliptical" when 35 then "rower" else "unknown" end, workouts.duration / 60.00, workouts.total_energy_burned, workouts.total_distance, workouts.total_distance*0.621371, case workouts.goal_type when 2 then "minutes" when 0 then "open" end, workouts.goal, workouts.total_flights_climbed, workouts.total_w_steps from samples, metadata_values, metadata_keys, workouts where metadata_values.object_id = samples.data_id and metadata_keys.rowid = metadata_values.key_id and workouts.data_id = samples.data_id and workouts.activity_type not null and key is "_HKPrivateWorkoutAverageHeartRate" ''') 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], row[8], row[9], row[10], row[11])) report = ArtifactHtmlReport('Health Workout Cadence') report.start_artifact_report(report_folder, 'Workout Cadence') report.add_script() data_headers = ('Start Date', 'End Date', 'Strides per Min.', 'Workout Type', 'Duration in Mins.', 'Calories Burned', 'Distance in KM', 'Distance in Miles', 'Goal Type', 'Goal', 'Flights Climbed', 'Steps') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Workout Cadence' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Workout Cadence' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Workout Cadence') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(""" SELECT DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH'), DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH'), QUANTITY, QUANTITY*3.28084, (SAMPLES.END_DATE-SAMPLES.START_DATE) FROM SAMPLES INNER JOIN QUANTITY_SAMPLES ON SAMPLES.DATA_ID = QUANTITY_SAMPLES.DATA_ID WHERE SAMPLES.DATA_TYPE = 8 """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries == 0: logfunc('No data available in Distance') else: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4])) description = '' report = ArtifactHtmlReport('Health Distance') report.start_artifact_report(report_folder, 'Distance', description) report.add_script() data_headers = ('Start Date', 'End Date', 'Distance in Meters', 'Distance in Feet', 'Time in Seconds') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Distance' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Distance' timeline(report_folder, tlactivity, data_list, data_headers) if version.parse(iOSversion) >= version.parse("12"): cursor = db.cursor() cursor.execute(""" select datetime(samples.start_date + 978307200, 'unixepoch'), datetime(samples.end_date + 978307200, 'unixepoch'), metadata_values.numerical_value, (samples.end_date-samples.start_date) from samples left join metadata_values on metadata_values.object_id = samples.data_id left join metadata_keys on metadata_keys.rowid = metadata_values.key_id left join workouts on workouts.data_id = samples.data_id where key is "_HKPrivateMetadataKeyElectrocardiogramHeartRate" """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries == 0: logfunc('No data available in ECG Avg. Heart Rate') else: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3])) description = '' report = ArtifactHtmlReport('Health ECG Avg Heart Rate') report.start_artifact_report(report_folder, 'ECG Avg. Heart Rate', description) report.add_script() data_headers = ('Start Date', 'End Date', 'ECG Avg. Heart Rate', 'Time in Seconds') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health ECG Avg Heart Rate' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health ECG Avg Heart Rate' timeline(report_folder, tlactivity, data_list, data_headers) if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(""" select datetime(samples.start_date + 978307200, 'unixepoch'), datetime(samples.end_date + 978307200, 'unixepoch'), quantity, (samples.end_date-samples.start_date) from samples, quantity_samples where samples.data_id = quantity_samples.data_id and samples.data_type = 12 """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries == 0: logfunc('No data available in Flights Climbed') else: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3])) description = '' report = ArtifactHtmlReport('Health Flights Climbed') report.start_artifact_report(report_folder, 'Flights Climbed', description) report.add_script() data_headers = ('Start Date', 'End Date', 'Flights Climbed', 'Time in Seconds') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Flights Climbed' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Flights Climbed' timeline(report_folder, tlactivity, data_list, data_headers) if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(""" select datetime(samples.start_date + 978307200, 'unixepoch'), original_quantity, unit_strings.unit_string, quantity from samples, quantity_samples, unit_strings where samples.data_type = 5 and quantity_samples.original_unit = unit_strings.rowid and samples.data_id = quantity_samples.data_id """) all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries == 0: logfunc('No data available in Heart Rate') else: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3])) description = '' report = ArtifactHtmlReport('Health Heart Rate') report.start_artifact_report(report_folder, 'Heart Rate', description) report.add_script() data_headers = ('Date', 'Heart Rate', 'Units', 'Quantity') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Heart Rate' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Heart Rate' timeline(report_folder, tlactivity, data_list, data_headers) if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' select datetime(samples.start_date + 978307200, 'unixepoch'), datetime(samples.end_date + 978307200, 'unixepoch'), quantity, (samples.end_date-samples.start_date) from samples, quantity_samples where samples.data_type = 75 and samples.data_id = quantity_samples.data_id ''') 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])) report = ArtifactHtmlReport('Health Stood Up') report.start_artifact_report(report_folder, 'Stood Up') report.add_script() data_headers = ('Start Date', 'End Date', 'Stood Up', 'Time in Seconds') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Stood Up' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Stood Up' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Stood Up') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT datetime(SAMPLES.START_DATE + 978307200, 'unixepoch'), datetime(SAMPLES.END_DATE + 978307200, 'unixepoch'), QUANTITY_SAMPLES.QUANTITY, (SAMPLES.END_DATE - SAMPLES.START_DATE), DATA_PROVENANCES.ORIGIN_PRODUCT_TYPE FROM SAMPLES, QUANTITY_SAMPLES, DATA_PROVENANCES, OBJECTS WHERE SAMPLES.DATA_TYPE = 7 AND SAMPLES.DATA_ID = QUANTITY_SAMPLES.DATA_ID AND SAMPLES.DATA_ID = OBJECTS.DATA_ID AND OBJECTS.PROVENANCE = data_provenances.ROWID ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] daily_steps_nested_list = [] c = 0 for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4])) date, hour = row[0].split(' ') if date not in chain(*daily_steps_nested_list): daily_steps_nested_list.append([date, row[2], row[3]]) else: for entry in daily_steps_nested_list: if entry[0] == date: entry[1] += row[2] entry[2] += row[3] daily_steps_list = [tuple(t) for t in daily_steps_nested_list] report = ArtifactHtmlReport('Health Steps') report.start_artifact_report(report_folder, 'Steps') report.add_script() data_headers = ('Start Date', 'End Date', 'Steps', 'Time in Seconds', 'Origin') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Steps' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Steps' timeline(report_folder, tlactivity, data_list, data_headers) description = 'Cumulative total of the steps gathered from all available sources for each day' report = ArtifactHtmlReport('Health Steps per Day') report.start_artifact_report(report_folder, 'Steps per Day', description) report.add_script() data_headers = ('Date', 'Steps', 'Time in Seconds') report.write_artifact_data_table(data_headers, daily_steps_list, file_found) report.end_artifact_report() tsvname = 'Health Steps per Day' tsv(report_folder, data_headers, daily_steps_list, tsvname) tlactivity = 'Health Steps per Day' timeline(report_folder, tlactivity, daily_steps_list, data_headers) else: logfunc('No data available in Steps') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH'), QUANTITY, QUANTITY*2.20462 FROM SAMPLES , QUANTITY_SAMPLES WHERE SAMPLES.DATA_TYPE = 3 AND "DATE" IS NOT NULL and SAMPLES.DATA_ID = QUANTITY_SAMPLES.DATA_ID ''') 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])) report = ArtifactHtmlReport('Health Weight') report.start_artifact_report(report_folder, 'Weight') report.add_script() data_headers = ('Date', 'Weight in KG', 'Weight in LBS') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Weight' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Weight' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Weight') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' select datetime(samples.start_date + 978307200, 'unixepoch'), datetime(samples.end_date + 978307200, 'unixepoch'), metadata_values.numerical_value/100.00, (metadata_values.numerical_value/100.00)*3.28084, case workouts.activity_type when 63 then "high intensity interval training (hiit)" when 37 then "indoor / outdoor run" when 3000 then "other" when 52 then "indoor / outdoor walk" when 20 then "functional training" when 13 then "indoor cycle" when 16 then "elliptical" when 35 then "rower" else "unknown" end, workouts.duration / 60.00, workouts.total_energy_burned, workouts.total_distance, workouts.total_distance*0.621371, case workouts.goal_type when 2 then "minutes" when 0 then "open" end, workouts.goal, workouts.total_flights_climbed, workouts.total_w_steps from samples, metadata_values, metadata_keys, workouts where metadata_values.object_id = samples.data_id and metadata_keys.rowid = metadata_values.key_id and workouts.data_id = samples.data_id and workouts.activity_type not null and (key is null or key is "HKIndoorWorkout") ''') 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], row[8], row[9], row[10], row[11])) report = ArtifactHtmlReport('Health Workout General') report.start_artifact_report(report_folder, 'Workout General') report.add_script() data_headers = ('Start Date', 'End Date', 'Workout Type', 'Duration in Min.', 'Calories Burned', 'Distance in KM', 'Distance in Miles', 'Total Base Energy Burned', 'Goal Type', 'Goal', 'Flights Climbed', 'Steps') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Health Workout General' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Health Workout General' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Workout General') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH'), DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH'), cast(ROUND(QUANTITY) as int) FROM SAMPLES, QUANTITY_SAMPLES WHERE SAMPLES.DATA_TYPE = 172 AND SAMPLES.DATA_ID = QUANTITY_SAMPLES.DATA_ID ''') 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])) report = ArtifactHtmlReport('Environmental Sound Levels') report.start_artifact_report(report_folder, 'Environmental Sound Levels') report.add_script() data_headers = ('Start Date', 'End Date', 'Sound in dBA SPL') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Environmental Sound Levels' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Environmental Sound Levels' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Environmental Sound Levels') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH'), DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH'), (SAMPLES.END_DATE - SAMPLES.START_DATE) FROM SAMPLES WHERE SAMPLES.DATA_TYPE = 63 ''') 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])) report = ArtifactHtmlReport('Sleep Analysis') report.start_artifact_report(report_folder, 'Sleep Analysis') report.add_script() data_headers = ('Start Date', 'End Date', 'Time in Seconds') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Sleep Analysis' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Sleep Analysis' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in Sleep Analysis')
def get_sWipehist(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not file_found.endswith('history'): continue # Skip all other files data_list = [] timestamp = wipe = promptwipe = reason = rebootreason = locale = updateorg = updatepkg = reqtime = '' with open(file_found, 'r') as f: for line in f: #print(line) if line.startswith('+'): if '|' in line: timestamp = line.split('|') timestamp = timestamp[1].strip() timestamp = timestamp.replace('/', '-') else: timestamp = line.split(':',1) timestamp = timestamp[1].strip() timestamp = timestamp.replace(']', '') timestamp = timestamp.replace('/', '-') if line.startswith('--wipe_data'): wipe = 'Yes' if line.startswith('--reason'): reason = line.split('=') reason = reason[1] if line.startswith('reboot_reason'): rebootreason = line.split('=') rebootreason = rebootreason[1] if line.startswith('reboot reason'): rebootreason = line.split(':', 1) rebootreason = rebootreason[1] if line.startswith('--locale'): locale = line.split('=') locale = locale[1] if line.startswith('--requested_time'): reqtime = line.split('=') reqtime = reqtime[1].replace('/', '-') if line.startswith('--update_org_package'): updateorg = line.split('=') updateorg = updateorg[1] if line.startswith('--update_package'): updatepkg = line.split('=') updatepkg = updatepkg[1] if line.startswith('--prompt_and_wipe_data'): promptwipe = 'Yes' wipe = 'Yes' if line.startswith('-\n'): if wipe == 'Yes': data_list.append((timestamp, wipe, promptwipe, reason, rebootreason, locale, reqtime, updateorg, updatepkg)) timestamp = wipe = promptwipe = reason = rebootreason = locale = updateorg = updatepkg = reqtime = '' if data_list: report = ArtifactHtmlReport('Samsung Wipe History') report.start_artifact_report(report_folder, 'Samsung Wipe History') report.add_script() data_headers = ('Timestamp', 'Wipe', 'Promtp & Wipe', 'Reason', 'Reboot Reason', 'Locale', 'Request Timestamp', 'Update ORG', 'Update PKG') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Samsung Wipe History' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Samsung Wipe History' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Samsung Wipe History data available')
def get_knowCwidget(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc("Unsupported version for KnowledgC Widgets Viewed " + 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/knowledge_widgets_viewed.txt # from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/widgets/viewed" """) 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], row[8], row[9])) description = '' report = ArtifactHtmlReport('KnowledgeC Widgets Viewed') report.start_artifact_report(report_folder, 'Widgets Viewed', description) report.add_script() data_headers = ('Start', 'End', 'Bundle ID', 'Usage in Seconds', 'Usage in Minutes', 'Day of Week', 'GMT Offset', 'Entry Creation', 'UUID', 'Zobject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Widgets Viewed' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Widgets Viewed' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in table') db.close() return
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_filesAppsdb(files_found, report_folder, seeker): for file_found in files_found: file_found = str(file_found) if file_found.endswith('server.db'): break db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT * FROM DEVICES ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: data_list.append((row[1], )) description = 'Device names that are able to sync to iCloud Drive.' report = ArtifactHtmlReport('Files App - iCloud Sync Names') report.start_artifact_report(report_folder, 'Files App - iCloud Sync Names', description) report.add_script() data_headers = ('Name', ) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Files App - Filenames' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No Files App - iCloud Sync Names data available') cursor.execute(''' SELECT item_birthtime, item_filename, version_mtime FROM server_items ''') all_rows = cursor.fetchall() usageentries = len(all_rows) data_list = [] if usageentries > 0: for row in all_rows: birthtime = datetime.datetime.fromtimestamp(row[0]) versionmtime = datetime.datetime.fromtimestamp(row[2]) data_list.append((birthtime, row[1], versionmtime)) description = '' report = ArtifactHtmlReport('Files App - iCloud Server Items') report.start_artifact_report(report_folder, 'Files App - iCloud Server Items', description) report.add_script() data_headers = ('Birthtime', 'Filename', 'Version Modified Time') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Files App - iCloud Server Items' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'Files App - iCloud Server Items' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Files App - iCloud Server Items data available')
def get_knowClocation(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc("Unsupported version for KnowledgC Location on iOS " + iOSversion) return () file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__LATITUDE || ", " || ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__LONGITUDE AS "COORDINATES", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__LOCATIONNAME AS "NAME", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__DISPLAYNAME AS "DISPLAY NAME", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__FULLYFORMATTEDADDRESS AS "FORMATTED ADDRESS", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__CITY AS "CITY", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__STATEORPROVINCE AS "STATE/PROVINCE", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__COUNTRY AS "COUNTRY", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__POSTALCODE_V2 AS "POSTAL CODE", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__SUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__THOROUGHFARE AS "THOROUGHFARE", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__PHONENUMBERS AS "PHONE NUMBERS", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__URL AS "URL", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__CONTENTDESCRIPTION AS "CONTENT DESCRIPTION", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYREQUIREDSTRING AS "USER ACTIVITY REQUIRED STRING", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDCONTENTURL AS "CONTENT URL", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDUNIQUEIDENTIFIER AS "UNIQUE ID", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__LATITUDE AS "LATITUDE", ZSTRUCTUREDMETADATA.Z_DKLOCATIONAPPLICATIONACTIVITYMETADATAKEY__LONGITUDE AS "LONGITUDE", ZSOURCE.ZSOURCEID AS "SOURCE ID", ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYUUID AS "ACTIVITY UUID", ZSOURCE.ZITEMID AS "ITEM ID", ZSOURCE.ZSOURCEID AS "SOURCE ID", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/app/locationActivity" """) 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], 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])) description = '' report = ArtifactHtmlReport('KnowledgeC Location Activity') report.start_artifact_report(report_folder, 'Location Activity', description) report.add_script() data_headers = ('Start', 'End', 'Bundle ID', 'Coordinates', 'Name', 'Display Name', 'Formatted Address', 'City', 'State/Province', 'Country', 'Postal Code', 'Subthoroughfare', 'Thoroughfare', 'Phone Numebers', 'URL', 'Activity Type', 'Content Description', 'User Activity Required String', 'Content URL', 'Unique ID', 'Latitude', 'Logitude', 'Source ID', 'Activity UUID', 'Item ID', 'Source ID', 'Day of the Week', 'GMT Offset', 'Entry Creation', 'UUID', 'Zonject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Location Activity' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Location Activity' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available for KnowledgeC Location Activity')
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 = open_sqlite_db_readonly(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_chromeCookies(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not os.path.basename( file_found) == 'Cookies': # skip -journal and other files continue browser_name = get_browser_name(file_found) if file_found.find('app_sbrowser') >= 0: browser_name = 'Browser' elif file_found.find('.magisk') >= 0 and file_found.find( 'mirror') >= 0: continue # Skip sbin/.magisk/mirror/data/.. , it should be duplicate data?? db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT CASE last_access_utc WHEN "0" THEN "" ELSE datetime(last_access_utc / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "last_access_utc", host_key, name, value, CASE creation_utc WHEN "0" THEN "" ELSE datetime(creation_utc / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "creation_utc", CASE expires_utc WHEN "0" THEN "" ELSE datetime(expires_utc / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "expires_utc", path FROM cookies ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport(f'{browser_name} Cookies') #check for existing and get next name for report file, so report from another file does not get overwritten report_path = os.path.join(report_folder, f'{browser_name} Cookies.temphtml') report_path = get_next_unused_name( report_path)[:-9] # remove .temphtml report.start_artifact_report(report_folder, os.path.basename(report_path)) report.add_script() data_headers = ('Last Access Date', 'Host', 'Name', 'Value', 'Created Date', 'Expiration Date', 'Path') data_list = [] for row in all_rows: if wrap_text: data_list.append( (row[0], row[1], (textwrap.fill(row[2], width=50)), row[3], row[4], row[5], row[6])) else: 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'{browser_name} cookies' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'{browser_name} Cookies' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc(f'No {browser_name} cookies data available') db.close()
def get_mobileActivationLogs(files_found, report_folder, seeker): data_list = [] data_list_info = [] source_files = [] for file_found in files_found: file_found = str(file_found) if file_found.startswith('\\\\?\\'): file_name = pathlib.Path(file_found[4:]).name source_files.append(file_found[4:]) else: file_name = pathlib.Path(file_found).name source_files.append(file_found) with open(file_found, 'r') as fp: data = fp.readlines() linecount = 0 hitcount = 0 activationcount = 0 for line in data: linecount += 1 date_filter = re.compile( r'(([A-Za-z]+[\s]+([a-zA-Z]+[\s]+[0-9]+)[\s]+([0-9]+\:[0-9]+\:[0-9]+)[\s]+([0-9]{4}))([\s]+[\[\d\]]+[\s]+[\<a-z\>]+[\s]+[\(\w\)]+[\s]+[A-Z]{2}\:[\s]+)([main\:\s]*.*)$)' ) line_match = re.match(date_filter, line) if line_match: date_time = (line_match.group(3, 5, 4)) conv_time = ' '.join(date_time) dtime_obj = datetime.strptime(conv_time, '%b %d %Y %H:%M:%S') ma_datetime = str(dtime_obj) values = line_match.group(7) if 'perform_data_migration' in values: hitcount += 1 upgrade_match = re.search(( r'((.*)(Upgrade\s+from\s+[\w]+\s+to\s+[\w]+\s+detected\.$))' ), values) if upgrade_match: upgrade = upgrade_match.group(3) data_list.append((ma_datetime, upgrade, file_name)) if '____________________ Mobile Activation Startup _____________________' in values: activationcount += 1 ma_startup_line = str(linecount) ma_startup = ( f'Mobile Activation Startup at line: {ma_startup_line}' ) data_list.append((ma_datetime, ma_startup, file_name)) upgrade_entries = ( f'Found {hitcount} Upgrade entries in {file_name}') boot_entries = ( f'Found {activationcount} Mobile Activation entries in {file_name}' ) data_list_info.append((boot_entries, upgrade_entries)) report = ArtifactHtmlReport('Mobile Activation Logs') report.start_artifact_report(report_folder, 'Mobile Activation Logs') report.add_script() data_headers_info = ('Mobile Activation', 'Upgrade') data_headers = ('Datetime', 'Event', 'Log Name') source_files_found = ', '.join(source_files) report.write_artifact_data_table(data_headers_info, data_list_info, source_files_found, cols_repeated_at_bottom=False) report.write_artifact_data_table(data_headers, data_list, source_files_found, True, False) report.end_artifact_report() tsvname = 'Mobile Activation Logs' tsv(report_folder, data_headers, data_list, tsvname) tsv(report_folder, data_headers_info, data_list_info, tsvname)
def process_siminfo(folder, uid, report_folder): #Query to create report db = open_sqlite_db_readonly(folder) cursor = db.cursor() #Query to create report try: cursor.execute(''' SELECT number, imsi, display_name, carrier_name, iso_country_code, carrier_id, icc_id FROM siminfo ''') except: cursor.execute(''' SELECT number, card_id, display_name, carrier_name, carrier_name, carrier_name, icc_id FROM siminfo ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Device Info') report.start_artifact_report(report_folder, f'SIM_info_{uid}') report.add_script() data_headers = ('Number', 'IMSI', 'Display Name', 'Carrier Name', 'ISO Code', 'Carrier ID', 'ICC ID') data_list = [] for row in all_rows: if row[3] == row[4]: row1 = '' row4 = '' row5 = '' else: row1 = row[1] row4 = row[4] row5 = row[5] data_list.append( (row[0], row1, row[2], row[3], row4, row5, row[6])) report.write_artifact_data_table(data_headers, data_list, folder) report.end_artifact_report() tsvname = f'sim info {uid}' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc(f'No SIM_Info{uid} data available') db.close()
def get_chromeLoginData(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not os.path.basename( file_found) == 'Login Data': # skip -journal and other files continue browser_name = 'Chrome' if file_found.find('app_sbrowser') >= 0: browser_name = 'Browser' elif file_found.find('.magisk') >= 0 and file_found.find( 'mirror') >= 0: continue # Skip sbin/.magisk/mirror/data/.. , it should be duplicate data?? db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT username_value, password_value, CASE date_created WHEN "0" THEN "" ELSE datetime(date_created / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "date_created_win_epoch", CASE date_created WHEN "0" THEN "" ELSE datetime(date_created / 1000000 + (strftime('%s', '1970-01-01')), "unixepoch") END AS "date_created_unix_epoch", origin_url, blacklisted_by_user FROM logins ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport(f'{browser_name} Login Data') #check for existing and get next name for report file, so report from another file does not get overwritten report_path = os.path.join(report_folder, f'{browser_name} Login Data.temphtml') report_path = get_next_unused_name( report_path)[:-9] # remove .temphtml report.start_artifact_report(report_folder, os.path.basename(report_path)) report.add_script() data_headers = ('Created Time', 'Username', 'Password', 'Origin URL', 'Blacklisted by User') data_list = [] for row in all_rows: password = '' password_enc = row[1] if password_enc: password = decrypt(password_enc).decode("utf-8", 'replace') valid_date = get_valid_date(row[2], row[3]) data_list.append( (valid_date, row[0], password, row[4], row[5])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'{browser_name} login data' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'{browser_name} Login Data' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc(f'No {browser_name} Login Data available') db.close()
def get_powerlogAll(files_found, report_folder, seeker): file_found = str(files_found[0]) db = sqlite3.connect(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, DATETIME(TIMESTAMPLOGGED, 'UNIXEPOCH') AS "TIMESTAMP LOGGED", APPLICATIONNAME AS "APPLICATION NAME / BUNDLE ID", ASSERTIONID AS "ASERTION ID", ASSERTIONNAME AS "ASSERTION NAME", AUDIOROUTE AS "AUDIO ROUTE", MIRRORINGSTATE AS "MIRRORING STATE", OPERATION, PID, ID AS "PLAUDIOAGENT_EVENTPOINT_AUDIOAPP TABLE ID" FROM PLAUDIOAGENT_EVENTPOINT_AUDIOAPP ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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 = ArtifactHtmlReport('Powerlog Audio Routing via App') report.start_artifact_report(report_folder, 'Audio Routing') report.add_script() data_headers = ('Timestamp', 'Timestamped Logged', 'Bundle ID', 'Assertion Name', 'Audio Route', 'Mirroring State', 'Operation', 'PID', 'Audio App Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Audio Routing via App' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Airdop Connection Info') if version.parse(iOSversion) >= version.parse("10"): cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, BULLETINBUNDLEID AS "BULLETIN BUNDLE ID", TIMEINTERVAL / 60 AS "TIME INTERVAL IN SECONDS", COUNT AS "COUNT", POSTTYPE AS "POST TYPE", ID AS "PLSPRINGBOARDAGENT_AGGREGATE_SBBULLETINS_AGGREGATE TABLE ID" FROM PLSPRINGBOARDAGENT_AGGREGATE_SBBULLETINS_AGGREGATE ''') 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])) report = ArtifactHtmlReport('Powerlog Aggregate Bulletins') report.start_artifact_report(report_folder, 'Aggregate Bulletins') report.add_script() data_headers = ('Timestamp', 'Bulletin Bundle ID', 'Time Interval in Seconds', 'Count', 'Post Type', 'Aggregate Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Agg Bulletins' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Aggregate Bulletins') if version.parse(iOSversion) >= version.parse("10"): cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, NOTIFICATIONBUNDLEID AS "BULLETIN BUNDLE ID", TIMEINTERVAL / 60 AS "TIME INTERVAL IN SECONDS", COUNT AS "COUNT", NOTIFICATIONTYPE AS "NOTIFICATION TYPE", ID AS "PLSPRINGBOARDAGENT_AGGREGATE_SBNOTIFICATIONS_AGGREGATE TABLE ID" FROM PLSPRINGBOARDAGENT_AGGREGATE_SBNOTIFICATIONS_AGGREGATE ''') 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])) report = ArtifactHtmlReport('Powerlog Aggregate Notifications') report.start_artifact_report(report_folder, 'Aggregate Notifications') report.add_script() data_headers = ('Timestamp', 'Notification Bundle ID', 'Time Interval in Seconds', 'Count', 'Notification Type', 'Aggregate Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Agg Notifications' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Aggregate Notifications') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(AIRDROP_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, STATE, SUBEVENT, BUNDLEID AS BUNDLE_ID, PID, DATETIME(AIRDROP_TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_AIRDROP_TIMESTAMP, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, AIRDROP_ID AS "PLXPCAGENT_EVENTFORWARD_AIRDROP TABLE ID" FROM ( SELECT BUNDLEID, AIRDROP_ID, AIRDROP_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, SYSTEM, PID, SUBEVENT, STATE FROM ( SELECT PLXPCAGENT_EVENTFORWARD_AIRDROP.TIMESTAMP AS AIRDROP_TIMESTAMP, PLXPCAGENT_EVENTFORWARD_AIRDROP.BUNDLEID, PLXPCAGENT_EVENTFORWARD_AIRDROP.PID, PLXPCAGENT_EVENTFORWARD_AIRDROP.SUBEVENT, PLXPCAGENT_EVENTFORWARD_AIRDROP.STATE, PLXPCAGENT_EVENTFORWARD_AIRDROP.ID AS "AIRDROP_ID", PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM, BUNDLEID FROM PLXPCAGENT_EVENTFORWARD_AIRDROP LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS AIRDROPSTATE GROUP BY AIRDROP_ID ) ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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 = ArtifactHtmlReport( 'Powerlog Airdrop Connections Info') report.start_artifact_report(report_folder, 'Airdrop Connections Info') report.add_script() data_headers = ('Adjusted Timestamp', 'State', 'Subevent', 'Bundle ID', 'PID', 'Original Airdrop Timestamp', 'Offset Timestamp', 'Time Offset', 'Airdrop Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Airdrop Connections Info' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Airdop Connection Info') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPEXECUTABLE AS "APP EXECUTABLE NAME", APPBUNDLEID AS "BUNDLE ID", APPBUILDVERSION AS "APP BUILD VERSION", APPBUNDLEVERSION AS "APP BUNDLE VERSION", APPTYPE AS "APP TYPE", CASE APPDELETEDDATE WHEN 0 THEN "NOT DELETED" ELSE DATETIME(APPDELETEDDATE, 'UNIXEPOCH') END "APP DELETED DATE", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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 = ArtifactHtmlReport('Powerlog App Info') report.start_artifact_report(report_folder, 'App Info') report.add_script() data_headers = ('Timestamp', 'App Name', 'App Executable Name', 'Bundle ID', 'App Build Version', 'App Bundle Version', 'App TYpe', 'App Deleted Date', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog App Info' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog App Info') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPEXECUTABLE AS "APP EXECUTABLE NAME", APPBUNDLEID AS "BUNDLE ID", APPBUILDVERSION AS "APP BUILD VERSION", APPBUNDLEVERSION AS "APP BUNDLE VERSION", APPTYPE AS "APP TYPE", CASE APPDELETEDDATE WHEN 0 THEN "NOT DELETED" ELSE DATETIME(APPDELETEDDATE, 'UNIXEPOCH') END "APP DELETED DATE", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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 = ArtifactHtmlReport('Powerlog App Info') report.start_artifact_report(report_folder, 'App Info') report.add_script() data_headers = ('Timestamp', 'App Name', 'App Executable Name', 'Bundle ID', 'App Build Version', 'App Bundle Version', 'App TYpe', 'App Deleted Date', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog App Info' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog App Info') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, DATETIME(TIMESTAMPLOGGED, 'UNIXEPOCH') AS "TIMESTAMP LOGGED", APPLICATIONNAME AS "APPLICATION NAME / BUNDLE ID", ASSERTIONID AS "ASERTION ID", ASSERTIONNAME AS "ASSERTION NAME", AUDIOROUTE AS "AUDIO ROUTE", MIRRORINGSTATE AS "MIRRORING STATE", OPERATION, PID, ID AS "PLAUDIOAGENT_EVENTPOINT_AUDIOAPP TABLE ID" FROM PLAUDIOAGENT_EVENTPOINT_AUDIOAPP ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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 = ArtifactHtmlReport('Powerlog Audio Routing via App') report.start_artifact_report(report_folder, 'Audio Routing') report.add_script() data_headers = ('Timestamp', 'Timestamped Logged', 'Bundle ID', 'Assertion Name', 'Audio Route', 'Mirroring State', 'Operation', 'PID', 'Audio App Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Audio Routing via App' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Audio Routing Info') if version.parse(iOSversion) >= version.parse("11"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, DATETIME(START, 'UNIXEPOCH') AS "START", DATETIME(END, 'UNIXEPOCH') AS "END", STATE AS "STATE", FINISHED AS "FINISHED", HASERROR AS "HAS ERROR", ID AS "PLXPCAGENT_EVENTPOINT_MOBILEBACKUPEVENTS TABLE ID" FROM PLXPCAGENT_EVENTPOINT_MOBILEBACKUPEVENTS ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("11"): for row in all_rows: data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6])) report = ArtifactHtmlReport('Powerlog Backup Info') report.start_artifact_report(report_folder, 'Backup Info') report.add_script() data_headers = ('Timestamp', 'Start', 'End', 'State', 'Finished', 'Has error', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Backup Info' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Backup Info') if version.parse(iOSversion) >= version.parse("11"): cursor = db.cursor() cursor.execute(""" SELECT DATETIME(APPDELETEDDATE, 'UNIXEPOCH') AS "APP DELETED DATE", DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPEXECUTABLE AS "APP EXECUTABLE NAME", APPBUNDLEID AS "BUNDLE ID", APPBUILDVERSION AS "APP BUILD VERSION", APPBUNDLEVERSION AS "APP BUNDLE VERSION", APPTYPE AS "APP TYPE", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS WHERE APPDELETEDDATE > 0 """) elif version.parse(iOSversion) == version.parse("10"): cursor = db.cursor() cursor.execute(""" SELECT DATETIME(APPDELETEDDATE, 'UNIXEPOCH') AS "APP DELETED DATE", DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPEXECUTABLE AS "APP EXECUTABLE NAME", APPBUNDLEID AS "BUNDLE ID", APPBUILDVERSION AS "APP BUILD VERSION", APPBUNDLEVERSION AS "APP BUNDLE VERSION", --APPTYPE AS "APP TYPE", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS WHERE APPDELETEDDATE > 0 """) elif version.parse(iOSversion) == version.parse("9"): cursor = db.cursor() cursor.execute(""" SELECT DATETIME(APPDELETEDDATE, 'UNIXEPOCH') AS "APP DELETED DATE", DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPBUNDLEID AS "BUNDLE ID", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS WHERE APPDELETEDDATE > 0 """) else: logfunc("Unsupported version for Powerlog Deleted Apps iOS version: " + iOSversion) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("11"): 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 = ArtifactHtmlReport('Powerlog Deleted Apps') report.start_artifact_report(report_folder, 'Deleted Apps') report.add_script() data_headers = ('App Deleted Date', 'Timestamp', 'App Name', 'App Executable Name', 'Bundle ID', 'App Build Version', 'App Bundle Version', 'App Type', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Deleted Apps' tsv(report_folder, data_headers, data_list, tsvname) elif version.parse(iOSversion) == version.parse("10"): 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 = ArtifactHtmlReport('Powerlog Deleted Apps') report.start_artifact_report(report_folder, 'Deleted Apps') report.add_script() data_headers = ('App Deleted Date', 'Timestamp', 'App Name', 'App Executable Name', 'Bundle ID', 'App Build Version', 'App Bundle Version', 'App Type', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Deleted Apps' tsv(report_folder, data_headers, data_list, tsvname) elif version.parse(iOSversion) == version.parse("9"): for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5], row[6])) report = ArtifactHtmlReport('Powerlog Deleted Apps') report.start_artifact_report(report_folder, 'Deleted Apps') report.add_script() data_headers = ('App Deleted Date', 'Timestamp', 'App Name', 'Bundle ID', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Deleted Apps' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Deleted Apps') if version.parse(iOSversion) >= version.parse("10"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(LIGHTNINGCONNECTOR_TIMESTAMP + SYSTEM, 'UNIXEPOCH','LOCALTIME') AS ADJUSTED_TIMESTAMP, CASE IOACCESSORYPOWERMODE WHEN "1" THEN "UNPLUGGED" WHEN "3" THEN "PLUGGED IN" END AS "IO ACCESSORY POWER MODE", DATETIME(LIGHTNINGCONNECTOR_TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_LIGHTNINGCONNECTOR_TIMESTAMP, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, LIGHTNINGCONNECTOR_ID AS "PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS TABLE ID" FROM ( SELECT LIGHTNINGCONNECTOR_ID, LIGHTNINGCONNECTOR_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, IOACCESSORYPOWERMODE, SYSTEM FROM ( SELECT PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS.TIMESTAMP AS LIGHTNINGCONNECTOR_TIMESTAMP, IOACCESSORYPOWERMODE, PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS.ID AS "LIGHTNINGCONNECTOR_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS LIGHTNINGCONNECTOR_STATE GROUP BY LIGHTNINGCONNECTOR_ID ) ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5])) report = ArtifactHtmlReport( 'Powerlog Lightning Connector Status') report.start_artifact_report(report_folder, 'Lightning Connector Status') report.add_script() data_headers = ('Adjusted Timestamp', 'Accesory Power Mode', 'Original Lightnint Connector Timestamp', 'Offset Timestamp', 'Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Lightning Connector Status' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Lightning Connector Status') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(LOCATIONAGENT_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, DATETIME(TIMESTAMPLOGGED+ SYSTEM, 'UNIXEPOCH') AS "TIMESTAMP LOGGED (ADJ)", DATETIME(TIMESTAMPEND + SYSTEM, 'UNIXEPOCH') AS "TIMESTAMP END (ADJ)", BUNDLEID AS "BUNDLE ID", TYPE AS "TYPE", LOCATIONDESIREDACCURACY AS "LOCATION DESIRED ACCURACY", LOCATIONDISTANCEFILTER AS "LOCATION DISTANCE FILTER", CLIENT AS "CLIENT", EXECUTABLE AS "EXECUTABLE", DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, LOCATIONAGENT_ID AS "PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS TABLE ID" FROM ( SELECT LOCATIONAGENT_ID, LOCATIONAGENT_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, TIMESTAMPEND, TIMESTAMPLOGGED, BUNDLEID, TYPE, LOCATIONDESIREDACCURACY, LOCATIONDISTANCEFILTER, CLIENT, EXECUTABLE, SYSTEM FROM ( SELECT PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS.TIMESTAMP AS LOCATIONAGENT_TIMESTAMP, TIMESTAMPEND, TIMESTAMPLOGGED, BUNDLEID, TYPE, LOCATIONDESIREDACCURACY, LOCATIONDISTANCEFILTER, CLIENT, EXECUTABLE, PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS.ID AS "LOCATIONAGENT_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS LOCATIONAGENT_STATE GROUP BY LOCATIONAGENT_ID ) ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("9"): 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('Powerlog Location Use by App') report.start_artifact_report(report_folder, 'Location Use by App') report.add_script() data_headers = ('Adjusted Timestamp', 'Timestamp Logged', 'Timestamp End', 'Bundle ID', 'Type', 'Location Desired Accuracy', 'Location Distance Filter', 'Client', 'Executable', 'Offset Timestamp', 'Time Offset', 'Client Status Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Location Use by App' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Location Use by App') if version.parse(iOSversion) >= version.parse("10"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, BUILD, DEVICE, HWMODEL, PAIRINGID AS "PAIRING ID", ID AS "PLCONFIGAGENT_EVENTNONE_PAIREDDEVICECONFIG TABLE ID" FROM PLCONFIGAGENT_EVENTNONE_PAIREDDEVICECONFIG ''') else: cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, BUILD, DEVICE, ID AS "PLCONFIGAGENT_EVENTNONE_PAIREDDEVICECONFIG TABLE ID" FROM PLCONFIGAGENT_EVENTNONE_PAIREDDEVICECONFIG ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: data_list = [] if version.parse(iOSversion) >= version.parse("10"): for row in all_rows: data_list.append( (row[0], row[1], row[2], row[3], row[4], row[5])) report = ArtifactHtmlReport('Powerlog Paired Device Configuration') report.start_artifact_report(report_folder, 'Paired Device Configuration') report.add_script() data_headers = ('Timestamp', 'Build', 'Device', 'HW Model', 'Pairing ID', 'PairedDeviceConfig Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Paired Device Conf' tsv(report_folder, data_headers, data_list, tsvname) else: for row in all_rows: data_list.append((row[0], row[1], row[2], row[3])) report = ArtifactHtmlReport('Powerlog Paired Device Configuration') report.start_artifact_report(report_folder, 'Paired Device Configuration') report.add_script() data_headers = ('Timestamp', 'Build', 'Device', 'PairedDeviceConfig Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Paired Device Conf' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in table') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, DATETIME(TIMESTAMPEND + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_END_TIMESTAMP, BUNDLENAME AS 'BUNDLE ID', PROCESSNAME AS 'PROCESS NAME', CELLIN AS 'CELL IN', CELLOUT AS 'CELL OUT', WIFIIN AS 'WIFI IN', WIFIOUT AS 'WIFI OUT', DATETIME(TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_TIMESTAMP, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, TABLE_ID AS "PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF TABLE ID" FROM ( SELECT TABLE_ID, TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, TIMESTAMPEND, BUNDLENAME, PROCESSNAME, CELLIN, CELLOUT, WIFIIN, WIFIOUT, SYSTEM FROM ( SELECT PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.TIMESTAMP, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.TIMESTAMPEND, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.BUNDLENAME, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.PROCESSNAME, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.CELLIN, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.CELLOUT, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.WIFIIN, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.WIFIOUT, PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF.ID AS "TABLE_ID", PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLPROCESSNETWORKAGENT_EVENTINTERVAL_USAGEDIFF LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) GROUP BY TABLE_ID ) ''') 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], row[8], row[9], row[10], row[11])) report = ArtifactHtmlReport('Powerlog Process Data Usage') report.start_artifact_report(report_folder, 'Process Data Usage') report.add_script() data_headers = ('Adjusted Timestamp', 'Adjusted End Timestamp', 'Bundle ID', 'Process Name', 'Cell In', 'Cell Out', 'WiFI In', 'WiFi Out', 'Original Timestamp', 'Offset Timestamp', 'Time Offset', 'Usage Diff Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Process Data Usage' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in table') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, BUNDLEID AS 'BUNDLE ID', CONNECTIONTYPE AS 'CONNECTION TYPE', ISDROPPED AS 'IS DROPPED', LINKQUALITY AS 'LINK QUALITY', PRIORITY AS 'PRIORITY', TOPIC AS 'TOPIC', SERVERHOSTNAME AS 'SERVERHOSTNAME', SERVERIP AS 'SERVER IP', DATETIME(TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_TIMESTAMP, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, TABLE_ID AS "PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH TABLE ID" FROM ( SELECT TABLE_ID, TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, BUNDLEID, CONNECTIONTYPE, ISDROPPED, LINKQUALITY, PRIORITY, TOPIC, SERVERHOSTNAME, SERVERIP, SYSTEM FROM ( SELECT PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.TIMESTAMP, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.BUNDLEID, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.CONNECTIONTYPE, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.ISDROPPED, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.LINKQUALITY, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.PRIORITY, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.TOPIC, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.SERVERHOSTNAME, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.SERVERIP, PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH.ID AS "TABLE_ID", PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLPUSHAGENT_EVENTPOINT_RECEIVEDPUSH LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) GROUP BY TABLE_ID ) ''') 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], row[8], row[9], row[10], row[11], row[12])) report = ArtifactHtmlReport('Powerlog Push Message Received') report.start_artifact_report(report_folder, 'Push Message Received') report.add_script() data_headers = ('Adjusted Timestamp', 'Bundle ID', 'Connection Type', 'Is Dropped', 'Link Quality', 'Priority', 'Topic', 'Server Hostname', 'Server IP', 'Original Timestamp', 'Offset Timestamp', 'Time Offset', 'Aggregate Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Push Message Received' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in table') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMEZONE_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, TIMEZONENAME AS "TIME ZONE NAME", COUNTRYCODE AS "COUNTRY CODE", LOCALEID AS "LOCALE ID", SECONDSFROMGMT / 3600 AS "SECONDS FROM GMT", TIMEZONEISINDST AS "TIME ZONE IN DST", TRIGGER AS "TRIGGER", DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, TIMEZONE_ID AS "PLLOCALEAGENT_EVENTFORWARD_TIMEZONE TABLE ID" FROM ( SELECT TIMEZONE_ID, TIMEZONE_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, TIMEZONENAME, COUNTRYCODE, LOCALEID, SECONDSFROMGMT, TIMEZONEISINDST, TRIGGER, SYSTEM FROM ( SELECT PLLOCALEAGENT_EVENTFORWARD_TIMEZONE.TIMESTAMP AS TIMEZONE_TIMESTAMP, TIMEZONENAME, COUNTRYCODE, LOCALEID, SECONDSFROMGMT, TIMEZONEISINDST, TRIGGER, PLLOCALEAGENT_EVENTFORWARD_TIMEZONE.ID AS "TIMEZONE_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLLOCALEAGENT_EVENTFORWARD_TIMEZONE LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS TIMEZONE_STATE GROUP BY TIMEZONE_ID ) ''') 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], row[8], row[9])) report = ArtifactHtmlReport('Powerlog Timezones') report.start_artifact_report(report_folder, 'Timezones') report.add_script() data_headers = ('Adjusted Timestamp', 'Timezone Name', 'Country Code', 'Locale ID', 'Seconds from GMT', 'Timezone in DTS', 'Trigger', 'Offset Timestamp', 'Time Offset', 'Timezon Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Timezones' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Timezones') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TORCH_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, BUNDLEID AS BUNDLE_ID, CASE LEVEL WHEN "0" THEN "OFF" WHEN "1" THEN "ON" END AS STATUS, DATETIME(TORCH_TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_TORCH_TIMESTAMP, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, TORCH_ID FROM ( SELECT BUNDLEID, TORCH_ID, TORCH_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, SYSTEM, LEVEL FROM ( SELECT PLCAMERAAGENT_EVENTFORWARD_TORCH.TIMESTAMP AS TORCH_TIMESTAMP, PLCAMERAAGENT_EVENTFORWARD_TORCH.BUNDLEID, PLCAMERAAGENT_EVENTFORWARD_TORCH.LEVEL, PLCAMERAAGENT_EVENTFORWARD_TORCH.ID AS "TORCH_ID", PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM, BUNDLEID FROM PLCAMERAAGENT_EVENTFORWARD_TORCH LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS TORCHESTATE GROUP BY TORCH_ID ) ''') 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])) report = ArtifactHtmlReport('Powerlog Torch') report.start_artifact_report(report_folder, 'Torch') report.add_script() data_headers = ('Adjusted Timestamp', 'Bundle ID', 'Status', 'Original Torch Timestamp', 'Offset Timestamp', 'Time Offset', 'Torch ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Torch' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Torch') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(VIDEO_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, CLIENTDISPLAYID AS "CLIENT DISPLAY ID", STATE, CLIENTPID AS "CLIENT PID", DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, VIDEO_ID AS "PLVIDEOAGENT_EVENTFORWARD_VIDEO TABLE ID" FROM ( SELECT VIDEO_ID, VIDEO_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, CLIENTDISPLAYID, STATE, CLIENTPID, SYSTEM FROM ( SELECT PLVIDEOAGENT_EVENTFORWARD_VIDEO.TIMESTAMP AS VIDEO_TIMESTAMP, CLIENTDISPLAYID, STATE, CLIENTPID, PLVIDEOAGENT_EVENTFORWARD_VIDEO.ID AS "VIDEO_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLVIDEOAGENT_EVENTFORWARD_VIDEO LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS VIDEO_STATE GROUP BY VIDEO_ID ) ''') 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])) report = ArtifactHtmlReport('Powerlog App Playing Video') report.start_artifact_report(report_folder, 'App Playing Video') report.add_script() data_headers = ('Adjusted Timestamp', 'Client Display ID', 'State', 'Client PID', 'Offset Timestamp', 'Time Offset', 'Event Forward Video Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog App Playing Video' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog App Playing Video') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, VOLUME, CASE MUTED WHEN "0" THEN "NO" WHEN "1" THEN "YES" END "MUTED", ID AS "PLAUDIOAGENT_EVENTFORWARD_OUTPUT TABLE ID" FROM PLAUDIOAGENT_EVENTFORWARD_OUTPUT ''') 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])) report = ArtifactHtmlReport('Powerlog Volume') report.start_artifact_report(report_folder, 'Volume') report.add_script() data_headers = ('Timestamp', 'Volume', 'Muted', 'Event Forward Output Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Volume' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog Volume') if version.parse(iOSversion) >= version.parse("9"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(WIFIPROPERTIES_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, CURRENTSSID, CURRENTCHANNEL, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, WIFIPROPERTIES_ID AS "PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES TABLE ID" FROM ( SELECT WIFIPROPERTIES_ID, WIFIPROPERTIES_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, CURRENTSSID, CURRENTCHANNEL, SYSTEM FROM ( SELECT PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES.TIMESTAMP AS WIFIPROPERTIES_TIMESTAMP, CURRENTSSID, CURRENTCHANNEL, PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES.ID AS "WIFIPROPERTIES_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS WIFIPROPERTIES_STATE GROUP BY WIFIPROPERTIES_ID ) ''') 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])) report = ArtifactHtmlReport('Powerlog WiFi Network Connections') report.start_artifact_report(report_folder, 'WiFi Network Connections') report.add_script() data_headers = ('Adjusted Timestamp', 'Current SSID', 'Current Channel', 'Offset Timestamp', 'Time Offset', 'Cummilative Prop. Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Wifi Network Connections' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in Powerlog WiFi Network Connections')
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_powerlogWifiprop(files_found, report_folder, seeker): file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(''' SELECT DATETIME(WIFIPROPERTIES_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP, CURRENTSSID, CURRENTCHANNEL, DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP, SYSTEM AS TIME_OFFSET, WIFIPROPERTIES_ID AS "PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES TABLE ID" FROM ( SELECT WIFIPROPERTIES_ID, WIFIPROPERTIES_TIMESTAMP, TIME_OFFSET_TIMESTAMP, MAX(TIME_OFFSET_ID) AS MAX_ID, CURRENTSSID, CURRENTCHANNEL, SYSTEM FROM ( SELECT PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES.TIMESTAMP AS WIFIPROPERTIES_TIMESTAMP, CURRENTSSID, CURRENTCHANNEL, PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES.ID AS "WIFIPROPERTIES_ID" , PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID, PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM FROM PLWIFIAGENT_EVENTBACKWARD_CUMULATIVEPROPERTIES LEFT JOIN PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET ) AS WIFIPROPERTIES_STATE GROUP BY WIFIPROPERTIES_ID ) ''') 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])) report = ArtifactHtmlReport('Powerlog WiFi Network Connections') report.start_artifact_report(report_folder, 'WiFi Network Connections') report.add_script() data_headers = ('Adjusted Timestamp', 'Current SSID', 'Current Channel', 'Offset Timestamp', 'Time Offset', 'Cummilative Prop. Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'Powerlog Wifi Prop' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No data available in table') db.close() return
def get_firefox(files_found, report_folder, seeker, wrap_text): for file_found in files_found: file_found = str(file_found) if not os.path.basename( file_found ) == 'places.sqlite': # skip -journal and other files continue db = open_sqlite_db_readonly(file_found) cursor = db.cursor() cursor.execute(''' SELECT datetime(moz_places.last_visit_date_local/1000, 'unixepoch') AS LastVisitDate, moz_places.url AS URL, moz_places.title AS Title, moz_places.visit_count_local AS VisitCount, moz_places.description AS Description, CASE WHEN moz_places.hidden = 0 THEN 'No' WHEN moz_places.hidden = 1 THEN 'Yes' END AS Hidden, CASE WHEN moz_places.typed = 0 THEN 'No' WHEN moz_places.typed = 1 THEN 'Yes' END AS Typed, moz_places.frecency AS Frecency, moz_places.preview_image_url AS PreviewImageURL FROM moz_places INNER JOIN moz_historyvisits ON moz_places.origin_id = moz_historyvisits.id INNER JOIN moz_places_metadata ON moz_places.id = moz_places_metadata.id ORDER BY moz_places.last_visit_date_local ASC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Firefox - Web History') report.start_artifact_report(report_folder, 'Firefox - Web History') report.add_script() data_headers = ('Last Visit Date', 'URL', 'Title', 'Visit Count', 'Description', 'Hidden', 'Typed', 'Frecency', 'Preview Image URL') 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'Firefox - Web History' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Firefox - Web History' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Firefox - Web History data available') cursor = db.cursor() cursor.execute(''' SELECT datetime(moz_historyvisits.visit_date/1000, 'unixepoch') AS VisitDate, moz_places.url AS URL, moz_places.title AS Title, moz_historyvisits.id AS VisitID, moz_historyvisits.from_visit AS FromVisitID, CASE moz_historyvisits.visit_type WHEN 1 THEN 'TRANSITION_LINK' WHEN 2 THEN 'TRANSITION_TYPED' WHEN 3 THEN 'TRANSITION_BOOKMARK' WHEN 4 THEN 'TRANSITION_EMBED' WHEN 5 THEN 'TRANSITION_REDIRECT_PERMANENT' WHEN 6 THEN 'TRANSITION_REDIRECT_TEMPORARY' WHEN 7 THEN 'TRANSITION_DOWNLOAD' WHEN 8 THEN 'TRANSITION_FRAMED_LINK' WHEN 9 THEN 'TRANSITION_RELOAD' END AS VisitType, CASE WHEN moz_places.typed = 0 THEN 'No' WHEN moz_places.typed = 1 THEN 'Yes' END AS Typed FROM moz_historyvisits INNER JOIN moz_places ON moz_places.id = moz_historyvisits.place_id ORDER BY moz_historyvisits.visit_date ASC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Firefox - Web Visits') report.start_artifact_report(report_folder, 'Firefox - Web Visits') report.add_script() data_headers = ('Visit Date', 'URL', 'Title', 'Visit ID', 'From Visit ID', 'Visit Type', 'Typed') 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'Firefox - Web Visits' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Firefox - Web Visits' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Firefox - Web Visits data available') cursor = db.cursor() cursor.execute(''' SELECT datetime(moz_bookmarks.dateAdded/1000,'unixepoch'), datetime(moz_bookmarks.lastModified/1000,'unixepoch'), moz_bookmarks.title, moz_places.url, CASE moz_bookmarks.type WHEN 1 THEN 'URL' WHEN 2 THEN 'Folder' WHEN 3 THEN 'Separator' END, moz_bookmarks.id, moz_bookmarks.parent, moz_bookmarks.position, moz_bookmarks.syncStatus FROM moz_bookmarks LEFT JOIN moz_places ON moz_bookmarks.fk = moz_places.id ORDER BY moz_bookmarks.id ASC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Firefox - Bookmarks') report.start_artifact_report(report_folder, 'Firefox - Bookmarks') report.add_script() data_headers = ('Added Timestamp', 'Modified Timestamp', 'Title', 'URL', 'Bookmark Type', 'ID', 'Parent', 'Position', 'Sync 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'Firefox - Bookmarks' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Firefox - Bookmarks' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Firefox - Bookmarks data available') cursor = db.cursor() cursor.execute(''' SELECT id AS 'ID', term AS 'Search Term' FROM moz_places_metadata_search_queries ORDER BY id ASC ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Firefox - Search Terms') report.start_artifact_report(report_folder, 'Firefox - Search Terms') report.add_script() data_headers = ('ID', 'Search Term') data_list = [] for row in all_rows: data_list.append((row[0], row[1])) report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Firefox - Search Terms' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc('No Firefox - Search Terms data available') db.close()
def get_mailprotect(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) <= version.parse("11"): logfunc("Unsupported version for iOS emails in iOS " + iOSversion) return () if version.parse(iOSversion) < version.parse("13"): head, end = os.path.split(files_found[0]) tempf = report_folder db = sqlite3.connect(report_folder + "/emails.db") cursor = db.cursor() cursor.execute(""" create table email1(rowid int, ds text, dr text, size int, sender text, messid int, subject text, receipt text, cc text, bcc text) """) db.commit() cursor.execute(""" create table email2(rowid int, data text) """) db.commit() db = sqlite3.connect(head + "/Envelope Index") db.execute(f'ATTACH DATABASE "{head}/Protected Index" AS PI') db.execute(f'ATTACH DATABASE "{tempf}/emails.db" AS emails') cursor = db.cursor() cursor.execute(""" select main.messages.ROWID, main.messages.date_sent, main.messages.date_received, main.messages.size, PI.messages.sender, PI.messages.message_id, PI.messages.subject, PI.messages._to, PI.messages.cc, PI.messages.bcc from main.messages, PI.messages where main.messages.ROWID = PI.messages.message_id """) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: print(f"Total emails {str(usageentries)}") usageentries1 = str(usageentries) for row in all_rows: # print(row) datainsert = ( row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], ) cursor.execute( "INSERT INTO emails.email1 (rowid, ds, dr, size, sender, messid, subject, receipt, cc, bcc) VALUES(?,?,?,?,?,?,?,?,?,?)", datainsert, ) db.commit() else: print("Zero rows") cursor = db.cursor() cursor.execute(""" select main.messages.ROWID, PI.message_data.data from main.message_data, main.messages, PI.messages, PI.message_data where main.messages.ROWID = main.message_data.message_id and PI.messages.message_id = main.message_data.message_id and PI.message_data.message_data_id = main.message_data.ROWID """) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: print(f"Total emails with message data {str(usageentries)}") usageentries2 = str(usageentries) for row in all_rows: datainsert = ( row[0], row[1], ) cursor.execute( "INSERT INTO emails.email2 (rowid, data) VALUES(?,?)", datainsert, ) db.commit() else: print("Zero rows") cursor.execute(""" select email1.rowid, datetime(email1.ds, 'unixepoch') as ds, datetime(email1.dr, 'unixepoch') as dr, email1.sender, email1.messid, email1.subject, email1.receipt, email1.cc, email1.bcc, email2.data from email1 left outer join email2 on email2.rowid = email1.rowid """) 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[9], row[7], row[8])) file_found = head description = '' report = ArtifactHtmlReport('iOS Mail') report.start_artifact_report(report_folder, 'Emails', description) report.add_script() data_headers = ('Row ID', 'Date Sent', 'Date Received', 'Sender', 'Message ID', 'Subject', 'Recipient', 'Message', 'CC', 'BCC') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'iOS Mail' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc("No iOS emails available") if version.parse(iOSversion) >= version.parse("13"): head, end = os.path.split(files_found[0]) tempf = report_folder db = sqlite3.connect(head + "/Envelope Index") db.execute(f'ATTACH DATABASE "{head}/Protected Index" AS PI') cursor = db.cursor() cursor.execute(""" SELECT datetime(main.messages.date_sent, 'UNIXEPOCH') as datesent, datetime(main.messages.date_received, 'UNIXEPOCH') as datereceived, PI.addresses.address, PI.addresses.comment, PI.subjects.subject, PI.summaries.summary, main.messages.read, main.messages.flagged, main.messages.deleted, main.mailboxes.url from main.mailboxes, main.messages, PI.subjects, PI.addresses, PI.summaries where main.messages.subject = PI.subjects.ROWID and main.messages.sender = PI.addresses.ROWID and main.messages.summary = PI.summaries.ROWID and main.mailboxes.ROWID = main.messages.mailbox """) 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], row[8], row[9])) file_found = head description = '' report = ArtifactHtmlReport('iOS Mail') report.start_artifact_report(report_folder, 'Emails', description) report.add_script() data_headers = ('Date Sent', 'Date Received', 'Address', 'Comment', 'Subject', 'Summary', 'Read?', 'Flagged?', 'Deleted', 'Mailbox') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'iOS Mail' tsv(report_folder, data_headers, data_list, tsvname) else: logfunc("No iOS emails available")
def get_knowCappshortcut(files_found, report_folder, seeker): iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) < version.parse("12"): logfunc("Unsupported version for KnowledgC Location" + iOSversion) return () file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT WHERE ZSTREAMNAME IS "/app/relevantShortcuts" """) 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( 'KnowledgeC Application Relevant Shortcuts') report.start_artifact_report(report_folder, 'App Relevant Shortcuts', description) report.add_script() data_headers = ('Start', 'End', 'Bundle ID', 'Day of the Week', 'GMT Offset', 'Entry Creation', 'UUID', 'Zobject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Application Relevant Shortcuts' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Application Relevant Shortcuts' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No data available in table') db.close() return
def get_cloudkitParticipants(files_found, report_folder, seeker): user_dictionary = {} for file_found in files_found: file_found = str(file_found) # Can add a separate section for each file this information is found in. # This is for Apple Notes. if file_found.endswith('NoteStore.sqlite'): db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(''' SELECT Z_PK, ZSERVERSHAREDATA FROM ZICCLOUDSYNCINGOBJECT WHERE ZSERVERSHAREDATA NOT NULL ''') all_rows = cursor.fetchall() for row in all_rows: filename = os.path.join( report_folder, 'zserversharedata_' + str(row[0]) + '.bplist') output_file = open(filename, "wb") output_file.write(row[1]) output_file.close() deserialized_plist = nd.deserialize_plist(io.BytesIO(row[1])) for item in deserialized_plist: if 'Participants' in item: for participant in item['Participants']: record_id = participant['UserIdentity'][ 'UserRecordID']['RecordName'] email_address = participant['UserIdentity'][ 'LookupInfo']['EmailAddress'] phone_number = participant['UserIdentity'][ 'LookupInfo']['PhoneNumber'] first_name = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.givenName'] middle_name = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.middleName'] last_name = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.familyName'] name_prefix = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.namePrefix'] name_suffix = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.nameSuffix'] nickname = participant['UserIdentity'][ 'NameComponents']['NS.nameComponentsPrivate'][ 'NS.nickname'] user_dictionary[record_id] = [ record_id, email_address, phone_number, name_prefix, first_name, middle_name, last_name, name_suffix, nickname ] db.close() # Build the array after dealing with all the files user_list = list(user_dictionary.values()) if len(user_list) > 0: description = 'CloudKit Participants - Cloudkit accounts participating in CloudKit shares.' report = ArtifactHtmlReport('Participants') report.start_artifact_report(report_folder, 'Participants', description) report.add_script() user_headers = ('Record ID', 'Email Address', 'Phone Number', 'Name Prefix', 'First Name', 'Middle Name', 'Last Name', 'Name Suffix', 'Nickname') report.write_artifact_data_table(user_headers, user_list, '', write_location=False) report.end_artifact_report() tsvname = 'Cloudkit Participants' tsv(report_folder, user_headers, user_list, tsvname) else: logfunc('No Cloudkit - Cloudkit Participants data available')
def get_restoreLog(files_found, report_folder, seeker): data_list = [] pattern = 'data = ' pattern1 = '\"originalOSVersion\":\"' pattern2 = '\"currentOSVersion\":\"' pattern3 = '\"deviceModel\":\"' pattern4 = '\"eventTime\":\"' pattern5 = '\"batteryIsCharging\":' pattern6 = '\"deviceClass\":\"' pattern7 = '\"event\":\"' og_version_num = '' cur_version_num = '' originalOSBuild = '' currentOSBuild = '' OS_dict = { "15A372": "11.0", "15A402": "11.0.1", "15A403": "11.0.1", "15A8391": "11.0.1", "15A421": "11.0.2", "15A432": "11.0.3", "15B93": "11.1", "15B101": "11.1", "15B150": "11.1.1", "15B202": "11.1.2", "15C114": "11.2", "15C153": "11.2.1", "15C202": "11.2.2", "15D60": "11.2.5", "15D100": "11.2.6", "15E216": "11.3", "15E218": "11.3", "15E302": "11.3.1", "15F79": "11.4", "15G77": "11.4.1", "16A366": "12.0", "16A404": "12.0.1", "16A405": "12.0.1", "16B92": "12.1", "16B93": "12.1", "16B94": "12.1", "16C50": "12.1.1", "16C104": "12.1.2", "16D39": "12.1.3", "16D40": "12.1.3", "16D57": "12.1.4", "16E227": "12.2", "16F156": "12.3", "16F203": "12.3.1", "16F8202": "12.3.1", "16F250": "12.3.2", "16G77": "12.4", "16G102": "12.4.1", "16G114": "12.4.2", "16G130": "12.4.3", "16G140": "12.4.4", "16G161": "12.4.5", "16G183": "12.4.6", "16G192": "12.4.7", "16G201": "12.4.8", "16H5": "12.4.9", "16H20": "12.5", "16H22": "12.5.1", "16H30": "12.5.2", "16H41": "12.5.3", "16H50": "12.5.4", "16H62": "12.5.5", "17A577": "13.0", "17A844": "13.1", "17A854": "13.1.1", "17A860": "13.1.2", "17A861": "13.1.2", "17A878": "13.1.3", "17B84": "13.2", "17B90": "13.2.1", "17B102": "13.2.2", "17B111": "13.2.3", "17C54": "13.3", "17D50": "13.3.1", "17E255": "13.4", "17E8255": "13.4", "17E262": "13.4.1", "17E8258": "13.4.1", "17F75": "13.5", "17F80": "13.5.1", "17G68": "13.6", "17G80": "13.6.1", "17H35": "13.7", "18A373": "14.0", "18A393": "14.0.1", "18A8395": "14.1", "18B92": "14.2", "18B111": "14.2", "18B121": "14.2.1", "18C66": "14.3", "18D52": "14.4", "18D61": "14.4.1", "18D70": "14.4.2", "18E199": "14.5", "18E212": "14.5.1", "18F72": "14.6", "18G69": "14.7", "18G82": "14.7.1", "18H17": "14.8", "18H107": "14.8.1", "19A346": "15.0", "19A348": "15.0.1", "19A404": "15.0.2", "19B74": "15.1", "19B81": "15.1.1", "19C56": "15.2", "19C57": "15.2", "19C63": "15.2.1", "19D50": "15.3", "19D52": "15.3.1", "19E241": "15.4", "19E258": "15.4.1", "19F77": "15.5", } for file_found in files_found: file_found = str(file_found) with open(file_found, "r", encoding="utf-8") as f: data = f.readlines() for line in data: if pattern in line: if pattern1 in line: if pattern1 in line: splitline1 = line.partition(pattern1)[2] originalOSBuild = splitline1[:splitline1.find("\"" )] for key, value in OS_dict.items(): if originalOSBuild == key: og_version_num = value break else: og_version_num = "Unknown" else: pass if pattern2 in line: splitline2 = line.partition(pattern2)[2] currentOSBuild = splitline2[:splitline2.find("\"")] for key, value in OS_dict.items(): if currentOSBuild == key: cur_version_num = value break else: cur_version_num = "Unknown" if pattern3 in line: splitline3 = line.partition(pattern3)[2] deviceModel = splitline3[:splitline3.find("\"")] else: pass if pattern4 in line: splitline4 = line.partition(pattern4)[2] eventTime = splitline4[:splitline4.find("\"")] timestamp_formatted = datetime.datetime.fromtimestamp( int(eventTime) / 1000).strftime('%Y-%m-%d %H:%M:%S') else: pass if pattern5 in line: splitline5 = line.partition(pattern5)[2] batteryIsCharging = splitline5[:splitline5.find("," )] else: pass if pattern6 in line: splitline6 = line.partition(pattern6)[2] deviceClass = splitline6[:splitline6.find("\"")] else: pass if pattern7 in line: splitline7 = line.partition(pattern7)[2] event = splitline7[:splitline7.find("\"")] else: pass data_list.append((timestamp_formatted, originalOSBuild, og_version_num, currentOSBuild, cur_version_num, event, deviceClass, deviceModel, batteryIsCharging)) else: pass num_entries = len(data_list) if num_entries > 0: report = ArtifactHtmlReport('Mobile Software Update - Restore Log') report.start_artifact_report(report_folder, 'Mobile Software Update - Restore Log') report.add_script() data_headers = ('Timestamp', 'Original OS Build', 'Original OS Version', 'Current OS Build', 'Current OS Version', 'Event', 'Device', 'Model', 'Battery Is Charging') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = f'Mobile Software Update - Restore Log' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = f'Mobile Software Update - Restore Log' timeline(report_folder, tlactivity, data_list, data_headers) else: logfunc('No Mobile Software Update - Restore Log data available')
def get_knowCsafari(files_found, report_folder, seeker): file_found = str(files_found[0]) db = sqlite3.connect(file_found) iOSversion = scripts.artifacts.artGlobals.versionf if version.parse(iOSversion) >= version.parse("12"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZSTRUCTUREDMETADATA.Z_DKSAFARIHISTORYMETADATAKEY__TITLE AS "TITLE", ZOBJECT.ZVALUESTRING AS "URL", ZSOURCE.ZBUNDLEID AS "BUNDLE ID", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME IS "/safari/history" ''') elif version.parse(iOSversion) == version.parse("11"): cursor = db.cursor() cursor.execute(''' SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "URL", ZSOURCE.ZBUNDLEID AS "BUNDLE ID", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME IS "/safari/history" ''') else: logfunc("Unsupported version for KnowledgC Safari iOS " + iOSversion) return () 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])) report = ArtifactHtmlReport('KnowledgeC Safari Browsing') report.start_artifact_report(report_folder, 'Safari Browsing') report.add_script() data_headers = ('Start', 'End', 'Title', 'URL', 'Bundle ID', 'Day of Week', 'GMT Offset', 'Entry Creation', 'UUID', 'ZOBJECT Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Safari Browsing' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Safari Browsing' timeline(report_folder, tlactivity, data_list) 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])) report = ArtifactHtmlReport('KnowledgeC Safari Browsing') report.start_artifact_report(report_folder, 'Safari Browsing') report.add_script() data_headers = ('Start', 'End', 'URL', 'Bundle ID', 'Day of Week', 'GMT Offset', 'Entry Creation', 'ZOBJECT Table ID') report.write_artifact_data_table(data_headers, data_list, file_found) report.end_artifact_report() tsvname = 'KnowledgeC Safari Browsing' tsv(report_folder, data_headers, data_list, tsvname) tlactivity = 'KnowledgeC Safari Browsing' timeline(report_folder, tlactivity, data_list) else: logfunc('No data available in table') db.close() return
def get_sbrowserDownloads(files_found, report_folder, seeker): file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(''' SELECT tab_url, CASE start_time WHEN "0" THEN "0" ELSE datetime(start_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "Start Time", CASE end_time WHEN "0" THEN "0" ELSE datetime(end_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "End Time", CASE last_access_time WHEN "0" THEN "0" ELSE datetime(last_access_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch") END AS "Last Access Time", target_path, state, opened, received_bytes, total_bytes FROM downloads ''') all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: report = ArtifactHtmlReport('Browser Downloads') report.start_artifact_report(report_folder, 'Browser Downloads') report.add_script() data_headers = ('URL','Start Time','End Time','Last Access Time','Target Path','State','Opened?','Received Bytes','Total Bytes' ) # Don't remove the comma, that is required to make this a tuple as there is only 1 element 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() else: logfunc('No Browser download data available') db.close() return
def get_knowCusage(files_found, report_folder, seeker): file_found = str(files_found[0]) db = sqlite3.connect(file_found) cursor = db.cursor() cursor.execute(""" SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", ZSOURCE.ZDEVICEID AS "DEVICE ID (HARDWARE UUID)", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/app/usage" ) """) all_rows = cursor.fetchall() usageentries = len(all_rows) 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])) description = '' report = ArtifactHtmlReport('KnowledgeC App Usage') report.start_artifact_report(report_folder, 'KnowledgeC App Usage', description) report.add_script() data_headers = ('Start', 'End', 'Bundle ID', 'Usage in Seconds', 'Usage in Minutes', 'Device ID', 'Day of the Week', 'GMT Offset', 'Entry Creation', 'UUID', 'Zobject Table ID') report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False) report.end_artifact_report() tsvname = 'KnowledgeC App Usage' tsv(report_folder, data_headers, data_list, tsvname)