6.6 Building an Investigation Query Library

Building an Investigation Query Library

Introduction

Up to this point, you have written queries for individual exercises — one query, one question, one answer. In production, you will investigate the same types of incidents repeatedly. A brute-force investigation this week uses the same query patterns as the one next month. An AiTM investigation follows the same correlation logic every time. Building a personal query library means you do not start from scratch on each investigation — you reach for a tested, proven query and adapt it to the current incident.

This subsection teaches you to structure queries for reuse, parameterize them with let for different investigations, and organize them into functional categories. This is not a KQL syntax lesson — it is a professional practice lesson. The best SOC analysts have libraries of 50-100 queries they have built and refined over years. You start building yours here.

Structuring queries for reuse

A reusable query has three characteristics:

1. Parameterized — time windows, target users, IP addresses, and thresholds are defined in let statements at the top, not hardcoded in the query body
2. Commented — a brief header explaining what the query detects, when to use it, and what the output means
3. Self-contained — the query runs without dependencies on other queries or external data

Example: a reusable brute-force investigation query

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

// BRUTE FORCE INVESTIGATION
// Purpose: Find IPs that failed authentication and then succeeded
// When to use: After a brute-force or password spray alert
// Output: Successful sign-ins from IPs with high failure counts
// Customization: Adjust failThreshold and timeWindow as needed

let timeWindow = 7d;
let failThreshold = 10;
let excludedIPs = dynamic(["10.0.0.1", "192.168.1.1"]);  // Your known proxies

let attackerIPs =
    SigninLogs
    | where TimeGenerated > ago(timeWindow)
    | where ResultType != "0"
    | where IPAddress !in (excludedIPs)
    | summarize FailCount = count(), Users = dcount(UserPrincipalName) by IPAddress
    | where FailCount > failThreshold;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == "0"
| where IPAddress in ((attackerIPs | project IPAddress))
| extend Country = tostring(LocationDetails.countryOrRegion)
| project TimeGenerated, UserPrincipalName, IPAddress, Country, AppDisplayName
| order by TimeGenerated asc

Compare this to the ad-hoc version from subsection 6.1: the parameters are at the top, the exclusion list prevents false positives from known proxies, the comments explain the purpose. When a brute-force alert fires at 3am, you open this query, change failThreshold if needed, and run it. Investigation starts in 10 seconds instead of 5 minutes.

Organizing by investigation type

Group your queries into categories that match how you work:

Where to store your library

In Sentinel: Save queries as favorites (star icon in the query editor). Create a shared folder for your SOC team.

In a Git repository: For version control and sharing. Each query in a separate .kql file with a comment header. This is the approach used by detection engineering teams and covered in Module 15.

In a personal wiki or note system: Markdown files with the query, explanation, sample output, and notes on when to use it. Obsidian, Notion, or a simple folder of .md files works.