Windows Forensic Analysis queries

Windows Forensic Analysis queries

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 ...
    • 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 ...