Hash, store, join: A modern solution to log deduplication with ES|QL LOOKUP JOIN

In the world of cybersecurity and observability, leadership faces a persistent dilemma: The nonnegotiable mandate for complete visibility clashes with the stark reality of finite budgets. This conflict is nowhere more apparent than with PowerShell logging. For any modern enterprise with a significant Windows footprint, PowerShell is the engine of administration and automation. Its power and ubiquity, however, make it a primary target for adversaries who exploit it for fileless malware execution, lateral movement, and credential theft.   

To counter this threat, security frameworks mandate comprehensive logging, specifically PowerShell script block logging (Event ID 4104). These logs are essential for threat hunting as they capture the exact deobfuscated code an attacker runs. However, this security mandate can create a data management crisis. Enabling full-script block logging generates a prohibitively large number of events, potentially creating terabytes of data that lead to significant storage and indexing costs.

This creates a direct conflict between security requirements and budget constraints. This blog introduces a more intelligent strategy, representing a paradigm shift in how to approach log data. What if it were possible to retain every piece of forensic evidence without paying to store it millions of times? The answer lies in treating logs less like a verbose diary and more like an efficient, searchable note — a strategy powered by the Elastic Stack and the revolutionary Elasticsearch Query Language (ES|QL) LOOKUP JOIN command.

The core principle of this solution is intelligent data deduplication. In any large enterprise, the same set of PowerShell scripts for health checks, application monitoring, and administrative tasks is executed millions of times a day. The traditional approach captures the full, multikilobyte text of these scripts with every single execution, leading to massive data redundancy.  

Consider your daily commute. You don't need a new, detailed map for every trip; you just need a note saying: took the usual route. Our logging strategy applies this same logic. Instead of storing the full "map" (the script text) every time, we store it once and then simply log a lightweight reference — a hash — for each execution.

This architecture is built on three pillars within the Elastic Stack:

1. Event cloning (Logstash): As a versatile server-side data processing pipeline, Logstash is ideal for complex routing. We will use its clone filter to create an in-flight duplicate of every PowerShell script block log event.  

2. Deduplication via hashing (ingest pipeline): An Elasticsearch ingest pipeline will process both the original and cloned events. It generates a unique hash of the script text, which serves as the key for deduplication. The pipeline strips the bulky script text from the original event, transforming the clone into a minimal "lookup" document that contains only the script text, relevant metadata for security detection rules, and its hash. The "lookup" document is stored in a single-shard lookup index, where duplicates are automatically overwritten. 

3. Enrichment on demand (ES|QL): ES|QL is a powerful piped query engine that supports native joins. At query time, an analyst can use the LOOKUP JOIN command to seamlessly reunite the lean event metadata like timestamp, host, and user with the full script text from the lookup index, providing complete context on demand.

This approach represents a strategic shift from the traditional "denormalize at ingest" model to a more flexible and cost-efficient "enrich at query time" paradigm, showcasing the power of the new ES|QL query engine. 

Before beginning, ensure your environment meets the following requirements:

• An Elastic Stack version 8.18/9.0 and newer: This pattern uses the LOOKUP JOIN command in ES|QL, which is now generally available in 8.19/9.1. This gives you access to powerful, new join capabilities. Lookup indices enable this powerful query-time operation and must be single-shard only.

• PowerShell logging enabled: You must have PowerShell script block logging (Event ID 4104) enabled in your Windows environment. This is typically configured via Group Policy under Administrative Templates > Windows Components > Windows PowerShell > Turn on PowerShell Script Block Logging. View the Microsoft documentation for additional reference.

• Elastic Windows integration: This solution is optimized for use with the official Elastic Windows integration. While the technique can be adapted to older integration versions or Winlogbeat, using a recent version of the Elastic Windows integration is recommended for optimal results. This guide will use the powershell.file.script_block_hash field to set the document_id for deduplication.

• Logstash: This solution will use Logstash to duplicate the relevant event before it is optimized. Follow the documentation to set it up if you don’t have this deployed. 

• Understand your data: This solution focuses on PowerShell script blocks that are not segmented due to the size limit of Event Tracing for Windows (ETW) events. Review your data and data access pattern over a meaningful timeframe to understand the potential benefit in your environment and plan for the volume accordingly.
  
  Use this sample Kibana Query Language (KQL) search to understand the volume of target data in your environment: “event.module: "windows" and event.dataset: "windows.powershell_operational" and event.code: 4104 and powershell.total: 1”.

The first step is to create the templates for our lookup index. This includes a component template with minimal mapping and an index template that sets the crucial index.mode to lookup. Using an alias and an index lifecycle management (ILM) policy from the start will automate size management later.

