NFQL Cookbook
Generated from internal/web/cookbook/cookbook.yaml. Do not edit by hand — run make docs.
Investigation
Sessions touching a host (15 min)
Intent. Every conversation involving a given host in the recent window.
When to use. An external feed flags an IP, or you spotted one in another tool — confirm whether you saw it.
FROM sessions
| LAST 900
| WHERE ip == "host:srv-db-01"
| SORT opened_at DESC
| LIMIT 100
Cartography refs resolve symbolic names to IP sets at compile time, so the query survives a host renumbering. Forms: host:NAME (every interface IP), group:NAME (every member host), network:NAME (the CIDR).
The virtual ip column matches either side of the conversation (server_ip OR client_ip) — direction does not matter.
Variants:
By raw IP literal (if the host is not in cartography)
FROM sessions | LAST 900 | WHERE ip == "10.0.0.42" | SORT opened_at DESCAnywhere inside a CIDR (WITHIN, IPv6-friendly)
FROM sessions | LAST 900 | WHERE ip WITHIN "10.0.0.0/24" | SORT opened_at DESCEvery host in a cartography group
FROM sessions | LAST 3600 | WHERE ip == "group:databases" | SORT opened_at DESCOne of several hosts (set membership)
FROM sessions | LAST 3600 | WHERE ip IN ("host:srv-db-01", "host:srv-db-02") | SORT opened_at DESC
Outbound sessions toward the public internet (1h)
Intent. Internal hosts that reached external services in the last hour.
When to use. Spot exfiltration candidates or unexpected outbound traffic.
FROM sessions
| LAST 3600
| WHERE server_ip WITHIN "internet"
| KEEP client_ip, server_ip, server_port, client_to_server_bytes, server_to_client_bytes
| SORT client_to_server_bytes DESC
| LIMIT 100
internet is a reserved token: every routable address, IPv4 and IPv6 (everything NOT in RFC1918/ULA, loopback, link-local, CGNAT, multicast, reserved). Per-family companions: internet4, internet6, internal, internal4, internal6, any, any4, any6.
WITHIN reads it correctly — the token names a SET of addresses and you are asking whether the server sits in it. == accepts the same tokens.
client_ip is the internal requester and client_to_server_bytes the upload direction — the true exfiltration signal (a big download does not inflate it).
Variants:
From one specific host
FROM sessions | LAST 3600 | WHERE ip WITHIN "host:proxy" AND server_ip WITHIN "internet" | KEEP server_ip, server_port, client_to_server_bytesFrom the internal network only
FROM sessions | LAST 3600 | WHERE ip WITHIN "internal" AND server_ip WITHIN "internet" | SORT client_to_server_bytes DESCToward HTTPS (port + protocol filter together)
FROM sessions | LAST 3600 | WHERE server_ip WITHIN "internet" AND server_port == 443 AND protocol == TCP | SORT client_to_server_bytes DESCOne address family only
FROM sessions | LAST 3600 | WHERE server_ip WITHIN "internet6" | SORT client_to_server_bytes DESC
Server ports active on the network (1h)
Intent. Discover which ports hosts are actually accepting connections on.
When to use. Verify that observed services match what is declared in cartography — anything else is an open question.
FROM sessions
| LAST 3600
| EVAL total_bytes = client_to_server_bytes + server_to_client_bytes
| STATS n = COUNT(*), bytes = SUM(total_bytes) BY server_ip, server_port
| SORT n DESC
| LIMIT 50
server_ip is the side the sessionizer inferred as the server; server_port is its listening port.
A surprise row (a port you did not declare in cartography) deserves a look. EVAL builds the bidirectional byte total before STATS aggregates it — aggregate functions take a single column, not an expression.
Variants:
Restrict to one server host
FROM sessions | LAST 3600 | WHERE server_ip == "host:srv-db-01" | STATS n = COUNT(*) BY server_port | SORT n DESC
SYN-only TCP flows — connection attempts that never advanced (1h)
Intent. TCP flows whose flag set is exactly SYN (no ACK, FIN, RST) — connection openings that did not progress.
When to use. Hint at TCP scans, refused connections, or unreachable hosts.
FROM flows
| LAST 3600
| WHERE protocol == TCP AND tcp_flags == 2
| STATS n = COUNT(*) BY src_addr, dst_addr, dst_port
| SORT n DESC
| LIMIT 50
tcp_flags is the cumulative OR of every TCP flag seen during the flow window. The canonical bits are SYN = 2, ACK = 16, FIN = 1, RST = 4, PSH = 8, URG = 32. tcp_flags == 2 matches flows that ONLY carry SYN — the initial packet of a handshake with no follow-up.
flows is the raw surface; for the full conversation use sessions.client_to_server_flags / server_to_client_flags, where the SYN+ACK pair lives on the response side.
Sessions in the Investigation time range
Intent. Replay the conversations seen inside the window selected above the editor.
When to use. Post-mortem or incident response: choose an exact absolute range in the time filter, then refine the query without editing timestamps.
FROM sessions
| BETWEEN $from AND $to
| WHERE server_ip == "host:srv-db-01"
| SORT opened_at ASC
$from and $to are TIMESTAMP variables resolved from Investigation’s relative or absolute time filter. The lower bound is inclusive and the upper bound exclusive.
This keeps a saved query reusable: change the UI range instead of rewriting ISO-8601 literals.
Cross-exporter visibility gaps
Intent. Find real conversations seen by several exporters whose packet totals disagree.
When to use. Validate sensor coverage, investigate asymmetric routing, sampling bias or a collector that is dropping exports.
FROM sessions_consolidated
| BETWEEN $from AND $to
| WHERE sampler_count > 1 AND coherence_pct < 80
| KEEP opened_at, client_ip, server_ip, server_port, samplers, coherence_pct, min_client_to_server_packets, max_client_to_server_packets
| SORT coherence_pct ASC
| LIMIT 100
sessions_consolidated deduplicates the same conversation observed along several hops. Its min/max counters compare exporters without double-counting them; coherence_pct near 100 means they agree.
Start with the least coherent rows, then use samplers to identify the devices whose views diverge.
Observed sessions touching a threat-intel IP
Intent. Connect effective insert-time threat classifications back to the sessions that touched those IPs.
When to use. Triage a feed hit or verify whether a newly enabled threat source has classified traffic actually seen by obserae.
FROM enrichment_ips
| WHERE nature == "threat"
| KEEP ip, source, detail
> FROM sessions
| BETWEEN $from AND $to
| JOIN ip == ip
| KEEP opened_at, client_ip, server_ip, server_port, prev_source, prev_detail
| SORT opened_at DESC
| LIMIT 100
enrichment_ips contains exact IPs that were classified at ingest, unlike enrichment_ranges, which is the downloaded CIDR catalogue. Equality is therefore cheaper and more precise than a range scan here.
JOIN ip == ip expands the session’s virtual ip over client and server endpoints and exposes enrichment columns with the prev_ prefix.
Identify session endpoints from DHCP leases
Intent. Attach the hostname and MAC from observed DHCP leases to recent session endpoints.
When to use. A dynamic workstation appears only as an IP and you need an asset identity before escalating.
FROM dhcp
| LAST 86400
| STATS observations = COUNT(*) BY ip, mac, hostname, network
> FROM sessions
| BETWEEN $from AND $to
| JOIN ip == ip
| KEEP opened_at, client_ip, server_ip, server_port, prev_hostname, prev_mac, prev_network
| SORT opened_at DESC
| LIMIT 100
DHCP is a snapshot journal, so STATS first collapses repeated polls of the same lease. The following JOIN matches either session endpoint through the virtual ip column.
The resulting prev_hostname and prev_mac are observed network-device data; they complement, rather than rewrite, cartography annotations.
Identify session endpoints from ARP observations
Intent. Attach a MAC address and manufacturer to endpoints observed in recent traffic.
When to use. DHCP has no lease for a static or manually configured host, or you need the L2 identity seen by the gateway.
FROM arp
| LAST 86400
| STATS observations = COUNT(*) BY ip, mac, manufacturer, network
> FROM sessions
| BETWEEN $from AND $to
| JOIN ip == ip
| KEEP opened_at, client_ip, server_ip, server_port, prev_mac, prev_manufacturer, prev_network
| SORT opened_at DESC
| LIMIT 100
NetFlow cannot carry MAC addresses. The ARP journal supplies the IP↔MAC observation made by a network device; grouping first removes repeated polling snapshots. This is evidence from the selected day, not a permanent ownership claim: widen or tighten the ARP window according to lease churn.
What is being NAT-translated (24h)
Intent. The address pairs obserae reconstructed across a NAT device, and which proof oriented each one.
When to use. A machine’s traffic seems to come from its gateway, or you want to confirm a NAT declaration took effect.
FROM nat_relations
| LAST 86400
| STATS n=SUM(observation_count), conf=MAX(confidence_pct)
by nat_type, original_ip, translated_ip, direction_method
| SORT n DESC
| LIMIT 50
Group by original_ip / translated_ip — those are the addresses. pre_nat_sampler / post_nat_sampler are the EXPORTERS that observed each side, so grouping by them answers “which probes saw this”, never “what was translated to what”.
direction_method names the proof that decided which side came first: rule (you declared it on the translating device), scope (one side public, one private), cartography (the map implies it) or exporter_path (learned order between two probes). A translation seen by a single probe can only be oriented by a rule or the map — see the NAT page.
An empty result with NAT traffic you know exists usually means the translation is invisible for lack of a declaration.
Variants:
Only what a specific network is translated to
FROM nat_relations | LAST 86400 | WHERE original_ip == "172.18.0.0/16" | KEEP nat_type, original_ip, translated_ip, direction_method, confidence_pct, observation_count | SORT observation_count DESC
Recent NAT socket translations
Intent. Inspect individual original and translated IP:port pairs instead of the aggregated relation.
When to use. Reconstruct one connection through SNAT, DNAT or PAT, or verify that a port translation occurred.
FROM nat_translations
| BETWEEN $from AND $to
| KEEP last_seen, nat_type, protocol, original_ip, original_port, translated_ip, translated_port, anchor_ip, anchor_port, confidence_pct, direction_method
| SORT last_seen DESC
| LIMIT 100
nat_translations is short-lived, per-conversation evidence. original_* and translated_* describe the changing endpoint; anchor_* is the unchanged peer used to correlate both observations.
Use nat_relations for the retained aggregate and this table for socket-level incident reconstruction.
Aggregation
Top talkers by total bytes (1h)
Intent. Rank sources by outbound volume — the primary ‘who is heavy on the network’ lens.
When to use. Build a first traffic baseline, investigate saturation or create a top-N dashboard panel.
FROM flows
| LAST 3600
| STATS total = SUM(bytes), n = COUNT(*) BY src_addr
| SORT total DESC
| LIMIT 10
STATS rewrites the row set to one row per group (here src_addr) with the aggregate aliases as new columns; the original columns disappear.
Combine SORT … DESC | LIMIT N for top-N — the canonical pattern.
Variants:
Group by pair (source, destination)
FROM flows | LAST 3600 | STATS bytes = SUM(bytes) BY src_addr, dst_addr | SORT bytes DESC | LIMIT 20Only between hosts inside one network (CIDR scope)
FROM flows | LAST 3600 | WHERE src_addr WITHIN "10.0.0.0/24" AND dst_addr WITHIN "10.0.0.0/24" | STATS bytes = SUM(bytes) BY src_addr | SORT bytes DESC
Sessions touching a cloud provider
Intent. Sessions whose server-side IP falls inside prefixes collected from an enrichment source such as a cloud or threat-intel feed.
When to use. You suspect a host is reaching AWS / Azure / a known-bad IOC range, but you do not want to maintain the CIDR list manually.
FROM sessions | LAST 3600 | KEEP server_ip
> FROM enrichment_ranges | WHERE source == "aws" | PIVOT server_ip WITHIN cidr | KEEP cidr, source, details
PIVOT a WITHIN b keeps rows from the enrichment feed whose cidr contains one of the session IPs. This lets you compare traffic with large provider or threat-intel prefix lists without maintaining the list manually.
Swap to JOIN server_ip WITHIN cidr if you want the left columns surfaced as prev_* (useful to attribute each match back to its session).
Possible port scan (distinct dst ports per source)
Intent. Sources contacting many distinct destination ports — a vertical port-scan signal.
When to use. Triage suspicious activity; tune the > 20 threshold to your environment.
FROM flows
| LAST 3600
| STATS uniq_dst_ports = COUNT_DISTINCT(dst_port) BY src_addr
| HAVING uniq_dst_ports > 20
| SORT uniq_dst_ports DESC
| LIMIT 20
COUNT_DISTINCT counts unique values inside a group — heavier than COUNT(*), use it only when uniqueness matters.
HAVING filters after STATS (raw-row filters use WHERE).
Adaptive flow histogram for dashboards
Intent. Flow count and byte volume over the selected time range, with an automatic bucket width.
When to use. Create a reusable time-based line or bar panel that stays readable from minutes to weeks.
FROM flows
| BETWEEN $from AND $to
| STATS flows = COUNT(*), bytes_total = SUM(bytes) BY BUCKET(time_received, $interval)
$interval is a stable 1/2/5 bucket width in seconds derived from the selected range. STATS … BY BUCKET(...) names the time column bucket and returns it chronologically without an explicit SORT.
Map bucket to X and choose Line to connect values, or Bar to compare each interval independently.
Variants:
Fixed hourly buckets over 24 hours
FROM flows | LAST 86400 | EVAL hour = BUCKET(time_received, 3600) | STATS n = COUNT(*) BY hour | SORT hour ASC
Exporter coherence distribution
Intent. Count consolidated sessions in 10-point coherence ranges to expose systematic exporter disagreement.
When to use. Check sampling, dropped exports or partial visibility across conversations observed by more than one exporter.
FROM sessions_consolidated
| BETWEEN $from AND $to
| WHERE sampler_count > 1
| STATS sessions = COUNT(*) BY BUCKET(coherence_pct, 10)
Numeric BUCKET(value, width) groups in zero-aligned half-open ranges. The lower bound 0 means coherence 0–9, 10 means 10–19, and exact 100 stays separate as perfect agreement.
sampler_count > 1 removes conversations seen by only one exporter, where cross-exporter coherence is not meaningful. Investigation keeps numeric lower bounds for filters and exports, displays readable interval labels, and suggests a Bar chart with bucket on X and sessions on Y.
Variants:
Include single-exporter conversations
FROM sessions_consolidated | BETWEEN $from AND $to | STATS sessions = COUNT(*) BY BUCKET(coherence_pct, 10)
Possible exfiltration: high bytes-per-packet ratio
Intent. Sources whose flows are unusually large per packet — bulk transfer signature.
When to use. Hunt bulk-transfer outliers after a top-talker or exfiltration alert; tune the threshold against local MTU and sampling.
FROM flows
| LAST 3600
| EVAL bpp = bytes / packets
| STATS avg_bpp = AVG(bpp), total = SUM(bytes) BY src_addr
| HAVING avg_bpp > 1000
| SORT total DESC
| LIMIT 20
Division is null-safe in NFQL: a / b rewrites to a / NULLIF(b, 0), no divide-by-zero crash.
AVG truncates fractional results — for precision, use SUM(x) / COUNT(*) instead.
Dashboard summary for the selected range
Intent. Produce headline session, upload and download metrics together with the exact selected range length.
When to use. Create a metric/table panel whose meaning follows the dashboard time picker and whose subtitle can expose the window size.
FROM sessions
| BETWEEN $from AND $to
| EVAL selected_range_s = $range_s
| STATS sessions = COUNT(*), upload_bytes = SUM(client_to_server_bytes), download_bytes = SUM(server_to_client_bytes), range_s = MAX(selected_range_s)
$range_s is the time-filter duration in seconds. Unlike $interval, it does not choose buckets; it is useful for rates, contextual metrics and range-aware thresholds.
With no BY clause STATS returns one row, ideal for a metric panel or a compact operational summary.
Detection
Recent rule matches with rule name (1h)
Intent. Every detection that fired in the last hour, with the rule name resolved.
When to use. Triage: which detections are active right now, and which sessions did they hit.
FROM session_matches | LAST 3600
> FROM rules | PIVOT rule_id == rule_id | KEEP name, description, tags
session_matches carries rule_id only (UUID); the rules table holds the human name.
The cascade pivots the match set into the rules catalogue and projects the readable columns.
Variants:
Count matches per rule (top-N noisiest)
FROM session_matches | LAST 3600 | STATS n = COUNT(*) BY rule_id | SORT n DESC | LIMIT 10Match + session detail via inner JOIN (prev_ columns)*
FROM session_matches | LAST 3600 | KEEP session_id, rule_id > FROM sessions | LAST 3600 | KEEP session_id, server_ip, server_port | JOIN session_id == session_id | KEEP prev_rule_id, server_ip, server_port
Sessions that triggered a specific rule (by name)
Intent. Find every session matched by a rule, given the human name (not the UUID).
When to use. A detection name appeared in an alert and you want the underlying traffic.
FROM rules | WHERE name == "prod-ssh-allow" | KEEP rule_id
> FROM session_matches | LAST 86400 | PIVOT rule_id == rule_id
> FROM sessions | PIVOT session_id == session_id | KEEP opened_at, client_ip, server_ip, server_port
Three-pipeline cascade: filter rules by name, pivot the surviving rule_ids into session_matches, then pivot the resulting session_ids into sessions to recover the traffic.
Each > introduces a new pipeline whose PIVOT references the previous pipeline’s terminal CTE — efficient single semi-joins all the way down.
Closed sessions that matched no rule (1h)
Intent. Conversations the rules do NOT cover — the detection-gap surface.
When to use. Looking for traffic that bypasses every detection: the typical ‘shadow IT’ or undeclared service hunt.
FROM session_matches | LAST 3600
> FROM sessions | LAST 3600 | PIVOT NOT session_id == session_id | KEEP client_ip, server_ip, server_port
PIVOT NOT is an anti-semi-join: keep right rows whose key is NOT in the left.
Both sides must filter on the same time window or the anti-join slides under it.
Variants:
Same, restricted to one network (CIDR + anti-join)
FROM session_matches | LAST 3600 > FROM sessions | LAST 3600 | WHERE server_ip WITHIN "10.0.0.0/24" | PIVOT NOT session_id == session_id | KEEP server_ip, server_port