def aggdict(filefound): logfunc(f"Aggregated dictionary funcion executing") _create_aggregate_dict_dir() fetch_and_write_data( passcode_type_query, filefound[0], category="Passcode Type", report_table_columns=list(DEFAULT_REPORT_TABLE_COLUMNS) + ["Passcode Type"], ) fetch_and_write_data( passcode_success_fail_query, filefound[0], category="Passcode Success-Fail" ) fetch_and_write_data( passcode_finger_template_query, filefound[0], category="Passcode Finger Template", ) fetch_and_write_data(scalars_query, filefound[0], category="Scalars") fetch_and_write_data( distribution_keys_query, filefound[0], category="Distribution Keys", report_table_columns=["Day", "Seconds in a Day", "Key", "Value", "Table ID"], )
def wrapper(*args, **kwargs): ret = None try: ret = function(*args, **kwargs) except Exception as e: if os.environ.get("DEBUG"): logging.exception("Exception in processing:") logfunc(log_template.format(**kwargs)) return ret
def conndevices(filefound): with open(filefound[0], "rb") as f: data = f.read() logfunc(f"Connected devices function executing") outpath = os.path.join(reportfolderbase, "Devices Connected/") os.mkdir(outpath) nl = "\n" userComps = "" logfunc("Data being interpreted for FRPD is of type: " + str(type(data))) x = type(data) byteArr = bytearray(data) userByteArr = bytearray() magicOffset = byteArr.find(b"\x01\x01\x80\x00\x00") magic = byteArr[magicOffset:magicOffset + 5] flag = 0 if magic == b"\x01\x01\x80\x00\x00": logfunc( "Found magic bytes in iTunes Prefs FRPD... Finding Usernames and Desktop names now" ) f = open(outpath + "DevicesConnected.html", "w") f.write("<html>") f.write(f"Artifact name and path: {filefound[0]}<br>") f.write(f"Usernames and Computer names:<br><br>") for x in range(int(magicOffset + 92), len(data)): if (data[x]) == 0: x = int(magicOffset) + 157 if userByteArr.decode() == "": continue else: if flag == 0: userComps += userByteArr.decode() + " - " flag = 1 else: userComps += userByteArr.decode() + "\n" flag = 0 userByteArr = bytearray() continue else: char = data[x] userByteArr.append(char) logfunc(f"{userComps}{nl}") f.write(f"{userComps}<br>") f.write(f"</html>") f.close() logfunc(f"Connected devices function completed. ")
def confaccts(filefound): logfunc(f"Config Accounts function executing") try: if os.path.isdir(reportfolderbase + "Accounts/"): pass else: os.makedirs(reportfolderbase + "Accounts") except: logfunc("Error creating confaccts() report directory") try: with open(reportfolderbase + "Accounts/Config Accounts.html", "w") as f: f.write("<html><body>") f.write("<h2>Config Accounts Report</h2>") f.write(f"Config Accounts located at {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f"<table>") f.write(f"<tr><td>Key</td><td>Values</td></tr>") with open(filefound[0], "rb") as fp: pl = plistlib.load(fp) for key, val in pl.items(): f.write(f"<tr><td>{key}</td><td>{val}</td></tr>") f.write(f"</table></body></html>") except: logfunc("Error in Config Accounts function.") logfunc("Config Accounts function completed.")
def dbbuff(filefound): logfunc(f"Aggregated dictionary DBbuffer function executing") db_path = filefound[0] category = "DBBuffer" _create_aggregate_dict_dir() with open(db_path, "r") as rfp: rows = [line.split() for line in rfp.readlines()] report_table_columns = ["Value"] * 4 write_html_template( "passcode_type.html", rows, db_path, category, report_table_columns ) logfunc(f"Aggregated dictionary DBbuffer function completed")
def accs(filefound): try: db = sqlite3.connect(filefound[0]) cursor = db.cursor() cursor.execute(""" SELECT ZACCOUNTTYPEDESCRIPTION, ZUSERNAME, DATETIME(ZDATE+978307200,'UNIXEPOCH','UTC' ) AS 'ZDATE TIMESTAMP', ZACCOUNTDESCRIPTION, ZACCOUNT.ZIDENTIFIER, ZACCOUNT.ZOWNINGBUNDLEID FROM ZACCOUNT JOIN ZACCOUNTTYPE ON ZACCOUNTTYPE.Z_PK=ZACCOUNT.ZACCOUNTTYPE ORDER BY ZACCOUNTTYPEDESCRIPTION """) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"Account Data function executing") if os.path.isdir(os.path.join(reportfolderbase, "Accounts/")): pass else: os.makedirs(os.path.join(reportfolderbase, "Accounts")) with open( os.path.join(reportfolderbase, "Accounts/Accounts.html"), "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> Account Data report</h2>") f.write(f"Account Data entries: {usageentries}<br>") f.write(f"Account Data located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Account Desc</th><th>Usermane</th><th>Timestamp</th><th>Description</th><th>Identifier</th><th>Bundle ID</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"Account Data function completed") else: logfunc("No Account Data available") except: logfunc("Error in Account Data Section.")
def fetch_and_write_data( query, db_path, template_env, output_dir_name, category="", report_table_columns=DEFAULT_REPORT_TABLE_COLUMNS, ): """ Note: Always pass in category as a kwarg as long as the silence_and_log decorator requires it. """ logfunc(f"Aggregated dictionary {category} function executing") rows = get_sql_output(query, db_path) if not rows: logfunc(f"No Aggregated dictionary {category} data available") return write_html_template( "passcode_type.html", rows, db_path, report_table_columns, template_env, output_dir_name, category=category, ) logfunc(f"Aggregated dictionary {category} function completed")
def calhist(filefound): db = sqlite3.connect(filefound[0]) cursor = db.cursor() cursor.execute( """ SELECT ZADDRESS AS "ADDRESS", ZANSWERED AS "WAS ANSWERED", ZCALLTYPE AS "CALL TYPE", ZORIGINATED AS "ORIGINATED", ZDURATION AS "DURATION (IN SECONDS)", ZISO_COUNTRY_CODE as "ISO COUNTY CODE", ZLOCATION AS "LOCATION", ZSERVICE_PROVIDER AS "SERVICE PROVIDER", DATETIME(ZDATE+978307200,'UNIXEPOCH') AS "TIMESTAMP" FROM ZCALLRECORD """ ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"Call History function executing") if os.path.isdir(os.path.join(reportfolderbase, "Call History/")): pass else: os.makedirs(os.path.join(reportfolderbase, "Call History")) with open( reportfolderbase + "Call History/Call History.html", "w", encoding="utf8" ) as f: f.write("<html><body>") f.write("<h2> Call History report</h2>") f.write(f"Call History entries: {usageentries}<br>") f.write(f"Call History database located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Address</th><th>Was Answered</th><th>Call Type</th><th>Originated</th><th>Duration in Secs</th><th>ISO County Code</th><th>Location</th><th>Service Provider</th><th>Timestamp</th></tr>" ) for row in all_rows: an = str(row[0]) an = an.replace("b'", "") an = an.replace("'", "") f.write( f"<tr><td>{an}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"Call History function completed") else: logfunc("No call history available")
def fetch_and_write_data( query, db_path, category, report_table_columns=DEFAULT_REPORT_TABLE_COLUMNS ): logfunc(f"Aggregated dictionary {category} function executing") rows = get_sql_output(query, db_path) if not rows: logfunc(f"No Aggregated dictionary {category} data available") return write_html_template( "passcode_type.html", rows, db_path, category, report_table_columns ) logfunc(f"Aggregated dictionary {category} function completed")
def mobilact(filefound): logfunc(f"Mobile Activation function executing") try: linecount = 0 hitcount = 0 activationcount = 0 filescounter = 0 if os.path.exists(os.path.join(reportfolderbase, "SysDiagnose/")): pass else: os.makedirs(os.path.join(reportfolderbase, "SysDiagnose/")) f = open( os.path.join(reportfolderbase, "SysDiagnose/Mobile Activation Logs.html"), "w", ) f.write("<html><body>") f.write("<h2>Mobile Activation Report</h2>") f.write( "Logs in private/var/mobile/Library/Logs/mobileactivationd/mobileactivationd.log.*<br>" ) for filename in filefound: file = open(filename, "r", encoding="utf8") filescounter = filescounter + 1 for line in file: linecount += 1 if "perform_data_migration" in line: hitcount += 1 # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] frombuild = txts[12] tobuild = txts[14] f.write( "<br><br>" + day + " " + month + " " + year + " " + time + " Upgraded from " + frombuild + " to " + tobuild + " [line " + str(linecount) + "]" ) if ( "MA: main: ____________________ Mobile Activation Startup _____________________" in line ): activationcount += 1 # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] f.write( "<br><br>" + day + " " + month + " " + year + " " + time + " Mobile Activation Startup " + " [line " + str(linecount) + "]" ) if "MA: main: build_version:" in line: # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] buildver = txts[11] f.write( "<br>" + day + " " + month + " " + year + " " + time + " Mobile Activation Build Version = " + buildver ) if "MA: main: hardware_model:" in line: # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] hwmodel = txts[11] f.write( "<br>" + day + " " + month + " " + year + " " + time + " Mobile Activation Hardware Model = " + hwmodel ) if "MA: main: product_type:" in line: # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] prod = txts[11] f.write( "<br>" + day + " " + month + " " + year + " " + time + " Mobile Activation Product Type = " + prod ) if "MA: main: device_class:" in line: # print("\n" + line) txts = line.split() # print(txts, linecount) # print(len(txts)) dayofweek = txts[0] month = txts[1] day = txts[2] time = txts[3] year = txts[4] devclass = txts[11] f.write( "<br>" + day + " " + month + " " + year + " " + time + " Mobile Activation Device Class = " + devclass ) file.close() f.write("<br><br>Found " + str(hitcount) + " Upgrade entries") f.write( "<br> Found " + str(activationcount) + " Mobile Activation Startup entries" ) f.write("</body></html>") f.close() logfunc(f"Mobile Activation completed executing") except: logfunc("Error in MobileActivation Logs section")
def bkupstate(filefound): logfunc(f"BackupStateInfo function executing") try: if os.path.exists(os.path.join(reportfolderbase, "SysDiagnose/")): pass else: os.makedirs(os.path.join(reportfolderbase, "SysDiagnose/")) f = open(os.path.join(reportfolderbase, "SysDiagnose/BackupStateInfo.txt"), "w") p = open(filefound[0], "rb") plist = plistlib.load(p) # create html headers filedatahtml = open( os.path.join(reportfolderbase, "SysDiagnose/BackupStateInfo.html"), mode="a+", ) filedatahtml.write("<html><body>") filedatahtml.write("<h2>BackupStateInfo Report </h2>") filedatahtml.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) filedatahtml.write('<table class="table sticky">') filedatahtml.write(f'<tr><td colspan = "2">BackupStateInfo Items</td></tr>') pl = plist # This code taken from https://github.com/cheeky4n6monkey/iOS_sysdiagnose_forensic_scripts/blob/master/sysdiagnose-mobilebackup.py if "BackupStateInfo" in pl.keys(): for key, val in pl["BackupStateInfo"].items(): # print("key = " + str(key) + ", val = " + str(val)) if key == "date": filedatahtml.write( f"<tr><td>BackupStateInfo Date</td><td>{str(val)}</td></tr>" ) if key == "isCloud": filedatahtml.write( f"<tr><td>BackupStateInfo isCloud</td><td>{str(val)}</td></tr>" ) if "RestoreInfo" in pl.keys(): for key, val in pl["RestoreInfo"].items(): if key == "RestoreDate": filedatahtml.write( f"<tr><td>RestoreInfo Date</td><td>{str(val)}</td></tr>" ) if key == "BackupBuildVersion": filedatahtml.write( f"<tr><td>RestoreInfo BackupBuildVersion</td><td>{str(val)}</td></tr>" ) if key == "DeviceBuildVersion": filedatahtml.write( f"<tr><td>RestoreInfo DeviceBuildVersion</td><td>{str(val)}</td></tr>" ) if key == "WasCloudRestore": filedatahtml.write( f"<tr><td>RestoreInfo WasCloudRestore</td><td>{str(val)}</td></tr>" ) filedatahtml.write("</table></html>") filedatahtml.write("<br>") filedatahtml.write('<table class="table sticky">') filedatahtml.write(f'<tr><td colspan = "2">{filefound[0]}</td></tr>') filedatahtml.write("<tr><th>Key</th><th>Value</th></tr>") for key, val in plist.items(): f.write(f"{key} {val}{nl}") filedatahtml.write(f"<tr><td>{key}</td><td>{val}</td></tr>") f.close() # close html footer filedatahtml.write("</table></html>") filedatahtml.close() except: logfunc("Error in BackupStateInfo function section") logfunc(f"BackupStateInfo function completed.")
def smschat(filefound): db = sqlite3.connect(filefound[0]) cursor = db.cursor() try: cursor.execute( """ SELECT CASE WHEN LENGTH(MESSAGE.DATE)=18 THEN DATETIME(MESSAGE.DATE/1000000000+978307200,'UNIXEPOCH') WHEN LENGTH(MESSAGE.DATE)=9 THEN DATETIME(MESSAGE.DATE + 978307200,'UNIXEPOCH') ELSE "N/A" END "MESSAGE DATE", CASE WHEN LENGTH(MESSAGE.DATE_DELIVERED)=18 THEN DATETIME(MESSAGE.DATE_DELIVERED/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_DELIVERED)=9 THEN DATETIME(MESSAGE.DATE_DELIVERED+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE DELIVERED", CASE WHEN LENGTH(MESSAGE.DATE_READ)=18 THEN DATETIME(MESSAGE.DATE_READ/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_READ)=9 THEN DATETIME(MESSAGE.DATE_READ+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE READ", MESSAGE.TEXT as "MESSAGE", HANDLE.ID AS "CONTACT ID", MESSAGE.SERVICE AS "SERVICE", MESSAGE.ACCOUNT AS "ACCOUNT", MESSAGE.IS_DELIVERED AS "IS DELIVERED", MESSAGE.IS_FROM_ME AS "IS FROM ME", ATTACHMENT.FILENAME AS "FILENAME", ATTACHMENT.MIME_TYPE AS "MIME TYPE", ATTACHMENT.TRANSFER_NAME AS "TRANSFER TYPE", ATTACHMENT.TOTAL_BYTES AS "TOTAL BYTES" FROM MESSAGE LEFT OUTER JOIN MESSAGE_ATTACHMENT_JOIN ON MESSAGE.ROWID = MESSAGE_ATTACHMENT_JOIN.MESSAGE_ID LEFT OUTER JOIN ATTACHMENT ON MESSAGE_ATTACHMENT_JOIN.ATTACHMENT_ID = ATTACHMENT.ROWID LEFT OUTER JOIN HANDLE ON MESSAGE.HANDLE_ID = HANDLE.ROWID """ ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"SMS Chat function executing") if os.path.isdir(os.path.join(reportfolderbase, "SMS Chat/")): pass else: os.makedirs(os.path.join(reportfolderbase, "SMS Chat")) with open( reportfolderbase + "SMS Chat/SMS Chat.html", "w", encoding="utf8" ) as f: f.write("<html><body>") f.write("<h2> SMS Chat report</h2>") f.write(f"SMS Chat entries: {usageentries}<br>") f.write(f"SMS Chat database located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Message Date</th><th>Date Delivered</th><th>Date Read</th><th>Message</th><th>Contact ID</th><th>Service</th><th>Account</th><th>Is Delivered</th><th>Is from Me</th><th>Filename</th><th>MIME Type</th><th>Transfer Type</th><th>Total Bytes</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td><td>{row[9]}</td><td>{row[10]}</td><td>{row[11]}</td><td>{row[12]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"SMS Chat function completed") else: logfunc("No SMS Chats available") except: logfunc("Error on SMS Chat function. Possible empty database.") db = sqlite3.connect(filefound[0]) cursor = db.cursor() try: cursor.execute( ''' SELECT CASE WHEN LENGTH(MESSAGE.DATE)=18 THEN DATETIME(MESSAGE.DATE/1000000000+978307200,'UNIXEPOCH') WHEN LENGTH(MESSAGE.DATE)=9 THEN DATETIME(MESSAGE.DATE + 978307200,'UNIXEPOCH') ELSE "N/A" END "MESSAGE DATE", CASE WHEN LENGTH(MESSAGE.DATE_DELIVERED)=18 THEN DATETIME(MESSAGE.DATE_DELIVERED/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_DELIVERED)=9 THEN DATETIME(MESSAGE.DATE_DELIVERED+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE DELIVERED", CASE WHEN LENGTH(MESSAGE.DATE_READ)=18 THEN DATETIME(MESSAGE.DATE_READ/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_READ)=9 THEN DATETIME(MESSAGE.DATE_READ+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE READ", MESSAGE.TEXT as "MESSAGE", HANDLE.ID AS "CONTACT ID", MESSAGE.SERVICE AS "SERVICE", MESSAGE.ACCOUNT AS "ACCOUNT", MESSAGE.IS_DELIVERED AS "IS DELIVERED", MESSAGE.IS_FROM_ME AS "IS FROM ME", ATTACHMENT.FILENAME AS "FILENAME", ATTACHMENT.MIME_TYPE AS "MIME TYPE", ATTACHMENT.TRANSFER_NAME AS "TRANSFER TYPE", ATTACHMENT.TOTAL_BYTES AS "TOTAL BYTES" FROM MESSAGE LEFT OUTER JOIN MESSAGE_ATTACHMENT_JOIN ON MESSAGE.ROWID = MESSAGE_ATTACHMENT_JOIN.MESSAGE_ID LEFT OUTER JOIN ATTACHMENT ON MESSAGE_ATTACHMENT_JOIN.ATTACHMENT_ID = ATTACHMENT.ROWID LEFT OUTER JOIN HANDLE ON MESSAGE.HANDLE_ID = HANDLE.ROWID WHERE "DATE DELIVERED" IS NOT "N/A"''' ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"SMS Chat Message Delivered function executing") with open( reportfolderbase + "SMS Chat/SMS Message Delivered.html", "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> SMS Chat Message Delivered report</h2>") f.write(f"SMS Chat Message Delivered entries: {usageentries}<br>") f.write( f"SMS Chat Message Delivered database located at: {filefound[0]}<br>" ) f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Message Date</th><th>Date Delivered</th><th>Date Read</th><th>Message</th><th>Contact ID</th><th>Service</th><th>Account</th><th>Is Delivered</th><th>Is from Me</th><th>Filename</th><th>MIME Type</th><th>Transfer Type</th><th>Total Bytes</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td><td>{row[9]}</td><td>{row[10]}</td><td>{row[11]}</td><td>{row[12]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"SMS Chat Message function completed") else: logfunc("No SMS Chat Message Delivered available") except: logfunc( "Error on SMS Chat Message Delivered function. Possible empty database." ) db = sqlite3.connect(filefound[0]) cursor = db.cursor() try: cursor.execute( ''' SELECT CASE WHEN LENGTH(MESSAGE.DATE)=18 THEN DATETIME(MESSAGE.DATE/1000000000+978307200,'UNIXEPOCH') WHEN LENGTH(MESSAGE.DATE)=9 THEN DATETIME(MESSAGE.DATE + 978307200,'UNIXEPOCH') ELSE "N/A" END "MESSAGE DATE", CASE WHEN LENGTH(MESSAGE.DATE_DELIVERED)=18 THEN DATETIME(MESSAGE.DATE_DELIVERED/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_DELIVERED)=9 THEN DATETIME(MESSAGE.DATE_DELIVERED+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE DELIVERED", CASE WHEN LENGTH(MESSAGE.DATE_READ)=18 THEN DATETIME(MESSAGE.DATE_READ/1000000000+978307200,"UNIXEPOCH") WHEN LENGTH(MESSAGE.DATE_READ)=9 THEN DATETIME(MESSAGE.DATE_READ+978307200,"UNIXEPOCH") ELSE "N/A" END "DATE READ", MESSAGE.TEXT as "MESSAGE", HANDLE.ID AS "CONTACT ID", MESSAGE.SERVICE AS "SERVICE", MESSAGE.ACCOUNT AS "ACCOUNT", MESSAGE.IS_DELIVERED AS "IS DELIVERED", MESSAGE.IS_FROM_ME AS "IS FROM ME", ATTACHMENT.FILENAME AS "FILENAME", ATTACHMENT.MIME_TYPE AS "MIME TYPE", ATTACHMENT.TRANSFER_NAME AS "TRANSFER TYPE", ATTACHMENT.TOTAL_BYTES AS "TOTAL BYTES" FROM MESSAGE LEFT OUTER JOIN MESSAGE_ATTACHMENT_JOIN ON MESSAGE.ROWID = MESSAGE_ATTACHMENT_JOIN.MESSAGE_ID LEFT OUTER JOIN ATTACHMENT ON MESSAGE_ATTACHMENT_JOIN.ATTACHMENT_ID = ATTACHMENT.ROWID LEFT OUTER JOIN HANDLE ON MESSAGE.HANDLE_ID = HANDLE.ROWID WHERE "DATE READ" IS NOT "N/A"''' ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"SMS Chat Message Read function executing") with open( reportfolderbase + "SMS Chat/SMS Message Read.html", "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> SMS Chat Message Read report</h2>") f.write(f"SMS Chat Message Read entries: {usageentries}<br>") f.write(f"SMS Chat Message Readdatabase located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Message Date</th><th>Date Delivered</th><th>Date Read</th><th>Message</th><th>Contact ID</th><th>Service</th><th>Account</th><th>Is Delivered</th><th>Is from Me</th><th>Filename</th><th>MIME Type</th><th>Transfer Type</th><th>Total Bytes</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td><td>{row[9]}</td><td>{row[10]}</td><td>{row[11]}</td><td>{row[12]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"SMS Chat Message Read function completed") else: logfunc("No SMS Chat Message Read available") except: logfunc("Error on SMS Chat Message Read available. Posible empty database. ")
def medlib(filefound): try: db = sqlite3.connect(filefound[0]) cursor = db.cursor() cursor.execute( """ select ext.title AS "Title", ext.media_kind AS "Media Type", itep.format AS "File format", ext.location AS "File", ext.total_time_ms AS "Total time (ms)", ext.file_size AS "File size", ext.year AS "Year", alb.album AS "Album Name", alba.album_artist AS "Artist", com.composer AS "Composer", gen.genre AS "Genre", art.artwork_token AS "Artwork", itev.extended_content_rating AS "Content rating", itev.movie_info AS "Movie information", ext.description_long AS "Description", ite.track_number AS "Track number", sto.account_id AS "Account ID", strftime('%d/%m/%Y %H:%M:%S', datetime(sto.date_purchased + 978397200,'unixepoch'))date_purchased, sto.store_item_id AS "Item ID", sto.purchase_history_id AS "Purchase History ID", ext.copyright AS "Copyright" from item_extra ext join item_store sto using (item_pid) join item ite using (item_pid) join item_stats ites using (item_pid) join item_playback itep using (item_pid) join item_video itev using (item_pid) left join album alb on sto.item_pid=alb.representative_item_pid left join album_artist alba on sto.item_pid=alba.representative_item_pid left join composer com on sto.item_pid=com.representative_item_pid left join genre gen on sto.item_pid=gen.representative_item_pid left join item_artist itea on sto.item_pid=itea.representative_item_pid left join artwork_token art on sto.item_pid=art.entity_pid """ ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"Media Library function executing") os.makedirs(os.path.join(reportfolderbase, "Media Library/")) with open( os.path.join(reportfolderbase, "Media Library/Media Library.html"), "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> Media Library report</h2>") f.write(f"Media Library entries: {usageentries}<br>") f.write(f"Media Library located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Title</th><th>Media Type</th><th>File Format</th><th>File</th><th>Total Time (ms)</th><th>File Size</th><th>Year</th><th>Album Name</th><th>Artist</th><th>Composer</th><th>Genre</th><th>Artwork</th><th>Content Rating</th><th>Movie Information</th><th>Description</th><th>Track Number</th><th>Account ID</th><th>Date Purchased</th><th>Item ID</th><th>Purchase History ID</th><th>Copyright</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td><td>{row[9]}</td><td>{row[10]}</td><td>{row[11]}</td><td>{row[12]}</td><td>{row[13]}</td><td>{row[14]}</td><td>{row[15]}</td><td>{row[16]}</td><td>{row[17]}</td><td>{row[18]}</td><td>{row[19]}</td><td>{row[20]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"Media Library function completed") else: logfunc("No Media Library available") except: logfunc("Error in Media Library Section.")
def datausage(filefound): os.makedirs(os.path.join(reportfolderbase, "Data Usage/")) try: db = sqlite3.connect(filefound[0]) cursor = db.cursor() cursor.execute( """ SELECT DATETIME(ZPROCESS.ZTIMESTAMP + 978307200, 'unixepoch') AS "PROCESS TIMESTAMP", DATETIME(ZPROCESS.ZFIRSTTIMESTAMP + 978307200, 'unixepoch') AS "PROCESS FIRST TIMESTAMP", DATETIME(ZLIVEUSAGE.ZTIMESTAMP + 978307200, 'unixepoch') AS "LIVE USAGE TIMESTAMP", ZBUNDLENAME AS "BUNDLE ID", ZPROCNAME AS "PROCESS NAME", ZWIFIIN AS "WIFI IN", ZWIFIOUT AS "WIFI OUT", ZWWANIN AS "WWAN IN", ZWWANOUT AS "WWAN OUT", ZLIVEUSAGE.Z_PK AS "ZLIVEUSAGE TABLE ID" FROM ZLIVEUSAGE LEFT JOIN ZPROCESS ON ZPROCESS.Z_PK = ZLIVEUSAGE.ZHASPROCESS """ ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"Data Usage - Zliveusage function executing") with open( os.path.join(reportfolderbase, "Data Usage/Zliveusage.html"), "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> Zliveusage report</h2>") f.write(f"Zliveusage entries: {usageentries}<br>") f.write(f"Zliveusage located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Process Timestamp</th><th>Process First Timestamp</th><th>Live Usage Timestamp</th><th>Bundle ID</th><th>Process Name</th><th>WIFI In</th><th>WIFI Out</th><th>WWAN IN</th><th>WWAN Out</th><th>Table ID</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td><td>{row[5]}</td><td>{row[6]}</td><td>{row[7]}</td><td>{row[8]}</td><td>{row[9]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"Data Usage - Zliveusage function completed") else: logfunc("No Data Usage - Zliveusage available") except: logfunc("Error in Data Usage - Zliveusage section.") try: db = sqlite3.connect(filefound[0]) cursor = db.cursor() cursor.execute( """ SELECT DATETIME(ZPROCESS.ZTIMESTAMP+ 978307200, 'unixepoch') AS "TIMESTAMP", DATETIME(ZPROCESS.ZFIRSTTIMESTAMP + 978307200, 'unixepoch') AS "PROCESS FIRST TIMESTAMP", ZPROCESS.ZPROCNAME AS "PROCESS NAME", ZPROCESS.ZBUNDLENAME AS "BUNDLE ID", ZPROCESS.Z_PK AS "ZPROCESS TABLE ID" FROM ZPROCESS """ ) all_rows = cursor.fetchall() usageentries = len(all_rows) if usageentries > 0: logfunc(f"Data Usage - Zprocess function executing") with open( os.path.join(reportfolderbase, "Data Usage/Zprocess.html"), "w", encoding="utf8", ) as f: f.write("<html><body>") f.write("<h2> Zprocess report</h2>") f.write(f"Zprocess entries: {usageentries}<br>") f.write(f"Zprocess located at: {filefound[0]}<br>") f.write( "<style> table, td {border: 1px solid black; border-collapse: collapse;}tr:nth-child(even) {background-color: #f2f2f2;} .table th { background: #888888; color: #ffffff}.table.sticky th{ position:sticky; top: 0; }</style>" ) f.write("<br/>") f.write("") f.write(f'<table class="table sticky">') f.write( f"<tr><th>Process Timestamp</th><th>Process First Timestamp</th><th>Live Usage Timestamp</th><th>Bundle ID</th><th>Table ID</th></tr>" ) for row in all_rows: f.write( f"<tr><td>{row[0]}</td><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td><td>{row[4]}</td></tr>" ) f.write(f"</table></body></html>") logfunc(f"Data Usage - Zprocess function completed") else: logfunc("No Data Usage - Zprocess available") except: logfunc("Error in Data Usage - Zprocess Section.")