How to Build a Firewall Log Pipeline With NXLog, SQLite, and Cursor

Build a local firewall log pipeline on Windows with NXLog and SQLite, then let Cursor query it read-only — no SIEM required. You did everything the docs said. Enabled remote logging on the pfSense box, pointed it at your Windows machine, opened UDP 514. Nothing arrived. The Netgate forum has collected threads about this exact silence for over a decade — logs generated but never sent, syslogd quietly dead after a reboot. Somewhere between the firewall and your disk, the event vanished — and nothing in the chain will tell you where. The standard fix is a SIEM, which trades one problem for a bigger one: now you own Elasticsearch. This guide takes a different path. You open Cursor, answer five questions, and approve commands while an AI agent builds the firewall log pipeline. By the end you’ll have one verified firewall event on disk, searchable history in a local database, and an AI analyst that reads your logs — but can’t touch your firewall. Total footprint: one Windows service, one folder, one SQLite file. The dashed line is the security model: look, don't touch. The Firewall Log Pipeline You’re Building pfSense is the example throughout, but any syslog-capable firewall works — OPNsense, UniFi, SonicWall, a managed switch. NXLog Community Edition receives on port 514 and writes JSON spool files. A scheduled task loads them into SQLite. The agent queries it read-only and reports what it finds. That’s the marketing. Underneath, Cursor is an agent that reads files, writes files, and runs terminal commands with your approval. A sysadmin’s world is files: configs, logs, scripts, databases. You won’t write a line of code — you’ll use two controls, the chat box and the approve button. The agent writes the NXLog config, the SQL schema, and the ingest script; you read and click yes. Prerequisites Step 1: Five Answers Before Anything Runs Create a folder — C:\fw-pipeline — and open it in Cursor. Create one file, answers.yaml: Everything the agent generates derives from these five lines. Vendor and version decide the exact clicks it gives you. The firewall’s IP becomes the device key in the database. data_stays_local: true means analysis runs against the local database — raw logs never get pasted wholesale into a prompt.The details this post skips live in two companion files — the deployment reference and the troubleshooting FAQ. The agent downloads them in the next step; the links are there if you want a look first. Pixel-art clipboard of five answers feeding a machine labeled building agent config, with a cloth-covered object on the conveyor The covered box is the point: nothing gets built until those five lines are filled. Step 2: Guardrails Before the Agent Fifty-three percent of sysadmins wouldn’t let AI touch production unsupervised. Correct instinct. Don’t put the rules in the prompt — put them where the agent can’t ignore them. Create .cursor/rules/pipeline.md: - Show every terminal command and wait for approval. No auto-run. Query fwlogs.db with the read-only "analyst" account only. Never connect to the firewall. Its config is the human's job. If any value in answers.yaml is blank, stop and ask. Never guess. Append every database query you run to agent-audit.log, with timestamp. Cursor loads these rules into every session in this folder. The agent proposes; you approve. It’s the same trust model you’d apply to a new junior admin — except this one never gets bored and reads every log line. Pixel-art agent holding proposed changes while a person points at Approve on a pending-commands screen, a red dashed line before a firewall and a locked database Nothing on that clipboard is a fact until it crosses the dashed line. Step 3: Let the Agent Build the Collector Now the first real prompt — paste it into the chat: Download both companion files from https://gist.github.com/matbanik/18dadee60389913b982493c8cbbe99ad into this folder and read them, then read answers.yaml. Install NXLog Community Edition as a service, configure it to receive syslog on the chosen transport and write JSON spool files to C:\logdata\spool with filenames like fw-{timestamp}.json, and open the Windows Firewall port — bound to the Private and Domain profiles, not Public. Show me each command before running it. You’ll approve an MSI install, a generated nxlog.conf, and one New-NetFirewallRule. Check two details in the generated config: parse_syslog() on the input, so both RFC 3164 and RFC 5424 land cleanly, and a raised SockBufSize, because the default Windows UDP buffer is tiny and drops bursts. Verify: Get-Service nxlog # Status: Running Step 4: Point the Firewall at It The one manual step. The agent reads answers.yaml and hands you the exact clicks for your platform, but you type them — the firewall stays outside its reach. On pfSense: Status → System Logs → Settings → Remote Logging — enable, collector IP, port 514, tick the categories you want. Other firewalls, same recipe: destination = collector IP, port 514, RFC 5424 if offered. One rule everyone forgets: if the collector sits on a different segment, the firewall needs an egress rule allowing itself to reach port 514. A firewall will not log its own dropped syslog. Step 5: The First Event — or Where It Died Watch the spool folder. If a fw-*.json file appears and grows, the hard part is over. If nothing appears — this is where every other guide shrugs. Ask the agent to walk the ladder: The agent tests most rungs itself — sends a synthetic syslog packet, checks the listener, reads NXLog’s own log — and tells you which hop dropped your event. The usual Windows 11 culprit: Wi-Fi joined as Public, the rule bound to Private.Step 6: Make It Durable One more prompt: Create fwlogs.db with an events table keyed by device IP and receive time, an ingest path and a read-only analyst access pattern, and a scheduled task that loads closed spool files every five minutes, checks the sqlite3 exit code before archiving each file, and prunes anything older than 90 days. Spool-then-ingest isn’t a compromise — NXLog Community Edition can’t write to a database directly on Windows anyway, and the spool files double as replayable raw evidence. The exit-code check matters: archive a file only after SQLite confirms the load, or a transient lock silently eats an hour of logs. Pixel-art document flowing to a cable spool, then a striped barricade, a shielded cylinder, a green check, and a crate with a keyhole The barricade is the exit-code check: the crate closes only after SQLite confirms the load. Step 7: Ask Your Logs Questions The payoff, deliberately last — analysis is only as good as the pipeline under it. Try: Using the analyst account, summarize the last 24 hours: repeated auth failures, deny spikes, source IPs never seen before, config changes outside business hours. For each finding, list the supporting event IDs and one plausible benign explanation. That last clause does real work. An agent forced to argue against its own findings alerts you to the VPN brute-force attempt, not to your TV checking for firmware at 3 a.m. Every finding arrives with evidence you can verify — because the agent’s account can’t do anything but read. Pixel-art person with a magnifying glass at a monitor listing finding types with event IDs, and a notepad of plausible benign explanations The notepad is the last clause of the prompt: every finding has to survive a boring explanation. Pitfalls That Will Waste Your Time The firewall rule is bound to the Public profile. Windows 11 on Wi-Fi defaults to Public and silently drops inbound 514. Set the network to Private, or bind the rule to every profile you use. The collector went to sleep. UDP has no retry — a sleeping Win11 box loses every event until it wakes. powercfg /change standby-timeout-ac 0. Logs arrive, but not the ones you need. “Syslog works” and “security events forwarded” are separate switches on most firewalls. A missing category has its own filter. EDR quarantined NXLog. A fresh binary opening a listening port looks like malware. Add a path exclusion before installing, not after. The agent wants to fix the firewall for you. Don’t extend its reach because step 5 went well. Read-only with proposals is the safe standing state. Where This Leaves You Your firewall was always talking. Now there’s proof on disk, history you can query, and an analyst on call that reads everything and touches nothing. The next time something looks wrong on the network, you won’t be reading forum threads — you’ll be asking your own logs. Resources Remote Logging with Syslog — pfSense Documentation Syslog generating logfiles, not sending to remote server — Netgate Forum syslogd can terminate when the log server refuses connections — pfSense bug #16362 NXLog Community Edition Reference Cursor documentation The Syslog Protocol (RFC 5424) The BSD syslog Protocol (RFC 3164) Windows Firewall Overview What are people collecting syslog in? — r/networking 53% of Sysadmins Won’t Trust AI Alone With Your Servers — Kiteworks SQLite Downloads