In this section

0.4 The Rule Languages: KQL, SPL and Sigma

Module 0

Introduction

The course writes every rule in at least two languages. KQL is what Microsoft Sentinel and Defender XDR run; SPL is what Splunk runs; Sigma is a vendor-neutral format that describes a rule once and converts into either. A detection engineer who works across estates reads all three, and needs to know where they agree, where they differ, and what is lost moving a rule from one to another.

Three rule languages and how they meet Sigma rule logsource + detection sigma-cli backend + pipeline KQL Sentinel, Defender XDR SPL Splunk Written directly KQL or SPL by hand KQL and SPL are what the platforms run. Sigma is a way to write a rule once and generate them, through a pipeline that has to match the estate.

This lesson writes one rule twice, a password spray against the VPN in KQL and SPL against the same records, and shows that both return the same rows. Then it writes a second rule in Sigma, converts it with the same tool Module 7 uses, and runs each conversion against the month, where the results depend less on the rule than on the field names and sources each target expects.

1

Reading KQL

a pipeline of tables

A KQL query starts from a table and passes it through a sequence of operators, each separated by a pipe and each returning a new table. The rule for the VPN spray of 10 March, stage by stage:

The KQL rule, one stage at a time let Fails = Syslog start from one table | where Computer == "NE-FGT-VPN01" and ... has "act=ssl-login-fail" keep only failed VPN logins | project TimeGenerated, User = extract(...), Src = extract(...); pull two fields out of the text Fails | summarize Accounts = dcount(User) ... by Src, bin(TimeGenerated, 1h) one row per address per hour | where Accounts >= 3 the detection condition Each line takes a table and returns a table. Reading a KQL rule means reading what each stage hands to the next.

The whole rule, as it runs against the month in the page's engine, returns one row for each address and hour that crossed the line:

let Fails = Syslog
| where Computer == "NE-FGT-VPN01"
    and SyslogMessage startswith "CEF:0|Fortinet|Fortigate|"
    and SyslogMessage has "act=ssl-login-fail"
| project TimeGenerated,
    User = extract(@"(?:^|\s)duser=(\S+)", 1, SyslogMessage),
    Src = extract(@"(?:^|\s)FTNTFGTremip=(\S+)", 1, SyslogMessage);
Fails
| summarize Accounts = dcount(User), Failures = count()
    by Src, Hour = bin(TimeGenerated, 1h)
| where Accounts >= 3

Read that way, a rule is a set of decisions. The first two stages decide what evidence counts: records from the VPN appliance, and only failed logins. The third decides which fields matter and pulls them out.

The fourth decides the unit of judgment: one row per source address per hour, with the number of distinct accounts it tried. The last decides the condition: three or more accounts. Every rule in the course can be read the same way, and most mistakes in a rule sit in one specific stage.

The let statement at the top names an intermediate table, Fails, so the rule can use it later. Longer rules use several lets to keep each step readable, and Module 6 relies on them for joins, where the same intermediate table is read twice.

The operators in this rule, where, project, extend, summarize and bin, are most of what detection queries need; Module 6 adds parsing, joins, time series and the scheduled-rule settings that change what a query sees.

KQL is also strict in ways that matter. Column names are case-sensitive, == compares case-sensitively while =~ does not, and has matches whole terms while contains matches any substring. Lesson 0.7's certutil rule turned on the last of those, and lesson 0.10's AI draft failed on the first.

The page's engine runs every KQL block in this course against the month, so each query can be changed and rerun in place. That is the fastest way to learn the language: change one stage, rerun, and read what the next stage now receives. The threshold in the last line is a good first experiment, and lesson 0.1 showed what moving a threshold does and does not change.

2

Getting Fields Out of Text

what a raw CEF record holds

The VPN appliance does not send columns. It sends CEF over syslog, and Syslog stores each message as one string:

One failed VPN login, as the firewall sends it CEF:0|Fortinet|Fortigate|v7.4.4| the header: format, vendor, product and version 39426|event:vpn ssl-login-fail|7| event ID, name and severity act=ssl-login-fail what happened: the field the rule filters on FTNTFGTremip=198.51.100.76 the client's address, under the vendor's own key duser=r.scott the account it tried To Syslog this is one string. Every field a rule needs is a key inside it, and both languages have to pull the keys out.

CEF has a fixed header of seven fields separated by pipes, then an extension of key-value pairs. The keys that matter here are vendor-specific: the client address is under FTNTFGTremip, Fortinet's own key, not the standard src field CEF defines.

That is why the rule extracts with a regular expression that anchors on the key name and a space or the start of the string, so that a key ending in remip from some other field cannot match by accident.

Extraction is where most text-based rules break, as lesson 0.3 showed when a collector upgrade dropped a field. A rule that extracts its own fields has to be tested against the real messages its source sends, because a vendor can rename a key, reorder pairs, or add one with a similar name between firmware versions.

