network-data-analysis

name	network-data-analysis
description	Ad-hoc read-only SQL analysis over exported network data (Zeek logs, Suricata eve.json, generated reports) using DuckDB. Use when aggregating across a packet capture's sessions, correlating IDS alerts with connection metadata, or answering counting and grouping questions that a per-log view cannot
version	1.0.0
license	Apache-2.0
tags	["analysis","sql","duckdb","zeek","suricata","forensics","read-only"]

Network Data Analysis (read-only SQL)

MCP Server

• Server: analysis-mcp (NetClaw-authored, spec 092)
• Tools: analysis_status, analysis_datasets, analysis_query
• Engine: DuckDB, in-memory, sandboxed and locked

What this reads, and what it can never read

Loads files from an allowlist of roots — ~/.openclaw/nsm/runs (Zeek/Suricata output from nsm-mcp), the workspace output directory, and ~/.openclaw/analysis for your own exports.

NetClaw's own stores are permanently unreachable: ~/.openclaw/memory/, ~/.openclaw/rag/, ~/.openclaw/n2n/ and ~/.openclaw/gait/, plus .ssh, .aws, .kube and .env. A generic SQL surface over those would be a backdoor, not an analysis tool.

That is not enforced by pattern matching. Datasets are materialised, then DuckDB's own enable_external_access=false and lock_configuration=true close every filesystem and network path irreversibly for the life of the process. Verified: read_csv('/etc/passwd'), glob('/home/**'), ATTACH of the memory or RAG stores, COPY … TO, INSTALL/LOAD, and re-enabling access all raise.

If you need a file analysed, put it under ~/.openclaw/analysis — do not try to reach it in SQL, because you cannot.

Workflow: analyse a capture

1. Produce the data first. nsm_analyze (skill nsm-session-pivot) writes Zeek logs and Suricata eve.json. Without a run, this surface has nothing to read and says so.
2. analysis_status — is the sandbox locked, how many datasets loaded, what are the caps?
3. analysis_datasets — table names, row counts, columns. Read the columns before writing SQL; guessing column names wastes a round trip.
4. analysis_query — one read statement per call.

Zeek tables carry their real column names (id_orig_h, id_resp_p, uid), lifted from the log's #fields header. Without that they would be column0…columnN and unusable.

The pivot that makes this worth using

Every Zeek log shares uid with conn.log, so a join is the session pivot — and unlike walking logs one at a time, it aggregates:

SELECT c.id_orig_h, c.id_resp_h, c.service, h.method, h.host, h.uri
FROM zeek_<run>_conn c
LEFT JOIN zeek_<run>_http h USING (uid)
ORDER BY c.ts

Counting questions a per-log view cannot answer:

-- top talkers by connection count
SELECT id_orig_h, count(*) AS conns FROM zeek_<run>_conn
GROUP BY 1 ORDER BY conns DESC LIMIT 20

-- which services appeared at all
SELECT service, count(*) FROM zeek_<run>_conn GROUP BY 1 ORDER BY 2 DESC

-- Suricata alert signatures by frequency
SELECT json_extract_string(alert, '$.signature') AS sig, count(*)
FROM suricata_<run>_eve WHERE event_type = 'alert' GROUP BY 1 ORDER BY 2 DESC

Reading results honestly

• truncated: true means you are looking at a page. Never present a capped result as a total — run COUNT(*) for the real number. The tool reports this in gaps.
• Zeek columns are all text. Datasets load as varchar so a malformed field cannot abort the load; cast explicitly (CAST(duration AS DOUBLE)) and say that you did.
• A row count reflects what was LOADED, not what existed. Files above the size cap are skipped and listed in notes; a per-table row cap applies. Check analysis_status before claiming completeness.
• 0 datasets means no exports exist, never "the network was quiet."
• This inherits every caveat of its source. A Zeek run made with checksum validation on may be missing whole protocol logs (see nsm-session-pivot) — and SQL over an incomplete log is confidently wrong. Check the posture of the run you are querying.

Important Rules

• Read-only. INSERT/UPDATE/DELETE/DROP/CREATE/ATTACH/COPY/INSTALL/SET are refused, and independently impossible after lockdown.
• One statement per call. A stacked second statement is refused.
• Queries time out (default 30s) and are interrupted, not left running.
• Record in GAIT — log the query and the dataset it ran against, not just the answer.

Integration with Other Skills

Skill	How They Work Together
nsm-session-pivot	Produces the Zeek logs this queries; use it for single-session detail
nsm-ids-triage	Produces eve.json; use SQL here to aggregate alerts across a capture
packet-analysis	Drop to individual packet decode once SQL narrows the field
document-generation	Turn a query result into a report table
gait-session-tracking	Record all analysis runs

Environment Variables

• ANALYSIS_QUERY_TIMEOUT — per-query seconds (default 30)
• ANALYSIS_MAX_RESULT_ROWS — result cap (default 500)
• ANALYSIS_EXTRA_ROOTS — extra allowlisted roots, os.pathsep-separated
• ANALYSIS_MAX_FILE_BYTES / ANALYSIS_MAX_ROWS — load caps