Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 37 ยท Threat Intelligence and Hunting

Hunting with Log Queries

Practice the query patterns hunters use in a SIEM: filter, group, count rare values, time buckets and joins, shown in SPL-style, KQL-style and runnable SQL.

Hunting comes down to asking questions of logs. Different SIEMs speak different query languages (Splunk's SPL, Microsoft's KQL, Elastic's ES|QL and Lucene, Sigma for portable rules), but the thinking is the same: filter the events you care about, extract the fields that matter, aggregate with counts, sort to see the extremes, and pivot to related data.

This lesson teaches those patterns in plain SQL, which runs in your browser against a made-up log table, and shows how each one reads in SPL-style and KQL-style syntax. The SPL and KQL snippets are read-only illustrations: they are written from general knowledge of those languages and not run here, so check the vendor documentation before using them.

The dataset

The next examples use a small authentication and process log. All users, hosts and addresses are invented (addresses come from reserved documentation ranges).

SQL All six patterns on one log table

Press Run (or Ctrl + Enter) to see the result here.

The editor above runs the setup and all six pattern queries in order; each SELECT shows its own result table, in the order of the comments.

Pattern 1: filter

Narrow to the events of interest first. Always include a time range and a data source, because that makes queries fast and keeps results meaningful.

TEXT
-- SPL-style (illustrative)
index=auth result=failure earliest=-24h

-- KQL-style (illustrative)
Auth | where Timestamp > ago(24h) and Result == "failure"

Run the combined example under "The dataset"; it contains this query.

Pattern 2: aggregate and count

Counting turns thousands of rows into a short list you can read. Group by the entity you suspect (an IP, a user, a host) and count.

TEXT
-- SPL-style (illustrative)
index=auth result=failure | stats count by src_ip | sort - count

-- KQL-style (illustrative)
Auth | where Result == "failure" | summarize count() by SrcIp | order by count_ desc

Run the combined example under "The dataset"; it contains this query.

An address that fails five times against two different accounts looks different from a user who mistyped one password. COUNT(DISTINCT user) is the field that separates guessing from forgetfulness.

Pattern 3: rare values (stack counting)

Ascending order of counts shows what is unusual. Hunt campaigns often start here.

TEXT
-- SPL-style (illustrative)
index=proc | rare limit=20 process_name

-- KQL-style (illustrative)
Proc | summarize n=count() by ProcessName | order by n asc | take 20

Run the combined example under "The dataset"; it contains this query.

Pattern 4: time buckets

Group events into intervals to see bursts and off-hours activity.

TEXT
-- SPL-style (illustrative)
index=auth | bin _time span=1h | stats count by _time

-- KQL-style (illustrative)
Auth | summarize count() by bin(Timestamp, 1h)

Run the combined example under "The dataset"; it contains this query.

Hour 02 stands out: failures in the middle of the night from an external address. In a real SIEM you would compare to a baseline of that user or account type.

Pattern 5: sequence (failure then success)

Many attacks are visible as a sequence: repeated failures, then a success from the same address. The next query joins the table to itself to find a success that follows at least three failures within one minute.

Run the combined example under "The dataset"; it contains this query.

The top row is the svc-backup success from 203.0.113.50: four failures in the previous minute. This pattern is the core of the correlation rule you will build in the brute-force detection project.

Pattern 6: pivot and join

Pivoting means taking a value from one finding and using it in a different data source. If one address looks bad in authentication logs, ask what else it did in proxy or VPN logs. In SQL that is a join; in SPL it is a subsearch or lookup; in KQL a join or let.

Run the combined example under "The dataset"; it contains this query.

Try it: write your own query

Practice on a smaller table. Find users who logged in successfully from more than one source address, which is a first step toward spotting shared or stolen accounts.

SQL Users seen from several addresses

Press Run (or Ctrl + Enter) to see the result here.

Change the threshold in HAVING, or add a column listing the addresses with GROUP_CONCAT(DISTINCT src_ip).

Habits of effective hunters

  • Save and name your queries with the hypothesis they test, in a shared repository.
  • Start narrow, then widen: a query over one day on one source returns quickly and teaches you the fields.
  • Check field meaning: src_ip may be a proxy's address, not the user's. Validate with a known event.
  • Mind the time zone and the clock source of the log.
  • Keep result sizes manageable: aggregate rather than scrolling through thousands of rows.
  • Treat leads as leads: a rare process is a reason to look closer, not a verdict.

The Sigma project defines a vendor-neutral rule format that can be converted to SPL, KQL, Elastic queries and others. Learning the patterns above makes any of those easy to read.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does COUNT(DISTINCT user) help when hunting for credential guessing?

    Breadth across accounts is a strong behavioral signal that raw failure counts miss.
  2. 2. What does sorting counts in ascending order show you?

    Rare-value (stack) analysis surfaces what appears on few hosts or users.
  3. 3. What is a pivot?

    Pivoting follows an entity (IP, user, host, hash) across logs to build the full story.
  4. 4. Why include a time range in every query?

    Unbounded searches are slow and bury relevant events in unrelated history.

Summary

  • Hunting queries follow a few patterns: filter, aggregate, rare values, time buckets, sequences and pivots.
  • SQL, SPL and KQL express the same ideas; learn the pattern, then the syntax.
  • Combine counts with distinct counts and time windows to separate attacks from noise.
  • Save queries with their hypothesis, verify field meanings, and treat results as leads.

Next: SOAR and Playbook Design