Module 4 moves this work into a shared parser so that every rule reading the VPN uses the same extraction and a change is fixed once.

Run on its own, the extraction shows what the rule will work with, a few of the failures from the spray's busiest hour:

let Fails = Syslog
| where Computer == "NE-FGT-VPN01"
    and SyslogMessage startswith "CEF:0|Fortinet|Fortigate|"
    and SyslogMessage has "act=ssl-login-fail"
| project TimeGenerated,
    User = extract(@"(?:^|\s)duser=(\S+)", 1, SyslogMessage),
    Src = extract(@"(?:^|\s)FTNTFGTremip=(\S+)", 1, SyslogMessage);
Fails
| where TimeGenerated between (datetime(2026-03-10 02:00)
    .. datetime(2026-03-10 03:00))
| take 6

The extraction also decides what the rule can count. Without the account name there is no way to count distinct accounts, and without the client address there is no unit to group by. Getting the two fields out reliably is half the rule.

A useful habit is to run the extraction alone before writing anything after it, as above, and to look for empty values.

An empty account name or address means the pattern missed a message shape, and every row with an empty key is silently grouped together or dropped by the summarize that follows. The rule's count looks plausible either way, so the check has to be made on the extraction itself.

3

The Same Rule in SPL

search first, then pipes

SPL starts with a search: an index, a source type and terms, which Splunk matches against its indexed events. Everything after the first pipe transforms the results, much as KQL does. The same rule against the same records forwarded to Splunk:

index=network sourcetype=syslog host=NE-FGT-VPN01 "act=ssl-login-fail"
| rex field=_raw "duser=(?<user>\S+)"
| rex field=_raw "FTNTFGTremip=(?<src>\S+)"
| bin _time span=1h
| stats dc(user) as accounts count as failures by src _time
| where accounts >= 3

Both return the same 12 rows: 6 addresses, each trying three or more accounts in one hour, during the spray on the morning of 10 March.

The SPL search filters on a quoted string in the raw event, extracts the two fields with rex, buckets time with bin before stats, and counts with dc(). Each step has a KQL counterpart, and the lessons in this course show both forms side by side and assert that their numbers agree, which is how a translation mistake is caught at build rather than in production.

The SPL version also shows the other way Splunk handles fields. Here rex extracts them in the search, but an add-on for the appliance would usually define the extractions once, so that every search sees user and src as fields without a rex line.

Which approach an estate uses changes what a rule has to contain, and a rule copied from another estate may silently depend on an add-on that is not installed.

The structural difference is where the filtering happens. In SPL, the first line is a search that the index answers efficiently, and everything a search term can express should be there rather than in a later where. In KQL, the where clauses near the top play the same role, and the order of operators affects cost the same way. Module 16 measures that cost.

The pairing is also how the course handles estates that run both platforms, which many do during a migration or after an acquisition. A rule that exists in both forms with matching numbers can move with the data.

A rule that exists in one form, or in two forms that drift apart, becomes a reason the migration stalls, and the difference between two forms is usually discovered only when one of them misses an attack the other would have caught.

4

Where the Languages Differ

translation notes for every rule

Most rules translate line by line, and most translation bugs come from a short list of differences:

Where KQL and SPL differ, on this rule and the next

translation notes

Field extraction

KQL: extract() or parse in the query; SPL: rex at search time, or extractions the add-on defines

Matching text

KQL has matches whole terms, == is case-sensitive; SPL search terms are case-insensitive

Time buckets

KQL: bin(TimeGenerated, 1h) inside summarize; SPL: bin _time span=1h before stats

Counting distinct

KQL: dcount(); SPL: dc(), both exact at this scale

Joining

KQL: join and lookup; SPL: usually stats over a shared key, join used sparingly

Operator precedence

SPL search: NOT, then OR, then AND; KQL: and before or, as most languages

Case is the commonest. A SPL search term matches regardless of case; a KQL == does not. Translating FileName == "certutil.exe" into SPL is safe; translating SPL's FileName=certutil.exe into KQL as == is not, because Defender may record CertUtil.exe. The course uses =~ for names in KQL for that reason.

Precedence is the most dangerous, because it produces a query that runs and quietly means something else. In a Splunk search, OR binds more tightly than the implicit AND between terms, so A OR B C means A or B, and also C.

The Sigma conversion in section six relies on exactly that. In KQL, a or b and c means a, or b and c. A rule moved between the two without parentheses can change from catching an attack to catching everything, or nothing.

Joins differ in habit more than syntax. KQL rules join tables freely; SPL rules more often combine sources in one search and group with stats over a shared key, because Splunk's join has limits on the rows it reads.

Lesson 0.3's three-source union showed the same task done each way. Module 6 teaches KQL's join kinds and when to use each; Module 5's specifications name the key a rule joins on before either version is written.

