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.")
