SIEM and queries

Why it matters

Logs scattered across hundreds of machines are almost useless during an incident. A SIEM (security information and event management) system collects them in one place, normalises them into common fields, and lets analysts search, correlate and alert. Writing good queries against that data is the core daily skill of a SOC analyst, whether the job uses Splunk, Microsoft Sentinel, Elastic, Wazuh or something else.

What a SIEM does

1. Collects logs from agents, syslog, cloud APIs and appliances.
2. Parses and normalises them, so a failed login from Windows and one from Linux both end up with fields such as user, src_ip, host and outcome.
3. Stores and indexes them so searches over millions of events take seconds.
4. Correlates events with detection rules ("10 failed logins followed by a success from the same IP within 15 minutes") and raises alerts.
5. Supports investigation with dashboards, timelines and saved searches.

Each product has its own query language: SPL in Splunk, KQL in Microsoft Sentinel, ES|QL and KQL in Elastic. Sigma is an open, vendor-neutral format for writing detection rules once and converting them to many SIEMs. The thinking behind every query is the same, and SQL expresses it clearly, so this lesson uses SQL. Once you can write it in SQL, translating to SPL or KQL is mostly syntax.

Free options for a home lab include Wazuh (open source), Security Onion (open source, network-focused), the free tier of Elastic, and Splunk's free licence (check its current data limits on Splunk's site).

Threat hunting vs alerting

• Alerting runs saved rules continuously and wakes someone up when they match.
• Threat hunting is proactive: an analyst starts with a hypothesis ("an attacker may be password spraying our VPN") and searches for evidence, even though no alert fired. A good hunt that finds something often becomes a new alert rule.

The query patterns you will use most

Imagine a normalised table auth_events with columns ts, host, user, src_ip and outcome ('success' or 'failure').

1. Filter: narrow to what matters.

SELECT ts, host, user, src_ip
FROM auth_events
WHERE outcome = 'failure' AND ts >= '2026-10-23 00:00:00';

2. Count and group: turn thousands of rows into a short list of suspects.

SELECT src_ip, COUNT(*) AS failures
FROM auth_events
WHERE outcome = 'failure'
GROUP BY src_ip
ORDER BY failures DESC;

3. Threshold with HAVING: keep only groups above a limit.

SELECT src_ip, COUNT(*) AS failures
FROM auth_events
WHERE outcome = 'failure'
GROUP BY src_ip
HAVING COUNT(*) >= 5;

4. Distinct counts: separate attack shapes. Brute force is many attempts against one user; password spraying is a few attempts against many users, to stay under lockout limits.

SELECT src_ip,
       COUNT(*) AS failures,
       COUNT(DISTINCT user) AS users_tried
FROM auth_events
WHERE outcome = 'failure'
GROUP BY src_ip;

An IP with 40 failures and 1 user is guessing one password; an IP with 40 failures and 38 users is spraying.

5. Correlate: did the noisy IP ever get in? A subquery or join links failures to a later success.

SELECT DISTINCT s.src_ip, s.user, s.ts
FROM auth_events AS s
WHERE s.outcome = 'success'
  AND s.src_ip IN (
    SELECT src_ip FROM auth_events
    WHERE outcome = 'failure'
    GROUP BY src_ip HAVING COUNT(*) >= 5
  );

That last query is the one that turns a noisy event into an incident: a success after many failures from the same source.

Worked example: a hypothesis-driven hunt

Hypothesis: someone is spraying passwords at our SSH servers.

1. Count failures by source IP for the last 24 hours. Two IPs stand out with 30+ failures each.
2. Add COUNT(DISTINCT user). One IP tried 1 user (a misconfigured backup script with an old password); the other tried 26 users.
3. Check the spraying IP for any success. One success for deploy on web02.
4. Pivot: search every event for user = 'deploy' and host = 'web02' after that time to see what happened next.
5. Result: escalate as an incident, and save the query as a rule so it alerts next time.

The same hunt in Splunk SPL looks like this, to show how close the ideas are:

index=auth outcome=failure
| stats count AS failures dc(user) AS users_tried BY src_ip
| where failures >= 5

Common mistakes

• Searching all time. Always set a time window; it is faster and keeps you focused.
• Forgetting normalisation. Admin, admin and CORP\admin may be the same account. Check how your SIEM normalises fields.
• Alerting on single events. One failed login is normal; thresholds and correlation turn noise into signal.
• Not writing down the hypothesis. A hunt without a question becomes aimless scrolling.

Quick recap

• A SIEM collects, normalises, stores, correlates and alerts on logs.
• Hunting starts with a hypothesis; alerting runs saved rules.
• Filter, group and count, threshold with HAVING, count distinct values, and correlate failures with successes.
• The logic carries over to SPL, KQL and Sigma.

Try it

Open the Do tab and hunt through a small authentication log with SQL.