Set up the component template with minimal mapping (see screenshot below) using the following request: PUT _component_template/logs-windows.powershell_operational_lookup@package with the contents from this artifact in the GitHub repository or the code block below.

Set up the index template that references the component template above, and ensure the index.mode is set to lookup using the following request: PUT _index_template/logs-windows.powershell_operational_lookup with the contents from this artifact in the GitHub repository or the code block below.

Set up the ILM referenced in the component template above using the following request: PUT _ilm/policy/logs-powershell_script_block with the contents from this artifact in the GitHub repository or the code block below.

Next, intercept PowerShell script block logs (Event ID 4104) in your Logstash pipeline, and create a duplicate using the clone filter.

In this filter block, we target only the relevant PowerShell script block events that haven’t been segmented. The clone filter creates a copy and adds the tag powershell_scriptblock_reduction to it. The original event is flagged with fields.duplicated: true for downstream processing. We also update the data stream namespace to route it to our new data stream (optional).

With events cloned and tagged, the Logstash output configuration below routes them to different destinations. The original event goes to a standard data stream while the clone is directed to the dedicated lookup index.

This configuration uses a simple conditional:

• If the powershell_scriptblock_reduction tag is present, the event is a clone destined for our lookup index. We explicitly name this index (logs-windows.powershell_operational_lookup-default) and use the index action (lookup indices are not data streams and are single-shard only).

• Otherwise, it's the original event, which is sent to its data stream as usual (in this case, it’s logs-windows.powershell_operational-default_reduced).

Based on Elastic integration’s ingest processing flow, both events are processed by a custom ingest pipeline that uses conditional logic to apply different processors based on the flags added in Logstash. 

The following table breaks down the processing paths for clarity.

This method of setting the _id to the script hash is architecturally superior and far more performant at scale than the alternative (running a "search-then-insert" query for every single incoming log event). While cloning events in Logstash and processing them twice in the ingest pipeline adds some overhead, it is a deliberate trade-off for the storage reduction.

Set up the ingest pipeline using the following request: PUT _ingest/pipeline/logs-windows.powershell_operational@custom with the contents from this artifact in the GitHub repository or the code block below.

This will result in two documents: the minimal lookup document (on the left) and the optimized event document (on the right).

After implementation, your primary data stream contains lean events, and the lookup index holds a unique set of nonsegmented PowerShell scripts. The final step is bringing this context together during analysis. This is where ES|QL LOOKUP JOIN shines.

An analyst can now run a simple piped query to investigate PowerShell activity, seamlessly enriching the lean events with the full script text from the lookup index.

Let's break down what we will do with the query:

1. FROM logs-windows.powershell_operational-default_reduced*: We start by querying our primary lean event data.

2. LOOKUP JOIN... ON powershell.file.script_block_hash: This is the key operation. For each event, it looks for a document in our lookup index where the _id (which we set to the hash) matches the event's hash — powershell.file.script_block_hash.

3. When a match is found, all fields from the lookup document — most importantly, powershell.file.script_block_text — are added to the results.

4. The duplicated metadata fields from the data stream are renamed to prevent them from being overwritten and renamed back to their original names.

Example query result below:

This capability is invaluable for both security analysts and site reliability engineers (SREs). An SRE troubleshooting a system can see the exact script that was executed during an incident. A security analyst investigating a detection alert can instantly retrieve the full, deobfuscated malicious script for forensic analysis just as they would have before but now with a much more cost-effective data store. 

A particularly powerful aspect of this design is the naming convention of the lookup index: logs-windows.powershell_operational_lookup-default. Elastic Security's prebuilt detection rules are designed to run against specific index patterns, such as logs-windows.powershell*. Because our lookup index name matches this pattern, the detection rules will automatically scan it. This means that rules looking for suspicious content in PowerShell scripts will continue to function perfectly, analyzing the complete, deduplicated set of script bodies. This clever design choice ensures that we achieve massive data reduction without creating a blind spot for our automated security analytics.

A common concern with data reduction is the risk of creating blind spots. This architecture cleverly avoids that risk. 

This design has a key implication for the analyst's workflow. The timestamp on a document in the lookup index reflects the last time that the script was seen. If a detection rule alerts on malicious content in the lookup index, the analyst has the “what” (the script text). Their next step is to use the powershell.file.script_block_hash from the alert to pivot to the primary ...-default_reduced data stream. There, they will find every single execution of that script with the correct original timestamp, host, and user context, enabling a complete forensic investigation. This two-step process — detecting unique content and investigating across all executions — is incredibly powerful.

The release and timing of any features or functionality described in this post remain at Elastic's sole discretion. Any features or functionality not currently available may not be delivered on time or at all.