AI-Powered Ethical Hacking (GitHub + Agent Skills), Part 7: Test and Tune Your Sigma Rule Locally with SQLite
Series: AI-Powered Ethical Hacking (GitHub + Agent Skills), Part 7. Previous: Part 6, triage lab logs and write a Sigma detection rule. Start: Part 1, AI meets Kali.
This part covers Sigma rule testing and tuning on your own machine. You load the Part 6 sample auth log into SQLite, turn the rule into a query with sigma-cli, compare its hits with grep, add one harmless admin typo, ask a local model for ideas, then fix and hash the rule yourself.
Where we are: Since Part 1 you have used one host only lab and one local Ollama agent. Parts 2 to 5 set up the agent, its recon prompts, triage, retests and evidence folders. Part 6 had it triage a sample SSH log and write a first Sigma rule, and now you find out whether that rule really works.
First, the legal bit
Disclaimer: This post is for education and authorized security work only. Scanning, auditing or accessing a computer, network or account without permission is illegal, including under India’s Information Technology Act, 2000 and similar laws in other countries. Only work with logs from systems you own, or systems where you have clear written permission from the owner before you start.
Apart from installing packages, every step reads or writes files on your own machine. Nothing scans or contacts another host. The log lines are fake samples, using the same documentation address (203.0.113.45) and host only lab address (192.168.56.102) as Part 6. If you later test with real logs, take them only from a VM in a lab you own or are authorized to test, set up like the Kali Linux safe practice lab.
Why test a Sigma rule before you trust it?
In Part 6 the rule passed sigma check. That only proves the rule file is well formed. It says nothing about what the rule catches, what it misses, or how often it cries wolf.
I think of a new rule like a smoke alarm. You press the test button before you go to bed, and you also want to know it won’t scream every time someone makes toast. This part does both: one test against the attack sample, and one against a harmless mistake.
SQLite is a good place for that. It is a single file database, it runs offline, and sigma-cli has a SQLite backend, so the exact query you test is the one the rule produces.
What do you need before you start?
- The Part 6 folder with
sample-auth.logandlab-ssh-failed-logins.yml. The steps assume it is at~/lab-notes/2026-10-26/logs. - sigma-cli 3.1.0 from Part 6, installed with
pipx. - Python 3.10 or newer. The loader script uses only the standard library.
- Local Ollama from Part 2 with the
qwen3:4bmodel. The latest release on GitHub is v0.40.11 (24 Oct 2026). On a fresh Debian based system, Ollama’s install script stops with “This version requires zstd for extraction”, so runsudo apt install zstdfirst if you see that. - A Debian based system such as Kali. The expected outputs in this post were captured on Debian 13, so version numbers on Kali may be newer.
- About 45 minutes. The model step alone can take ten minutes or more on a machine without a GPU.
How do you load the sample log into SQLite?
Step 1: Make a working folder and copy the Part 6 files
P6=~/lab-notes/2026-10-26/logs
WORK=~/lab-notes/2026-10-27/tuning
mkdir -p "$WORK/rules"
cp "$P6/sample-auth.log" "$P6/lab-ssh-failed-logins.yml" "$WORK/"
cd "$WORK"
ls
Expected output:
lab-ssh-failed-logins.yml rules sample-auth.log
Step 2: Install the SQLite tool and the sigma-cli SQLite backend
You installed sigma-cli with pipx in Part 6, so add the SQLite backend into that same pipx environment:
sudo apt install -y sqlite3
sqlite3 --version
python3 -m pipx inject sigma-cli 'pysigma-backend-sqlite==2.0.0'
sigma list targets | grep sqlite
Expected output (the SQLite version is the Debian 13 package on 26 Oct 2026; Kali may show a newer one):
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
installing pysigma-backend-sqlite from spec 'pysigma-backend-sqlite==2.0.0'...
done! ✨ 🌟 ✨
injected package pysigma-backend-sqlite into venv sigma-cli
| sqlite | SQLite backend | No | sqlite |
The last line tells you the conversion target is sqlite. The version pin matters. pySigma-backend-sqlite 2.0.0 is the last release that works with pySigma 1.5.1, which sigma-cli 3.1.0 needs (it asks for pySigma below 2.0). Without the pin, pipx pulls the newest backend, which upgrades pySigma underneath sigma-cli.
sigma-cli also has its own installer, sigma plugin install sqlite, where sqlite is the plugin name. It picks the same 2.0.0 release and works when sigma-cli lives in a plain Python virtual environment, ending with:
Successfully installed plugin 'sqlite'
pySigma version is compatible with sigma-cli
Inside a pipx environment it can fail with No module named pip, because pipx shares one pip instead of putting a copy in each app. That is why the inject line above is the safer choice here.
Step 3: Save the loader and load the sample log
Syslog lines are plain text, so a short script splits each line into a time, user, source address and message, and writes them to a table called logs. That table name and the timestamp column are what the SQLite backend expects by default. Syslog lines have no year, so the script sets 2026.
Save this as load_auth_log.py:
import re, sqlite3, sys
LINE = re.compile(r'^(\w{3}) +(\d+) (\d\d:\d\d:\d\d) (\S+) (\w+)\[\d+\]: (.*)$')
IP = re.compile(r'from (\d+\.\d+\.\d+\.\d+)')
BAD_USER = re.compile(r'[Ii]nvalid user (\S+)')
USER = re.compile(r'(?:password|publickey) for (\S+)')
MONTHS = {'Jan': 1, 'Feb': 2, 'Mar': 3, 'Apr': 4, 'May': 5, 'Jun': 6,
'Jul': 7, 'Aug': 8, 'Sep': 9, 'Oct': 10, 'Nov': 11, 'Dec': 12}
YEAR = 2026 # syslog lines carry no year, so set it here
log_file, db_file = sys.argv[1], sys.argv[2]
db = sqlite3.connect(db_file)
db.execute('CREATE TABLE IF NOT EXISTS logs (timestamp TEXT, host TEXT, '
'process TEXT, user TEXT, src_ip TEXT, message TEXT)')
rows = 0
for line in open(log_file):
m = LINE.match(line.strip())
if not m:
continue
mon, day, clock, host, proc, msg = m.groups()
ts = f"{YEAR}-{MONTHS[mon]:02d}-{int(day):02d} {clock}"
ip = IP.search(msg)
user = BAD_USER.search(msg) or USER.search(msg)
db.execute('INSERT INTO logs VALUES (?, ?, ?, ?, ?, ?)',
(ts, host, proc, user.group(1) if user else None,
ip.group(1) if ip else None, msg))
rows += 1
db.commit()
print(f"Loaded {rows} lines from {log_file} into {db_file}")
Then load the log and look at the table:
python3 load_auth_log.py sample-auth.log auth.db
sqlite3 -box auth.db "SELECT timestamp, user, src_ip FROM logs;"
Expected output (sample data):
Loaded 9 lines from sample-auth.log into auth.db
┌─────────────────────┬─────────┬────────────────┐
│ timestamp │ user │ src_ip │
├─────────────────────┼─────────┼────────────────┤
│ 2026-10-25 09:12:01 │ admin │ 203.0.113.45 │
│ 2026-10-25 09:12:03 │ admin │ 203.0.113.45 │
│ 2026-10-25 09:12:06 │ test │ 203.0.113.45 │
│ 2026-10-25 09:12:08 │ test │ 203.0.113.45 │
│ 2026-10-25 09:12:11 │ root │ 203.0.113.45 │
│ 2026-10-25 09:12:14 │ root │ 203.0.113.45 │
│ 2026-10-25 09:20:40 │ labuser │ 192.168.56.102 │
│ 2026-10-25 09:20:47 │ labuser │ 192.168.56.102 │
│ 2026-10-25 09:31:02 │ labuser │ 192.168.56.102 │
└─────────────────────┴─────────┴────────────────┘
Nine lines in, nine rows out. If the count is lower, a line didn’t match the pattern, so open the log and compare it with the regular expression at the top of the script.
How do you turn the rule into a SQLite query?
Step 4: Try the Part 6 rule as it is
sigma convert -t sqlite --without-pipeline lab-ssh-failed-logins.yml
Expected output:
Parsing Sigma rules
Error: Error while converting: Value-only string expressions (i.e Full Text Search or 'keywords' search) are not supported by the backend.
That error is your first real test result. The Part 6 rule used a keywords search, which means “these words anywhere in the line”. The SQLite backend needs a named field to search in.
Step 5: Point the search at the message field
Open lab-ssh-failed-logins.yml and replace it with this version. Three things change: the keywords block becomes a selection on the message field, the condition follows it, and a name line lets the next rule refer to this one.
title: Repeated SSH login failures in a lab auth log
id: 6b1f0c2e-3d4a-4e8b-9a51-2f7c8d9e0a13
name: lab_ssh_failed_logins
status: experimental
description: Flags failed SSH password and invalid user lines in the auth log of an authorized lab VM.
author: Deepu S
date: 2026-10-27
tags: [attack.credential-access, attack.t1110]
logsource: {product: linux, service: auth}
detection:
selection:
message|contains: ['Failed password for', 'Invalid user']
condition: selection
falsepositives: ['A user mistyping their password on a lab VM']
level: medium
Keep your own id from Part 6 if it differs. Then check it:
sigma check lab-ssh-failed-logins.yml
Expected output, last lines:
=== Summary ===
Found 0 errors, 0 condition errors and 0 issues.
No rule errors found.
No condition errors found.
No validation issues found.
Step 6: Convert it and save the query
sigma convert -t sqlite --without-pipeline lab-ssh-failed-logins.yml -o rule-v1.sql
cat rule-v1.sql
Expected output:
Parsing Sigma rules
SELECT * FROM logs WHERE message LIKE '%Failed password for%' ESCAPE '\' OR message LIKE '%Invalid user%' ESCAPE '\'
Read it once. It is plain SQL, and it says exactly what the rule says: any line whose message contains either phrase.
Does the rule catch what grep caught?
Step 7: Run the query and compare it with the Part 6 count
sqlite3 auth.db < rule-v1.sql | wc -l
sqlite3 -box auth.db "SELECT src_ip, COUNT(*) AS hits FROM ($(cat rule-v1.sql)) GROUP BY src_ip ORDER BY hits DESC;"
grep -E 'Failed password|Invalid user' sample-auth.log | grep -oE 'from [0-9.]+' | sort | uniq -c | sort -rn
Expected output (sample data):
7
┌────────────────┬──────┐
│ src_ip │ hits │
├────────────────┼──────┤
│ 203.0.113.45 │ 6 │
│ 192.168.56.102 │ 1 │
└────────────────┴──────┘
6 from 203.0.113.45
1 from 192.168.56.102
The rule and grep agree: seven lines, six from 203.0.113.45 and one from 192.168.56.102. That is the same ground truth you wrote down in Part 6. If the numbers ever differ, trust grep and find out why before you go on.
What happens when a real admin mistypes a password?
Step 8: Add a harmless sample and run the rule again
This SAMPLE shows an admin on the lab network typing the wrong password twice, then getting in. Nobody logged in anywhere to make it.
cat > benign-auth.log <<'LOG'
Oct 25 10:05:10 lab-web-01 sshd[2301]: Failed password for labadmin from 192.168.56.102 port 40210 ssh2
Oct 25 10:05:16 lab-web-01 sshd[2301]: Failed password for labadmin from 192.168.56.102 port 40210 ssh2
Oct 25 10:05:24 lab-web-01 sshd[2301]: Accepted password for labadmin from 192.168.56.102 port 40210 ssh2
LOG
python3 load_auth_log.py benign-auth.log auth.db
sqlite3 -box auth.db "SELECT src_ip, COUNT(*) AS hits FROM ($(cat rule-v1.sql)) GROUP BY src_ip ORDER BY hits DESC;"
sqlite3 -separator ' | ' auth.db "SELECT timestamp, user, src_ip FROM ($(cat rule-v1.sql)) ORDER BY timestamp;" > v1-hits.txt
cat v1-hits.txt
Expected output (sample data):
Loaded 3 lines from benign-auth.log into auth.db
┌────────────────┬──────┐
│ src_ip │ hits │
├────────────────┼──────┤
│ 203.0.113.45 │ 6 │
│ 192.168.56.102 │ 3 │
└────────────────┴──────┘
2026-10-25 09:12:01 | admin | 203.0.113.45
2026-10-25 09:12:03 | admin | 203.0.113.45
2026-10-25 09:12:06 | test | 203.0.113.45
2026-10-25 09:12:08 | test | 203.0.113.45
2026-10-25 09:12:11 | root | 203.0.113.45
2026-10-25 09:12:14 | root | 203.0.113.45
2026-10-25 09:20:40 | labuser | 192.168.56.102
2026-10-25 10:05:10 | labadmin | 192.168.56.102
2026-10-25 10:05:16 | labadmin | 192.168.56.102
Now the rule flags nine lines, and three of them are your own people. The Part 6 rule fires on every single failure, so each typo becomes an alert. On a real network with many users, that is how alert fatigue starts.
How do you tune the rule with a local model?
Step 9: Ask the model for tuning ideas, not a new rule
Save this as tune-prompt.txt:
You are helping me tune a Sigma rule for SSH login failures on an authorized lab VM.
Do not suggest scanning, attacking or contacting any host. Do not write a new rule.
The RULE block is the current rule. The HITS block lists every line it matched as time | user | source address.
The two labadmin lines at 10:05 are a known admin typing the wrong password twice before logging in. Treat them as false positives.
Suggest at most three changes that keep the 203.0.113.45 burst but drop the admin's two mistakes.
For each change give: Change | Why | Risk of missing a real attack.
Prefer Sigma correlation settings (type, group-by, timespan, condition) over filters on usernames.
Build the input from the prompt, the rule and the hits, then run it:
{ cat tune-prompt.txt; echo; echo 'RULE:'; cat lab-ssh-failed-logins.yml; echo; echo 'HITS:'; cat v1-hits.txt; } > tune-input.txt
ollama run --hidethinking qwen3:4b "$(cat tune-input.txt)" > tune-notes.txt
cat tune-notes.txt
If the reply starts with a <think> block, your model build did not separate its reasoning, so skip down to the line after </think>.
Expected output (a real reply from qwen3:4b, built from Qwen’s official GGUF file, trimmed to the three suggestions; your wording will differ):
### **Change 1: Restrict to the Target IP (203.0.113.45)**
Update the selection to include a filter for the **source IP** of `203.0.113.45`.
### **Change 2: Exclude the `labadmin` User**
Add a filter to **exclude entries where the user is `labadmin`**.
This avoids false positives from the `labadmin` user, who is known to trigger the rule on the target IP.
### **Change 3: Refine Grouping for Accurate Correlation**
Update the `group_by` field to include **source IP and user**.
Now read it the way you read the triage notes in Part 6, line by line against your own data.
- Change 1 hard codes today’s attacker. Tomorrow a different address tries the same thing and the rule stays silent.
- Change 2 hides an account. If someone ever guesses at
labadmin, those failures vanish. It also gets a fact wrong:labadmincame from192.168.56.102, not203.0.113.45. - Change 3 is the useful one. Grouping is the right idea. But grouping by user as well would let an attacker spread guesses across many usernames, and a group means nothing without a count and a time window.
The model also wrote its examples with and and == inside the selection, which is not Sigma. So keep the grouping idea and leave its code behind.
Step 10: Fix the rule by hand with a correlation rule
Your data already gives you the numbers. The attack is six failures from one address in 13 seconds. The admin is two failures in six seconds. The Part 6 typo is one. So “three or more failures from the same source address within one minute” catches the first and lets the other two pass.
Sigma writes that as a correlation rule, a second file that counts matches of the first one. Move the base rule into rules/ and save the new rule next to it:
mv lab-ssh-failed-logins.yml rules/
cat > rules/lab-ssh-brute-force-burst.yml <<'Y'
title: SSH login failure burst from one source in a lab auth log
id: 0c6e2d1a-7b4f-4f0e-8d2a-5e9b1c3a7f42
status: experimental
description: Three or more failed SSH logins from the same source address within one minute on an authorized lab VM.
author: Deepu S
date: 2026-10-27
correlation:
type: event_count
rules: [lab_ssh_failed_logins]
group-by: [src_ip]
timespan: 1m
condition: {gte: 3}
falsepositives: ['A script or user retrying a wrong saved password from one lab address']
level: medium
Y
sigma check rules/
Replace the id with a fresh one from python3 -c 'import uuid; print(uuid.uuid4())' if you like.
Expected output, last lines:
=== Summary ===
Found 0 errors, 0 condition errors and 0 issues.
No rule errors found.
No condition errors found.
No validation issues found.
Check the folder, not the two files one by one. When the correlation file was passed on its own next to the base rule file, sigma-cli stopped with “Rule ‘lab_ssh_failed_logins’ not found in rule collection”. Pointing it at rules/ loads both into one collection, so the rules: reference resolves.
How do you re-test and lock the final rule?
Step 11: Convert the folder and run the burst query
The correlation query is long, so save it to a file and wrap it in a view:
sigma convert -t sqlite --without-pipeline rules/ -o rule-v2.sql
grep -o 'RANGE BETWEEN 60 PRECEDING AND CURRENT ROW' rule-v2.sql
{ printf 'CREATE VIEW burst_alerts AS '; cat rule-v2.sql; printf ';\n'; } | sqlite3 auth.db
cat > burst-summary.sql <<'SQL'
SELECT json_extract(group_keys, '$.src_ip') AS src_ip,
datetime(MIN(window_end), 'unixepoch') AS first_alert,
MAX(metric_value) AS peak_failures
FROM burst_alerts
GROUP BY src_ip;
SQL
sqlite3 -box auth.db < burst-summary.sql
Expected output (sample data):
Parsing Sigma rules
RANGE BETWEEN 60 PRECEDING AND CURRENT ROW
┌──────────────┬─────────────────────┬───────────────┐
│ src_ip │ first_alert │ peak_failures │
├──────────────┼─────────────────────┼───────────────┤
│ 203.0.113.45 │ 2026-10-25 09:12:06 │ 6 │
└──────────────┴─────────────────────┴───────────────┘
One source, flagged at 09:12:06, the moment its third failure landed. The admin and the Part 6 typo are gone. The grep line shows how this backend counts: a sliding 60 second window that looks back from each failure. The Sigma correlation rules specification warns that some backends use fixed time blocks instead, which can split a burst in two, so check this line whenever you move a rule to a new backend.
Step 12: Check the result against a count you made yourself
As in Part 6, don’t take the query’s word for it. Count the failures from each source in each minute with awk:
awk '/Failed password|Invalid user/ { match($0, /from [0-9.]+/); print substr($3, 1, 5), substr($0, RSTART+5, RLENGTH-5) }' sample-auth.log benign-auth.log | sort | uniq -c
Expected output (sample data):
6 09:12 203.0.113.45
1 09:20 192.168.56.102
2 10:05 192.168.56.102
Only one row reaches three. The 192.168.56.102 failures add up to three across the morning, but never inside one minute, which is exactly why the time window matters.
Step 13: Hash the final rules with their test samples
Treat the tuned rule like a finding from Part 5. Keep it with the samples that prove it works, and hash everything together:
sha256sum rules/*.yml sample-auth.log benign-auth.log load_auth_log.py rule-v2.sql tune-prompt.txt > SHA256SUMS.txt
sha256sum -c SHA256SUMS.txt
Expected output:
rules/lab-ssh-brute-force-burst.yml: OK
rules/lab-ssh-failed-logins.yml: OK
sample-auth.log: OK
benign-auth.log: OK
load_auth_log.py: OK
rule-v2.sql: OK
tune-prompt.txt: OK
Next time you change a threshold, run Steps 7 to 12 again and write a new hash list. If an old OK turns into FAILED, something changed that you didn’t record.
What to do next
Your plan for this week:
- Load the Part 6 sample into SQLite and get the same seven hits as
grep. - Add the harmless admin sample and watch the single line rule overreact.
- Ask the model for ideas, then argue with each one before you change anything.
- Write the burst rule by hand, re-test it, and check it against your own
awkcount. - Hash the rules with both samples so every future change starts from a known state.
Three in a minute will not catch a slow guesser who tries once every 30 seconds. A second rule with a longer window, such as ten failures in an hour from one source, covers that. Write a slow sample log first, then the rule, then run the same checks you ran here.
Next up: In Part 8 you go back to the agent itself and lock it down, with an allowlist of lab only tools and targets, a human approval step before every command, a harmless prompt injection test and a log of everything it does.
Leave a comment