Time is the last difference worth noting here. KQL rules compare TimeGenerated, the time the record was created; Splunk searches use _time, which an add-on sets from the event at index time. For most sources they agree.

For a source with its own timestamp in the message, like CEF, they can differ by the collector's delay, and a rule that buckets by hour can put the same event in different hours on the two platforms. The paired lessons assert agreement partly to catch exactly that.

5

Sigma: One Rule for Many Platforms

describing what to match, not how

Sigma rules describe a detection in YAML, independent of any one SIEM. The certutil download from lesson 0.7, as a Sigma rule:

title: Certutil Downloads a File With the URL Cache Option
id: 7e3c1a52-9b40-4d18-a6f2-0c5e8b9d1f37
status: test
description: Detects certutil.exe run with -urlcache, the option that fetches any
  URL it is given and writes the result to disk.
author: Ridgeline Cyber
date: 2026-03-16
tags:
    - attack.command-and-control
    - attack.t1105
logsource:
    category: process_creation
    product: windows
detection:
    selection_img:
        - Image|endswith: '\certutil.exe'
        - OriginalFileName: 'CertUtil.exe'
    selection_cli:
        CommandLine|contains: '-urlcache'
    condition: all of selection_*
falsepositives:
    - Administrators fetching certificate revocation lists with -urlcache
level: high

The logsource says what kind of event the rule reads: process creation on Windows. It does not name a table or an index, because those differ between products and estates.

The detection block names selections, sets of field conditions, and a condition that combines them: here, the program is certutil by path or by its original file name, and the command line contains -urlcache. Field names follow Sysmon's conventions, Image, OriginalFileName and CommandLine, because Sigma's process-creation fields were modeled on Sysmon.

The selection on OriginalFileName is a small piece of robustness. Windows records the name a binary was compiled with, separately from the name on disk, so an attacker who copies certutil.exe to a new name still matches.

Lesson 0.10 called that keying on a field the attacker cannot easily change. The rest of the file, its ATT&CK tags, false positives and level, is the same header the course's own rule files use.

Sigma's appeal is that one rule serves every platform and every team can share it. The Sigma project publishes thousands of community rules in this format. The cost is that the rule does not say how to find the data in any particular estate, and the next section shows what that costs.

Modifiers are the other piece of Sigma syntax the rule uses. A field followed by a pipe and a modifier changes how its value matches: endswith, contains and startswith for substrings, and others for lists, regular expressions and encodings.

Without a modifier a value must match exactly, in Sigma's case-insensitive comparison. Module 7 works through the modifiers on a rule for access to LSASS, where the choice of modifier decides which records a selection matches.

6

Converting a Rule, and Running the Result

what the pipeline decides

sigma-cli converts a Sigma rule using a backend, which writes the target language, and a pipeline, which maps Sigma's fields and log sources to the target's. The certutil rule, converted with the Defender XDR pipeline for Kusto:

DeviceProcessEvents
| where (FolderPath endswith "\\certutil.exe"
    or ProcessVersionInfoOriginalFileName =~ "CertUtil.exe")
    and ProcessCommandLine contains "-urlcache"

The pipeline chose the table, DeviceProcessEvents, and renamed every field: Image became FolderPath, OriginalFileName became ProcessVersionInfoOriginalFileName, CommandLine became ProcessCommandLine. Run on the month, it catches the download and leaves both look-alikes alone. The same rule, converted with the Splunk Windows pipeline:

Image="*\\certutil.exe" OR OriginalFileName="CertUtil.exe" CommandLine="*-urlcache*"

This conversion keeps Sysmon's field names, because the pipeline assumes Sysmon. Pointed at the estate's Sysmon records it returns nothing, and the reason is not the rule. The laptop that ran the download has no Sysmon at all:

Event
| where Source == "Microsoft-Windows-Sysmon"
| where Computer startswith "ne-taylor-lt"
| summarize SysmonRecords = count()

The download is in Defender's records, which this estate also forwards to Splunk. The converted search, with its field names changed to Defender's, finds it:

index=defender category="AdvancedHunting-DeviceProcessEvents"
    FolderPath="*\\certutil.exe" OR ProcessVersionInfoOriginalFileName="CertUtil.exe"
    ProcessCommandLine="*-urlcache*"
| table _time DeviceName ProcessCommandLine

Put side by side, the three runs of the same Sigma rule make the dependency plain:

One Sigma rule, three places it can run

sigma-cli 3.1.0, run here

Defender XDR, microsoft_xdr

DeviceProcessEvents with FolderPath and ProcessCommandLine: catches the download

Splunk, splunk_windows, on Sysmon

Image and CommandLine, Sysmon's names: no rows, the laptop has no Sysmon

Splunk, on the Defender records

the same search with Defender's field names: catches the download

What decided each result

the pipeline's field names and which source the estate actually has

