Telecom analytics: CDRs and network logs in one SQL engine
Telecom analytics turns what a mobile network records into answers: which cell is dropping calls, which alarm explains it, which customers it hit, where the traffic went. SereneDB is its storage and query layer: call detail records, the network log and trouble tickets sit in one Postgres-compatible engine, and SQL aggregates them over time, searches the log text and joins them.
curl https://install.serenedb.com | shWhat is telecom analytics? Five questions about one evening on a mobile network
Telecom analytics turns what a network records about itself, call detail records, alarms, log lines and trouble tickets, into answers: which cell is dropping calls, what the network log said when it started, which customers it hit, how long a cell was out of service. Below is one simulated evening on six cells of two sites. Pick a question to run it; each is one SQL statement.
SELECT time_bucket(INTERVAL '30 minutes', start_ts)::TIME AS from_time, count(*) FILTER (WHERE cell_id = '142B') AS calls_142b, round(100.0 * count(*) FILTER (WHERE cell_id = '142B' AND end_cause = 'dropped') / nullif(count(*) FILTER (WHERE cell_id = '142B'), 0), 2) AS dropped_pct_142b, round(100.0 * count(*) FILTER (WHERE cell_id <> '142B' AND end_cause = 'dropped') / count(*) FILTER (WHERE cell_id <> '142B'), 2) AS dropped_pct_others FROM cdrs WHERE end_cause <> 'failed' GROUP BY 1 ORDER BY 1;
| from_time | calls_142b | dropped_pct_142b | dropped_pct_others |
|---|---|---|---|
| 17:00:00 | 376 | 0.53 | 0.6 |
| 17:30:00 | 388 | 0.77 | 0.51 |
| 18:00:00 | 427 | 0.7 | 0.48 |
| 18:30:00 | 527 | 0.57 | 0.55 |
| 19:00:00 | 604 | 4.47 | 0.47 |
| 19:30:00 | 610 | 7.54 | 0.75 |
| 20:00:00 | 95 | 10.53 | 0.5 |
| 20:30:00 | 112 | 0 | 0.51 |
end_cause = 'failed') are left out of the rate.SELECT ts::TIME AS t, cell_id, kind, message FROM network_log_idx WHERE site = '142' AND message @@ ts_levenshtein('antenna', 1) ORDER BY ts;
| t | cell_id | kind | message |
|---|---|---|---|
| 19:04:12 | 142B | alarm | VSWR 2.6 on antenna port B1, threshold 1.5 |
| 19:15:30 | 142B | note | Drops on 142B up since the VSWR alarm, antena feeder suspected, field team sent, TT-24019 |
| 20:05:00 | 142B | state | Cell 142B locked for antenna repair, 142A and 142C take its traffic |
| 20:41:35 | 142B | note | Water in the feeder connector at the antenna, connector replaced, VSWR 1.2 |
| 20:52:06 | 142B | clear | VSWR 1.2 on antenna port B1, alarm cleared |
ts_levenshtein accepts one edit, so it also finds "antena feeder suspected", a typo an exact search for antenna misses.SELECT a.kind, a.code, a.ts::TIME AS since, count(*) AS calls, count(*) FILTER (WHERE c.end_cause = 'dropped') AS dropped, round(100.0 * count(*) FILTER (WHERE c.end_cause = 'dropped') / count(*), 2) AS dropped_pct FROM cdrs c ASOF LEFT JOIN (SELECT ts, cell_id, kind, code FROM network_log WHERE kind IN ('alarm', 'clear')) a ON a.cell_id = c.cell_id AND c.start_ts >= a.ts WHERE c.cell_id = '142B' AND c.end_cause <> 'failed' GROUP BY ALL ORDER BY since NULLS FIRST;
| kind | code | since | calls | dropped | dropped_pct |
|---|---|---|---|---|---|
| 1800 | 12 | 0.67 | |||
| alarm | VSWR_HIGH | 19:04:12 | 1229 | 82 | 6.67 |
| clear | VSWR_HIGH | 20:52:06 | 110 | 0 | 0 |
ASOF LEFT JOIN pairs each call on 142B with the last alarm or clear on its cell at or before the call started. Calls before any alarm keep a NULL row and dropped 0.67 % of the time; while VSWR_HIGH was active, 6.67 %; after the clear at 20:52:06, none of 110.SELECT caller, count(*) AS calls, count(*) FILTER (WHERE end_cause = 'dropped') AS dropped, max(start_ts) FILTER (WHERE end_cause = 'dropped')::TIME AS last_drop FROM cdrs WHERE cell_id = '142B' GROUP BY caller HAVING count(*) FILTER (WHERE end_cause = 'dropped') >= 2 ORDER BY dropped DESC, last_drop;
| caller | calls | dropped | last_drop |
|---|---|---|---|
| u2414 | 5 | 2 | 19:18:13 |
| u2446 | 8 | 2 | 19:29:17 |
| u2397 | 8 | 2 | 19:34:02 |
| u2360 | 8 | 2 | 19:49:05 |
| u2248 | 9 | 2 | 19:51:06 |
| u2271 | 9 | 2 | 19:55:32 |
| u2147 | 6 | 2 | 20:02:03 |
| u2546 | 8 | 2 | 20:04:46 |
caller holds pseudonymous ids, not phone numbers.SELECT cell_id, ts::TIME AS out_at, round(minutes, 1) AS minutes, round(100 * (240 - minutes) / 240, 1) AS availability_pct, message FROM ( SELECT cell_id, ts, code, message, date_diff('second', ts, lead(ts, 1, TIMESTAMP '2026-11-06 21:00:00') OVER (PARTITION BY cell_id ORDER BY ts)) / 60.0 AS minutes FROM network_log WHERE kind = 'state' ) WHERE code = 'OUT_OF_SERVICE' ORDER BY ts;
| cell_id | out_at | minutes | availability_pct | message |
|---|---|---|---|---|
| 217C | 18:21:40 | 3.5 | 98.5 | Cell 217C out of service, no S1 link |
| 142B | 20:05:00 | 47 | 80.4 | Cell 142B locked for antenna repair, 142A and 142C take its traffic |
lead() gives each state change the time of the next one, so an OUT_OF_SERVICE row lasts until the cell is back: 3.5 minutes for 217C's lost S1 link and 47 for 142B's repair, out of the evening's 240. While 142B was locked, 142A and 142C carried more calls.What telecom analytics needs from its database, and how SereneDB does it in SQL
The database under telecom analytics loads call detail records as the network writes them, turns them into KPIs per cell and per hour, lines up calls with alarms that never share a timestamp, searches what network elements and engineers write, and keeps months of records. Telecom analytics solutions are usually compared as products; these are the jobs, each with the SereneDB feature that does it and its docs.
| Need | How SereneDB does it | Looks like |
|---|---|---|
| Load CDR files | COPY reads the CSV files a switch or mediation system writes, header included, and INSERT takes records from any PostgreSQL client library. Docs → | COPY cdrs FROM '…/msc-01_20261106_2100.csv' (FORMAT csv, HEADER) |
| KPIs over time | time_bucket groups calls into minutes or half hours, and FILTER counts dropped and failed calls per cell in the same pass. Docs → | count(*) FILTER (WHERE end_cause = 'dropped') |
| Line up calls and alarms | A call and an alarm never share a timestamp. ASOF LEFT JOIN pairs each call with the last alarm on its cell at or before it, and keeps the calls that have none. Docs → | ASOF LEFT JOIN … a ON a.cell_id = c.cell_id AND c.start_ts >= a.ts |
| Search network logs | An inverted index over alarm texts, log lines and engineers' notes: exact codes and cell ids, BM25 ranking, phrases, and matches within an edit distance for typos. Docs → | message @@ ts_levenshtein('antenna', 1) |
| Count alarms by code | Codes are indexed whole, so a count per alarm code reads the index's term dictionary. Docs → | ts_dict_count(code) |
| Outage minutes and availability | Window functions give each state change the time of the next one, so out-of-service minutes and cell availability are a sum. Docs → | lead(ts, 1, …) OVER (PARTITION BY cell_id ORDER BY ts) |
| Subscriber patterns | GROUP BY the subscriber and HAVING keep the callers who match a pattern: repeated drops, or many calls from one cell to many numbers. Docs → | GROUP BY caller HAVING count(DISTINCT cell_id) = 1 |
| Search ticket history | Trouble tickets are a table with their own inverted index, so a phrase finds every earlier ticket about the same fault. Docs → | summary @@ ts_phrase('feeder connector') |
| Dashboards | Grafana's built-in PostgreSQL data source sends each panel's SQL to SereneDB. Docs → | floor(extract(epoch from start_ts)/300)*300 AS "time" |
| Keep the history as files | COPY writes old CDRs to Parquet files partitioned by day, and read_parquet queries them in place, on disk or in S3. Docs → | (FORMAT parquet, PARTITION_BY (day), APPEND) |
| Retention | A scheduled job inside the server archives and deletes the records past their period. Docs → | CREATE JOB cdr_history EVERY 1 DAY OFFSET 2 HOURS AS $$ … $$ |
Telecom analytics architecture: CDRs, network logs and tickets in one engine
The mediation system's CDR files load with COPY, alarms, log lines, notes and tickets land in tables with inverted indexes over their text, codes and times, and every question is a SELECT: a time bucket, a search, an ASOF JOIN or a GROUP BY. Grafana reads the answers, and a job moves old CDRs to Parquet. Hover a stage, or a lane inside the index.
CDR files load with COPY
When CDRs arrive as files, from a mediation system for example, COPY … FROM reads each CSV file with its header into the table. INSERT takes single records from any PostgreSQL client library.
Alarms, state changes, notes and tickets as rows
The network log holds five kinds of entries: alarms and their clears, state changes, log lines from the network elements, and the notes engineers type. Tickets come from the ticketing system, or stay in a PostgreSQL database and are read in place with ATTACH, as on the zero-ETL search page.
A call detail record is a row
A plain table, one row per call: who called whom, from which cell, when, for how long and how the call ended. A call that never connected has end_cause = 'failed' and a duration of 0. CDRs are aggregated, not searched, so they need no index.
The network log, words and codes
The log's text goes through the noc_text dictionary: lowercased words, numbers such as 2.6 kept whole. Alarm and state codes are indexed whole, so a count per code reads the term dictionary.
Ticket history, searchable
Every trouble ticket with its site, cell, times and summary. The history goes back to March, so a fault search finds the earlier tickets for the same part.
What is searched and filtered is indexed
One inverted index per searched table: the text through a dictionary, sites, cells, kinds and codes as exact terms, times as ranges. New rows become searchable at the next refresh, every second by default, or at once after VACUUM (REFRESH_TABLE).
Dropped-call rate per cell and half hour
time_bucket groups the calls into half hours, and FILTER counts one cell's dropped calls apart from the rest in the same pass. This is the lab's first question.
Search the log and the tickets, ranked
Words in a query match any entry that has them, and BM25 ranks the entries that fit best first. ts_levenshtein matches within an edit distance, for the typos in engineers' notes.
Each call, with the alarm active on its cell
ASOF LEFT JOIN pairs every call with the last alarm or clear on the same cell at or before it, so calls and alarms need no shared clock, and calls before any alarm keep their row.
Questions about subscribers
A GROUP BY on the caller with a HAVING clause keeps the subscribers who match a pattern: repeated drops here, many calls to many numbers from one cell in the fraud example.
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.
Old CDRs to Parquet, by a job
COPY … TO with PARTITION_BY writes one folder of Parquet files per day, and read_parquet queries them in place. With an S3 secret the same COPY writes to a bucket. A job does it daily and deletes what it moved.
What network data needs from one engine
cdrs · network_log · ticketsNo special table type: a call is a row, and a KPI per cell and half hour is an aggregate over its rows.
SELECT count(*) FROM cdrsVSWR_HIGH is indexed whole, so a count per alarm code is a dictionary read.
ts_dict_count(code)Engineers' notes match within an edit distance, and BM25 puts the closest entries first.
ts_levenshtein('antenna', 1)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: analyze CDRs and network logs in SQL, in five steps
From an empty server to the alarm that explains the dropped calls: start SereneDB, create the tables, load one evening, find the failing cell and search its log, then tie the drops to the alarm and find the customers hit.
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 cdrs ( call_id BIGINT, start_ts TIMESTAMP, caller VARCHAR, -- pseudonymous subscriber ids callee VARCHAR, cell_id VARCHAR, -- the cell that served the caller duration_s INTEGER, end_cause VARCHAR -- normal, dropped, or failed (never connected) ); CREATE TABLE network_log ( id INTEGER, ts TIMESTAMP, site VARCHAR, cell_id VARCHAR, -- NULL for an entry about the whole site kind VARCHAR, -- alarm, clear, state, log or note code VARCHAR, -- the alarm, or IN_SERVICE / OUT_OF_SERVICE message VARCHAR ); CREATE TABLE tickets ( ticket_id VARCHAR, site VARCHAR, cell_id VARCHAR, opened_at TIMESTAMP, closed_at TIMESTAMP, summary VARCHAR ); CREATE TEXT SEARCH DICTIONARY noc_text AS split_text(case := 'lower') WITH (frequency, position, norm); CREATE INDEX network_log_idx ON network_log USING inverted (id, ts, site, cell_id, kind, code, message noc_text); CREATE INDEX tickets_idx ON tickets USING inverted (ticket_id, site, cell_id, opened_at, summary noc_text) INCLUDE (closed_at);
-- One simulated evening, 2026-11-06 17:00 to 21:00, on six cells of two -- sites. From 18:50 water in an antenna feeder connector degrades cell 142B: -- its VSWR alarm fires at 19:04, the cell is locked for repair from 20:05 to -- 20:52, and 142A and 142C carry its calls meanwhile. 217C loses its link to -- the core from 18:21:40 to 18:25:10. Two SIMs on 217C call all evening. INSERT INTO cdrs WITH cells(cell_id, k, base) AS ( VALUES ('142A', 1, 14), ('142B', 2, 18), ('142C', 3, 12), ('217A', 4, 10), ('217B', 5, 13), ('217C', 6, 11) ), per_minute AS ( -- m: minutes after 17:00; n: calls started that minute SELECT cell_id, k, m, round(base * (0.7 + 0.5 * exp(-power((m - 150) / 60.0, 2))) * (0.85 + 0.3 * ((m * 37 + k * 101) % 97) / 97.0) * CASE WHEN m NOT BETWEEN 185 AND 231 THEN 1 WHEN cell_id = '142B' THEN 0 -- locked WHEN cell_id = '142A' THEN 1.55 WHEN cell_id = '142C' THEN 1.6 ELSE 1 END)::INTEGER AS n FROM cells, generate_series(0, 239) AS g(m) ), calls AS ( -- h, x: deterministic pseudo-random digits per call SELECT *, (h * h + 11 * j) % 999999937 AS x FROM (SELECT *, (k * 1000003 + m * 1009 + j) * 2654435761 % 1000000007 AS h FROM per_minute, generate_series(1, n) AS s(j)) ), outcomes AS ( SELECT *, x % 1000 / 1000.0 AS r, CASE WHEN cell_id = '142B' AND m BETWEEN 124 AND 184 THEN 0.03 ELSE 0.008 END AS p_failed, CASE WHEN cell_id <> '142B' OR m < 110 THEN 0.006 WHEN m < 124 THEN 0.006 + 0.0015 * (m - 110) WHEN m < 185 THEN 0.04 + 0.0008 * (m - 124) ELSE 0.005 END AS p_dropped FROM calls ), evening AS ( SELECT TIMESTAMP '2026-11-06 17:00:00' + INTERVAL '1 second' * (m * 60 + 60 * (j - 1) / n) AS start_ts, 'u' || (k * 1000 + x / 1000 % 600) AS caller, 'u' || ((1 + h % 6) * 1000 + h / 10 % 600) AS callee, cell_id, CASE WHEN r < p_failed THEN 0 WHEN r < p_failed + p_dropped THEN 5 + x / 1000000 % 150 ELSE 20 + power(x / 1000000 % 1000, 2) / 2500 END AS duration_s, CASE WHEN r < p_failed THEN 'failed' WHEN r < p_failed + p_dropped THEN 'dropped' ELSE 'normal' END AS end_cause FROM outcomes UNION ALL -- two SIMs on 217C: a call every 50 s, never called SELECT TIMESTAMP '2026-11-06 17:00:00' + INTERVAL '1 second' * (i * 50 + sim * 20), 'u900' || sim, 'u' || ((1 + i % 6) * 1000 + (i * 37 + sim * 101) % 600), '217C', 60 + (i * 13 + sim * 7) % 120, 'normal' FROM (VALUES (1), (2)) AS b(sim), generate_series(0, 287) AS g(i) ) SELECT row_number() OVER (ORDER BY start_ts, cell_id, caller), start_ts, caller, callee, cell_id, round(duration_s), end_cause FROM evening WHERE NOT (cell_id = '217C' AND start_ts >= TIMESTAMP '2026-11-06 18:21:40' AND start_ts < TIMESTAMP '2026-11-06 18:25:10'); SELECT end_cause, count(*) FROM cdrs GROUP BY end_cause ORDER BY end_cause; INSERT INTO network_log VALUES ( 1, '2026-11-06 17:12:31', '217', '217A', 'log', NULL, 'ANR added neighbour relation 217A to 142C'), ( 2, '2026-11-06 17:46:05', '142', NULL, 'log', NULL, 'Battery test on site 142 passed, 4 h backup'), ( 3, '2026-11-06 18:21:40', '217', '217C', 'alarm', 'S1_LINK_DOWN', 'S1 link to the core down, SCTP association lost'), ( 4, '2026-11-06 18:21:40', '217', '217C', 'state', 'OUT_OF_SERVICE', 'Cell 217C out of service, no S1 link'), ( 5, '2026-11-06 18:25:10', '217', '217C', 'clear', 'S1_LINK_DOWN', 'S1 link restored, SCTP association up'), ( 6, '2026-11-06 18:25:10', '217', '217C', 'state', 'IN_SERVICE', 'Cell 217C back in service'), ( 7, '2026-11-06 18:27:00', '217', NULL, 'note', NULL, 'Transport router port on 217 flapped and recovered by itself, TT-24012'), ( 8, '2026-11-06 18:52:44', '142', '142B', 'note', NULL, 'Customer care: three complaints of dropped calls near the stadium, 142B'), ( 9, '2026-11-06 19:04:12', '142', '142B', 'alarm', 'VSWR_HIGH', 'VSWR 2.6 on antenna port B1, threshold 1.5'), (10, '2026-11-06 19:15:30', '142', '142B', 'note', NULL, 'Drops on 142B up since the VSWR alarm, antena feeder suspected, field team sent, TT-24019'), (11, '2026-11-06 19:38:20', '142', '142C', 'log', NULL, 'Handover attempts from 142B to 142C up 40 percent'), (12, '2026-11-06 20:05:00', '142', '142B', 'state', 'OUT_OF_SERVICE', 'Cell 142B locked for antenna repair, 142A and 142C take its traffic'), (13, '2026-11-06 20:41:35', '142', '142B', 'note', NULL, 'Water in the feeder connector at the antenna, connector replaced, VSWR 1.2'), (14, '2026-11-06 20:52:00', '142', '142B', 'state', 'IN_SERVICE', 'Cell 142B unlocked, back in service'), (15, '2026-11-06 20:52:06', '142', '142B', 'clear', 'VSWR_HIGH', 'VSWR 1.2 on antenna port B1, alarm cleared'); INSERT INTO tickets VALUES ('TT-20931', '142', '142A', '2026-03-14 08:20:00', '2026-03-14 13:05:00', 'VSWR alarm after rain on 142A, antenna feeder connector corroded, connector replaced'), ('TT-21460', '217', '217B', '2026-05-02 16:40:00', '2026-05-02 18:10:00', 'Microwave link from 217 fading in heavy rain, dish alignment checked, no fault found'), ('TT-22118', '142', '142C', '2026-06-19 10:05:00', '2026-06-19 15:30:00', 'Remote electrical tilt stuck at 4 degrees on 142C, tilt motor replaced'), ('TT-22790', '217', '217C', '2026-08-08 14:15:00', '2026-08-08 17:00:00', 'Cabinet fan failed on 217, high temperature alarm, fan replaced'), ('TT-23305', '142', '142B', '2026-09-27 07:50:00', '2026-09-27 11:40:00', 'VSWR alarm on 142B after the storm, water in the feeder connector, connector resealed'), ('TT-24012', '217', '217C', '2026-11-06 18:22:10', '2026-11-06 18:40:00', 'S1 link down on 217C for 3.5 minutes, transport router port flapped, recovered by itself'), ('TT-24019', '142', '142B', '2026-11-06 19:15:30', '2026-11-06 20:55:00', 'Dropped calls on 142B after VSWR alarm, water in the feeder connector at the antenna, connector replaced'); VACUUM (REFRESH_TABLE) network_log; VACUUM (REFRESH_TABLE) tickets; SELECT (SELECT count(*) FROM cdrs) AS cdrs, (SELECT count(*) FROM network_log) AS log_entries, (SELECT count(*) FROM tickets) AS tickets;
SELECT time_bucket(INTERVAL '30 minutes', start_ts)::TIME AS from_time, count(*) FILTER (WHERE cell_id = '142B') AS calls_142b, round(100.0 * count(*) FILTER (WHERE cell_id = '142B' AND end_cause = 'dropped') / nullif(count(*) FILTER (WHERE cell_id = '142B'), 0), 2) AS dropped_pct_142b, round(100.0 * count(*) FILTER (WHERE cell_id <> '142B' AND end_cause = 'dropped') / count(*) FILTER (WHERE cell_id <> '142B'), 2) AS dropped_pct_others FROM cdrs WHERE end_cause <> 'failed' GROUP BY 1 ORDER BY 1; SELECT ts::TIME AS t, cell_id, kind, message FROM network_log_idx WHERE site = '142' AND message @@ ts_levenshtein('antenna', 1) ORDER BY ts;
SELECT a.kind, a.code, a.ts::TIME AS since, count(*) AS calls, count(*) FILTER (WHERE c.end_cause = 'dropped') AS dropped, round(100.0 * count(*) FILTER (WHERE c.end_cause = 'dropped') / count(*), 2) AS dropped_pct FROM cdrs c ASOF LEFT JOIN (SELECT ts, cell_id, kind, code FROM network_log WHERE kind IN ('alarm', 'clear')) a ON a.cell_id = c.cell_id AND c.start_ts >= a.ts WHERE c.cell_id = '142B' AND c.end_cause <> 'failed' GROUP BY ALL ORDER BY since NULLS FIRST; SELECT caller, count(*) AS calls, count(*) FILTER (WHERE end_cause = 'dropped') AS dropped, max(start_ts) FILTER (WHERE end_cause = 'dropped')::TIME AS last_drop FROM cdrs WHERE cell_id = '142B' GROUP BY caller HAVING count(*) FILTER (WHERE end_cause = 'dropped') >= 2 ORDER BY dropped DESC, last_drop; CREATE JOB cdr_history EVERY 1 DAY OFFSET 2 HOURS AS $$ COPY (SELECT *, start_ts::DATE AS day FROM cdrs WHERE start_ts < current_date - INTERVAL '90 days') TO '/tmp/cdr_history' (FORMAT parquet, PARTITION_BY (day), APPEND); DELETE FROM cdrs WHERE start_ts < current_date - INTERVAL '90 days'; $$;
What telecom teams build on SereneDB
SIM-box fraud: callers that never move and are never called
A SIM box takes calls that arrive over the internet and places them into the mobile network through local SIM cards, so they reach the network as local calls. The query below flags SIMs that make many calls, each to a different number, all from one cell, and are never called themselves. The thresholds are SQL, so they are yours to tune.
Real-time analytics →SELECT caller, count(*) AS calls, count(DISTINCT callee) AS callees, count(DISTINCT cell_id) AS cells, round(avg(duration_s)) AS avg_s FROM cdrs GROUP BY caller HAVING count(*) >= 100 AND count(DISTINCT cell_id) = 1 AND caller NOT IN (SELECT callee FROM cdrs) ORDER BY calls DESC, caller;
| caller | calls | callees | cells | avg_s |
|---|---|---|---|---|
| u9001 | 284 | 284 | 1 | 119 |
| u9002 | 284 | 284 | 1 | 119 |
Capacity: Erlangs and the busy hour per cell
Telephone traffic is measured in Erlangs: one Erlang is one line busy for a whole hour. Summing call durations per cell and hour and dividing by 3,600 seconds gives each cell's Erlangs, and arg_max picks its busiest hour. Here 142A and 142C peak at 20:00, the hour they carried 142B's calls.
OLAP database →SELECT cell_id, arg_max(hour, erlangs)::TIME AS busy_hour, max(erlangs) AS erlangs FROM ( SELECT cell_id, time_bucket(INTERVAL '1 hour', start_ts) AS hour, round(sum(duration_s) / 3600.0, 1) AS erlangs FROM cdrs WHERE end_cause <> 'failed' GROUP BY ALL ) GROUP BY cell_id ORDER BY cell_id;
| cell_id | busy_hour | erlangs |
|---|---|---|
| 142A | 20:00:00 | 44.1 |
| 142B | 19:00:00 | 52.4 |
| 142C | 20:00:00 | 40.4 |
| 217A | 19:00:00 | 29 |
| 217B | 19:00:00 | 37.5 |
| 217C | 19:00:00 | 35.8 |
Has this fault happened before? Search the ticket history
Trouble tickets record what failed and what was done about it. A phrase search over their summaries finds the earlier tickets for the same fault on the same site, the question to ask before sealing the same connector a third time.
Full-text search →SELECT ticket_id, opened_at::DATE AS opened, cell_id, summary FROM tickets_idx WHERE site = '142' AND summary @@ ts_phrase('feeder connector') ORDER BY opened_at;
| ticket_id | opened | cell_id | summary |
|---|---|---|---|
| TT-20931 | 2026-03-14 | 142A | VSWR alarm after rain on 142A, antenna feeder connector corroded, connector replaced |
| TT-23305 | 2026-09-27 | 142B | VSWR alarm on 142B after the storm, water in the feeder connector, connector resealed |
| TT-24019 | 2026-11-06 | 142B | Dropped calls on 142B after VSWR alarm, water in the feeder connector at the antenna, connector replaced |
Telecom analytics is the analysis of the data a telecom network produces about its calls, its equipment and its customers: call detail records, alarms and log lines from network elements, and trouble tickets. Operators use it to find failing cells, plan capacity, spot fraud and see which customers were affected. Big data analytics in telecommunications is the same work over a whole network's records; SereneDB keeps them in one SQL engine.
Network troubleshooting from dropped calls and alarms, service KPIs such as the dropped-call rate and cell availability, capacity planning from traffic per cell, fraud detection in call records, and customer experience: which subscribers a fault hit, an input for churn analysis. The lab and the examples above run each of them as one SQL query over the same evening.
A call detail record is the record a telephone exchange or other network equipment writes for each call: the calling and called numbers, the start time, the duration and how the call ended. In SQL a CDR is one row; step 2 shows the table this page uses, with the serving cell and an end cause of normal, dropped or failed.
Count the dropped calls and divide by the connected calls, per cell and time window: count(*) FILTER (WHERE end_cause = 'dropped') over count(*), grouped with time_bucket, as the lab's first question does. Calls that never connected stay out of the denominator; they count against call setup instead.
From CDRs: the dropped-call rate, the share of call attempts that connect, and traffic in Erlangs per cell and hour. From the network log's state changes, with the lead() window function: the minutes each cell was out of service, and its availability over a period, as in the lab's "Outages" question. Each is a SQL aggregate, so the window and the grouping are yours.
Index the log's text and codes with an inverted index, then search in SQL: exact alarm codes and cell ids, words ranked by BM25, phrases, and matches within an edit distance for typos. Each hit is a row, so it can be counted per cell, grouped by hour or joined to the call records.
With an ASOF LEFT JOIN from the calls to the alarms, on the cell and the time. Each call gets the last alarm or clear on its cell at or before it, so the dropped-call rate can be grouped by the alarm that was active: 0.67 % before 142B's VSWR alarm, 6.67 % while it was active.
A SIM box places calls that arrive over the internet into the network through local SIM cards. The fraud example above looks for SIMs that make many calls, each to a different number, all from one cell, and receive none: one GROUP BY with HAVING, whose thresholds you tune to your own traffic.
Sum the call durations per cell and hour and divide by 3,600 seconds: one Erlang is one line busy for a whole hour. Then arg_max(hour, erlangs) returns each cell's busiest hour, as in the capacity example, where 142B peaks at 52.4 Erlangs.
As long as you choose: records stay in their table until deleted. A scheduled job can copy the CDRs past a period to Parquet files partitioned by day and delete them, and read_parquet still queries those files, on disk or in S3.
Yes. Grafana's built-in PostgreSQL data source connects to SereneDB on port 7890, and each panel is a SQL query, such as the dropped-call rate per 5 minutes. Grafana expands macros such as $__timeFilter into plain SQL before sending it.
Yes, under the Apache-2.0 license, with its source on GitHub. Its Docker image runs the walkthrough above with one command.
Get started with SereneDB
Start SereneDB, load your CDRs and network log, and query them in SQL from Grafana or any PostgreSQL client.