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_locationDsteps(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("10"):
cursor = db.cursor()
cursor.execute(
"""
SELECT
DATETIME(STARTTIME + 978307200, 'UNIXEPOCH') AS "START TIME",
TIMESTAMP AS "MOVEMENT TIME",
COUNT AS "COUNT",
DISTANCE AS "DISTANCE",
RAWDISTANCE AS "RAWDISTANCE",
FLOORSASCENDED AS "FLOORS ASCENDED",
FLOORSDESCENDED AS "FLOORS DESCENDED",
PACE AS "PACE",
ACTIVETIME AS "ACTIVE TIME",
FIRSTSTEPTIME AS "FIRST STEP TIME",
PUSHCOUNT AS "PUSH COUNT",
WORKOUTTYPE AS "WORKOUT TYPE",
STEPCOUNTHISTORY.ID AS "STEPCOUNTHISTORY TABLE ID"
FROM STEPCOUNTHISTORY
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12]))
description = ''
report = ArtifactHtmlReport('LocationD Steps')
report.start_artifact_report(report_folder, 'Steps', description)
report.add_script()
data_headers = ('Start Time','Movement Time','Count','Distance','Raw Distance','Floors Ascended','Floors Descended','Pace','Active Time','First Step Time','Push Count','Workout Type','Table ID')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'LocationD Steps'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD Steps'
timeline(report_folder, tlactivity, data_list)
else:
logfunc('No data available for Steps')
elif version.parse(iOSversion) >= version.parse("9"):
cursor = db.cursor()
cursor.execute(
"""
SELECT
DATETIME(STARTTIME + 978307200, 'UNIXEPOCH') AS "START TIME",
TIMESTAMP AS "MOVEMENT TIME",
COUNT AS "COUNT",
DISTANCE AS "DISTANCE",
RAWDISTANCE AS "RAWDISTANCE",
FLOORSASCENDED AS "FLOORS ASCENDED",
FLOORSDESCENDED AS "FLOORS DESCENDED",
PACE AS "PACE",
ACTIVETIME AS "ACTIVE TIME",
STEPCOUNTHISTORY.ID AS "STEPCOUNTHISTORY TABLE ID"
FROM STEPCOUNTHISTORY
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
description = ''
report = ArtifactHtmlReport('LocationD Steps')
report.start_artifact_report(report_folder, 'Steps', description)
report.add_script()
data_headers = ('Start Time','Movement Time','Count','Distance','Raw Distance','Floors Ascended','Floors Descended','Pace','Active Time','Table ID')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'LocationD Steps'
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No data available for Steps')
elif version.parse(iOSversion) >= version.parse("8"):
cursor = db.cursor()
cursor.execute(
"""
SELECT
DATETIME(STARTTIME + 978307200, 'UNIXEPOCH') AS "START TIME",
TIMESTAMP AS "MOVEMENT TIME",
COUNT AS "COUNT",
DISTANCE AS "DISTANCE",
RAWDISTANCE AS "RAWDISTANCE",
FLOORSASCENDED AS "FLOORS ASCENDED",
FLOORSDESCENDED AS "FLOORS DESCENDED",
STEPCOUNTHISTORY.ID AS "STEPCOUNTHISTORY TABLE ID"
FROM STEPCOUNTHISTORY
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7]))
description = ''
report = ArtifactHtmlReport('LocationD Steps')
report.start_artifact_report(report_folder, 'Steps', description)
report.add_script()
data_headers = ('Start Time','Movement Time','Count','Distance','Raw Distance','Floors Ascended','Floors Descended','Table ID')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'LocationD Steps'
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No data available for Steps')
else:
logfunc('No data available for Steps')
db.close()
return
def get_fitbitSocial(files_found, report_folder, seeker, wrap_text):
file_found = str(files_found[0])
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT
OWNING_USER_ID,
ENCODED_ID,
DISPLAY_NAME,
AVATAR_URL,
FRIEND,
CHILD
FROM FRIEND
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport('Fitbit Friends')
report.start_artifact_report(report_folder, 'Fitbit Friends')
report.add_script()
data_headers = ('Owning UserID', 'Encoded ID', 'Display Name',
'Avatar URL', 'Friend', 'Child')
data_list = []
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Fitbit Friends'
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No Fitbit Friend data available')
cursor = db.cursor()
cursor.execute('''
SELECT
datetime("LAST_UPDATED"/1000, 'unixepoch'),
DISPLAY_NAME,
FULL_NAME,
ABOUT_ME,
AVATAR_URL,
COVER_PHOTO_URL,
CITY,
STATE,
COUNTRY,
datetime("JOINED_DATE"/1000, 'unixepoch'),
datetime("DATE_OF_BIRTH"/1000, 'unixepoch'),
HEIGHT,
WEIGHT,
GENDER,
COACH
FROM USER_PROFILE
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport('Fitbit User Profile')
report.start_artifact_report(report_folder, 'Fitbit User Profile')
report.add_script()
data_headers = ('Last Updated', 'Display Name', 'Full Name',
'About Me', 'Avatar URL', 'Cover Photo URL', 'City',
'State', 'Country', 'Joined Date', 'Date of Birth',
'Height', 'Weight', 'Gender', 'Coach')
data_list = []
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9], row[10], row[11],
row[12], row[13], row[14]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Fitbit User Profile'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'Fitbit User Profile'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Fitbit User Profile data available')
db.close()
def get_knowCuserwaking(files_found, report_folder, seeker):
iOSversion = scripts.artifacts.artGlobals.versionf
if version.parse(iOSversion) < version.parse("12"):
logfunc("Unsupported version for KnowledgC User Waking Event " +
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",
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 = "/system/userWakingEvent"
""")
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]))
description = ''
report = ArtifactHtmlReport('KnowledgeC User Waking Event')
report.start_artifact_report(report_folder, 'User Waking Event',
description)
report.add_script()
data_headers = ('Start', 'End', '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 User Waking Event'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC User Waking Event'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_package_info(files_found, report_folder, seeker, wrap_text):
packages = []
for file_found in files_found:
file_found = str(file_found)
if file_found.find('{0}mirror{0}'.format(slash)) >= 0:
# Skip sbin/.magisk/mirror/data/.. , it should be duplicate data
continue
elif os.path.isdir(
file_found): # skip folders (there shouldn't be any)
continue
file_name = os.path.basename(file_found)
if (checkabx(file_found)):
multi_root = False
tree = abxread(file_found, multi_root)
xlmstring = (etree.tostring(tree.getroot()).decode())
doc = xmltodict.parse(xlmstring)
else:
with open(file_found) as fd:
doc = xmltodict.parse(fd.read())
package_dict = doc.get('packages', {}).get('package', {})
for package in package_dict:
name = package.get('@name', '')
ft = ReadUnixTimeMs(package.get('@ft', None))
it = ReadUnixTimeMs(package.get('@it', None))
ut = ReadUnixTimeMs(package.get('@ut', None))
install_originator = package.get('@installOriginator', '')
installer = package.get('@installer', '')
code_path = package.get('@codePath', '')
public_flags = hex(
int(package.get('@publicFlags', 0)) & (2**32 - 1))
private_flags = hex(
int(package.get('@privateFlags', 0)) & (2**32 - 1))
package = Package(name, ft, it, ut, install_originator, installer,
code_path, public_flags, private_flags)
packages.append(package)
if len(packages):
break
if report_folder[-1] == slash:
folder_name = os.path.basename(report_folder[:-1])
else:
folder_name = os.path.basename(report_folder)
entries = len(packages)
if entries > 0:
description = "All packages (user installed, oem installed and system) appear here. Many of these are not user apps"
report = ArtifactHtmlReport('Packages')
report.start_artifact_report(report_folder, 'Packages', description)
report.add_script()
data_headers = ('ft', 'Name', 'Install Time', 'Update Time',
'Install Originator', 'Installer', 'Code Path',
'Public Flags', 'Private Flags')
data_list = []
for p in packages:
data_list.append((p.ft, p.name, p.install_time, p.update_time,
p.install_originator, p.installer, p.code_path,
p.public_flags, p.private_flags))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Packages'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'Packages'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No package data available')
def get_routineDLocationsLocal(files_found, report_folder, seeker):
iOSversion = scripts.artifacts.artGlobals.versionf
if version.parse(iOSversion) < version.parse("12"):
logfunc("Unsupported version for RoutineD Locations Local.sqlite on iOS " + iOSversion)
else:
for file_found in files_found:
file_found = str(file_found)
if file_found.endswith('Local.sqlite'):
break
db = sqlite3.connect(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT",
(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "ENTRY TIME (MINUTES)",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID"
FROM
ZRTLEARNEDLOCATIONOFINTERESTVISITMO
LEFT JOIN
ZRTLEARNEDLOCATIONOFINTERESTMO
ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13]))
description = 'Significant Locations - Location of Interest Entry (Historical)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD Locations Entry', description)
report.add_script()
data_headers = ('Entry','Exit','Entry Time (Minutes)','Coordinates','Latitude', 'Longitude','Confidence','Location Uncertainty','Data Point Count','Place Creation Date','Expiration','Visit latitude', 'Visit Longitude', 'Table ID' )
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'RoutineD Locations Entry'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD Locations Entry'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No RoutineD Significant Locations Entry data available')
cursor.execute('''
SELECT
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT",
(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "EXIT TIME (MINUTES)",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID"
FROM
ZRTLEARNEDLOCATIONOFINTERESTVISITMO
LEFT JOIN
ZRTLEARNEDLOCATIONOFINTERESTMO
ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST
''')
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13]))
description = 'Significant Locations - Location of Interest Exit (Historical)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD Locations Exit', description)
report.add_script()
data_headers = ('Entry','Exit','Entry Time (Minutes)','Coordinates','Latitude', 'Longitude','Confidence','Location Uncertainty','Data Point Count','Place Creation Date','Expiration','Visit latitude', 'Visit Longitude', 'Table ID' )
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'RoutineD Locations Exit'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD Locations Exit'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No RoutineD Significant Locations Exit data available')
if version.parse(iOSversion) >= version.parse("12"):
cursor.execute('''
SELECT
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "ENTRY",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "EXIT",
(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "EXIT TIME (MINUTES)",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCONFIDENCE AS "CONFIDENCE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "PLACE CREATION DATE",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLATITUDE AS "VISIT LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONLONGITUDE AS "VISIT LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTVISITMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTVISITMO TABLE ID"
FROM
ZRTLEARNEDLOCATIONOFINTERESTVISITMO
LEFT JOIN
ZRTLEARNEDLOCATIONOFINTERESTMO
ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZLOCATIONOFINTEREST
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]))
description = 'Significant Locations - Location of Interest Transition Start (Historical)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD Transtition Start', description)
report.add_script()
data_headers = ('Start','Stop','Transition Time (Minutes)','Coordinates','Creation Date', 'Expiration','Latitude','Longitude', 'Table ID' )
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'RoutineD Transtition Start'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD Transtition Start'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No RoutineD Significant Locations Transtition Start data available')
if (version.parse(iOSversion) >= version.parse("11")) and (version.parse(iOSversion) < version.parse("12")):
cursor.execute('''
ELECT
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "START",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "STOP",
(ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZEXITDATE-ZRTLEARNEDLOCATIONOFINTERESTVISITMO.ZENTRYDATE)/60.00 AS "TRANSITION TIME (MINUTES)",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "COORDINATES",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "CREATION DATE",
DATETIME(ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLATITUDE AS "LATITUDE",
ZRTLEARNEDLOCATIONOFINTERESTMO.ZLOCATIONLONGITUDE AS "LONGITUDE",
ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.Z_PK AS "ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO TABLE ID"
FROM
ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO
LEFT JOIN
ZRTLEARNEDLOCATIONOFINTERESTMO
ON ZRTLEARNEDLOCATIONOFINTERESTMO.Z_PK = ZRTLEARNEDLOCATIONOFINTERESTTRANSITIONMO.ZLOCATIONOFINTEREST
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]))
description = 'Significant Locations - Location of Interest Transition Stop (Historical)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD Transtition Stop', description)
report.add_script()
data_headers = ('Start','Stop','Transition Time (Minutes)','Coordinates','Creation Date', 'Expiration','Latitude','Longitude', 'Table ID' )
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'RoutineD Transtition Stop'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD Transtition Stop'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No RoutineD Significant Locations Transtition Stop data available')
def get_Viber(files_found, report_folder, seeker, wrap_text):
for file_found in files_found:
if file_found.endswith('_messages'):
viber_messages_db = str(file_found)
# file_found = str(file_found)
if file_found.endswith('_data'):
viber_data_db = str(file_found)
# if file_found.endswith('-db'):
# break
db = open_sqlite_db_readonly(viber_data_db)
cursor = db.cursor()
try:
cursor.execute('''
SELECT canonized_number, case type when 2 then "Outgoing" else "Incoming" end AS direction,
duration as duration_in_seconds, date AS start_time,
case viber_call_type when 1 then "Audio Call" when 4 then "Video Call" else "Unknown" end AS call_type
FROM calls
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Viber - call logs')
report.start_artifact_report(report_folder, 'Viber - call logs')
report.add_script()
data_headers = ('canonized_number','call_direction', 'duration_in_seconds', 'start_time', '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:
data_list.append((row[0], row[1], row[2], row[3], row[4]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Viber - call logs'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'Viber - call logs'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Viber Call Logs found')
try:
cursor.execute('''
SELECT C.display_name, coalesce(D.data2, D.data1, D.data3) as phone_number
FROM phonebookcontact AS C JOIN phonebookdata AS D ON C._id = D.contact_id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Viber - Contacts')
report.start_artifact_report(report_folder, 'Viber - Contacts')
report.add_script()
data_headers = ('display_name','phone_number') # 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'Viber - Contacts'
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No Viber Contacts data available')
db.close()
db = open_sqlite_db_readonly(viber_messages_db)
cursor = db.cursor()
try:
cursor.execute('''
SELECT convo_participants.from_number AS from_number,
convo_participants.recipients AS recipients,
M.conversation_id AS thread_id,
M.body AS msg_content,
case M.send_type when 1 then "Outgoing" else "Incoming" end AS direction,
M.msg_date AS msg_date,
case M.unread when 0 then "Read" else "Unread" end AS read_status,
M.extra_uri AS file_attachment
FROM (SELECT *,
group_concat(TO_RESULT.number) AS recipients
FROM (SELECT P._id AS FROM_ID,
P.conversation_id,
PI.number AS FROM_NUMBER
FROM participants AS P
JOIN participants_info AS PI
ON P.participant_info_id = PI._id) AS FROM_RESULT
JOIN (SELECT P._id AS TO_ID,
P.conversation_id,
PI.number
FROM participants AS P
JOIN participants_info AS PI
ON P.participant_info_id = PI._id) AS TO_RESULT
ON FROM_RESULT.from_id != TO_RESULT.to_id
AND FROM_RESULT.conversation_id = TO_RESULT.conversation_id
GROUP BY FROM_RESULT.from_id) AS convo_participants
JOIN messages AS M
ON M.participant_id = convo_participants.from_id
AND M.conversation_id = convo_participants.conversation_id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Viber - Messages')
report.start_artifact_report(report_folder, 'Viber - Messages')
report.add_script()
data_headers = ('from_number','recipients', 'thread_id', 'msg_content', 'direction', 'msg_date', 'read_status', 'file_attachment') # 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]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Viber - Messages'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'Viber - Messages'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Viber Messages found')
db.close
return
def get_calllog(files_found, report_folder, seeker, wrap_text):
file_found = str(files_found[0])
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT
datetime(date /1000, 'unixepoch') as date,
CASE
WHEN phone_account_address is NULL THEN ' '
ELSE phone_account_address
end as phone_account_address,
number,
CASE
WHEN type = 1 THEN 'Incoming'
WHEN type = 2 THEN 'Outgoing'
WHEN type = 3 THEN 'Missed'
WHEN type = 4 THEN 'Voicemail'
WHEN type = 5 THEN 'Rejected'
WHEN type = 6 THEN 'Blocked'
WHEN type = 7 THEN 'Answered Externally'
ELSE 'Unknown'
end as types,
duration,
CASE
WHEN geocoded_location is NULL THEN ' '
ELSE geocoded_location
end as geocoded_location,
countryiso,
CASE
WHEN _data is NULL THEN ' '
ELSE _data
END as _data,
CASE
WHEN mime_type is NULL THEN ' '
ELSE mime_type
END as mime_type,
CASE
WHEN transcription is NULL THEN ' '
ELSE transcription
END as transcription,
deleted
FROM
calls
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport('Call logs')
report.start_artifact_report(report_folder, 'Call logs')
report.add_script()
data_headers = ('Call Date', 'Phone Account Address', 'Partner',
'Type', 'Duration in Secs', 'Partner Location',
'Country ISO', 'Data', 'Mime Type', 'Transcription',
'Deleted')
data_list = []
for row in all_rows:
# Setup icons for call type
call_type = row[3]
if call_type == 'Incoming':
call_type_html = call_type + ' <i data-feather="phone-incoming" stroke="green"></i>'
elif call_type == 'Outgoing':
call_type_html = call_type + ' <i data-feather="phone-outgoing" stroke="green"></i>'
elif call_type == 'Missed':
call_type_html = call_type + ' <i data-feather="phone-missed" stroke="red"></i>'
elif call_type == 'Voicemail':
call_type_html = call_type + ' <i data-feather="voicemail" stroke="brown"></i>'
elif call_type == 'Rejected':
call_type_html = call_type + ' <i data-feather="x" stroke="red"></i>'
elif call_type == 'Blocked':
call_type_html = call_type + ' <i data-feather="phone-off" stroke="red"></i>'
elif call_type == 'Answered Externally':
call_type_html = call_type + ' <i data-feather="phone-forwarded"></i>'
else:
call_type_html = call_type
data_list.append(
(row[0], row[1], row[2], call_type_html, str(row[4]), row[5],
row[6], row[7], row[8], row[9], str(row[10])))
report.write_artifact_data_table(data_headers,
data_list,
file_found,
html_escape=False)
report.end_artifact_report()
tsvname = f'Call Logs'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Call Logs'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Call Log data available')
db.close()
return
def get_chromeAutofill(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) == 'Web Data': # 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()
columns = [i[1] for i in cursor.execute('PRAGMA table_info(autofill)')]
if 'date_created' in columns:
cursor.execute(f'''
select
datetime(date_created, 'unixepoch'),
name,
value,
datetime(date_last_used, 'unixepoch'),
count
from autofill
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Autofill - Entries')
#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} - Autofill - Entries.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 = ('Date Created','Field','Value','Date Last Used','Count')
data_list = []
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3],row[4]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Autofill - Entries'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Autofill - Entries'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Autofill - Entries data available')
else:
cursor.execute(f'''
select
datetime(autofill_dates.date_created, 'unixepoch'),
autofill.name,
autofill.value,
autofill.count
from autofill
join autofill_dates on autofill_dates.pair_id = autofill.pair_id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Autofill - Entries')
#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} - Autofill - Entries.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 = ('Date Created','Field','Value','Count')
data_list = []
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Autofill - Entries'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Autofill - Entries'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Autofill - Entries data available')
cursor.execute(f'''
select
datetime(date_modified, 'unixepoch'),
autofill_profiles.guid,
autofill_profile_names.first_name,
autofill_profile_names.middle_name,
autofill_profile_names.last_name,
autofill_profile_emails.email,
autofill_profile_phones.number,
autofill_profiles.company_name,
autofill_profiles.street_address,
autofill_profiles.city,
autofill_profiles.state,
autofill_profiles.zipcode,
datetime(use_date, 'unixepoch'),
autofill_profiles.use_count
from autofill_profiles
inner join autofill_profile_emails ON autofill_profile_emails.guid = autofill_profiles.guid
inner join autofill_profile_phones ON autofill_profiles.guid = autofill_profile_phones.guid
inner join autofill_profile_names ON autofill_profile_phones.guid = autofill_profile_names.guid
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Autofill - Profiles')
#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} - Autofill - Profiles.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 = ('Date Modified','GUID','First Name','Middle Name','Last Name','Email','Phone Number','Company Name','Address','City','State','Zip Code','Date Last Used','Use Count')
data_list = []
for row in all_rows:
data_list.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Autofill - Profiles'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Autofill - Profiles'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Autofill - Profiles data available')
db.close()
def get_calendarAll(files_found, report_folder, seeker):
file_found = str(files_found[0])
#os.chmod(file_found, 0o0777)
db = sqlite3.connect(file_found)
cursor = db.cursor()
cursor.execute("""
select
title,
flags,
color,
symbolic_color_name,
external_id,
self_identity_email
from Calendar
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5]))
description = ''
report = ArtifactHtmlReport('Calendar List')
report.start_artifact_report(report_folder, 'List', description)
report.add_script()
data_headers = ('Title', 'Flags', 'Color', 'Symbolic Color Name',
'External ID', 'Self Identity Email')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'Calendar List '
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No data available for Calendar List')
cursor.execute("""
Select
DATETIME(start_date + 978307200, 'UNIXEPOCH') as startdate,
start_tz,
DATETIME(end_date + 978307200, 'UNIXEPOCH') as enddate,
end_tz,
all_day,
summary,
calendar_id,
DATETIME(last_modified+ 978307200, 'UNIXEPOCH') as lastmod
from CalendarItem
order by startdate
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7]))
description = ''
report = ArtifactHtmlReport('Calendar Items')
report.start_artifact_report(report_folder, 'Items', description)
report.add_script()
data_headers = ('Start Date', 'Start Timezone', 'End Date',
'End Timezone', 'All Day?', 'Summary', 'Calendar ID',
'Last Modified')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'Calendar Items'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Calendar Items'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available for Calendar Items')
cursor.execute("""
SELECT
display_name,
address,
first_name,
last_name
from Identity
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3]))
description = ''
report = ArtifactHtmlReport('Calendar Identity')
report.start_artifact_report(report_folder, 'Identity', description)
report.add_script()
data_headers = ('Display Name', 'Address', 'First Name', 'Last Name')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'Calendar Identity'
tsv(report_folder, data_headers, data_list, tsvname)
else:
logfunc('No data available for Calendar Identity')
def get_locationDcellloc(files_found, report_folder, seeker):
file_found = str(files_found[0])
#os.chmod(file_found, 0o0777)
db = sqlite3.connect(file_found)
cursor = db.cursor()
# The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/locationd_cacheencryptedAB_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')
db.close()
return
def get_googleNowPlaying(files_found, report_folder, seeker, wrap_text):
for file_found in files_found:
file_found = str(file_found)
if file_found.find('{0}mirror{0}'.format(slash)) >= 0:
# Skip sbin/.magisk/mirror/data/.. , it should be duplicate data
continue
elif not file_found.endswith('history_db'):
continue # Skip all other files (-wal)
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
Select
CASE
timestamp
WHEN
"0"
THEN
""
ELSE
datetime(timestamp / 1000, "unixepoch")
END AS "timestamp",
history_entry
FROM
recognition_history
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
description = 'This is data stored by the Now Playing feature in Pixel phones, which '\
'shows song data on the lock screen for any music playing nearby. It\'s ' \
'part of <a href="https://play.google.com/store/apps/details?id=com.google.intelligence.sense"'\
' target="_blank">Pixel Ambient Services</a> or part of <a href="https://play.google.com/store/apps/details?id=com.google.android.as"'\
' target="_blank">Pixel Device Personalization Services</a> depending on OS version.'
report = ArtifactHtmlReport('Now Playing History')
report.start_artifact_report(report_folder, 'Now Playing', description)
report.add_script()
data_headers = ('Timestamp', 'Timezone', 'Song Title', 'Artist', 'Duration',
'Album', 'Album Year')
data_list = []
pb_types = {'9': {'type': 'message', 'message_typedef':
{
'6': {'type': 'double', 'name': ''} # This definition converts field to a double from generic fixed64
} }
}
last_data_set = [] # Since there are a lot of similar entries differing only in timestamp, we can combine them.
for row in all_rows:
timestamp = row[0]
pb = row[1]
data, actual_types = blackboxprotobuf.decode_message(pb, pb_types)
data = recursive_convert_bytes_to_str(data)
try: timezones = FilterInvalidValue(data["7"])
except KeyError: timezones = ''
try: songtitle = FilterInvalidValue(data["9"]["3"])
except KeyError: songtitle = ''
try: artist = FilterInvalidValue(data["9"]["4"])
except KeyError: artist = ''
try: durationinsecs = data["9"]["6"]
except KeyError: durationinsecs = ''
try: album = FilterInvalidValue(data["9"]["13"])
except KeyError: album = ''
try: year = FilterInvalidValue(data["9"]["14"])
except KeyError: year = ''
if durationinsecs:
duration = time.strftime('%H:%M:%S', time.gmtime(durationinsecs))
if not last_data_set:
last_data_set = [timestamp, escape(timezones), escape(songtitle), escape(artist), duration, escape(album), year]
elif AreContentsSame(last_data_set, timezones, songtitle, artist, duration, album, year):
if last_data_set[0] == timestamp: # exact duplicate, do not add
pass
else:
last_data_set[0] += ',<br />' + timestamp
else:
data_list.append(last_data_set)
last_data_set = []
if last_data_set:
data_list.append(last_data_set)
logfunc("{} entries grouped into {}".format(usageentries, len(data_list)))
report.write_artifact_data_table(data_headers, data_list, file_found, html_escape=False)
report.end_artifact_report()
tsvname = f'google now playing'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'Google Now Playing'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Now playing history')
db.close()
return
def get_knowCappact(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",
ZOBJECT.ZVALUESTRING AS "BUNDLE ID",
ZSOURCE.ZGROUPID AS "GROUP ID",
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__SUGGESTEDINVOCATIONPHRASE AS "SUGGESTED IN VOCATION PHRASE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDUNIQUEIDENTIFIER AS "UNIQUE ID",
ZSOURCE.ZSOURCEID AS "SOURCE ID",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMIDENTIFIER AS "ID",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYUUID AS "ACTIVITY 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",
DATETIME(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION DATE",
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 "/app/activity"
''')
else:
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_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__TITLE AS "TITLE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDCONTENTURL AS "CONTENT URL",
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",
DATETIME(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION DATE",
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 "/app/activity"
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
data_list = []
if version.parse(iOSversion) >= version.parse("12"):
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11], row[12],
row[13], row[14], row[15], row[16], row[17], row[18]))
report = ArtifactHtmlReport('KnowledgeC Application Activity')
report.start_artifact_report(report_folder, 'Application Activity')
report.add_script()
data_headers = ('Start', 'End', 'Bundle ID', 'Group ID',
'Activity Type', 'Content Description',
'User Activity Required String', 'Content URL',
'Suggest Invocation Phrase', 'Unique ID',
'Source ID', 'ID', 'Activity UUID', 'Day of Week',
'GMT Offset', 'Entry Creation', 'Expiration Date',
'UUID', 'ZOBJECT Table ID')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'KnowledgeC Application Activity'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC Application Activity'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11]))
report = ArtifactHtmlReport('KnowledgeC Application Activity')
report.start_artifact_report(report_folder, 'Application Activity')
report.add_script()
data_headers = ('Start', 'End', 'Bundle ID', 'Activity Type',
'Title', 'Content URL', 'Day of Week',
'GMT Offset', 'Entry Creation', 'Expiration Date',
'ZOBJECT Table ID')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'KnowledgeC Application Activity'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC Application Activity'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_Oruxmaps(files_found, report_folder, seeker, wrap_text):
file_found = str(files_found[0])
source_file = file_found.replace(seeker.directory, '')
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT poilat, poilon, poialt, poitime/1000, poiname FROM pois
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport('Oruxmaps POI')
report.start_artifact_report(report_folder, 'Oruxmaps POI')
report.add_script()
data_headers = (
'poilat', 'poilon', 'poialt', 'poitime', 'poiname'
) # 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:
timestamp = datetime.datetime.fromtimestamp(int(
row[3])).strftime('%Y-%m-%d %H:%M:%S')
data_list.append((row[0], row[1], row[2], timestamp, row[4]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Oruxmaps POI'
tsv(report_folder, data_headers, data_list, tsvname, source_file)
tlactivity = f'Oruxmaps POI'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Oruxmaps POI data available')
cursor.execute('''
SELECT tracks._id, trackname, trackciudad, segname, trkptlat, trkptlon, trkptalt, trkpttime/1000
FROM tracks, segments, trackpoints
where tracks._id = segments.segtrack and segments._id = trackpoints.trkptseg
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport('Oruxmaps Tracks')
report.start_artifact_report(report_folder, 'Oruxmaps Tracks')
report.add_script()
data_headers = (
'track id', 'track name', 'track description', 'segment name',
'latitude', 'longitude', 'altimeter', 'datetime'
) # 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:
timestamp = datetime.datetime.fromtimestamp(int(
row[7])).strftime('%Y-%m-%d %H:%M:%S')
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5],
row[6], timestamp))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Oruxmaps Tracks'
tsv(report_folder, data_headers, data_list, tsvname, source_file)
tlactivity = f'Oruxmaps Tracks'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Oruxmaps Tracks data available')
db.close()
return
def get_powerlogVolumePercentage(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = sqlite3.connect(file_found)
cursor = db.cursor()
# The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/powerlog_audio_volume.txt
# from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt
cursor.execute('''
SELECT
DATETIME(VOLUME_TIMESTAMP + SYSTEM, 'UNIXEPOCH') AS ADJUSTED_TIMESTAMP,
VOLUME AS "VOLUME PERCENTAGE",
CASE MUTED
WHEN "0" THEN "NO"
WHEN "1" THEN "YES"
END AS "MUTED",
DATETIME(VOLUME_TIMESTAMP, 'UNIXEPOCH') AS ORIGINAL_VOLUME_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'UNIXEPOCH') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
VOLUME_ID AS "PLAUDIOAGENT_EVENTFORWARD_OUTPUT TABLE ID"
FROM
(
SELECT
VOLUME_ID,
VOLUME_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
VOLUME,
MUTED,
SYSTEM
FROM
(
SELECT
PLAUDIOAGENT_EVENTFORWARD_OUTPUT.TIMESTAMP AS VOLUME_TIMESTAMP,
VOLUME,
MUTED,
PLAUDIOAGENT_EVENTFORWARD_OUTPUT.ID AS "VOLUME_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLAUDIOAGENT_EVENTFORWARD_OUTPUT
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS VOLUME_STATE
GROUP BY
VOLUME_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 Volume Percentage')
report.start_artifact_report(report_folder, 'Volume Percentage')
report.add_script()
data_headers = ('Adjusted Timestamp', 'Volume Percentage', 'Muted',
'Original Volume Timestamp', 'Offset Timestamp',
'Time Offset', 'Event Forward Output Table ID')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'Powerlog Volume Percentage'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Powerlog Volume Percentage'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_knowCappactsafari(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()
# The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/knowledge_safari_browsing.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",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__CONTENTDESCRIPTION AS "CONTENT DESCRIPTION",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDCONTENTURL AS "CONTENT URL",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYREQUIREDSTRING AS "USER ACTIVITY REQUIRED STRING",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMIDENTIFIER AS "ID",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDUNIQUEIDENTIFIER AS "UNIQUE ID",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYUUID AS "ACTIVITY UUID",
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",
DATETIME(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION DATE",
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 "/app/activity"
AND ("BUNDLE ID" = "com.apple.mobilesafari" OR "BUNDLE ID" = "com.apple.Safari")
''')
else:
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_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDCONTENTURL AS "CONTENT URL",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMIDENTIFIER AS "ID",
ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ITEMRELATEDUNIQUEIDENTIFIER AS "UNIQUE 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",
DATETIME(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "EXPIRATION DATE",
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 "/app/activity"
AND ("BUNDLE ID" = "com.apple.mobilesafari" OR "BUNDLE ID" = "com.apple.Safari")
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
data_list = []
if version.parse(iOSversion) >= version.parse("12"):
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11], row[12],
row[13], row[14], row[15], row[16]))
report = ArtifactHtmlReport(
'KnowledgeC Application Activity Safari')
report.start_artifact_report(report_folder,
'Application Activity Safari')
report.add_script()
data_headers = ('Start', 'End', 'Bundle ID', 'Activity Type',
'Content Description', 'Content URL',
'User Activity Required String', 'ID', 'Unique ID',
'Activity UUID', 'Source ID', 'Day of Week',
'GMT Offset', 'Entry Creation', 'Expiration Date',
'UUID', 'ZOBJECT Table ID')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'KnowledgeC Application Activity Safari'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC Application Activity Safari'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13]))
report = ArtifactHtmlReport(
'KnowledgeC Application Activty Safari')
report.start_artifact_report(report_folder,
'Application Activity Safari')
report.add_script()
data_headers = ('Start', 'End', 'Bundle ID', 'Activity Type',
'Content URL', 'ID', 'Unique ID', 'Source ID',
'Day of Week', 'GMT Offset', 'Entry Creation',
'Expiration Date', 'UUID', 'ZOBJECT Table ID')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'KnowledgeC Application Activity Safari'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC Application Activity Safari'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_healthElevation(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = sqlite3.connect(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT
DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH') AS "START DATE",
DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH') AS "END DATE",
METADATA_VALUES.NUMERICAL_VALUE/100.00 AS "ELEVATION (METERS)",
(METADATA_VALUES.NUMERICAL_VALUE/100.00)*3.28084 AS "ELEVATION (FEET)",
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" || "-" || WORKOUTS.ACTIVITY_TYPE
END "WORKOUT TYPE",
WORKOUTS.DURATION / 60.00 AS "DURATION (IN MINUTES)",
WORKOUTS.TOTAL_ENERGY_BURNED AS "CALORIES BURNED",
WORKOUTS.TOTAL_DISTANCE AS "DISTANCE IN KILOMETERS",
WORKOUTS.TOTAL_DISTANCE*0.621371 AS "DISTANCE IN MILES",
WORKOUTS.TOTAL_BASAL_ENERGY_BURNED AS "TOTAL BASEL ENERGY BURNED",
CASE WORKOUTS.GOAL_TYPE
WHEN 2 THEN "MINUTES"
WHEN 0 THEN "OPEN"
END "GOAL TYPE",
WORKOUTS.GOAL AS "GOAL",
WORKOUTS.TOTAL_FLIGHTS_CLIMBED AS "FLIGHTS CLIMBED",
WORKOUTS.TOTAL_W_STEPS AS "STEPS"
FROM
SAMPLES
LEFT OUTER JOIN
METADATA_VALUES
ON METADATA_VALUES.OBJECT_ID = SAMPLES.DATA_ID
LEFT OUTER JOIN
METADATA_KEYS
ON METADATA_KEYS.ROWID = METADATA_VALUES.KEY_ID
LEFT OUTER JOIN
WORKOUTS
ON WORKOUTS.DATA_ID = SAMPLES.DATA_ID
WHERE
WORKOUTS.ACTIVITY_TYPE NOT NULL AND (KEY IS "_HKPrivateWorkoutElevationAscendedQuantity" OR KEY IS "HKElevationAscended")
''')
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]))
report = ArtifactHtmlReport('Health Workout Indoor Elevation')
report.start_artifact_report(report_folder, 'Workout Indoor Elevation')
report.add_script()
data_headers = ('Start Date', 'End Date', 'Elevation in Meters',
'Elevation in Feet', '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 Elevation'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Health Elevation'
timeline(report_folder, tlactivity, data_list)
else:
logfunc('No data available in table')
db.close()
return
def get_powerlogLocuseapp(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()
# The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/powerlog_location_client_status.txt
# from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt
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)
tlactivity = 'Powerlog Location Use by App'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in Location Use by App')
db.close()
return
def get_powerlogWifiprop(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = sqlite3.connect(file_found)
cursor = db.cursor()
# The following SQL query is taken from # The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/powerlog_wifi_properties.txt
# from Sarah Edward's APOLLO project, and used under terms of its license found under Licenses/apollo.LICENSE.txt
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)
tlactivity = 'Powerlog Wifi Prop'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_powerlogProcessdatausage(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = sqlite3.connect(file_found)
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)
tlactivity = 'Powerlog Process Data Usage'
timeline(report_folder, tlactivity, data_list)
else:
logfunc('No data available in table')
db.close()
return
def get_locationDallB(files_found, report_folder, seeker):
file_found = str(files_found[0])
db = open_sqlite_db_readonly(file_found)
iOSversion = scripts.artifacts.artGlobals.versionf
if version.parse(iOSversion) >= version.parse("11"):
logfunc("Unsupported version for LocationD App Harvest on iOS " +
iOSversion)
else:
logfunc(iOSversion)
cursor = db.cursor()
cursor.execute("""
select
datetime(timestamp + 978307200,'unixepoch'),
bundleid,
altitude,
horizontalaccuracy,
verticalaccuracy,
state,
age,
routinemode,
locationofinteresttype,
latitude,
longitude,
speed,
course,
confidence
from appharvest
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13]))
description = ''
report = ArtifactHtmlReport('LocationD App Harvest')
report.start_artifact_report(report_folder, 'App Harvest',
description)
report.add_script()
data_headers = ('Timestamp', 'Bundle ID', 'Altitude',
'Horizontal Accuracy', 'Vertical Accuracy',
'State', 'Age', 'Routine Mode',
'Location of Interest Type', 'Latitude',
'Longitude', 'Speed', 'Course', 'Confidence')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'LocationD Cell App Harvest'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD Cell App Harvest'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'LocationD Cell App Harvest'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No data available for LocationD App Harvest')
if does_table_exist(db, "cdmacelllocation"):
cursor = db.cursor()
cursor.execute("""
select
datetime(timestamp + 978307200,'unixepoch'),
mcc,
sid,
nid,
bsid,
zoneid,
bandclass,
channel,
pnoffset,
altitude,
speed,
course,
confidence,
horizontalaccuracy,
verticalaccuracy,
latitude,
longitude
from cdmacelllocation
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11], row[12],
row[13], row[14], row[15], row[16]))
description = ''
report = ArtifactHtmlReport('LocationD CDMA Location')
report.start_artifact_report(report_folder, 'CDMA Location',
description)
report.add_script()
data_headers = ('Timestamp', 'MCC', 'SID', 'NID', 'BSID', 'ZONEID',
'BANDCLASS', 'Channel', 'PNOFFSET', 'Altitude',
'Speed', 'Course', 'Confidence',
'Horizontal Accuracy', 'Vertical Accuracy',
'Latitude', 'Longitude')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'LocationD CDMA Location'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD CDMA Location'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'LocationD CDMA Location'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No data available for LocationD CDMA Location')
if does_table_exist(db, "celllocation"):
cursor = db.cursor()
cursor.execute("""
select
datetime(timestamp + 978307200,'unixepoch'),
mcc,
mnc,
lac,
ci,
uarfcn,
psc,
altitude,
speed,
course,
confidence,
horizontalaccuracy,
verticalaccuracy,
latitude,
longitude
from celllocation
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13], row[14]))
description = ''
report = ArtifactHtmlReport('LocationD Cell Location')
report.start_artifact_report(report_folder, 'Cell Location',
description)
report.add_script()
data_headers = ('Timestamp', 'MCC', 'MNC', 'LAC', 'CI', 'UARFCN',
'PSC', 'Altitude', 'Speed', 'Course', 'Confidence',
'Horizontal Accuracy', 'Vertical Accuracy',
'Latitude', 'Longitude')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'LocationD Cell Location'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD Cell Location'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'LocationD Cell Location'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No data available for LocationD Cell Location')
if does_table_exist(db, "ltecelllocation"):
cursor = db.cursor()
cursor.execute("""
select
datetime(timestamp + 978307200,'unixepoch'),
mcc,
mnc,
ci,
uarfcn,
pid,
altitude,
speed,
course,
confidence,
horizontalaccuracy,
verticalaccuracy,
latitude,
longitude
from ltecelllocation
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9],
row[10], row[11], row[12], row[13]))
description = ''
report = ArtifactHtmlReport('LocationD LTE Location')
report.start_artifact_report(report_folder, 'LTE Location',
description)
report.add_script()
data_headers = ('Timestamp', 'MCC', 'MNC', 'CI', 'UARFCN', 'PID',
'Altitude', 'Speed', 'Course', 'Confidence',
'Horizontal Accuracy', 'Vertical Accuracy',
'Latitude', 'Longitude')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'LocationD LTE Location'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD LTE Location'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'LocationD LTE Location'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No data available for LocationD LTE Location')
cursor = db.cursor()
cursor.execute("""
select
datetime(timestamp + 978307200,'unixepoch'),
mac,
channel,
infomask,
speed,
course,
confidence,
score,
reach,
horizontalaccuracy,
verticalaccuracy,
latitude,
longitude
from wifilocation
""")
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11], row[12]))
description = ''
report = ArtifactHtmlReport('LocationD WiFi Location')
report.start_artifact_report(report_folder, 'WiFi Location',
description)
report.add_script()
data_headers = ('Timestamp', 'MAC', 'Channel', 'Infomask', 'Speed',
'Course', 'Confidence', 'Score', 'Reach',
'Horizontal Accuracy', 'Vertical Accuracy', 'Latitude',
'Longitude')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'LocationD WiFi Location'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'LocationD WiFi Location'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'LocationD WiFi Location'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No data available for LocationD WiFi Location')
def get_routineDlocations(files_found, report_folder, seeker):
iOSversion = scripts.artifacts.artGlobals.versionf
if version.parse(iOSversion) < version.parse("10"):
logfunc(
"Unsupported version for RoutineD Locations Cache.sqlite on iOS " +
iOSversion)
else:
for file_found in files_found:
file_found = str(file_found)
if file_found.endswith('Cache.sqlite'):
break
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
select
datetime(ztimestamp + 978307200, 'unixepoch'),
zaltitude,
zcourse,
zspeed,
zspeed*2.23694,
zspeed*3.6,
zhorizontalaccuracy,
zverticalaccuracy,
zlatitude,
zlongitude
from
zrtcllocationmo
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9]))
description = 'Granular location data (~ 1 week)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder,
'RoutineD ZRTCLLOCATIONMO',
description)
report.add_script()
data_headers = ('Timestamp', 'Altitude', 'Course', 'Speed (M/S)',
'Speed (MPH)', 'Speed (KMPH)',
'Horizontal Accuracy', 'Vertical Accuracy',
'Latitude', 'Longitude')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'RoutineD ZRTCLLOCATIONMO'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD ZRTCLLOCATIONMO'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'RoutineD ZRTCLLOCATIONMO'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No RoutineD ZRTCLLOCATIONMO data available')
cursor.execute('''
select
datetime(zdate + 978307200, 'unixepoch'),
zsource,
zlatitude,
zlongitude
from
zrthintmo
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3]))
description = 'Semi-granular location data (~ 1 week)'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD ZRTHINTMO',
description)
report.add_script()
data_headers = ('Timestamp', 'Source', 'Latitude', 'Longitude')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'RoutineD ZRTHINTMO'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD ZRTHINTMO'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'RoutineD ZRTHINTMO'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No RoutineD ZRTHINTMO data available')
cursor.execute('''
select
datetime(zentrydate + 978307200, 'unixepoch'),
datetime(zexitdate + 978307200, 'unixepoch'),
datetime(zdetectiondate + 978307200, 'unixepoch'),
(zexitdate-zentrydate)/60.00,
ztype,
zlocationlatitude,
zlocationlongitude,
zlocationuncertainty
from
zrtvisitmo
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7]))
description = 'Visit locations'
report = ArtifactHtmlReport('Locations')
report.start_artifact_report(report_folder, 'RoutineD ZRTVISITMO',
description)
report.add_script()
data_headers = ('Timestamp', 'Exit Timestamp',
'Detection Timestamp', 'Visit Time (Minutes)',
'Type', 'Latitude', 'Longitude', 'Uncertainty')
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = 'RoutineD ZRTVISITMO'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'RoutineD ZRTVISITMO'
timeline(report_folder, tlactivity, data_list, data_headers)
kmlactivity = 'RoutineD ZRTVISITMO'
kmlgen(report_folder, kmlactivity, data_list, data_headers)
else:
logfunc('No RoutineD ZRTVISITMO data available')
def get_knowCwatchnear(files_found, report_folder, seeker):
iOSversion = scripts.artifacts.artGlobals.versionf
if version.parse(iOSversion) == version.parse("11"):
pass
else:
logfunc("Unsupported version for KnowledgC Watch Near on iOS " +
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_device_watch_nearby.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",
CASE ZOBJECT.ZVALUEINTEGER
WHEN '0' THEN 'NO'
WHEN '1' THEN 'YES'
END "WATCH NEARBY",
(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 "/watch/nearby"
""")
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 Watch Near')
report.start_artifact_report(report_folder, 'Watch Near', description)
report.add_script()
data_headers = ('Start', 'End', 'Watch Nearby', '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 Watch Near'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'KnowledgeC Watch Near'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
db.close()
return
def get_chrome(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) == 'History': # 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()
#Web History
cursor.execute('''
SELECT
datetime(last_visit_time/1000000 + (strftime('%s','1601-01-01')),'unixepoch') AS LastVisitDate,
url AS URL,
title AS Title,
visit_count AS VisitCount,
typed_count AS TypedCount,
id AS ID,
CASE hidden
WHEN 0 THEN ''
WHEN 1 THEN 'Yes'
END as Hidden
FROM urls
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Web History')
#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} - Web History.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 Visit Time', 'URL', 'Title', 'Visit Count',
'Typed Count', 'ID', 'Hidden')
data_list = []
for row in all_rows:
if wrap_text:
data_list.append((row[0], textwrap.fill(row[1], width=100),
row[2], 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} - Web History'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Web History'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Web History data available')
#Web Visits
cursor.execute('''
SELECT
datetime(visits.visit_time/1000000 + (strftime('%s','1601-01-01')),'unixepoch'),
urls.url,
urls.title,
CASE visits.visit_duration
WHEN 0 THEN ''
ELSE strftime('%H:%M:%f', visits.visit_duration / 1000000.000,'unixepoch')
END as Duration,
CASE visits.transition & 0xff
WHEN 0 THEN 'LINK'
WHEN 1 THEN 'TYPED'
WHEN 2 THEN 'AUTO_BOOKMARK'
WHEN 3 THEN 'AUTO_SUBFRAME'
WHEN 4 THEN 'MANUAL_SUBFRAME'
WHEN 5 THEN 'GENERATED'
WHEN 6 THEN 'START_PAGE'
WHEN 7 THEN 'FORM_SUBMIT'
WHEN 8 THEN 'RELOAD'
WHEN 9 THEN 'KEYWORD'
WHEN 10 THEN 'KEYWORD_GENERATED'
ELSE NULL
END AS CoreTransitionType,
trim((CASE WHEN visits.transition & 0x00800000 THEN 'BLOCKED, ' ELSE '' END ||
CASE WHEN visits.transition & 0x01000000 THEN 'FORWARD_BACK, ' ELSE '' END ||
CASE WHEN visits.transition & 0x02000000 THEN 'FROM_ADDRESS_BAR, ' ELSE '' END ||
CASE WHEN visits.transition & 0x04000000 THEN 'HOME_PAGE, ' ELSE '' END ||
CASE WHEN visits.transition & 0x08000000 THEN 'FROM_API, ' ELSE '' END ||
CASE WHEN visits.transition & 0x10000000 THEN 'CHAIN_START, ' ELSE '' END ||
CASE WHEN visits.transition & 0x20000000 THEN 'CHAIN_END, ' ELSE '' END ||
CASE WHEN visits.transition & 0x40000000 THEN 'CLIENT_REDIRECT, ' ELSE '' END ||
CASE WHEN visits.transition & 0x80000000 THEN 'SERVER_REDIRECT, ' ELSE '' END ||
CASE WHEN visits.transition & 0xC0000000 THEN 'IS_REDIRECT_MASK, ' ELSE '' END),', ')
AS Qualifiers,
Query2.url AS FromURL
FROM visits
LEFT JOIN urls ON visits.url = urls.id
LEFT JOIN (SELECT urls.url,urls.title,visits.visit_time,visits.id FROM visits LEFT JOIN urls ON visits.url = urls.id) Query2 ON visits.from_visit = Query2.id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Web Visits')
#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} - Web Visits.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 = ('Visit Timestamp', 'URL', 'Title', 'Duration',
'Transition Type', 'Qualifier(s)',
'From Visit URL')
data_list = []
for row in all_rows:
if wrap_text:
data_list.append((row[0], textwrap.fill(row[1], width=100),
row[2], 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} - Web Visits'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Web Visits'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Web Visits data available')
#Web Search
cursor.execute('''
SELECT
url,
title,
visit_count,
datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch")
FROM urls
WHERE url like '%search?q=%'
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Search Terms')
#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} - Search Terms.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 Visit Time', 'Search Term', 'URL', 'Title',
'Visit Count')
data_list = []
for row in all_rows:
search = row[0].split('search?q=')[1].split('&')[0]
search = urllib.parse.unquote(search).replace('+', ' ')
if wrap_text:
data_list.append(
(row[3], search, (textwrap.fill(row[0], width=100)),
row[1], row[2]))
else:
data_list.append((row[3], search, row[0], row[1], row[2]))
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Search Terms'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Search Terms'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Search Terms data available')
#Downloads
# check for last_access_time column, an older version of chrome db (32) does not have it
if does_column_exist_in_db(db, 'downloads',
'last_access_time') == True:
last_access_time_query = '''
CASE last_access_time
WHEN "0"
THEN ""
ELSE datetime(last_access_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch")
END AS "Last Access Time"'''
else:
last_access_time_query = "'' as last_access_query"
cursor.execute(f'''
SELECT
CASE start_time
WHEN "0"
THEN ""
ELSE datetime(start_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch")
END AS "Start Time",
CASE end_time
WHEN "0"
THEN ""
ELSE datetime(end_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch")
END AS "End Time",
{last_access_time_query},
tab_url,
target_path,
CASE state
WHEN "0" THEN "In Progress"
WHEN "1" THEN "Complete"
WHEN "2" THEN "Canceled"
WHEN "3" THEN "Interrupted"
WHEN "4" THEN "Interrupted"
END,
CASE danger_type
WHEN "0" THEN ""
WHEN "1" THEN "Dangerous"
WHEN "2" THEN "Dangerous URL"
WHEN "3" THEN "Dangerous Content"
WHEN "4" THEN "Content May Be Malicious"
WHEN "5" THEN "Uncommon Content"
WHEN "6" THEN "Dangerous But User Validated"
WHEN "7" THEN "Dangerous Host"
WHEN "8" THEN "Potentially Unwanted"
WHEN "9" THEN "Allowlisted by Policy"
WHEN "10" THEN "Pending Scan"
WHEN "11" THEN "Blocked - Password Protected"
WHEN "12" THEN "Blocked - Too Large"
WHEN "13" THEN "Warning - Sensitive Content"
WHEN "14" THEN "Blocked - Sensitive Content"
WHEN "15" THEN "Safe - Deep Scanned"
WHEN "16" THEN "Dangerous, But User Opened"
WHEN "17" THEN "Prompt For Scanning"
WHEN "18" THEN "Blocked - Unsupported Type"
END,
CASE interrupt_reason
WHEN "0" THEN ""
WHEN "1" THEN "File Error"
WHEN "2" THEN "Access Denied"
WHEN "3" THEN "Disk Full"
WHEN "5" THEN "Path Too Long"
WHEN "6" THEN "File Too Large"
WHEN "7" THEN "Virus"
WHEN "10" THEN "Temporary Problem"
WHEN "11" THEN "Blocked"
WHEN "12" THEN "Security Check Failed"
WHEN "13" THEN "Resume Error"
WHEN "20" THEN "Network Error"
WHEN "21" THEN "Operation Timed Out"
WHEN "22" THEN "Connection Lost"
WHEN "23" THEN "Server Down"
WHEN "30" THEN "Server Error"
WHEN "31" THEN "Range Request Error"
WHEN "32" THEN "Server Precondition Error"
WHEN "33" THEN "Unable To Get File"
WHEN "34" THEN "Server Unauthorized"
WHEN "35" THEN "Server Certificate Problem"
WHEN "36" THEN "Server Access Forbidden"
WHEN "37" THEN "Server Unreachable"
WHEN "38" THEN "Content Lenght Mismatch"
WHEN "39" THEN "Cross Origin Redirect"
WHEN "40" THEN "Canceled"
WHEN "41" THEN "Browser Shutdown"
WHEN "50" THEN "Browser Crashed"
END,
opened,
received_bytes,
total_bytes
FROM downloads
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(f'{browser_name} - Downloads')
#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} - Downloads.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 = ('Start Time', 'End Time', 'Last Access Time',
'URL', 'Target Path', 'State', 'Danger Type',
'Interrupt Reason', 'Opened?', 'Received Bytes',
'Total Bytes')
data_list = []
for row in all_rows:
data_list.append(
(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10]))
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Downloads'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Downloads'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Downloads data available')
#Search Terms
cursor.execute('''
SELECT
url_id,
term,
id,
url,
datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), "unixepoch")
FROM keyword_search_terms, urls
WHERE url_id = id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
if usageentries > 0:
report = ArtifactHtmlReport(
f'{browser_name} - Keyword Search Terms')
#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} - Keyword Search Terms.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 Visit Time', 'Term', 'URL')
data_list = []
for row in all_rows:
if wrap_text:
data_list.append(
(row[4], row[1], (textwrap.fill(row[3], width=100))))
else:
data_list.append((row[4], row[1], row[3]))
report.write_artifact_data_table(data_headers, data_list,
file_found)
report.end_artifact_report()
tsvname = f'{browser_name} - Keyword Search Terms'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = f'{browser_name} - Keyword Search Terms'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc(f'No {browser_name} - Keyword Search Terms data available')
db.close()
def get_powerlogAirdrop(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(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)
tlactivity = 'KnowledgeC Airdrop Connections Info'
timeline(report_folder, tlactivity, data_list)
else:
logfunc('No data available in Airdop Connection Info')
db.close()
return
def get_kikMessages(files_found, report_folder, seeker):
for file_found in files_found:
file_found = str(file_found)
if file_found.endswith('.sqlite'):
break
db = open_sqlite_db_readonly(file_found)
cursor = db.cursor()
cursor.execute('''
SELECT
datetime(ZKIKMESSAGE.ZRECEIVEDTIMESTAMP +978307200,'UNIXEPOCH') AS RECEIVEDTIME,
datetime(ZKIKMESSAGE.ZTIMESTAMP +978307200,'UNIXEPOCH') as TIMESTAMP,
ZKIKMESSAGE.ZBODY,
case ZKIKMESSAGE.ZTYPE
when 1 then 'Received'
when 2 then 'Sent'
when 3 then 'Group Admin'
when 4 then 'Group Message'
else 'Unknown' end as 'Type',
ZKIKMESSAGE.ZUSER,
ZKIKUSER.ZDISPLAYNAME,
ZKIKUSER.ZUSERNAME,
ZKIKATTACHMENT.ZCONTENT
from ZKIKMESSAGE
left join ZKIKUSER on ZKIKMESSAGE.ZUSER = ZKIKUSER.Z_PK
left join ZKIKATTACHMENT on ZKIKMESSAGE.Z_PK = ZKIKATTACHMENT.ZMESSAGE
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
attachmentName = str(row[7])
thumb = ''
for match in files_found:
if attachmentName in match:
shutil.copy2(match, report_folder)
data_file_name = os.path.basename(match)
thumb = f'<img src="{report_folder}{data_file_name}" width="300"></img>'
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], thumb))
description = 'Kik Messages'
report = ArtifactHtmlReport('Kik Messages')
report.start_artifact_report(report_folder, 'Kik Messages',
description)
report.add_script()
data_headers = ('Received Time', 'Timestamp', 'Message', 'Type',
'User', 'Display Name', 'User Name', 'Attachment Name',
'Attachment')
report.write_artifact_data_table(data_headers,
data_list,
file_found,
html_no_escape=['Attachment'])
report.end_artifact_report()
tsvname = 'Kik Messages'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Kik Messages'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Kik Messages data available')
cursor.execute('''
SELECT
Z_PK,
ZDISPLAYNAME,
ZUSERNAME,
ZEMAIL,
ZJID,
ZFIRSTNAME,
ZLASTNAME,
datetime(ZPPTIMESTAMP/1000,'unixepoch'),
ZPPURL
FROM ZKIKUSER
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
data_list = []
if usageentries > 0:
for row in all_rows:
data_list.append((row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], row[8]))
description = 'Kik Users'
report = ArtifactHtmlReport('Kik Users')
report.start_artifact_report(report_folder, 'Kik Users', description)
report.add_script()
data_headers = ('PK', 'Display Name', 'User Name', 'Email', 'JID',
'First Name', 'Last Name', 'Profile Pic Timestamp',
'Profile Pic URL')
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = 'Kik Users'
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = 'Kik Users'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Kik Users data available')
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()
# The following SQL query is taken from https://github.com/mac4n6/APOLLO/blob/master/modules/knowledge_app_webusage.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 "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_Whatsapp(files_found, report_folder, seeker, wrap_text):
source_file_msg = ''
source_file_wa = ''
whatsapp_msgstore_db = ''
whatsapp_wa_db = ''
for file_found in files_found:
file_name = str(file_found)
if file_name.endswith('msgstore.db'):
whatsapp_msgstore_db = str(file_found)
source_file_msg = file_found.replace(seeker.directory, '')
if file_name.endswith('wa.db'):
whatsapp_wa_db = str(file_found)
source_file_wa = file_found.replace(seeker.directory, '')
db = open_sqlite_db_readonly(whatsapp_msgstore_db)
cursor = db.cursor()
try:
cursor.execute('''
SELECT case CL.video_call when 1 then "Video Call" else "Audio Call" end as call_type,
CL.timestamp/1000 as start_time,
((cl.timestamp/1000) + CL.duration) as end_time,
case CL.from_me when 0 then "Incoming" else "Outgoing" end as call_direction,
J1.raw_string AS from_id,
group_concat(J.raw_string) AS group_members
FROM call_log_participant_v2 AS CLP
JOIN call_log AS CL
ON CL._id = CLP.call_log_row_id
JOIN jid AS J
ON J._id = CLP.jid_row_id
JOIN jid as J1
ON J1._id = CL.jid_row_id
GROUP BY CL._id
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Whatsapp - Group Call Logs')
report.start_artifact_report(report_folder,
'Whatsapp - Group Call Logs')
report.add_script()
data_headers = (
'Start Time', 'End Time', 'Call Type', 'Call Direction', 'From ID',
'Group Members'
) # 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:
starttime = datetime.datetime.fromtimestamp(int(
row[1])).strftime('%Y-%m-%d %H:%M:%S')
endtime = datetime.datetime.fromtimestamp(int(
row[2])).strftime('%Y-%m-%d %H:%M:%S')
data_list.append(
(starttime, endtime, row[0], row[3], row[4], row[5]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Whatsapp - Group Call Logs'
tsv(report_folder, data_headers, data_list, tsvname, source_file_msg)
tlactivity = f'Whatsapp - Group Call Logs'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Whatsapp Group Call Logs found')
try:
cursor.execute('''
SELECT CL.timestamp/1000 as start_time,
case CL.video_call when 1 then "Video Call" else "Audio Call" end as call_type,
((CL.timestamp/1000) + CL.duration) as end_time,
J.raw_string AS num,
case CL.from_me when 0 then "Incoming" else "Outgoing" end as call_direction
FROM call_log AS CL
JOIN jid AS J
ON J._id = CL.jid_row_id
WHERE CL._id NOT IN (SELECT DISTINCT call_log_row_id
FROM call_log_participant_v2)
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Whatsapp - Single Call Logs')
report.start_artifact_report(report_folder,
'Whatsapp - Single Call Logs')
report.add_script()
data_headers = (
'Start Time', 'Call Type', 'End Time', 'Number', 'Call Direction'
) # 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:
starttime = datetime.datetime.fromtimestamp(int(
row[0])).strftime('%Y-%m-%d %H:%M:%S')
endtime = datetime.datetime.fromtimestamp(int(
row[2])).strftime('%Y-%m-%d %H:%M:%S')
data_list.append((starttime, row[1], endtime, row[3], row[4]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Whatsapp - Single Call Logs'
tsv(report_folder, data_headers, data_list, tsvname, source_file_msg)
tlactivity = f'Whatsapp - Single Call Logs'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Whatsapp Single Call Log available')
cursor.execute('''attach database "''' + whatsapp_wa_db + '''" as wadb ''')
try:
cursor.execute('''
SELECT messages.key_remote_jid AS id,
case
when contact_book_w_groups.recipients is null then messages.key_remote_jid
else contact_book_w_groups.recipients
end as recipients,
key_from_me AS direction,
messages.data AS content,
messages.timestamp/1000 AS send_timestamp,
messages.received_timestamp/1000,
case
when messages.remote_resource is null then messages.key_remote_jid
else messages.remote_resource
end AS group_sender,
messages.media_url AS attachment
FROM (SELECT jid,
recipients
FROM wadb.wa_contacts AS contacts
left join (SELECT gjid,
Group_concat(CASE
WHEN jid == "" THEN NULL
ELSE jid
END) AS recipients
FROM group_participants
GROUP BY gjid) AS groups
ON contacts.jid = groups.gjid
GROUP BY jid) AS contact_book_w_groups
join messages
ON messages.key_remote_jid = contact_book_w_groups.jid
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Whatsapp - Messages')
report.start_artifact_report(report_folder, 'Whatsapp - Messages')
report.add_script()
data_headers = (
'Send Timestamp', 'Received Timestamp', 'Message ID', 'Recipients',
'Direction', 'Content', 'Group Sender', 'Attachment'
) # 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:
sendtime = datetime.datetime.fromtimestamp(int(
row[4])).strftime('%Y-%m-%d %H:%M:%S')
receivetime = datetime.datetime.fromtimestamp(int(
row[5])).strftime('%Y-%m-%d %H:%M:%S')
data_list.append((sendtime, receivetime, row[0], row[1], row[2],
row[3], row[6], row[7]))
report.write_artifact_data_table(data_headers, data_list, file_found)
report.end_artifact_report()
tsvname = f'Whatsapp - Messages'
tsv(report_folder, data_headers, data_list, tsvname, source_file_msg)
tlactivity = f'Whatsapp - Messages'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No Whatsapp messages data available')
db.close()
db = open_sqlite_db_readonly(whatsapp_wa_db)
cursor = db.cursor()
try:
cursor.execute('''
SELECT jid,
CASE
WHEN WC.number IS NULL THEN WC.jid
WHEN WC.number == "" THEN WC.jid
ELSE WC.number
END number,
CASE
WHEN WC.given_name IS NULL
AND WC.family_name IS NULL
AND WC.display_name IS NULL THEN WC.jid
WHEN WC.given_name IS NULL
AND WC.family_name IS NULL THEN WC.display_name
WHEN WC.given_name IS NULL THEN WC.family_name
WHEN WC.family_name IS NULL THEN WC.given_name
ELSE WC.given_name
|| " "
|| WC.family_name
END name
FROM wa_contacts AS WC
''')
all_rows = cursor.fetchall()
usageentries = len(all_rows)
except:
usageentries = 0
if usageentries > 0:
report = ArtifactHtmlReport('Whatsapp - Contacts')
report.start_artifact_report(report_folder, 'Whatsapp - Contacts')
report.add_script()
data_headers = (
'Number', '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'Whatsapp - Contacts'
tsv(report_folder, data_headers, data_list, tsvname, source_file_wa)
else:
logfunc('No Whatsapp Contacts found')
db.close
for file_found in files_found:
if ('com.whatsapp_preferences_light.xml' in file_found):
with open(file_found, encoding='utf-8') as fd:
xml_dict = xmltodict.parse(fd.read())
string_dict = xml_dict.get('map', '').get('string', '')
data = []
for i in range(len(string_dict)):
if (string_dict[i]['@name'] == 'push_name'
): # User Profile Name
data.append(string_dict[i]['#text'])
if (string_dict[i]['@name'] == 'my_current_status'
): # User Current Status
data.append(string_dict[i]['#text'])
if (string_dict[i]['@name'] == 'version'
): # User current whatsapp version
data.append(string_dict[i]['#text'])
if (string_dict[i]['@name'] == 'ph'): # User Mobile Number
data.append(string_dict[i]['#text'])
if (string_dict[i]['@name'] == 'cc'): # User country code
data.append(string_dict[i]['#text'])
if (len(data) > 0):
report = ArtifactHtmlReport('Whatsapp - User Profile')
report.start_artifact_report(report_folder,
'Whatsapp - User Profile')
report.add_script()
data_headers = ('Version', 'Name', 'User Status',
'Country Code', 'Mobile Number')
data_list = []
data_list.append(
(data[0], data[3], data[2], data[1], data[4]))
report.write_artifact_data_table(data_headers,
data_list,
file_found,
html_escape=False)
report.end_artifact_report()
tsvname = "Whatsapp - User Profile"
tsv(report_folder, data_headers, data_list, tsvname)
tlactivity = "Whatsapp - User Profile"
timeline(report_folder, tlactivity, data_list,
data_headers)
else:
logfunc("No Whatsapp - Profile data found")
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 = []
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('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)
tlactivity = 'KnowledgeC App Usage'
timeline(report_folder, tlactivity, data_list, data_headers)
else:
logfunc('No data available in table')
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')