This article includes a list of queries to conduct forensic analysis on Windows systems.
These are categorized into sections as -
processes,
services,
file system activities,
user login/session activities,
network traffic.
These queries are written with generic where clauses or with an intention to direct/introduce the analyst to relevant tables.
As an example a process which was running at certain times, files downloaded/uploaded, user login/logouts, sites visited, installed applications, changes to security settings, etc.
-- A.Processes, exes, services
-- A.1 capture list of process exe - global data /Time machine/ Real-time query.
Query
|
SELECT upt_hostname,
pf_process_exe,
pf_filename,
From_unixtime(last_execution_time) AS lastExcutionTime,
upt_gateway_time,
upt_add_time,
upt_remove_time
FROM prefetch_process_exe_times
WHERE upt_hostname LIKE '%abc%'
AND pf_process_exe LIKE '%abc%'
AND upt_day = '20200223' |
-- A.2 Generate a list of services from the asset
Query |
SELECT *
FROM services
WHERE user_account LIKE '%LocalSystem%' |
Related Articles
Windows Disk Usage query
The article includes query to fetch the Windows Disk usage: Query SELCET DISTINCT upt_hostname, upt_time, device_id, size, free_space, size - free_space ...
Top resource consuming queries for an asset
Top 10 wall_time consuming queries SQL Query SELECT name, query, wall_time, user_time, system_time, executions FROM osquery_schedule WHERE upt_asset_id = '<asset_id>' AND upt_day = <upt_day> ORDER BY wall_time DESC LIMIT 10; Top 10 Overall CPU time ...
Find Realtime queries executed in the last 24 hours
This article includes the query to find all the real time queries that were executed in the last 24 hours Query WITH queries AS (SELECT Json_extract_scalar(logs.api_body, '$.query') AS query, ...
Query for CVE-2020-1350 Vulnerability for Windows Domain Name Systems
Microsoft released an update on July 14 2020 for CVE-2020-1350 , a Critical Remote Code Execution (RCE) vulnerability in Windows DNS Server that is classified as a ‘wormable’ vulnerability and has a CVSS base score of 10.0. This issue results from a ...
Tracing command execution back to a user across an ssh session
Some IT shops have sysadmins logging into a Jumpbox or Bastion host (with their individual id’s) then logging into production servers as root and running commands. In such a case it can be difficult to trace command execution back to the sysadmin who ...