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.
Query Language and Hunting Techniques
Sección titulada «El Lenguaje de Consultas y Técnicas de Caza»1. Overview
Sección titulada «1. Visión General»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.
2. Main Attributes for Forensic Search
Sección titulada «2. Atributos Principales para Búsqueda Forense»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.,REGfor log,FILEfor file system,WEBHISTfor web). - ✔
timestamp_desc: Detail of the timestamp type (e.g.,File Creation Time,Last Access Time).
3. Query Syntax and Supported Operators
Sección titulada «3. Sintaxis de Consultas y Operadores Soportados»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 (').
A. Strict Equality (==)
Sección titulada «A. Igualdad Estricta (==)»Searches for an exact text match, respecting case sensitivity:
# Filtrar para recopilar solo eventos creados por el parser Prefetch--filter "parser == 'prefetch'"B. Inequality or Exclusion (!=)
Sección titulada «B. Desigualdad o Exclusión (!=)»Excludes certain values or types from the query to eliminate repetitive noise:
# Omitir todos los registros genéricos del sistema de archivos (MFT)--filter "data_type != 'fs:stat'"C. Content Match (CONTAINS)
Sección titulada «C. Coincidencia de Contenido (CONTAINS)»The most powerful operator in the forensic suite. Performs substring-type text searches and is case-insensitive on the indicated attribute:
# 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'"D. Regular Expressions (=~)
Sección titulada «D. Expresiones Regulares (=~)»Allows designing searches with complex character patterns (regex):
# 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}'"4. Combined Logical Operators (AND, OR, NOT)
Sección titulada «4. Operadores Lógicos Combinados (AND, OR, NOT)»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.exein the execution cache.
--filter "message CONTAINS 'mimikatz.exe' AND (parser == 'prefetch' OR data_type == 'windows:appcompatcache:entry')"Case Study B: Isolating Browsing History and Downloads
Sección titulada «Casuística B: Aislar el Historial de Navegación y Descargas»- Case Objective: Exclusively list the connections and downloads carried out with the Chrome and Firefox browsers installed on the victim's system.
--filter "parser == 'chrome_history' OR parser == 'firefox_history' OR parser == 'chrome_downloads' OR parser == 'firefox_downloads'"Case Study C: Excluding Disk Background Noise (MFT)
Sección titulada «Casuística C: Exclusión del Ruido de Fondo del Disco (MFT)»- 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).
--filter "source_short != 'FILE'"5. The Complete Workflow: The Master Command
Sección titulada «5. El Flujo de Trabajo Completo: La Comando Maestra»Combining all the knowledge from the fundamentals manual and advanced analysis, we design the ultimate forensic query:
Incident Response Scenario:
Sección titulada «Escenario de Respuesta a Incidentes:»- 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).
Execution of the Blue Team Master Command:
Sección titulada «Ejecución de la Comando Maestra del Blue Team:»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'"Command Architecture Breakdown:
Sección titulada «Desglose de la Arquitectura de la Comando:»-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
messagefield contains the PowerShell executable OR the compromised IP address172.16.99.100. - AND that the entire volume of generic file system entries (
data_type != 'fs:stat') is excluded.
- That the

