Search and Group File Hash Data Using Variables

Search for specific file hashes across systems using placeholder variables

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result

logscale

MD5HashData=?PutMD5Here FileName=?PutFileNameHere SHA256HashData=?PutSHA256Here
("event_simpleName" = ImageHash OR event_simpleName = ProcessRollup2)
| groupBy([ComputerName, FileName, SHA256HashData])

Introduction

The groupBy() function can be used with placeholder variables to search for specific file hashes across systems, helping security analysts track known malicious files in their environment.

In this example, a query template is used with placeholder variables for hash values and filename, followed by the groupBy() function. This approach allows analysts to quickly search for known malicious files using their hash values and group the results by system, filename, and hash data.

Example incoming data might look like this:

@timestamp	event_simpleName	ComputerName	FileName	MD5HashData	SHA256HashData	FilePath	FileSize
2025-01-15T08:00:00Z	ImageHash	DESKTOP-A1	notepad.exe	5f356a2f870435769147c6981dd6a0d3	e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855	C:\Windows\System32\	245760
2025-01-15T08:01:00Z	ProcessRollup2	DESKTOP-B2	putty.exe	d41d8cd98f00b204e9800998ecf8427e	7b4d6361f3fca7c58a47f54f276197edf6bb698b4dd6d0729f76f271d45735be	C:\Program Files\	1024000
2025-01-15T08:02:00Z	ImageHash	DESKTOP-A1	badfile.exe	aaf4c61ddcc5e8a2dabede0f3b482cd9	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	C:\Users\Admin\Downloads\	506880
2025-01-15T08:03:00Z	ProcessRollup2	DESKTOP-C3	cmd.exe	5f356a2f870435769147c6981dd6a0d3	e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855	C:\Windows\System32\	323584
2025-01-15T08:04:00Z	ImageHash	DESKTOP-B2	badfile.exe	aaf4c61ddcc5e8a2dabede0f3b482cd9	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	C:\Users\User1\Desktop\	506880
2025-01-15T08:05:00Z	ProcessRollup2	DESKTOP-A1	winword.exe	1234567890abcdef1234567890abcdef	a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0u1v2w3x4y5z6a7b8c9d0	C:\Program Files\Microsoft Office\	2048000
2025-01-15T08:06:00Z	ImageHash	DESKTOP-C3	badfile.exe	aaf4c61ddcc5e8a2dabede0f3b482cd9	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	C:\Users\Admin\Desktop\	506880
2025-01-15T08:07:00Z	ProcessRollup2	DESKTOP-B2	excel.exe	0987654321fedcba0987654321fedcba	1a2b3c4d5e6f7g8h9i0j1k2l3m4n5o6p7q8r9s0t1u2v3w4x5y6z7a8b9c0	C:\Program Files\Microsoft Office\	1536000

Step-by-Step

Starting with the source repository events.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
MD5HashData=?PutMD5Here FileName=?PutFileNameHere SHA256HashData=?PutSHA256Here
```
Defines search criteria using placeholder variables that can be replaced with actual values:
- ?PutMD5Here: Insert a specific MD5 hash value (for example, aaf4c61ddcc5e8a2dabede0f3b482cd9).
- ?PutFileNameHere: Insert a specific filename (for example, badfile.exe).
- ?PutSHA256Here: Insert a specific SHA256 hash value (for example, d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f).
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
("event_simpleName" = ImageHash OR event_simpleName = ProcessRollup2)
```
Filters for specific event types that contain file hash information:
- ImageHash: Events generated when DLL or executable images are loaded into memory.
- ProcessRollup2: Events generated when processes are created or terminated.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| groupBy([ComputerName, FileName, SHA256HashData])
```
Groups the matching events by three key fields to show the distribution of the searched file across systems:
- ComputerName: Shows which systems contain the file.
- FileName: Shows if the same file appears under different names.
- SHA256HashData: Confirms the file content matches across systems.
Event Result set.

Summary and Results

The query is used to search for specific known files across all systems using their hash values and group the results to show their distribution in the environment.

This query is useful, for example, to track the spread of known malware, verify the presence of legitimate files, or identify renamed malicious files that maintain the same hash value.

Sample output when searching for a specific malicious file:

ComputerName	FileName	SHA256HashData	event_simpleName	FilePath	MD5HashData	FileSize
DESKTOP-A1	badfile.exe	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	ImageHash	C:\Users\Admin\Downloads\	aaf4c61ddcc5e8a2dabede0f3b482cd9	506880
DESKTOP-B2	badfile.exe	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	ImageHash	C:\Users\User1\Desktop\	aaf4c61ddcc5e8a2dabede0f3b482cd9	506880
DESKTOP-C3	badfile.exe	d14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f	ImageHash	C:\Users\Admin\Desktop\	aaf4c61ddcc5e8a2dabede0f3b482cd9	506880

Note how the suspicious file is present on three different systems and maintains consistent hash values and size across systems.

Also, the file appears in different user directories, suggesting possible user-initiated spread.

Examples Library