This article includes a list of queries to conduct forensic analysis on Windows systems.
These are categorized into sections as -
file system activities,
user login/session activities,
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.
From_unixtime(last_execution_time) AS lastExcutionTime,
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
WHERE user_account LIKE '%LocalSystem%'
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 ...
What are the Standard OSQuery Tables?
Question: What are the standard osquery tables? Answer: As of osquery version 4.5.1 the standard osquery schema tables are listed below: account_policy_data acpi_tables ad_config alf alf_exceptions alf_explicit_auths app_schemes apparmor_events ...