Every result in that record came from the same Sigma rule. The rule was right each time; what changed was whether the pipeline's field names matched the source, and whether the estate had that source on the machine that mattered.

Converting a Sigma rule is the start of deploying it, not the end: the output has to be run against the estate's own data, and Module 7 builds the course's own pipeline so that conversions land on the fields Northgate actually has.

The first result is also a coverage finding. A rule converted for Sysmon assumes Sysmon is on every machine, and in this estate it is not. Lesson 0.3's point about sources applies to portable rules with extra force, because a community rule written for one estate's telemetry carries that assumption silently into another. Checking which machines each source covers, before trusting a converted rule's silence, is part of deploying it.

7

What a Sigma Rule Does Not Carry

counting, scheduling and the rest of a deployment

The spray rule from the first sections is harder to write in Sigma than the certutil rule, because it counts. A single-event Sigma rule matches one record at a time; counting distinct accounts per address per hour is an aggregation across records.

Sigma added correlation rules for that, which reference other rules and count or sequence their matches, and backends convert them with varying support. Module 7 writes one, SSH guessing followed by sudo, and measures what each conversion keeps.

A Sigma rule also says nothing about how it runs. It has no frequency or lookback, so the schedule decisions from lesson 0.6 are made separately for each platform. It has no entity mapping, no alert grouping, no suppression and no response action. It has no test set; the course adds one in its own header.

Set out against the settings a deployed rule needs, the gap is easy to see:

What a deployed rule needs, and where it lives

Sigma against the rule file

Detection logic

in the Sigma rule

Tables and field names

in the conversion pipeline, per platform

Frequency and lookback

in the rule file's platform settings

Threshold, grouping, suppression

in the rule file's platform settings

Entity mapping

in the rule file's platform settings

Test set and acceptance bar

in the course's rule header and tests

All of that lives in the rule file around the query, which is why the course keeps Sigma rules in the same repository as KQL and SPL rules, with the same header and the same tests.

None of that makes Sigma less useful. It is the right format for sharing detection logic between teams and products, for importing community rules, and for keeping one statement of a rule's intent while its platform forms change. It is the wrong place to look for whether a rule works in a particular estate.

That answer comes from running the converted form on the estate's data, the method the rest of this course uses.

In practice teams use Sigma in two directions. Inbound, community rules are a fast way to cover known techniques, provided each is converted through the estate's own pipeline, tested on its data and tuned for its look-alikes, which is as much work as many rules written from scratch.

Outbound, writing a team's own rules in Sigma as well makes them shareable and protects the logic against a change of platform. Both directions need the rule file and tests around the Sigma, which is the structure the course uses throughout. Module 7 ends with exactly that: five portable rules validated, converted, tested and managed together.

8

What Modules 6 and 7 Build On It

KQL in depth, then portable rules

Module 6 teaches KQL for detection: decoding an encoded PowerShell command, aggregating a spray across many accounts, joining a replayed session to the mailbox it read, a time series for a beacon that never sleeps, and the scheduled-rule frequency, lookback and cost that decide what a query sees in production. Each lesson pairs its KQL with SPL and asserts that they agree.

Module 7 teaches Sigma: a rule for a logon task in a temp folder, selections and modifiers on LSASS access, a correlation rule for SSH guessing then sudo, converting with sigma-cli and pySigma and measuring what conversion loses, and validating and managing a set of five portable rules. Every conversion in the module is run on the corpus, as the conversions above were.

Together they set a standard for every later rule: a KQL form, an SPL form whose numbers match it, and, for detection rules, a Sigma form converted and run. Writing one rule three ways is slower than writing it once. It is also the fastest way to find a rule that only works because of a quirk of one language or one estate.

A difference between the forms is either a translation mistake or something one platform's data says that the other's does not, and both are worth finding before the rule is relied on.

Practice

The exercise below is the spray rule with the wrong unit of judgment. Change what it counts.

Do this Change what the rule counts
  1. Run the starting rule. It groups failures by account and finds nothing.
  2. Look at the failures. Note how many accounts each source address tried.
  3. Group by address. Count distinct accounts per address per hour.
  4. Set the condition. Three or more accounts from one address in an hour.
  5. Check the score. Every address in the spray caught, the saved-password laptop left alone.
What you should end up with: a spray rule in KQL that groups by the right entity, and a note of the SPL line that would do the same.

KQL and SPL are both pipelines over tables of events, and most rules translate between them line by line, with care for case, precedence and time buckets. Text sources need their fields extracted first, in both, and the extraction is tested on its own.

Sigma states a rule once, and its conversions are only as good as the pipeline's field names and the estate's sources, so every conversion is run before it is trusted.

The next lesson joins records into sequences: correlation across sources and over time, and the multi-stage attacks it exists to catch, from a phishing message to encryption on a file server.

Resources