def get_sms(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = open_sqlite_db_readonly(file_found)
sms_df = pd.read_sql_query(
'''
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",
MESSAGE.ROWID as "MESSAGE ID",
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
''', db)
usageentries = sms_df.shape[0]
if usageentries > 0:
data_list = sms_df.to_records(index=False)
report = ArtifactHtmlReport('SMS - iMessage')
report.start_artifact_report(report_folder, 'SMS - iMessage')
report.add_script()
sms_df = sms_df.rename(
columns={
"MESSAGE DATE": "data-time",
'MESSAGE ID': "message-id",
"MESSAGE": "message",
"CONTACT ID": "data-name",
"IS FROM ME": "from_me",
"MIME TYPE": "content-type"
})
sms_df["data-time"] = pd.to_datetime(sms_df["data-time"])
def copyAttachments(rec):
pathToAttachment = None
if rec["FILENAME"]:
attachment = seeker.search('**' +
rec["FILENAME"].replace('~', '', 1),
return_on_first_hit=True)
if not attachment:
logfunc(
' [!] Unable to extract attachment file: "{}"'.format(
rec['FILENAME']))
return
if is_platform_windows():
destFileName = sanitize_file_name(
os.path.basename(rec["FILENAME"]))
else:
destFileName = os.path.basename(rec["FILENAME"])
pathToAttachment = os.path.join(
(os.path.basename(os.path.abspath(report_folder))),
destFileName)
shutil.copy(attachment[0],
os.path.join(report_folder, destFileName))
return pathToAttachment
sms_df["file-path"] = sms_df.apply(lambda rec: copyAttachments(rec),
axis=1)
num_entries = sms_df.shape[0]
report.write_minor_header(f'Total number of entries: {num_entries}',
'h6')
if file_found.startswith('\\\\?\\'):
file_found = file_found[4:]
report.write_lead_text(f'SMS - iMessage located at: {file_found}')
report.write_raw_html(chat_HTML)
report.add_script(render_chat(sms_df))
data_headers = ('Message Date', 'Message ID', 'Date Delivered',
'Date Read', 'Message', 'Contact ID', 'Service',
'Account', 'Is Delivered', 'Is from Me', 'Filename',
'MIME Type', 'Transfer Type', 'Total Bytes')
report.write_artifact_data_table(data_headers,
data_list,
file_found,
write_total=False,
write_location=False)
report.end_artifact_report()
tsvname = 'SMS - iMessage'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'SMS - iMessage'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No SMS & iMessage data available')
db.close()
return
