Many applications maintain internal state using SQLite databases. The SQLECmd project is an open source resource for known applications and the type of forensic information we can recover.
This artifact is automatically generated from the SQLECmd project
This artifact uses the SQLite library, since the library does not support accurate CPU limits, this artifact can use a lot of CPU despite a CPU limit specified.
Locked or in use SQLite files will be copied to a tempfile and then queried.
If UseFilenames is enabled we only look at known filenames. Disabling it will try to identify all sqlite files within the search glob. This is slower but may find more potential files (e.g. renamed).
name: Generic.Collectors.SQLECmd
description: |
Many applications maintain internal state using SQLite
databases. The SQLECmd project is an open source resource for known
applications and the type of forensic information we can recover.
## NOTES
1. This artifact is automatically generated from the SQLECmd project
2. This artifact uses the SQLite library, since the library does not
support accurate CPU limits, this artifact can use a lot of CPU
despite a CPU limit specified.
3. Locked or in use SQLite files will be copied to a tempfile and
then queried.
4. If UseFilenames is enabled we only look at known
filenames. Disabling it will try to identify all sqlite files
within the search glob. This is slower but may find more
potential files (e.g. renamed).
reference:
- https://github.com/EricZimmerman/SQLECmd
export: |
LET Identify(Query, FileType, OSPath, IdentifyValue) = SELECT {
SELECT *
FROM sqlite(file=OSPath, query=Query)
} AS Hits
FROM scope()
WHERE Hits = IdentifyValue
AND log(message="%v was identified as %v", args=[OSPath, FileType])
LET ApplyFile(IdentifyQuery, FileType, SQLQuery, IdentifyValue) = SELECT *
FROM foreach(row=SQLiteFiles,
query={
SELECT * FROM if(
condition=Identify(Query=IdentifyQuery, FileType=FileType,
OSPath=OSPath, IdentifyValue=IdentifyValue),
then={
SELECT *, OSPath FROM sqlite(file=OSPath, query=SQLQuery)
})
})
parameters:
- name: GlobExpr
description: A glob to search for SQLite files.
type: csv
default: |
Name,Glob
"Bitdefender:Bitdefender Endpoint Security Logs","C:\ProgramData\Bitdefender\Endpoint Security\Logs/**10"
"Bitdefender:Bitdefender Internet Security Logs","C:\ProgramData\Bitdefender\Desktop\Profiles\Logs/**10"
"Chrome:Chrome bookmarks","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Bookmarks*"
"Chrome:Chrome Cookies","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Cookies*"
"Chrome:Chrome Current Session","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Current Session"
"Chrome:Chrome Current Tabs","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Current Tabs"
"Chrome:Chrome Download Metadata","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/DownloadMetadata"
"Chrome:Chrome Extension Cookies","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Extension Cookies"
"Chrome:Chrome Favicons","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Favicons*"
"Chrome:Chrome History","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/History*"
"Chrome:Chrome Last Session","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Last Session"
"Chrome:Chrome Last Tabs","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Last Tabs"
"Chrome:Chrome Sessions Folder","C:\Users\*\AppData\Local\Google\Chrome\User Data\*\Sessions/**10"
"Chrome:Chrome Login Data","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Login Data"
"Chrome:Chrome Media History","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Media History*"
"Chrome:Chrome Network Action Predictor","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Network Action Predictor"
"Chrome:Chrome Network Persistent State","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Network Persistent State"
"Chrome:Chrome Preferences","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Preferences"
"Chrome:Chrome Quota Manager","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/QuotaManager"
"Chrome:Chrome Reporting and NEL","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Reporting and NEL"
"Chrome:Chrome Shortcuts","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Shortcuts*"
"Chrome:Chrome Top Sites","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Top Sites*"
"Chrome:Chrome Trust Tokens","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Trust Tokens*"
"Chrome:Chrome SyncData Database","C:\Users\*\AppData\Local\Google\Chrome\User Data\*\Sync Data/**10/SyncData.sqlite3"
"Chrome:Chrome Visited Links","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Visited Links"
"Chrome:Chrome Web Data","C:\Users\*\AppData\Local\Google\Chrome\User Data\*/**10/Web Data*"
"Chrome:Windows Protect Folder","C:\Users\*\AppData\Roaming\Microsoft\Protect\*/**10"
"Cylance:Cylance ProgramData Logs","C:\ProgramData\Cylance\Desktop/**10"
"Cylance:Cylance Optics Logs","C:\ProgramData\Cylance\Optics\Log/**10"
"Cylance:Cylance Program Files Logs","C:\Program Files\Cylance\Desktop\log/**10"
"Firefox:Addons","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/addons.sqlite*"
"Firefox:Bookmarks","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*\weave/**10/bookmarks.sqlite*"
"Firefox:Cookies","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/cookies.sqlite*"
"Firefox:Downloads","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/downloads.sqlite*"
"Firefox:Extensions","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/extensions.json"
"Firefox:Favicons","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/favicons.sqlite*"
"Firefox:Form history","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/formhistory.sqlite*"
"Firefox:Permissions","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/permissions.sqlite*"
"Firefox:Places","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/places.sqlite*"
"Firefox:Protections","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/protections.sqlite*"
"Firefox:Search","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/search.sqlite*"
"Firefox:Signons","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/signons.sqlite*"
"Firefox:Storage Sync","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/storage-sync.sqlite*"
"Firefox:Webappstore","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/webappstore.sqlite*"
"Firefox:Password","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/key*.db"
"Firefox:Preferences","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/prefs.js"
"Firefox:Sessionstore","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*/**10/sessionstore*"
"Firefox:Sessionstore Folder","C:\Users\*\AppData\Roaming\Mozilla\Firefox\Profiles\*\sessionstore-backups/**10"
"MicrosoftStickyNotes:Microsoft Sticky Notes - Windows 7, 8, and 10 version 1511 and earlier","C:\Users\*\AppData\Roaming\Microsoft\StickyNotes/**10/StickyNotes.snt"
"MicrosoftStickyNotes:Microsoft Sticky Notes - 1607 and later","C:\Users\*\AppData\Local\Packages\Microsoft.MicrosoftStickyNotes*\LocalState/**10/plum.sqlite*"
"TeraCopy:TeraCopy","C:\Users\*\AppData\Roaming\TeraCopy/**10"
"WindowsNotificationsDB:Windows 10 Notification DB","C:\Users\*\AppData\Local\Microsoft\Windows\Notifications/**10/wpndatabase.db"
"WindowsOSUpgradeArtifacts:Update Store.db","C:\ProgramData\USOPrivate\UpdateStore/**10/store.db"
"WindowsYourPhone:Windows Your Phone - All Databases","C:\Users\*\AppData\Local\Packages\Microsoft.YourPhone_8wekyb3d8bbwe\LocalCache\Indexed/**10"
"pCloudDatabase:pCloud Database","C:\Users\*\AppData\Local\pCloud/**10/*.db"
"pCloudDatabase:pCloud Database WAL File","C:\Users\*\AppData\Local\pCloud/**10/*.db-wal"
"pCloudDatabase:pCloud Database Shared Memory File","C:\Users\*\AppData\Local\pCloud/**10/*.db-shm"
"Chrome:Chrome bookmarks","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Bookmarks*"
"Chrome:Chrome Cookies","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Cookies*"
"Chrome:Chrome Current Session","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Current Session"
"Chrome:Chrome Current Tabs","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Current Tabs"
"Chrome:Chrome Download Metadata","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/DownloadMetadata"
"Chrome:Chrome Extension Cookies","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Extension Cookies"
"Chrome:Chrome Favicons","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Favicons*"
"Chrome:Chrome History","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/History*"
"Chrome:Chrome Last Session","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Last Session"
"Chrome:Chrome Last Tabs","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Last Tabs"
"Chrome:Chrome Sessions Folder","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/Sessions/**10"
"Chrome:Chrome Login Data","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Login Data"
"Chrome:Chrome Media History","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Media History*"
"Chrome:Chrome Network Action Predictor","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Network Action Predictor"
"Chrome:Chrome Network Persistent State","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Network Persistent State"
"Chrome:Chrome Preferences","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Preferences"
"Chrome:Chrome Quota Manager","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/QuotaManager"
"Chrome:Chrome Reporting and NEL","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Reporting and NEL"
"Chrome:Chrome Shortcuts","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Shortcuts*"
"Chrome:Chrome Top Sites","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Top Sites*"
"Chrome:Chrome Trust Tokens","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Trust Tokens*"
"Chrome:Chrome SyncData Database","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/SyncData.sqlite3"
"Chrome:Chrome Visited Links","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Visited Links"
"Chrome:Chrome Web Data","/Users/*/Library/Application Support/{BraveSoftware/Brave,Google/Chrome,Microsoft Edge}/**10/Web Data*"
"Firefox:Addons","/Users/*/Library/Application Support/Firefox/Profiles/**10/addons.sqlite*"
"Firefox:Bookmarks","/Users/*/Library/Application Support/Firefox/Profiles/**10/bookmarks.sqlite*"
"Firefox:Cookies","/Users/*/Library/Application Support/Firefox/Profiles/**10/cookies.sqlite*"
"Firefox:Downloads","/Users/*/Library/Application Support/Firefox/Profiles/**10/downloads.sqlite*"
"Firefox:Extensions","/Users/*/Library/Application Support/Firefox/Profiles/**10/extensions.json"
"Firefox:Favicons","/Users/*/Library/Application Support/Firefox/Profiles/**10/favicons.sqlite*"
"Firefox:Form history","/Users/*/Library/Application Support/Firefox/Profiles/**10/formhistory.sqlite*"
"Firefox:Permissions","/Users/*/Library/Application Support/Firefox/Profiles/**10/permissions.sqlite*"
"Firefox:Places","/Users/*/Library/Application Support/Firefox/Profiles/**10/places.sqlite*"
"Firefox:Protections","/Users/*/Library/Application Support/Firefox/Profiles/**10/protections.sqlite*"
"Firefox:Search","/Users/*/Library/Application Support/Firefox/Profiles/**10/search.sqlite*"
"Firefox:Signons","/Users/*/Library/Application Support/Firefox/Profiles/**10/signons.sqlite*"
"Firefox:Storage Sync","/Users/*/Library/Application Support/Firefox/Profiles/**10/storage-sync.sqlite*"
"Firefox:Webappstore","/Users/*/Library/Application Support/Firefox/Profiles/**10/webappstore.sqlite*"
"Firefox:Password","/Users/*/Library/Application Support/Firefox/Profiles/**10/key*.db"
"Firefox:Preferences","/Users/*/Library/Application Support/Firefox/Profiles/**10/prefs.js"
"Firefox:Sessionstore","/Users/*/Library/Application Support/Firefox/Profiles/**10/sessionstore*"
"Firefox:Sessionstore Folder","/Users/*/Library/Application Support/Firefox/Profiles/sessionstore-backups/**10"
"Chrome:Chrome Current Session","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Current Session"
"Chrome:Chrome Current Tabs","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Current Tabs"
"Chrome:Chrome Download Metadata","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/DownloadMetadata"
"Chrome:Chrome Extension Cookies","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Extension Cookies"
"Chrome:Chrome Favicons","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Favicons*"
"Chrome:Chrome History","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/History*"
"Chrome:Chrome Last Session","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Last Session"
"Chrome:Chrome Last Tabs","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Last Tabs"
"Chrome:Chrome Sessions Folder","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/Sessions/**10"
"Chrome:Chrome Login Data","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Login Data"
"Chrome:Chrome Media History","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Media History*"
"Chrome:Chrome Network Action Predictor","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Network Action Predictor"
"Chrome:Chrome Network Persistent State","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Network Persistent State"
"Chrome:Chrome Preferences","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Preferences"
"Chrome:Chrome Quota Manager","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/QuotaManager"
"Chrome:Chrome Reporting and NEL","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Reporting and NEL"
"Chrome:Chrome Shortcuts","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Shortcuts*"
"Chrome:Chrome Top Sites","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Top Sites*"
"Chrome:Chrome Trust Tokens","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Trust Tokens*"
"Chrome:Chrome SyncData Database","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/SyncData.sqlite3"
"Chrome:Chrome Visited Links","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Visited Links"
"Chrome:Chrome Web Data","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/Web Data*"
"Firefox:Addons","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/addons.sqlite*"
"Firefox:Bookmarks","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/bookmarks.sqlite*"
"Firefox:Cookies","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/cookies.sqlite*"
"Firefox:Downloads","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/downloads.sqlite*"
"Firefox:Extensions","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/extensions.json"
"Firefox:Favicons","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/favicons.sqlite*"
"Firefox:Form history","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/formhistory.sqlite*"
"Firefox:Permissions","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/permissions.sqlite*"
"Firefox:Places","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/places.sqlite*"
"Firefox:Protections","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/protections.sqlite*"
"Firefox:Search","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/search.sqlite*"
"Firefox:Signons","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/signons.sqlite*"
"Firefox:Storage Sync","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/storage-sync.sqlite*"
"Firefox:Webappstore","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/webappstore.sqlite*"
"Firefox:Password","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/key*.db"
"Firefox:Preferences","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/prefs.js"
"Firefox:Sessionstore","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/**10/sessionstore*"
"Firefox:Sessionstore Folder","/home/*/.config/{google-chrome,chrome-remote-desktop/chrome-profile,chromium}/sessionstore-backups/**10"
- name: Accessor
default: auto
- name: UseFilenames
default: Y
type: bool
description: When set use filenames to optimize identification of files.
- name: AlsoUpload
description: Also upload the raw sqlite files
type: bool
sources:
- query: |
LET AllFilenamesRegex <= '''^(CarsDB.db|Contacts.db|random.sqlite|ActivitiesCache.db|Antiphishing.db|RansomwareRecover.db|cache.db|es.db|Web Data|Web Data|Cookies|History|Favicons|History|History|Web Data|Media History|Media History|Network Action Predictor|Shortcuts|Top Sites|chp.db|aggregation.dbx|config.db|filecache.db|icon.db|instance.dbx|home.db|home.db|home.db|home.db|sync_history.db|tray-thumbnails.db|WebAssistDatabase|EventTranscript.db|EventTranscript.db|queue.sqlite3|places.sqlite|cookies.sqlite|downloads.sqlite|places.sqlite|favicons.sqlite|formhistory.sqlite|places.sqlite|random.db|cloud_graph.db|snapshot.db|sync_config.db|metadata_sqlite_db|plum.sqlite|nessusd.db|MediaDb.v1.sqlite|Windows.db|Windows-gather.db|random.db|main.db|wpndatabase.db|wpndatabase.db|Store.db|contacts.db|Notifications.db|Phone.db|photos.db|settings.db|accounts4.db|callhistory.storedata)$'''
LET SQLiteFiles <=
SELECT OSPath,
read_file(filename=OSPath, length=15, accessor=Accessor) AS Magic,
if(condition=AlsoUpload,
then=upload(file=OSPath,
mtime=Mtime,
atime=Atime,
ctime=Ctime,
btime=Btime)) AS Upload
FROM glob(globs=GlobExpr.Glob, accessor=Accessor)
WHERE NOT IsDir
AND if(condition=UseFilenames, then=Name =~ AllFilenamesRegex, else=TRUE)
AND Magic =~ "SQLite format 3"
SELECT * FROM SQLiteFiles
- name: 4K Video Downloader
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='media_item_description' OR name='url_description' OR name='media_info' OR name='audio_info' OR name='video_info' OR name='url_description');'''
LET IdentifyValue = 6
LET SQLQuery = '''SELECT
audio_info.id AS ID,
url_description.service_name AS ServiceName,
media_item_description.title AS Title,
url_description.url AS URL,
download_item.filename AS Filename,
media_item_description.duration / 1000 / 60 AS 'Duration (Minutes)',
audio_info.bitrate / 1000 AS 'Bitrate (kbps)',
CASE
WHEN video_info.video_360 = 0 THEN
'No'
WHEN video_info.video_360 = 1 THEN
'Yes'
END AS Video360,
CASE
WHEN video_info.hdr = 0 THEN
'No'
WHEN video_info.hdr = 1 THEN
'Yes'
END AS VideoHDR
FROM
download_item
LEFT JOIN media_item_description ON download_item.id = media_item_description.id
LEFT JOIN url_description ON media_item_description.id = url_description.id
NATURAL LEFT JOIN media_info
LEFT JOIN audio_info ON download_item.id = audio_info.id
LEFT JOIN video_info ON media_info.id = video_info.id
AND url_description.id = video_info.id
ORDER BY
ID ASC'''
LET FileType = '''4K Video Downloader'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Activity Package Id
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Activity' OR name='Activity_PackageId' OR name='ActivityOperation');'''
LET IdentifyValue = 3
LET SQLQuery = '''Select substr(hex(ActivityId), 1, 8)
|| '-' || substr(hex(ActivityId), 9, 4)
|| '-' || substr(hex(ActivityId), 13, 4)
|| '-' || substr(hex(ActivityId), 17, 4)
|| '-' || substr(hex(ActivityId), 21, 12) as ActivityId,Platform,PackageName,
datetime(ExpirationTime,'unixepoch','localtime') as ExpirationTime from Activity_PackageId'''
LET FileType = '''Activity Package Id'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Activity Operation
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Activity' OR name='Activity_PackageId' OR name='ActivityOperation');'''
LET IdentifyValue = 3
LET SQLQuery = '''Select OperationOrder,AppId,ActivityType,
datetime(LastModifiedTime,'unixepoch','localtime') as LastModifiedTime,
datetime(ExpirationTime,'unixepoch','localtime') as ExpirationTime,
datetime(CreatedTime,'unixepoch','localtime') as CreatedTime,
datetime(EndTime,'unixepoch','localtime') as EndTime,
datetime(LastModifiedOnClient,'unixepoch','localtime') as LastModifiedOnClient,PlatformDeviceId from ActivityOperation;'''
LET FileType = '''Activity Operation'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Activity
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Activity' OR name='Activity_PackageId' OR name='ActivityOperation');'''
LET IdentifyValue = 3
LET SQLQuery = '''Select substr(hex(Id), 1, 8)
|| '-' || substr(hex(Id), 9, 4)
|| '-' || substr(hex(Id), 13, 4)
|| '-' || substr(hex(Id), 17, 4)
|| '-' || substr(hex(Id), 21, 12) as Id,payload,
datetime(LastModifiedTime,'unixepoch','localtime') as LastModifiedTime,
datetime(ExpirationTime,'unixepoch','localtime') as ExpirationTime,
datetime(CreatedInCloud,'unixepoch','localtime') as CreatedInCloud,
datetime(StartTime,'unixepoch','localtime') as StartTime,datetime(EndTime,'unixepoch','localtime') as EndTime,
datetime(LastModifiedOnClient,'unixepoch','localtime') as LastModifiedOnClient,
datetime(OriginalLastModifiedOnClient,'unixepoch','localtime') as OriginalLastModifiedOnClient,
ActivityType,IsLocalOnly,ETag,PackageIdHash,PlatformDeviceId from Activity'''
LET FileType = '''Activity'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Bitdefender Antiphishing DB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='aph_cache');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
url AS URL,
result AS Result,
datetime( expire / 1000, 'unixepoch', 'localtime' ) AS ExpireTime
FROM
aph_cache
ORDER BY
ExpireTime ASC;'''
LET FileType = '''Bitdefender Antiphishing DB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Bitdefender es DB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='es_cache');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
url AS URL,
md5 AS MD5,
content_size AS ContentSizeBytes,
datetime( expire / 1000, 'unixepoch', 'localtime' ) AS ExpireTime
FROM
es_cache
ORDER BY
ExpireTime ASC;'''
LET FileType = '''Bitdefender es DB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Bitdefender cache DB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='entries');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
quarId AS QuarantineID,
path AS FilePath,
threat AS Threat,
size AS Size,
datetime( quartime, 'unixepoch', 'localtime' ) AS QuarantineTime,
datetime( acctime, 'unixepoch', 'localtime' ) AS LastAccessedTime,
datetime( modtime, 'unixepoch', 'localtime' ) AS LastModifiedTime,
usersid AS UserSID
FROM
entries
ORDER BY
QuarantineTime ASC;'''
LET FileType = '''Bitdefender cache DB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Bitdefender RansomwareRecover DB Files
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='files' OR name='packs');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
files.packid AS PackID,
files.path AS Path,
files.restored AS Restored,
files.restored_path AS RestoredPath,
files.extern_itemid AS ExternItemID,
files.extern_groupid AS ExternGroupID,
packs.process AS Process,
packs.restore_attempt_count AS RestoreAttemptCount,
datetime( files.insert_time / 1000, 'unixepoch', 'localtime' ) AS InsertTime,
datetime( files.last_operation_time / 1000, 'unixepoch', 'localtime' ) AS LastOperationTime
FROM
files INNER JOIN packs ON files.packid = packs.uuid
ORDER BY
LastOperationTime ASC;'''
LET FileType = '''Bitdefender RansomwareRecover DB Files'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Autofill Entries
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='autofill' OR name='credit_cards' OR name='offer_data' OR name='server_addresses' OR name='keywords');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
autofill.name AS Name,
autofill.value AS Value,
autofill.value_lower AS ValueLowercase,
datetime( "date_created", 'unixepoch' ) AS DateCreated,
datetime( "date_last_used", 'unixepoch' ) AS LastUsed,
autofill.count AS Count
FROM
autofill
ORDER BY
autofill.name ASC'''
LET FileType = '''Chromium Browser Autofill Entries'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Autofill Profiles
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='autofill' OR name='credit_cards' OR name='offer_data' OR name='server_addresses' OR name='keywords');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
autofill_profiles.guid AS GUID,
datetime( "date_modified", 'unixepoch' ) AS DateModified,
datetime( "use_date", 'unixepoch' ) AS UseDate,
autofill_profile_names.first_name AS FirstName,
autofill_profile_names.middle_name AS MiddleName,
autofill_profile_names.last_name AS LastName,
autofill_profile_emails.email as EmailAddress,
autofill_profile_phones.number AS PhoneNumber,
autofill_profiles.company_name AS CompanyName,
autofill_profiles.street_address AS StreetAddress,
autofill_profiles.city AS City,
autofill_profiles.state AS State,
autofill_profiles.zipcode AS ZipCode,
autofill_profiles.use_count AS UseCount
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
ORDER BY
autofill_profiles.guid ASC'''
LET FileType = '''Chromium Browser Autofill Profiles'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Cookies
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cookies' OR name='meta');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
datetime ( cookies.creation_utc / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS CreationUTC,
datetime ( cookies.expires_utc / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS ExpiresUTC,
datetime ( cookies.last_access_utc / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastAccessUTC,
cookies.host_key AS HostKey,
cookies.name AS Name,
cookies.path AS Path,
CASE
WHEN cookies.is_secure = 1 THEN
'Yes'
WHEN cookies.is_secure = 0 THEN
'No'
END AS IsSecure,
CASE
WHEN cookies.is_httponly = 1 THEN
'Yes'
WHEN cookies.is_httponly = 0 THEN
'No'
END AS IsHttpOnly,
CASE
WHEN cookies.has_expires = 1 THEN
'Yes'
WHEN cookies.has_expires = 0 THEN
'No'
END AS HasExpiration,
CASE
WHEN cookies.is_persistent = 1 THEN
'Yes'
WHEN cookies.is_persistent = 0 THEN
'No'
END AS IsPersistent,
cookies.priority AS Priority,
cookies.source_port AS SourcePort
FROM
cookies
ORDER BY
cookies.creation_utc ASC'''
LET FileType = '''Chromium Browser Cookies'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Downloads
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='urls' OR name='visits' OR name='downloads' OR name='segments' OR name='keyword_search_terms');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
downloads.id AS ID,
downloads.guid AS GUID,
downloads.current_path AS CurrentPath,
downloads.target_path AS TargetPath,
downloads.original_mime_type AS OriginalMIMEType,
downloads.received_bytes AS ReceivedBytes,
downloads.total_bytes AS TotalBytes,
datetime( downloads.start_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS StartTime,
datetime( downloads.end_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS EndTime,
datetime( downloads.opened / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS Opened,
datetime( downloads.last_access_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastAccessTime,
downloads.last_modified AS LastModified,
CASE
WHEN downloads.state = 0 THEN
'In Progress'
WHEN downloads.state = 1 THEN
'Complete'
WHEN downloads.state = 2 THEN
'Cancelled'
WHEN downloads.state = 3 THEN
'Interrupted'
WHEN downloads.state = 4 THEN
'Interrupted'
END AS State,
CASE
WHEN downloads.danger_type = 0 THEN
'Not Dangerous'
WHEN downloads.danger_type = 1 THEN
'Dangerous'
WHEN downloads.danger_type = 2 THEN
'Dangerous URL'
WHEN downloads.danger_type = 3 THEN
'Dangerous Content'
WHEN downloads.danger_type = 4 THEN
'Content May Be Malicious'
WHEN downloads.danger_type = 5 THEN
'Uncommon Content'
WHEN downloads.danger_type = 6 THEN
'Dangerous But User Validated'
WHEN downloads.danger_type = 7 THEN
'Dangerous Host'
WHEN downloads.danger_type = 8 THEN
'Potentially Unwanted'
WHEN downloads.danger_type = 9 THEN
'Whitelisted by Policy'
END AS DangerType,
CASE
WHEN downloads.interrupt_reason = 0 THEN
'No Interrupt'
WHEN downloads.interrupt_reason = 1 THEN
'File Error'
WHEN downloads.interrupt_reason = 2 THEN
'Access Denied'
WHEN downloads.interrupt_reason = 3 THEN
'Disk Full'
WHEN downloads.interrupt_reason = 5 THEN
'Path Too Long'
WHEN downloads.interrupt_reason = 6 THEN
'File Too Large'
WHEN downloads.interrupt_reason = 7 THEN
'Virus'
WHEN downloads.interrupt_reason = 10 THEN
'Temporary Problem'
WHEN downloads.interrupt_reason = 11 THEN
'Blocked'
WHEN downloads.interrupt_reason = 12 THEN
'Security Check Failed'
WHEN downloads.interrupt_reason = 13 THEN
'Resume Error'
WHEN downloads.interrupt_reason = 20 THEN
'Network Error'
WHEN downloads.interrupt_reason = 21 THEN
'Operation Timed Out'
WHEN downloads.interrupt_reason = 22 THEN
'Connection Lost'
WHEN downloads.interrupt_reason = 23 THEN
'Server Down'
WHEN downloads.interrupt_reason = 30 THEN
'Server Error'
WHEN downloads.interrupt_reason = 31 THEN
'Range Request Error'
WHEN downloads.interrupt_reason = 32 THEN
'Server Precondition Error'
WHEN downloads.interrupt_reason = 33 THEN
'Unable to get file'
WHEN downloads.interrupt_reason = 34 THEN
'Server Unauthorized'
WHEN downloads.interrupt_reason = 35 THEN
'Server Certificate Problem'
WHEN downloads.interrupt_reason = 36 THEN
'Server Access Forbidden'
WHEN downloads.interrupt_reason = 37 THEN
'Server Unreachable'
WHEN downloads.interrupt_reason = 38 THEN
'Content Length Mismatch'
WHEN downloads.interrupt_reason = 39 THEN
'Cross Origin Redirect'
WHEN downloads.interrupt_reason = 40 THEN
'Cancelled'
WHEN downloads.interrupt_reason = 41 THEN
'Browser Shutdown'
WHEN downloads.interrupt_reason = 50 THEN
'Browser Crashed'
END AS InterruptReason,
downloads.referrer AS ReferrerURL,
downloads.site_url AS SiteURL,
downloads.tab_url AS TabURL,
downloads.tab_referrer_url AS TabReferrerURL,
DownloadURL.url AS DownloadURL
FROM
downloads
INNER JOIN downloads_url_chains AS DownloadURL ON downloads.id = DownloadURL.id
ORDER BY
downloads.id ASC'''
LET FileType = '''Chromium Browser Downloads'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Favicons
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='icon_mapping' OR name='favicons' OR name='favicon_bitmaps');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
favicons.id AS ID,
favicon_bitmaps.icon_id AS IconID,
datetime( favicon_bitmaps.last_updated / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastUpdated,
icon_mapping.page_url AS PageURL,
favicons.url AS FaviconURL
FROM
favicons
INNER JOIN
icon_mapping
INNER JOIN
favicon_bitmaps
ON icon_mapping.icon_id = favicon_bitmaps.icon_id
AND favicons.id = favicon_bitmaps.icon_id
ORDER BY
favicons.id ASC'''
LET FileType = '''Chromium Browser Favicons'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='urls' OR name='visits' OR name='downloads' OR name='segments' OR name='keyword_search_terms');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
urls.id AS ID,
datetime( visits.visit_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS 'VisitTime (Local)',
datetime( urls.last_visit_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS 'LastVisitedTime (Local)',
urls.title AS URLTitle,
urls.url AS URL,
urls.visit_count AS VisitCount,
urls.typed_count AS TypedCount,
CASE
WHEN urls.hidden = 1 THEN
'Yes'
WHEN urls.hidden = 0 THEN
'No'
END AS Hidden,
visits.id AS VisitID,
visits.from_visit AS FromVisitID,
CAST ( visits.visit_duration AS FLOAT ) / 1000000 AS VisitDurationInSeconds
FROM
urls
LEFT JOIN visits ON urls.id = visits.url
ORDER BY
visits.visit_time ASC;'''
LET FileType = '''Chromium Browser History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Keyword Searches
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='urls' OR name='visits' OR name='downloads' OR name='segments' OR name='keyword_search_terms');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
keyword_search_terms.keyword_id AS KeywordID,
keyword_search_terms.url_id AS URLID,
datetime( urls.last_visit_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastVisitTime,
keyword_search_terms.term AS KeywordSearchTerm,
urls.title AS Title,
urls.url AS URL
FROM
keyword_search_terms
INNER JOIN urls ON keyword_search_terms.url_id = urls.id
ORDER BY
keyword_search_terms.keyword_id ASC'''
LET FileType = '''Chromium Browser Keyword Searches'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Masked Credit Cards
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='autofill' OR name='credit_cards' OR name='offer_data' OR name='server_addresses' OR name='keywords');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
masked_credit_cards.id AS ID,
masked_credit_cards.status AS Status,
masked_credit_cards.name_on_card AS NameOnCard,
masked_credit_cards.network AS CardNetwork,
masked_credit_cards.last_four AS LastFour,
masked_credit_cards.exp_month AS ExpMonth,
masked_credit_cards.exp_year AS ExpYear,
masked_credit_cards.bank_name AS BankName,
masked_credit_cards.nickname AS CardNickname,
masked_credit_cards.card_issuer AS CardIssuer,
masked_credit_cards.instrument_id AS InstrumentID
FROM
masked_credit_cards
ORDER BY
masked_credit_cards.id ASC'''
LET FileType = '''Chromium Browser Masked Credit Cards'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Media History Playback
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='origin' OR name='playback' OR name='playbackSession');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
playback.id AS ID,
playback.url AS URL,
playback.watch_time_s AS WatchTimeSeconds,
CASE
WHEN playback.has_video = 1 THEN
'Yes'
WHEN playback.has_video = 0 THEN
'No'
END AS HasVideo,
CASE
WHEN playback.has_audio = 1 THEN
'Yes'
WHEN playback.has_audio = 0 THEN
'No'
END AS HasAudio,
datetime( playback.last_updated_time_s + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastUpdated,
playback.origin_id AS OriginID
FROM
playback
ORDER BY
playback.id ASC'''
LET FileType = '''Chromium Browser Media History Playback'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Media History Playback Session
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='origin' OR name='playback' OR name='playbackSession');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
playbackSession.id AS ID,
datetime( playbackSession.last_updated_time_s + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastUpdated,
playbackSession.url AS URL,
CAST ( playbackSession.duration_ms AS FLOAT ) / 1000 AS DurationInSeconds,
CAST ( playbackSession.position_ms AS FLOAT ) / 1000 AS PositionInSeconds,
playbackSession.title AS Title,
playbackSession.artist AS Artist,
playbackSession.album AS Album,
playbackSession.source_title AS SourceTitle,
playbackSession.origin_id AS OriginID
FROM
playbackSession
ORDER BY
playbackSession.id'''
LET FileType = '''Chromium Browser Media History Playback Session'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Network Action Predictor
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='resource_prefetch_predictor_host_redirect' OR name='network_action_predictor' OR name='resource_prefetch_predictor_metadata');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
network_action_predictor.id AS ID,
network_action_predictor.user_text AS UserText,
network_action_predictor.url AS URL,
network_action_predictor.number_of_hits AS NumberOfHits,
network_action_predictor.number_of_misses AS NumberOfMisses
FROM
network_action_predictor,
resource_prefetch_predictor_host_redirect
ORDER BY
network_action_predictor.id ASC'''
LET FileType = '''Chromium Browser Network Action Predictor'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Shortcuts
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='meta' OR name='omni_box_shortcuts');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
datetime( omni_box_shortcuts.last_access_time / 1000000 + ( strftime( '%s', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS LastAccessTime,
omni_box_shortcuts.text AS TextTyped,
omni_box_shortcuts.fill_into_edit AS FillIntoEdit,
omni_box_shortcuts.url AS URL,
omni_box_shortcuts.contents AS Contents,
omni_box_shortcuts.description AS Description,
omni_box_shortcuts.type AS Type,
omni_box_shortcuts.keyword AS Keyword,
omni_box_shortcuts.number_of_hits AS TimesSelectedByUser,
omni_box_shortcuts.id AS ID
FROM
omni_box_shortcuts
ORDER BY
omni_box_shortcuts.last_access_time ASC'''
LET FileType = '''Chromium Browser Shortcuts'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Chromium Browser Top Sites
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='meta' OR name='top_sites');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
top_sites.url_rank AS URLRank,
top_sites.url AS URL,
top_sites.title AS Title,
top_sites.redirects AS Redirects
FROM
top_sites
ORDER BY
top_sites.url_rank ASC'''
LET FileType = '''Chromium Browser Top Sites'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Cylance Analyzed Cache
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='AnalyzedCache' OR name='Quarantine' OR name='AddFileInfo');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM AnalyzedCache'''
LET FileType = '''Cylance Analyzed Cache'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Cylance Quarantine
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='AnalyzedCache' OR name='Quarantine' OR name='AddFileInfo');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM Quarantine'''
LET FileType = '''Cylance Quarantine'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Cylance AddFileInfo
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='AnalyzedCache' OR name='Quarantine' OR name='AddFileInfo');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM AddFileInfo'''
LET FileType = '''Cylance AddFileInfo'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Aggregation database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='snapshot');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
snapshot."key" AS "Key",
snapshot.value AS "Value(ConvertToJSON)"
FROM
snapshot
ORDER BY
snapshot."key" ASC'''
LET FileType = '''Dropbox Aggregation database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Drobpox
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='file_journal');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
id,
server_path,
parent_path,
local_host_id,
local_filename,
local_infinite_details,
local_size,
datetime(local_mtime,'unixepoch') AS "Local Modified Time",
datetime(local_ctime,'unixepoch') AS "Local Created Time",
local_attrs,
datetime(local_timestamp,'unixepoch') AS "Local Timestamp",
local_user_id,
Local_sync_type,
updated_filename,
updated_host_id,
updated_size,
datetime(updated_mtime) AS "Updated Modified Time",
datetime(updated_timestamp) AS "Updated Timestamp",
updated_dir,
updated_user_id,
updated_sync_type
from file_journal
order by "local created time" desc'''
LET FileType = '''Drobpox'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Icon DB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='ext_icon_table' OR name='folder_icon_table' OR name='path_icon_table');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
datetime( "created_time", 'unixepoch' ) AS CreatedTime,
datetime( "file_mtime", 'unixepoch' ) AS ModifiedTime,
path_icon_table.file_path AS FilePath
FROM
path_icon_table
ORDER BY
path_icon_table.created_time ASC'''
LET FileType = '''Dropbox Icon DB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='instance');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
id,
active,
appdata_path,
default_dropbox_path,
default_dropbox_folder_name,
business_name,
uid,
host_id
from instance'''
LET FileType = '''Dropbox'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Non-Local Resources
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='activity_feed' OR name='recents' OR name='starred_items' OR name='calendar_items' OR name='sfj_resources');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
datetime( nonlocal_resources.server_fetch_timestamp / 1000 + ( strftime( '%ms', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS timestamp,
nonlocal_resources.account_id AS AccountID,
nonlocal_resources.name AS Name,
nonlocal_resources.url AS URL,
nonlocal_resources.server_path AS ServerPath,
CASE
WHEN nonlocal_resources.is_dir = 0 THEN
'No'
WHEN nonlocal_resources.is_dir = 1 THEN
'Yes'
END AS IsDirectory,
CASE
WHEN nonlocal_resources.is_share = 0 THEN
'No'
WHEN nonlocal_resources.is_share = 1 THEN
'Yes'
END AS IsShare,
nonlocal_resources.resource_type AS ResourceType,
nonlocal_resources.resource_id AS ResourceID
FROM
nonlocal_resources'''
LET FileType = '''Dropbox Non-Local Resources'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Recent Items
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='activity_feed' OR name='recents' OR name='starred_items' OR name='calendar_items' OR name='sfj_resources');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
datetime( timestamp / 1000 + ( strftime( '%ms', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS Timestamp,
recents.account_id AS AccountID,
recents.server_path AS ServerPath,
datetime( server_fetch_timestamp / 1000 + ( strftime( '%ms', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS ServerFetchTimestamp,
recents.batch_key AS BatchKey,
recents.event_type AS EventType,
CASE
WHEN recents.is_local = 0 THEN
'No'
WHEN recents.is_local = 1 THEN
'Yes'
END AS IsLocal,
recents.keywords AS Keywords,
recents.resource_id AS ResourceID,
recents.resource_type AS ResourceType
FROM
recents
ORDER BY
recents.timestamp ASC'''
LET FileType = '''Dropbox Recent Items'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox SFJ Resources
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='activity_feed' OR name='recents' OR name='starred_items' OR name='calendar_items' OR name='sfj_resources');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
datetime( server_fetch_timestamp / 1000 + ( strftime( '%ms', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS ServerFetchTimestamp,
sfj_resources.name AS Name,
sfj_resources.cased_server_path AS ServerPath,
sfj_resources.resource_type AS ResourceType,
sfj_resources.resource_id AS ResourceID,
sfj_resources.account_id AS AccountID,
sfj_resources.icon_override AS IconOverride
FROM
sfj_resources
ORDER BY
sfj_resources.server_fetch_timestamp ASC'''
LET FileType = '''Dropbox SFJ Resources'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Starred Items
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='activity_feed' OR name='recents' OR name='starred_items' OR name='calendar_items' OR name='sfj_resources');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
datetime( timestamp / 1000 + ( strftime( '%ms', '1601-01-01' ) ), 'unixepoch', 'localtime' ) AS timestamp,
starred_items.account_id AS AccountID,
starred_items.server_path AS ServerPath,
CASE
WHEN starred_items.is_starred = 0 THEN
'No'
WHEN starred_items.is_starred = 1 THEN
'Yes'
END AS IsStarred,
starred_items.keywords AS Keywords,
starred_items.paper_path AS PaperPath,
starred_items.persist_state AS PersistState,
starred_items.resource_type AS ResourceType,
starred_items.resource_id AS ResourceID
FROM
starred_items
ORDER BY
starred_items.timestamp ASC'''
LET FileType = '''Dropbox Starred Items'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Sync History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='sync_history');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
datetime( "timestamp", 'unixepoch' ) AS Timestamp,
sync_history.event_type AS EventType,
sync_history.file_event_type AS FileEventType,
sync_history.direction AS Direction,
sync_history.local_path AS LocalPath,
sync_history.file_id AS FileID
FROM
sync_history
ORDER BY
sync_history.timestamp ASC'''
LET FileType = '''Dropbox Sync History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Dropbox Tray Thumbnails
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cached_thumbnail_table');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
datetime( "timestamp", 'unixepoch' ) AS Timestamp,
cached_thumbnail_table.file_name AS FileName,
cached_thumbnail_table.blocklist AS BlockList
FROM
cached_thumbnail_table
ORDER BY
cached_thumbnail_table.timestamp ASC'''
LET FileType = '''Dropbox Tray Thumbnails'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Drobpox
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='config');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
key,
VALUE
from config'''
LET FileType = '''Drobpox'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: WebAssistdatabase Navigation History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND name='navigation_history';'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
navigation_history.id AS ID,
datetime(navigation_history.last_visited_time, 'unixepoch') AS 'Last Visited Time',
navigation_history.title AS Title,
navigation_history.url AS URL,
navigation_history.num_visits AS VisitCount
FROM
navigation_history
ORDER BY
navigation_history.last_visited_time ASC;'''
LET FileType = '''WebAssistdatabase Navigation History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db BrowsingHistory
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Browsing History'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db BrowsingHistory'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Device Connectivity and Configuration
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Device Connectivity and Configuration'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Device Connectivity and Configuration'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Inking Typing and Speech Utterance
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Inking Typing and Speech Utterance'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Inking Typing and Speech Utterance'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db_ProductandServicePerformance
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Product and Service Performance'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db_ProductandServicePerformance'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Product and Service Usage
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Product and Service Usage'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Product and Service Usage'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Software Setup and Inventory
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 7
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
producers.producer_id_text AS ProducerIDText,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN producers ON events_persisted.producer_id = producers.producer_id
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Software Setup and Inventory'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Software Setup and Inventory'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db BrowsingHistory
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Browsing History'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db BrowsingHistory'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Device Connectivity and Configuration
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Device Connectivity and Configuration'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Device Connectivity and Configuration'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Inking Typing and Speech Utterance
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Inking Typing and Speech Utterance'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Inking Typing and Speech Utterance'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db_ProductandServicePerformance
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Product and Service Performance'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db_ProductandServicePerformance'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Product and Service Usage
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Product and Service Usage'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Product and Service Usage'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows EventTranscript.db Software Setup and Inventory
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='categories' OR name='event_categories' OR name='event_tags' OR name='events_persisted' OR name='producers' OR name='provider_groups' OR name='tag_descriptions');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
CASE
WHEN
events_persisted.sid = 'S-1-0' THEN
'S-1-0 (Null Authority)'
WHEN events_persisted.sid = 'S-1-0-0' THEN
'S-1-0-0 (Nobody)'
WHEN events_persisted.sid = 'S-1-1' THEN
'S-1-1 (World Authority)'
WHEN events_persisted.sid = 'S-1-1-0' THEN
'S-1-1-0 (Everyone)'
WHEN events_persisted.sid = 'S-1-16-0' THEN
'S-1-16-0 (Untrusted Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-12288' THEN
'S-1-16-12288 (High Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-16384' THEN
'S-1-16-16384 (System Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-20480' THEN
'S-1-16-20480 (Protected Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-28672' THEN
'S-1-16-28672 (Secure Process Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-4096' THEN
'S-1-16-4096 (Low Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8192' THEN
'S-1-16-8192 (Medium Mandatory Level)'
WHEN events_persisted.sid = 'S-1-16-8448' THEN
'S-1-16-8448 (Medium Plus Mandatory Level)'
WHEN events_persisted.sid = 'S-1-2' THEN
'S-1-2 (Local Authority)'
WHEN events_persisted.sid = 'S-1-2-0' THEN
'S-1-2-0 (Local)'
WHEN events_persisted.sid = 'S-1-2-1' THEN
'S-1-2-1 (Console Logon)'
WHEN events_persisted.sid = 'S-1-3' THEN
'S-1-3 (Creator Authority)'
WHEN events_persisted.sid = 'S-1-3-0' THEN
'S-1-3-0 (Creator Owner)'
WHEN events_persisted.sid = 'S-1-3-1' THEN
'S-1-3-1 (Creator Group)'
WHEN events_persisted.sid = 'S-1-3-2' THEN
'S-1-3-2 (Creator Owner Server)'
WHEN events_persisted.sid = 'S-1-3-3' THEN
'S-1-3-3 (Creator Group Server)'
WHEN events_persisted.sid = 'S-1-3-4' THEN
'S-1-3-4 (Owner Rights)'
WHEN events_persisted.sid = 'S-1-4' THEN
'S-1-4 (Non-unique Authority)'
WHEN events_persisted.sid = 'S-1-5' THEN
'S-1-5 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-1' THEN
'S-1-5-1 (Dialup)'
WHEN events_persisted.sid = 'S-1-5-10' THEN
'S-1-5-10 (Principal Self)'
WHEN events_persisted.sid = 'S-1-5-11' THEN
'S-1-5-11 (Authenticated Users)'
WHEN events_persisted.sid = 'S-1-5-12' THEN
'S-1-5-12 (Restricted Code)'
WHEN events_persisted.sid = 'S-1-5-13' THEN
'S-1-5-13 (Terminal Server Users)'
WHEN events_persisted.sid = 'S-1-5-14' THEN
'S-1-5-14 (Remote Interactive Logon)'
WHEN events_persisted.sid = 'S-1-5-15' THEN
'S-1-5-15 (This Organization)'
WHEN events_persisted.sid = 'S-1-5-17' THEN
'S-1-5-17 (IUSR)'
WHEN events_persisted.sid = 'S-1-5-18' THEN
'S-1-5-18 (Local System)'
WHEN events_persisted.sid = 'S-1-5-19' THEN
'S-1-5-19 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-2' THEN
'S-1-5-2 (Network)'
WHEN events_persisted.sid = 'S-1-5-20' THEN
'S-1-5-20 (NT Authority)'
WHEN events_persisted.sid = 'S-1-5-21domain-498' THEN
'S-1-5-21domain-498 (Enterprise Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-521' THEN
'S-1-5-21domain-521 (Read-only Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-571' THEN
'S-1-5-21domain-571 (Allowed RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-572' THEN
'S-1-5-21domain-572 (Denied RODC Password Replication Group)'
WHEN events_persisted.sid = 'S-1-5-21domain-500' THEN
'S-1-5-21domain-500 (Administrator)'
WHEN events_persisted.sid = 'S-1-5-21domain-501' THEN
'S-1-5-21domain-501 (Guest)'
WHEN events_persisted.sid = 'S-1-5-21domain-502' THEN
'S-1-5-21domain-502 (KRBTGT)'
WHEN events_persisted.sid = 'S-1-5-21domain-512' THEN
'S-1-5-21domain-512 (Domain Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-513' THEN
'S-1-5-21domain-513 (Domain Users)'
WHEN events_persisted.sid = 'S-1-5-21domain-514' THEN
'S-1-5-21domain-514 (Domain Guests)'
WHEN events_persisted.sid = 'S-1-5-21domain-515' THEN
'S-1-5-21domain-515 (Domain Computers)'
WHEN events_persisted.sid = 'S-1-5-21domain-516' THEN
'S-1-5-21domain-516 (Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-517' THEN
'S-1-5-21domain-517 (Cert Publishers)'
WHEN events_persisted.sid = 'S-1-5-21domain-520' THEN
'S-1-5-21domain-520 (Group Policy Creator Owners)'
WHEN events_persisted.sid = 'S-1-5-21-domain-522' THEN
'S-1-5-21-domain-522 (Cloneable Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-21domain-526' THEN
'S-1-5-21domain-526 (Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-527' THEN
'S-1-5-21domain-527 (Enterprise Key Admins)'
WHEN events_persisted.sid = 'S-1-5-21domain-553' THEN
'S-1-5-21domain-553 (RAS and IAS Servers)'
WHEN events_persisted.sid = 'S-1-5-21root domain-518' THEN
'S-1-5-21root domain-518 (Schema Admins)'
WHEN events_persisted.sid = 'S-1-5-21root domain-519' THEN
'S-1-5-21root domain-519 (Enterprise Admins)'
WHEN events_persisted.sid = 'S-1-5-3' THEN
'S-1-5-3 (Batch)'
WHEN events_persisted.sid = 'S-1-5-32-544' THEN
'S-1-5-32-544 (Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-545' THEN
'S-1-5-32-545 (Users)'
WHEN events_persisted.sid = 'S-1-5-32-546' THEN
'S-1-5-32-546 (Guests)'
WHEN events_persisted.sid = 'S-1-5-32-547' THEN
'S-1-5-32-547 (Power Users)'
WHEN events_persisted.sid = 'S-1-5-32-548' THEN
'S-1-5-32-548 (Account Operators)'
WHEN events_persisted.sid = 'S-1-5-32-549' THEN
'S-1-5-32-549 (Server Operators)'
WHEN events_persisted.sid = 'S-1-5-32-550' THEN
'S-1-5-32-550 (Print Operators)'
WHEN events_persisted.sid = 'S-1-5-32-551' THEN
'S-1-5-32-551 (Backup Operators)'
WHEN events_persisted.sid = 'S-1-5-32-552' THEN
'S-1-5-32-552 (Replicators)'
WHEN events_persisted.sid = 'S-1-5-32-554' THEN
'S-1-5-32-554 (Builtin\Pre-Windows 2000 Compatible Access)'
WHEN events_persisted.sid = 'S-1-5-32-555' THEN
'S-1-5-32-555 (Builtin\Remote Desktop Users)'
WHEN events_persisted.sid = 'S-1-5-32-556' THEN
'S-1-5-32-556 (Builtin\Network Configuration Operators)'
WHEN events_persisted.sid = 'S-1-5-32-557' THEN
'S-1-5-32-557 (Builtin\Incoming Forest Trust Builders)'
WHEN events_persisted.sid = 'S-1-5-32-558' THEN
'S-1-5-32-558 (Builtin\Performance Monitor Users)'
WHEN events_persisted.sid = 'S-1-5-32-559' THEN
'S-1-5-32-559 (Builtin\Performance Log Users)'
WHEN events_persisted.sid = 'S-1-5-32-560' THEN
'S-1-5-32-560 (Builtin\Windows Authorization Access Group)'
WHEN events_persisted.sid = 'S-1-5-32-561' THEN
'S-1-5-32-561 (Builtin\Terminal Server License Servers)'
WHEN events_persisted.sid = 'S-1-5-32-562' THEN
'S-1-5-32-562 (Builtin\Distributed COM Users)'
WHEN events_persisted.sid = 'S-1-5-32-569' THEN
'S-1-5-32-569 (Builtin\Cryptographic Operators)'
WHEN events_persisted.sid = 'S-1-5-32-573' THEN
'S-1-5-32-573 (Builtin\Event Log Readers)'
WHEN events_persisted.sid = 'S-1-5-32-574' THEN
'S-1-5-32-574 (Builtin\Certificate Service DCOM Access)'
WHEN events_persisted.sid = 'S-1-5-32-575' THEN
'S-1-5-32-575 (Builtin\RDS Remote Access Servers)'
WHEN events_persisted.sid = 'S-1-5-32-576' THEN
'S-1-5-32-576 (Builtin\RDS Endpoint Servers)'
WHEN events_persisted.sid = 'S-1-5-32-577' THEN
'S-1-5-32-577 (Builtin\RDS Management Servers)'
WHEN events_persisted.sid = 'S-1-5-32-578' THEN
'S-1-5-32-578 (Builtin\Hyper-V Administrators)'
WHEN events_persisted.sid = 'S-1-5-32-579' THEN
'S-1-5-32-579 (Builtin\Access Control Assistance Operators)'
WHEN events_persisted.sid = 'S-1-5-32-580' THEN
'S-1-5-32-580 (Builtin\Remote Management Users)'
WHEN events_persisted.sid = 'S-1-5-32-582' THEN
'S-1-5-32-582 (Storage Replica Administrators)'
WHEN events_persisted.sid = 'S-1-5-4' THEN
'S-1-5-4 (Interactive)'
WHEN events_persisted.sid = 'S-1-5-5-X-Y' THEN
'S-1-5-5-X-Y (Logon Session)'
WHEN events_persisted.sid = 'S-1-5-6' THEN
'S-1-5-6 (Service)'
WHEN events_persisted.sid = 'S-1-5-64-10' THEN
'S-1-5-64-10 (NTLM Authentication)'
WHEN events_persisted.sid = 'S-1-5-64-14' THEN
'S-1-5-64-14 (SChannelAuthentication)'
WHEN events_persisted.sid = 'S-1-5-64-21' THEN
'S-1-5-64-21 (Digest Authentication)'
WHEN events_persisted.sid = 'S-1-5-7' THEN
'S-1-5-7 (Anonymous)'
WHEN events_persisted.sid = 'S-1-5-8' THEN
'S-1-5-8 (Proxy)'
WHEN events_persisted.sid = 'S-1-5-80' THEN
'S-1-5-80 (NT Service)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (NT Services\All Services)'
WHEN events_persisted.sid = 'S-1-5-80-0' THEN
'S-1-5-80-0 (All Services)'
WHEN events_persisted.sid = 'S-1-5-83-0' THEN
'S-1-5-83-0 (NT Virtual Machine\Virtual Machines)'
WHEN events_persisted.sid = 'S-1-5-9' THEN
'S-1-5-9 (Enterprise Domain Controllers)'
WHEN events_persisted.sid = 'S-1-5-90-0' THEN
'S-1-5-90-0 (Windows Manager\Windows Manager Group)' ELSE events_persisted.sid
END AS UserSID,
datetime( ( events_persisted.timestamp / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
tag_descriptions.locale_name AS LocaleName,
tag_descriptions.tag_name AS TagName,
events_persisted.full_event_name AS FullEventName,
events_persisted.logging_binary_name AS LoggingBinaryName,
events_persisted.friendly_logging_binary_name AS FriendlyLoggingBinaryName,
events_persisted.full_event_name_hash AS FullEventNameHash,
events_persisted.event_keywords AS Keywords,
provider_groups.group_guid AS GroupGUID,
CASE
WHEN events_persisted.is_core = 0 THEN
'No'
WHEN events_persisted.is_core = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsCore,
events_persisted.compressed_payload_size AS CompressedPayloadSize,
events_persisted.payload AS JSONPayload
FROM
events_persisted
LEFT JOIN event_tags ON events_persisted.full_event_name_hash = event_tags.full_event_name_hash
LEFT JOIN tag_descriptions ON event_tags.tag_id = tag_descriptions.tag_id
LEFT JOIN provider_groups ON events_persisted.provider_group_id = provider_groups.group_id
WHERE
TagName = 'Software Setup and Inventory'
ORDER BY
events_persisted.timestamp ASC'''
LET FileType = '''Windows EventTranscript.db Software Setup and Inventory'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: FileZilla Client Queue
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='files');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
source_file,
download,
size,
error_count,
priority,
l.path AS Local_Path,
r.path AS Remote_Path,
s.host as Remote_Server_IP,
s.port as Remote_Server_Port,
s.user as Remote_Server_User,
s.password as Remote_Server_Password,
s.account as Remote_Server_Account,
s.name as Remote_Server_Name,
s.parameters as Remote_Server_Parameters,
s.site_path as Remote_Server_Site_Path
FROM files f
INNER JOIN servers s ON f.server = s.id
INNER JOIN remote_paths r ON f.local_path = r.id
INNER JOIN local_paths l ON f.remote_path = l.id'''
LET FileType = '''FileZilla Client Queue'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Bookmarks
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_historyvisits' OR name='moz_bookmarks' OR name='moz_places' OR name='moz_inputhistory');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
Bookmarks.id AS ID,
Bookmarks.parent AS ParentID,
CASE
WHEN Bookmarks.type = 1 THEN
'URL'
WHEN Bookmarks.type = 2 THEN
'Folder'
WHEN Bookmarks.type = 3 THEN
'Separator'
END AS Type,
datetime( Bookmarks.dateAdded / 1000000, 'unixepoch', 'localtime' ) AS DateAdded,
datetime( Bookmarks.lastModified / 1000000, 'unixepoch', 'localtime' ) AS LastModified,
Bookmarks.position AS Position,
Bookmarks.title AS Title,
moz_places.url AS URL,
Bookmarks.fk AS ForeignKey
FROM
moz_bookmarks AS Bookmarks
LEFT JOIN moz_places ON Bookmarks.fk = moz_places.id
ORDER BY
Bookmarks.id ASC'''
LET FileType = '''Bookmarks'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Firefox Cookies
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_cookies');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
moz_cookies.id AS ID,
moz_cookies.host AS Host,
moz_cookies.name AS Name,
moz_cookies.value AS Value,
datetime( moz_cookies.creationTime / 1000000, 'UNIXEPOCH', 'localtime' ) AS "Creation Time",
datetime( moz_cookies.lastAccessed / 1000000, 'UNIXEPOCH', 'localtime' ) AS "Last Accessed Time",
datetime( moz_cookies.expiry, 'UNIXEPOCH', 'localtime' ) AS Expiration,
CASE
WHEN moz_cookies.isSecure = 0 THEN
'No'
WHEN moz_cookies.isSecure = 1 THEN
'Yes'
END AS IsSecure,
CASE
WHEN moz_cookies.isHttpOnly = 0 THEN
'No'
WHEN moz_cookies.isHttpOnly = 1 THEN
'Yes'
END AS IsHTTPOnly
FROM
moz_cookies
ORDER BY
moz_cookies.id ASC'''
LET FileType = '''Firefox Cookies'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Firefox Downloads
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_historyvisits' OR name='moz_bookmarks' OR name='moz_places' OR name='moz_inputhistory');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
moz_annos.place_id AS PlaceID,
moz_annos.content AS Content,
datetime( dateAdded / 1000000, 'unixepoch', 'localtime' ) AS DateAdded,
datetime( lastModified / 1000000, 'unixepoch', 'localtime' ) AS LastModified
FROM
moz_annos
WHERE
anno_attribute_id IN (1,2)
ORDER BY
moz_annos.dateAdded ASC'''
LET FileType = '''Firefox Downloads'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Firefox Downloads
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_downloads');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
moz_downloads.id AS ID,
moz_downloads.name AS Name,
moz_downloads.mimeType AS MIMEType,
moz_downloads.source AS Source,
moz_downloads.target AS Target,
datetime( startTime / 1000000, 'unixepoch', 'localtime' ) AS StartTime,
datetime( endTime / 1000000, 'unixepoch', 'localtime' ) AS EndTime,
moz_downloads.currBytes AS CurrentBytes,
moz_downloads.maxBytes AS MaxBytes
FROM
moz_downloads
ORDER BY
moz_downloads.id ASC'''
LET FileType = '''Firefox Downloads'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Firefox Favicons
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_icons' OR name='moz_icons_to_pages' OR name='moz_pages_w_icons');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT
moz_icons.id AS ID,
moz_pages_w_icons.page_url AS PageURL,
moz_icons.icon_url AS FaviconURL,
datetime( moz_icons.expire_ms / 1000, 'unixepoch', 'localtime' ) AS Expiration
FROM
moz_icons
INNER JOIN moz_icons_to_pages ON moz_icons.id = moz_icons_to_pages.icon_id
INNER JOIN moz_pages_w_icons ON moz_icons_to_pages.page_id = moz_pages_w_icons.id
ORDER BY
moz_icons.expire_ms ASC'''
LET FileType = '''Firefox Favicons'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Firefox Form History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_formhistory');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
id AS ID,
fieldname AS FieldName,
value AS Value,
timesUsed AS TimesUsed,
datetime( firstUsed / 1000000, 'unixepoch', 'localtime' ) AS "First Used",
datetime( lastUsed / 1000000, 'unixepoch', 'localtime' ) AS "Last Used",
guid AS GUID
FROM
moz_formhistory
ORDER BY
id ASC'''
LET FileType = '''Firefox Form History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='moz_historyvisits' OR name='moz_bookmarks' OR name='moz_places' OR name='moz_inputhistory');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
moz_historyvisits.id AS VisitID,
moz_historyvisits.from_visit AS FromVisitID,
datetime( moz_places.last_visit_date / 1000000, 'unixepoch', 'localtime' ) AS LastVisitDate,
moz_places.visit_count AS VisitCount,
moz_places.url AS URL,
moz_places.title AS Title,
moz_places.description AS Description,
CASE
WHEN moz_historyvisits.visit_type = 1 THEN
'TRANSITION_LINK'
WHEN moz_historyvisits.visit_type = 2 THEN
'TRANSITION_TYPED'
WHEN moz_historyvisits.visit_type = 3 THEN
'TRANSITION_BOOKMARK'
WHEN moz_historyvisits.visit_type = 4 THEN
'TRANSITION_EMBED'
WHEN moz_historyvisits.visit_type = 5 THEN
'TRANSITION_REDIRECT_PERMANENT'
WHEN moz_historyvisits.visit_type = 6 THEN
'TRANSITION_REDIRECT_TEMPORARY'
WHEN moz_historyvisits.visit_type = 7 THEN
'TRANSITION_DOWNLOAD'
WHEN moz_historyvisits.visit_type = 8 THEN
'TRANSITION_FRAMED_LINK'
WHEN moz_historyvisits.visit_type = 9 THEN
'TRANSITION_RELOAD'
END AS VisitType,
CASE
WHEN moz_places.hidden = 0 THEN
'No'
WHEN moz_places.hidden = 1 THEN
'Yes'
END AS Hidden,
CASE
WHEN moz_places.typed = 0 THEN
'No'
WHEN moz_places.typed = 1 THEN
'Yes'
END AS Typed,
moz_places.frecency AS Frecency,
moz_places.preview_image_url AS PreviewImageURL
FROM
moz_places
INNER JOIN moz_historyvisits ON moz_places.origin_id = moz_historyvisits.id
ORDER BY
moz_places.last_visit_date ASC'''
LET FileType = '''History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive FS Changes
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='change_buffer_entries' OR name='fschanges');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
sqlite_sequence.seq AS Sequence,
fschanges.identifier AS Identifier,
fschanges.direction AS Direction,
fschanges."action" AS "Action",
fschanges.inode AS Inode,
fschanges.parent_inode AS ParentInode,
fschanges.volume AS Volume,
fschanges.parent_volume AS ParentVolume,
fschanges.path AS Path,
fschanges.name AS Name,
CASE
WHEN fschanges.is_folder = 0 THEN
'No'
WHEN fschanges.is_folder = 1 THEN
'Yes'
END AS IsFolder,
CASE
WHEN fschanges.affects_gdoc = 0 THEN
'No'
WHEN fschanges.affects_gdoc = 1 THEN
'Yes'
END AS AffectsGDocs,
datetime( modified, 'unixepoch' ) AS ModifiedTime,
fschanges.size AS SizeInBytes,
CASE
WHEN fschanges.shared = 0 THEN
'No'
WHEN fschanges.shared = 1 THEN
'Yes'
END AS Shared,
CASE
WHEN doc_type = 0 THEN
'Folder'
WHEN doc_type = 1 THEN
'Regular File'
WHEN doc_type = 2 THEN
'Google Slides'
WHEN doc_type = 3 THEN
'Google Forms'
WHEN doc_type = 4 THEN
'Google Sheets'
WHEN doc_type = 5 THEN
'Google Draw'
WHEN doc_type = 6 THEN
'Google Docs'
WHEN doc_type = 12 THEN
'Google Maps' ELSE 'Google File/Object'
END AS DocType,
fschanges.full_path AS FullPath,
fschanges.hash AS Hash,
change_buffer_entries.failure_count AS FailureCount,
change_buffer_entries.time AS Time,
change_buffer_entries.state AS State
FROM
fschanges
LEFT JOIN change_buffer_entries ON fschanges.identifier = change_buffer_entries.identifier,
sqlite_sequence
ORDER BY
fschanges.identifier ASC'''
LET FileType = '''Google Drive FS Changes'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive CloudGraphDB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cloud_graph_entry');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
filename AS 'Filename',
datetime( modified, 'unixepoch' ) AS 'ModifiedTime',
CASE
WHEN acl_role = 0 THEN
'Private/Google Drive Owner'
WHEN acl_role = 1 THEN
'Can Contribute'
WHEN acl_role = 2 THEN
'Can View' ELSE 'From Elsewhere'
END AS 'ACL Role',
CASE
WHEN doc_type = 0 THEN
'Folder'
WHEN doc_type = 1 THEN
'Regular File'
WHEN doc_type = 2 THEN
'Google Slides'
WHEN doc_type = 3 THEN
'Google Forms'
WHEN doc_type = 4 THEN
'Google Sheets'
WHEN doc_type = 5 THEN
'Google Draw'
WHEN doc_type = 6 THEN
'Google Docs'
WHEN doc_type = 12 THEN
'Google Maps' ELSE 'Google File/Object'
END AS Type,
size AS 'Size in bytes',
checksum AS 'MD5 Hash',
CASE
WHEN shared = 1 THEN
'Shared'
WHEN shared = 0 THEN
'Not Shared'
END AS 'Shared Status',
CASE
WHEN removed = 0 THEN
'Not Removed'
WHEN removed = 1 THEN
'Removed'
END AS 'Cloud Status'
FROM
cloud_graph_entry'''
LET FileType = '''Google Drive CloudGraphDB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive SnapshotDB - Cloud Files
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cloud_entry' OR name='volume_info' OR name='cloud_relations' OR name='local_entry' OR name='local_relations');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
cloud_entry.doc_id AS ID,
( SELECT cloud_entry.filename FROM cloud_entry WHERE cloud_relations.parent_doc_id = cloud_entry.doc_id ) AS ParentFolder,
filename AS Filename,
datetime( modified, 'unixepoch' ) AS ModifiedTime,
CASE
WHEN acl_role = 0 THEN
'Google Drive Owner' ELSE 'From Elsewhere'
END AS ACLRole,
CASE
WHEN doc_type = 0 THEN
'Folder'
WHEN doc_type = 1 THEN
'Regular File'
WHEN doc_type = 2 THEN
'Google Slides'
WHEN doc_type = 3 THEN
'Google Forms'
WHEN doc_type = 4 THEN
'Google Sheets'
WHEN doc_type = 5 THEN
'Google Draw'
WHEN doc_type = 6 THEN
'Google Docs'
WHEN doc_type = 12 THEN
'Google Maps' ELSE 'Google File/Object'
END AS Type,
size AS 'SizeInBytes',
checksum AS Checksum,
CASE
WHEN shared = 1 THEN
'Shared'
WHEN shared = 0 THEN
'Not Shared'
END AS SharedStatus,
CASE
WHEN removed = 1 THEN
'Yes'
WHEN removed = 0 THEN
'No'
END AS RemovedStatus
FROM
cloud_entry
LEFT JOIN cloud_relations ON cloud_relations.child_doc_id = cloud_entry.doc_id
ORDER BY
cloud_entry.modified ASC'''
LET FileType = '''Google Drive SnapshotDB - Cloud Files'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive SnapshotDB - Local Files
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cloud_entry' OR name='volume_info' OR name='cloud_relations' OR name='local_entry' OR name='local_relations');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
local_entry.inode AS FileID,
local_entry.volume AS Volume,
( SELECT local_entry.filename FROM local_entry WHERE local_relations.parent_inode = local_entry.inode ) AS ParentFolder,
local_entry.filename AS Filename,
datetime( modified, 'unixepoch' ) AS "ModifiedTime",
local_entry.checksum AS Checksum,
local_entry.size AS SizeInBytes,
CASE
WHEN is_folder = 0 THEN
'No'
WHEN is_folder = 1 THEN
'Yes'
END AS IsFolder
FROM
local_entry AS local_entry
LEFT JOIN local_relations ON local_relations.child_inode = local_entry.inode
ORDER BY
local_entry.inode ASC'''
LET FileType = '''Google Drive SnapshotDB - Local Files'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive SnapshotDB - Volume Info
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='cloud_entry' OR name='volume_info' OR name='cloud_relations' OR name='local_entry' OR name='local_relations');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
main.volume_info.volume AS Volume,
main.volume_info.full_path AS FullPath,
main.volume_info.uuid AS UUID,
main.volume_info.label AS DriveLabel,
main.volume_info.size AS SizeInBytes,
main.volume_info.filesystem AS DriveFormat,
main.volume_info.model AS DriveModel,
main.volume_info.device_type AS DeviceType,
main.volume_info.device_file AS DeviceFile,
main.volume_info.device_number AS DeviceSerialNumber
FROM
main.volume_info
ORDER BY
main.volume_info.full_path ASC'''
LET FileType = '''Google Drive SnapshotDB - Volume Info'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive Sync Config Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='data');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
data.entry_key AS EntryKey,
data.data_key AS DataKey,
data.data_value AS DataValue
FROM
data'''
LET FileType = '''Google Drive Sync Config Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Google Drive for Desktop Metadata
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='items');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
items.stable_id,
items.local_title as "Name",
items.file_size as "SizeInBytes",
items.mime_type,
datetime(items.modified_date / 1000, 'unixepoch') as "ModifiedTime",
datetime(items.viewed_by_me_date / 1000, 'unixepoch') as "LastInteractionTime",
CASE
when items.is_folder = 1 then "Folder"
when items.is_folder = 0 then "File"
end as "IsFolder",
CASE
when items.trashed = 1 then "Deleted"
when items.trashed = 0 then "Not Deleted"
end as "DeletionStatus",
CASE
when items.is_owner = 1 then "Owner"
when items.is_owner = 0 then "Not Owner"
end as "Ownership",
CASE
when items.shared_with_me_date = 1 then "Shared"
when items.shared_with_me_date = 0 then "Not Shared"
end as "SharedWithUser",
items.id AS "CloudIdentifier"
FROM
items'''
LET FileType = '''Google Drive for Desktop Metadata'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Microsoft Sticky Notes
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Note' OR name='Media' OR name='Insight' OR name='User' OR name='Stroke');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
datetime( ( "CreatedAt" / 10000000 ) - 62135596800, 'unixepoch' ) AS CreatedAt,
datetime( ( "UpdatedAt" / 10000000 ) - 62135596800, 'unixepoch' ) AS UpdatedAt,
datetime( ( "DeletedAt" / 10000000 ) - 62135596800, 'unixepoch' ) AS DeletedAt,
Note.WindowPosition AS WindowPosition,
CASE
WHEN Note.IsOpen = 0 THEN 'No'
WHEN Note.IsOpen = 1 THEN 'Yes'
ELSE 'Unknown'
END AS IsOpen,
CASE
WHEN Note.IsAlwaysOnTop = 0 THEN 'No'
WHEN Note.IsAlwaysOnTop = 1 THEN 'Yes'
ELSE 'Unknown'
END AS IsAlwaysOnTop,
Note.Theme AS Theme,
Note.Id AS NoteID,
Note.ParentId AS ParentID,
Note.Text AS Text,
Note.LastServerVersion AS LastServerVersion
FROM
Note
ORDER BY
Note.CreatedAt ASC'''
LET FileType = '''Microsoft Sticky Notes'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Nessus Preferences Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='PREFERENCES');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
PREFERENCES.name AS Name,
PREFERENCES.value AS Value
FROM
PREFERENCES'''
LET FileType = '''Nessus Preferences Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Company names
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Cars' OR name='CarScheduling' OR name='Customers');'''
LET IdentifyValue = 3
LET SQLQuery = '''select Company from Customers;'''
LET FileType = '''Company names'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Order payment type and amount, ordered
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Cars' OR name='CarScheduling' OR name='Customers');'''
LET IdentifyValue = 3
LET SQLQuery = '''select CustomerID,PaymentType,PaymentAmount from Orders ORDER BY PaymentAmount,PaymentType;'''
LET FileType = '''Order payment type and amount, ordered'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Distinct descriptions
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Cars' OR name='CarScheduling' OR name='Customers');'''
LET IdentifyValue = 3
LET SQLQuery = '''select distinct Description from CarScheduling'''
LET FileType = '''Distinct descriptions'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Make and model
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Cars' OR name='CarScheduling' OR name='Customers');'''
LET IdentifyValue = 3
LET SQLQuery = '''select Trademark,Model from cars ORDER BY Trademark'''
LET FileType = '''Make and model'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Customers table users
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Customers' OR name='Total');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT FirstName,LastName from Customers'''
LET FileType = '''Customers table users'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Another Customers table query
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Customers' OR name='Total');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT Id,State as Wizzo from Customers'''
LET FileType = '''Another Customers table query'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: JoinExample
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Customers' OR name='Total');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT Total.ID, Customers.FirstName || ' ' || Customers.LastName AS CustomerName, Total.Year, Total.January, Total.February, Total.March, Total.April, Total.May, Total.June, Total.July, Total.August, Total.September, Total.October, Total.November, Total.December FROM Customers INNER JOIN Total ON Customers.ID = Total.CustomerID'''
LET FileType = '''JoinExample'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SomeThingElse
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Customers' OR name='Total');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT Id,State as Wizzo froM Customers'''
LET FileType = '''SomeThingElse'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: TeraCopy MainDB
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='list');'''
LET IdentifyValue = 1
LET SQLQuery = '''select
Name AS "Name of SQLite DB",
datetime(julianday(Started)) as "Transfer Started",
datetime(julianday(Finished)) as "Transfer Finished",
source AS "Source",
target AS "Target",
Files AS "Number of Files",
size AS "Size (Bytes)"
from list'''
LET FileType = '''TeraCopy MainDB'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: TeraCopy History
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Files');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
Source,
size AS "Size (Bytes)",
CASE
WHEN IsFolder = 0 THEN
'No'
WHEN IsFolder = 1 THEN
'Yes'
END AS IsFolder,
CASE
WHEN Marked = 0 THEN
'No'
WHEN Marked = 1 THEN
'Yes'
END AS Marked,
CASE
WHEN Hidden = 0 THEN
'No'
WHEN Hidden = 1 THEN
'Yes'
END AS Hidden,
datetime( julianday( Creation ) ) AS Creation,
datetime( julianday( Access ) ) AS Access,
datetime( julianday( Write ) ) AS Write
FROM
Files'''
LET FileType = '''TeraCopy History'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: TeraCopy History Log
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Files');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
Log.Timestamp AS Timestamp,
Log.Message AS Message
FROM
Log
ORDER BY
Timestamp'''
LET FileType = '''TeraCopy History Log'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Photos Items
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Item' OR name='Folder');'''
LET IdentifyValue = 2
LET SQLQuery = '''select item.Item_FileName AS Item_FileName,
item.Item_FileSize AS Item_FileSize,
item.Item_Width AS Item_Width,
item.Item_Height AS Item_Height,
item.Item_Latitude AS Item_Latitude,
item.Item_Longitude AS Item_Longitude,
ApplicationName.ApplicationName_Text,
CameraManufacturer.CameraManufacturer_Text,
CameraModel.CameraModel_Text,
datetime((item.Item_DateTaken - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Item_DateTaken,
datetime((item.Item_DateCreated - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Item_DateCreated,
datetime((item.Item_DateModified - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Item_DateModified,
datetime((item.Item_DateIngested - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Item_DateIngested
FROM item
LEFT JOIN ApplicationName ON item.Item_ApplicationNameId = ApplicationName.ApplicationName_Id
LEFT JOIN CameraManufacturer ON item.Item_CameraManufacturerId = CameraManufacturer.CameraManufacturer_Id
LEFT JOIN CameraModel ON item.Item_CameraModelId = CameraModel.CameraModel_Id
ORDER BY Item_DateCreated DESC'''
LET FileType = '''Windows Photos Items'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Photos Folders
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Item' OR name='Folder');'''
LET IdentifyValue = 2
LET SQLQuery = '''select Folder.Folder_Path AS Folder_Path,
Folder.Folder_DisplayName AS Folder_DisplayName,
Folder.Folder_ItemCount AS Folder_ItemCount,
datetime((Folder.Folder_DateCreated - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Folder_DateCreated,
datetime((Folder.Folder_DateModified - 116444736000000000) / 10000000, 'unixepoch', 'localtime') AS Folder_DateModified
FROM Folder ORDER BY Folder_DateCreated DESC'''
LET FileType = '''Windows Photos Folders'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex Properties
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_1_Properties' OR name='SystemIndex_1_PropertyStore' OR name='SystemIndex_1_PropertyStore_Metadata');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_1_Properties;'''
LET FileType = '''SystemIndex Properties'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex PropertyStore
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_1_Properties' OR name='SystemIndex_1_PropertyStore' OR name='SystemIndex_1_PropertyStore_Metadata');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_1_PropertyStore;'''
LET FileType = '''SystemIndex PropertyStore'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex PropertyStore Metadata
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_1_Properties' OR name='SystemIndex_1_PropertyStore' OR name='SystemIndex_1_PropertyStore_Metadata');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_1_PropertyStore_Metadata;'''
LET FileType = '''SystemIndex PropertyStore Metadata'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Joined PropertyStore Metadata
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_1_Properties' OR name='SystemIndex_1_PropertyStore' OR name='SystemIndex_1_PropertyStore_Metadata');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_1_PropertyStore AS PS JOIN SystemIndex_1_PropertyStore_Metadata AS PS_META WHERE PS_META.Id = PS.ColumnId;'''
LET FileType = '''Joined PropertyStore Metadata'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex Gthr
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_Gthr' OR name='SystemIndex_GthrPth' OR name='SystemIndex_GthrAppOwner');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_Gthr;'''
LET FileType = '''SystemIndex Gthr'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex GthrPth
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_Gthr' OR name='SystemIndex_GthrPth' OR name='SystemIndex_GthrAppOwner');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_GthrPth;'''
LET FileType = '''SystemIndex GthrPth'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: SystemIndex GthrAppOwner
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='SystemIndex_Gthr' OR name='SystemIndex_GthrPth' OR name='SystemIndex_GthrAppOwner');'''
LET IdentifyValue = 3
LET SQLQuery = '''SELECT * FROM SystemIndex_GthrAppOwner;'''
LET FileType = '''SystemIndex GthrAppOwner'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Update Store.db
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='ACTIONRECORDS' OR name='COMPLETEDUPDATES' OR name='UPDATES' OR name='VARIABLES');'''
LET IdentifyValue = 4
LET SQLQuery = '''SELECT
datetime( Time / 1000, 'unixepoch', 'localtime' ) AS Time,
COMPLETEDUPDATES.PROVIDERID AS ProviderID,
COMPLETEDUPDATES.UPDATEID AS UpdateID,
COMPLETEDUPDATES.TITLE AS Title,
COMPLETEDUPDATES.DESCRIPTION AS Description,
COMPLETEDUPDATES.MOREINFOURL AS MoreInfoURL,
COMPLETEDUPDATES.HISTORYCATEGORY AS HistoryCategory,
COMPLETEDUPDATES.UNINSTALL AS Uninstall
FROM
COMPLETEDUPDATES
ORDER BY
COMPLETEDUPDATES.TIME ASC'''
LET FileType = '''Windows Update Store.db'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Notifications
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Notification' OR name='HandlerAssets' OR name='WNSPushChannel' OR name='TransientTable' OR name='NotificationData');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
Notification.Id AS ID,
Notification.'Order' AS 'Order',
Notification.HandlerId AS HandlerId,
NotificationHandler.PrimaryId AS Application,
CASE
WHEN NotificationHandler.ParentId THEN
NotificationHandler.ParentId ELSE ''
END AS Parent,
NotificationHandler.HandlerType AS HandlerType,
Notification.Type AS Type,
Notification.Payload AS Payload,
Notification.PayloadType AS PayloadType,
Notification.Tag AS Tag,
Notification."Group" AS "Group",
datetime( ( Notification.ArrivalTime - 116444736000000000 ) / 10000000, 'unixepoch' ) AS ArrivalTime,
CASE
WHEN Notification.ExpiryTime = 0 THEN
'Expired' ELSE datetime( ( Notification.ExpiryTime - 116444736000000000 ) / 10000000, 'unixepoch' )
END AS ExpirationTime,
NotificationHandler.CreatedTime AS HandlerCreated,
NotificationHandler.ModifiedTime AS HandlerModified,
CASE
WHEN NotificationHandler.WNSId NOTNULL THEN
NotificationHandler.WNSId ELSE ''
END AS WNSId,
CASE
WHEN NotificationHandler.WNFEventName NOTNULL THEN
NotificationHandler.WNFEventName ELSE ''
END AS WNFEventName,
CASE
WHEN WNSPushChannel.ChannelId NOTNULL THEN
WNSPushChannel.ChannelId ELSE ''
END AS ChannelID,
CASE
WHEN WNSPushChannel.Uri NOTNULL THEN
WNSPushChannel.Uri ELSE ''
END AS URI,
CASE
WHEN WNSPushChannel.CreatedTime NOTNULL THEN
datetime( ( WNSPushChannel.CreatedTime - 116444736000000000 ) / 10000000, 'unixepoch' ) ELSE ''
END AS WNSCreatedTime,
CASE
WHEN WNSPushChannel.ExpiryTime NOTNULL THEN
datetime( ( WNSPushChannel.ExpiryTime - 116444736000000000 ) / 10000000, 'unixepoch' ) ELSE ''
END AS WNSExpirationTime,
CASE
WHEN hex( Notification.ActivityId ) = '00000000000000000000000000000000' THEN
'' ELSE hex( Notification.ActivityId )
END AS ActivityId
FROM
Notification
JOIN NotificationHandler ON NotificationHandler.RecordId = Notification.HandlerId
LEFT JOIN WNSPushChannel ON WNSPushChannel.HandlerId = NotificationHandler.RecordId
ORDER BY
Id DESC'''
LET FileType = '''Windows Notifications'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Notifications
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='Notification' OR name='HandlerAssets' OR name='WNSPushChannel' OR name='TransientTable' OR name='NotificationData');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
NotificationHandler.PrimaryId AS PrimaryID,
WNSPushChannel.ChannelId AS ChannelID,
WNSPushChannel.HandlerId AS HandlerID,
WNSPushChannel.Uri AS URI,
datetime( ( WNSPushChannel.CreatedTime - 116444736000000000 ) / 10000000, 'unixepoch' ) AS CreatedTime,
datetime( ( WNSPushChannel.ExpiryTime - 116444736000000000 ) / 10000000, 'unixepoch' ) AS ExpirationTime
FROM
WNSPushChannel
JOIN NotificationHandler ON NotificationHandler.RecordId = WNSPushChannel.HandlerId
ORDER BY
CreatedTime ASC'''
LET FileType = '''Windows Notifications'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Contacts Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='contact' OR name='contactdate' OR name='contacturl' OR name='emailaddress' OR name='phonenumber' OR name='postaladdress');'''
LET IdentifyValue = 6
LET SQLQuery = '''SELECT DISTINCT
contact.display_name AS DisplayName,
contact.nickname AS Nickname,
datetime( ( last_updated_time / 10000000 ) - 11644473600, 'unixepoch' ) AS LastUpdatedTimeUTC,
contact.company AS Company,
contact.job_title AS Title,
contact.notes AS Notes,
contact.name_prefix AS Prefix,
contact.given_name AS GivenName,
contact.middle_name AS MiddleName,
contact.family_name AS FamilyName,
contact.name_suffix AS Suffix,
CASE
WHEN contactdate.date_type = 1 THEN
'Birthday'
WHEN contactdate.date_type = 2 THEN
'Anniversary'
WHEN contactdate.date_type = 3 THEN
'UserDefined' ELSE contactdate.date_type
END AS DateType,
contactdate.label AS DateLabel,
contactdate.display_date AS DisplayDate,
CASE
WHEN contacturl.type = 1 THEN
'HomePage'
WHEN contacturl.type = 3 THEN
'Work'
WHEN contacturl.type = 5 THEN
'Other'
WHEN contacturl.type = 6 THEN
'Blog/Profile/UserDefined' ELSE contacturl.type
END AS URLType,
contacturl.label AS URLLabel,
contacturl.url_address AS URLAddress,
CASE
WHEN emailaddress.type = 1 THEN
'Home'
WHEN emailaddress.type = 2 THEN
'Work'
WHEN emailaddress.type = 4 THEN
'Other'
WHEN emailaddress.type = 5 THEN
'UserDefined' ELSE emailaddress.type
END AS EmailType,
emailaddress.label AS EmailLabel,
emailaddress.address AS EmailAddress,
phonenumber.phone_number AS PhoneNumber,
phonenumber.display_phone_number AS DisplayPhoneNumber,
CASE
WHEN phonenumber.phone_number_type = 1 THEN
'Home'
WHEN phonenumber.phone_number_type = 2 THEN
'Mobile'
WHEN phonenumber.phone_number_type = 3 THEN
'Work'
WHEN phonenumber.phone_number_type = 4 THEN
'WorkMobile'
WHEN phonenumber.phone_number_type = 5 THEN
'Main'
WHEN phonenumber.phone_number_type = 6 THEN
'Other/HomeFax/WorkFax/Pager'
WHEN phonenumber.phone_number_type = 8 THEN
'UserDefined' ELSE phonenumber.phone_number_type
END AS PhoneNumberType,
phonenumber.label AS PhoneNumberLabel,
CASE
WHEN postaladdress.type = 1 THEN
'Home'
WHEN postaladdress.type = 2 THEN
'Work'
WHEN postaladdress.type = 4 THEN
'Other'
WHEN postaladdress.type = 5 THEN
'UserDefined' ELSE postaladdress.type
END AS PostalAddressType,
postaladdress.label AS PostalAddressLabel,
postaladdress.street AS PostalAddressStreet,
postaladdress.city AS PostalAddressCity,
postaladdress.region AS PostalAddressRegion,
postaladdress.postal_code AS PostalAddressPostalCode,
postaladdress.country_code AS PostalAddressCountryCode,
postaladdress.display_address AS PostalAddressDisplayAddress
FROM
contact
LEFT JOIN contactdate ON contact.contact_id = contactdate.contact_id
LEFT JOIN contacturl ON contact.contact_id = contacturl.contact_id
LEFT JOIN emailaddress ON contact.contact_id = emailaddress.contact_id
LEFT JOIN phonenumber ON contact.contact_id = phonenumber.contact_id
LEFT JOIN postaladdress ON contact.contact_id = postaladdress.contact_id
ORDER BY
contact.display_name ASC'''
LET FileType = '''Windows Your Phone Contacts Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Notifications Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='notifications');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
notifications.id AS 'ID',
json_extract ( json, '$.appName' ) AS 'Application',
datetime( json_extract ( json, '$.postTime' ) / 1000, 'unixepoch', 'localtime' ) AS 'PostTime',
datetime( json_extract ( json, '$.timestamp' ) / 1000, 'unixepoch', 'localtime' ) AS 'Timestamp',
json_extract ( json, '$.tickerText' ) AS 'TickerText',
json_extract ( json, '$.title' ) AS 'Title',
json_extract ( json, '$.bigText' ) AS 'BigText',
json_extract ( json, '$.text' ) AS 'Text',
json_extract ( json, '$.subText' ) AS 'SubText',
CASE
WHEN json_extract ( json, '$.isClearable' ) = 0 THEN
'No'
WHEN json_extract ( json, '$.isClearable' ) = 1 THEN
'Yes'
END AS 'IsClearable',
CASE
WHEN json_extract ( json, '$.isGroup' ) = 0 THEN
'No'
WHEN json_extract ( json, '$.isGroup' ) = 1 THEN
'Yes'
END AS 'IsGroup',
CASE
WHEN json_extract ( json, '$.isOngoing' ) = 0 THEN
'No'
WHEN json_extract ( json, '$.isOngoing' ) = 1 THEN
'Yes'
END AS 'IsOngoing',
json_extract ( json, '$.category' ) AS 'Category',
notifications.package_name AS 'Package Name',
notifications.json AS 'Payload'
FROM
notifications
ORDER BY
notifications.id ASC'''
LET FileType = '''Windows Your Phone Notifications Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Photos Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='media' OR name='photo');'''
LET IdentifyValue = 2
LET SQLQuery = '''SELECT
media.id AS 'Media ID',
media.mime_type AS 'MIME Type',
media.name AS Name,
datetime( ( last_updated_time / 10000000 ) - 11644473600, 'unixepoch' ) AS 'Last Updated Time',
datetime( ( taken_time / 10000000 ) - 11644473600, 'unixepoch' ) AS 'Taken Time',
datetime( ( last_seen_time / 10000000 ) - 11644473600, 'unixepoch' ) AS 'Last Seen Time',
media.height AS Height,
media.width AS Width,
media.orientation AS Orientation,
( media.size / 1000.00 ) AS 'Size (kb)',
media.uri AS URI
FROM
media
ORDER BY
media.id ASC'''
LET FileType = '''Windows Your Phone Photos Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Phone Database SMS Messages
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='message' OR name='mms' OR name='rcs_chat' OR name='sync' OR name='subscription');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
message.message_id AS MessageID,
message.thread_id AS ThreadID,
datetime( ( "timestamp" / 10000000 ) - 11644473600, 'unixepoch' ) AS Timestamp,
message.from_address AS "From",
CASE
WHEN message.type = 1 THEN
'Received'
WHEN message.type = 2 THEN
'Sent' ELSE 'Unknown'
END AS Type,
message.body AS Body
FROM
message
ORDER BY
message.thread_id ASC,
message.timestamp ASC'''
LET FileType = '''Windows Your Phone Phone Database SMS Messages'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Subscription Info
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='message' OR name='mms' OR name='rcs_chat' OR name='sync' OR name='subscription');'''
LET IdentifyValue = 5
LET SQLQuery = '''SELECT
subscription.subscription_id AS SubscriptionID,
subscription.sim_slot_index AS SimSlotIndex,
subscription.country_iso AS CountryISO,
subscription.name AS WirelessProviderName,
CASE
WHEN subscription.is_roaming = 0 THEN
'No'
WHEN subscription.is_roaming = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsRoaming,
subscription.number AS PhoneNumber,
CASE
WHEN subscription.is_mms_enabled = 0 THEN
'No'
WHEN subscription.is_mms_enabled = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsMMSEnabled,
CASE
WHEN subscription.is_audio_attachment_allowed = 0 THEN
'No'
WHEN subscription.is_audio_attachment_allowed = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsAudioAttachmentAllowed,
CASE
WHEN subscription.is_multipart_sms_enabled = 0 THEN
'No'
WHEN subscription.is_multipart_sms_enabled = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsMultiPartSMSEnabled,
CASE
WHEN subscription.is_group_mms_enabled = 0 THEN
'No'
WHEN subscription.is_group_mms_enabled = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsGroupMMSEnabled,
CASE
WHEN subscription.should_send_multipart_sms_as_separate_messages = 0 THEN
'No'
WHEN subscription.should_send_multipart_sms_as_separate_messages = 1 THEN
'Yes' ELSE 'Unknown'
END AS ShouldSendMultiPartSMSAsSeparateMessages,
subscription.max_message_size AS "MaxMessageSize (Bytes)",
subscription.recipient_limit AS RecipientLimit,
subscription.max_image_height AS MaxImageHeight,
subscription.max_image_width AS MaxImageWidth,
subscription.sms_multipart_to_mms_text_threshold AS SMSMultiParttoMMSTextThreshold,
subscription.sms_to_mms_text_length_threshold AS SMStoMMSTextLengthThreshold,
subscription.max_message_text_length AS MaxMessageTextLength,
subscription.max_subject_length AS MaxSubjectLength,
CASE
WHEN subscription.is_default_data_subscription = 0 THEN
'No'
WHEN subscription.is_default_data_subscription = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsDefaultDataSubscription,
CASE
WHEN subscription.is_default_sms_subscription = 0 THEN
'No'
WHEN subscription.is_default_sms_subscription = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsDefaultSMSSubscription,
CASE
WHEN subscription.is_default_subscription = 0 THEN
'No'
WHEN subscription.is_default_subscription = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsDefaultSubscription,
CASE
WHEN subscription.is_default_voice_subscription = 0 THEN
'No'
WHEN subscription.is_default_voice_subscription = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsDefaultVoiceSubscription,
CASE
WHEN subscription.is_rcs_supported = 0 THEN
'No'
WHEN subscription.is_rcs_supported = 1 THEN
'Yes' ELSE 'Unknown'
END AS IsRCSSupported,
subscription.max_rcs_message_size AS "MaxRCSMessageSize (Bytes)",
subscription.max_rcs_file_size AS "MaxRCSFileSize (Bytes)"
FROM
subscription'''
LET FileType = '''Windows Your Phone Subscription Info'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Windows Your Phone Settings Database
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='settings');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
phone_apps.app_name AS 'Application Name',
phone_apps.package_name AS 'Package Name',
phone_apps.version AS 'Version',
settings.setting_group_id AS 'GroupID',
CASE
WHEN settings.setting_value = 1 THEN
'On' ELSE 'Off'
END AS 'Settings Value',
settings.setting_type AS 'Settings Type',
settings.setting_key AS 'Settings Key',
settings.setting_group_id AS 'Group ID'
FROM
phone_apps
LEFT JOIN settings ON settings.setting_key = phone_apps.package_name
ORDER BY
phone_apps.app_name ASC'''
LET FileType = '''Windows Your Phone Settings Database'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Calls
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='ZCALLRECORD');'''
LET IdentifyValue = 1
LET SQLQuery = '''select
z_pk AS "Call Sequence #",
zaddress AS "Phone Number",
zduration AS "Call in Seconds",
case
when zoriginated = 0 then "Incoming"
when zoriginated = 1 then "Outgoing"
end AS "Call Direction",
case
when zanswered = 0 then "Call Missed"
when zanswered = 1 then "Call Answered"
end as "Call Status",
datetime(zdate+978307200,'unixepoch','localtime') AS "Timestamp"
from zcallrecord'''
LET FileType = '''Calls'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)
- name: Accounts
query: |
LET IdentifyQuery = '''SELECT count(*) FROM sqlite_master WHERE type='table' AND (name='ZACCOUNT');'''
LET IdentifyValue = 1
LET SQLQuery = '''SELECT
Z_PK,
ZACCOUNTTYPE AS "Account Type",
ZPARENTACCOUNT AS "Parent Account",
ZUSERNAME AS "Username",
DATETIME(ZDATE+978307200,'UNIXEPOCH') AS "TIMESTAMP",
ZACCOUNTDESCRIPTION AS "Account Description",
ZIDENTIFIER AS "Identifier",
ZOWNINGBUNDLEID AS "Bundle ID"
FROM ZACCOUNT'''
LET FileType = '''Accounts'''
SELECT * FROM ApplyFile(
SQLQuery=SQLQuery, FileType=FileType,
IdentifyQuery=IdentifyQuery, IdentifyValue=IdentifyValue)