Ir al contenido

Advanced Query Language and Threat Hunting with psort.py

SQL-like filtering syntax with psort.py, logical operators, regular expressions, and isolation of indicators of compromise in forensic investigations.

0views

Once the fundamentals of psort.py (definition of output formats, use of the -w write parameter, and local timezone definition) are learned, we already know how to extract and delimit the timeline by dates.

However, within this delimited time interval, we must still be able to quickly find suspicious attack indicators and malware presence data. Advanced content filtering is executed via a primary parameter:

  • -filter <CONSULTA> or --filter <CONSULTA>

This parameter uses a query language native to the Plaso platform (with a syntax very similar to SQL statements), allowing searches based on any attribute associated with an event.


To filter successfully, you must know the data fields processed by the .plaso file's internal SQLite database:

  • ✔ message: The textual description or content of the event (e.g., Execution of: cmd.exe, URL: ...). This is the primary search target for locating domains and executables.
  • ✔ parser: The analyzer module responsible for generating the log (e.g., winevt, prefetch, chrome_history).
  • ✔ data_type: The specific type of data structure (e.g., windows:prefetch:execution, fs:stat, chrome:history:visit). It is a very granular field with high analytical value.
  • ✔ filename: Physical file path where the evidence resides (e.g., /Windows/Prefetch/CMD.EXE.pf).
  • ✔ source_short: Compact classification of the evidence type (e.g., REG for log, FILE for file system, WEBHIST for web).
  • ✔ timestamp_desc: Detail of the timestamp type (e.g., File Creation Time, Last Access Time).

Syntax Quotation Marks

The entire string accompanying the --filter parameter must be written enclosed in double quotes ("). Text variables contained within must be defined using single quotes (').

Searches for an exact text match, respecting case sensitivity:

Ventana de terminal
# Filtrar para recopilar solo eventos creados por el parser Prefetch
--filter "parser == 'prefetch'"

Excludes certain values or types from the query to eliminate repetitive noise:

Ventana de terminal
# Omitir todos los registros genéricos del sistema de archivos (MFT)
--filter "data_type != 'fs:stat'"

The most powerful operator in the forensic suite. Performs substring-type text searches and is case-insensitive on the indicated attribute:

Ventana de terminal
# Localizar cualquier evento que contenga o mencione el binario powershell
--filter "message CONTAINS 'powershell.exe'"
# Búsqueda de dominios maliciosos detectados en la red
--filter "message CONTAINS 'evil-domain.com'"

Allows designing searches with complex character patterns (regex):

Ventana de terminal
# Identificar de forma automática cualquier dirección IP de tipo IPv4 contenida en el campo message
--filter "message =~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'"

We can combine different search directives to narrow down specific indicators of compromise within large volumes of logs:

Case Study A: Forensic Hunting for Malware Execution

Sección titulada «Casuística A: Caza Forense de Ejecución de Malware»
  • Case Objective: We want to quickly locate the trace of the execution of a suspicious software called mimikatz.exe in the execution cache.
Ventana de terminal
--filter "message CONTAINS 'mimikatz.exe' AND (parser == 'prefetch' OR data_type == 'windows:appcompatcache:entry')"
  • Case Objective: Exclusively list the connections and downloads carried out with the Chrome and Firefox browsers installed on the victim's system.
Ventana de terminal
--filter "parser == 'chrome_history' OR parser == 'firefox_history' OR parser == 'chrome_downloads' OR parser == 'firefox_downloads'"
  • Case Objective: We want to audit all system entries except for the millions of MFT queries that refer to the file system event file (FILE).
Ventana de terminal
--filter "source_short != 'FILE'"

Combining all the knowledge from the fundamentals manual and advanced analysis, we design the ultimate forensic query:

  • Suspicion: A credential theft attack via PowerShell occurred on March 15, 2026.
  • IOCs (Indicators of Compromise): The attacker used the PowerShell shell and communicated with the dummy IP 172.16.99.100.
  • Time Zone: The server is configured to Europe/Madrid time.
  • Objective: Generate a cleaned CSV list without generic file entries (fs:stat).
Ventana de terminal
psort.py -z "Europe/Madrid" -o l2tcsv -w /opt/forensics/cases/case-001/timeline_triage_export.csv /opt/forensics/cases/case-001/my_timeline.plaso --date_filter "2026-03-15" --filter "(message CONTAINS 'powershell.exe' OR message CONTAINS '172.16.99.100') AND data_type != 'fs:stat'"
  • -z "Europe/Madrid": Automatically adapts all report dates to the incident's local time zone.
  • -o l2tcsv: Perfectly formats the output in CSV table format.
  • -w /opt/.../timeline_triage_export.csv: Saves the resulting analysis file.
  • --date_filter "2026-03-15": (Fundamentals Filtering) Exclusively isolates the date when the incident occurred.
  • --filter "...": (Advanced Filtering) Intersects the search demanding:
    • That the message field contains the PowerShell executable OR the compromised IP address 172.16.99.100.
    • AND that the entire volume of generic file system entries (data_type != 'fs:stat') is excluded.