Database structure

Database schema reference for VoIPmonitor. The cdr table stores one record per call; related tables store RTP statistics, custom headers, and metadata.

Schema Overview

-- List all CDR-related tables
SHOW TABLES LIKE 'cdr%';

Column Naming Conventions

Pattern	Meaning	Example
a_	Caller's stream (A-leg)	a_lost, a_mos_f1_mult10
b_	Callee's stream (B-leg)	b_lost, b_mos_f1_mult10
_mult10	Divide by 10	jitter_mult10 = 15 → jitter = 1.5
_mult100	Divide by 100	Percentage values
_mult1000	Divide by 1000	packet_loss_perc_mult1000

The cdr Table

Call Information & Timing

Column	Description
ID	Primary key
id_sensor	Sensor ID (from voipmonitor.conf)
calldate / callend	Start/end timestamps
duration	Total length in seconds
connect_duration	Connected time (after 200 OK)
progress_time	Time to first provisional response (PDD)
bye	1 = normal termination with BYE
whohanged	Which party sent BYE (caller/callee)
lastSIPresponse_id	FK to cdr_sip_response

Participant Information

Column	Description
caller / called	Numbers from From/To headers
caller_domain / called_domain	Domain parts
sipcallerip / sipcalledip	IPs stored as integers (use INET_NTOA())
a_ua_id / b_ua_id	FK to cdr_ua (User-Agent)

Quality Metrics

Packet Loss

Column	Description
a_lost / b_lost	Total RTP packets lost
packet_loss_perc_mult1000	Max loss percentage × 1000
a_sl1 - a_sl10	Distribution of consecutive loss events

Jitter & Delay

Column	Description
a_avgjitter_mult10	Average jitter × 10
a_maxjitter	Maximum jitter observed
a_d50 - a_d300	PDV distribution buckets (0-50ms, 50-70ms, etc.)

MOS (Mean Opinion Score)

Column	Description
a_mos_f1_mult10	MOS with 50ms fixed buffer × 10
a_mos_f2_mult10	MOS with 200ms fixed buffer × 10
a_mos_adapt_mult10	MOS with adaptive buffer × 10
mos_min_mult10	Lowest MOS between A/B streams × 10

RTCP-Reported Metrics

These come from endpoint RTCP reports, not calculated by the sniffer. See Sniffer vs RTCP Packet Loss.

Column	Description
a_rtcp_loss	Packets lost (reported by endpoint)
a_rtcp_avgfr_mult10	Average fraction loss × 10
a_rtcp_avgjitter_mult10	Average jitter × 10
a_rtcp_avgrtd_mult10	Roundtrip delay (latency) in ms × 10

Related Tables

cdr_next (1:1 with cdr)

Extension table linked via cdr_ID.

Column	Description
fbasename	Derived from SIP Call-ID; used to link PCAP files. Special characters replaced with underscores (convertchar in config)
match_header	Content of custom header for linking call legs (matchheader in config)

cdr_rtp (Per-Stream RTP Stats)

One row per RTP stream. Multiple streams per call.

Column	Description
cdr_ID	FK to cdr
ssrc	RTP Synchronization Source identifier
saddr / daddr	Source/destination IP (as integer)
sport / dport	Source/destination port
payload	Codec type. Video streams: payload ≥ 10000 (offset applied)
received / lost	Packet counts

💡 Tip:

cdr_sdp (SDP-Negotiated Ports)

Stores media endpoints declared in SDP. Requires save_sdp_ipport = yes.

Column	Description
ip / port	Media IP and port from SDP
is_caller	1 = caller side, 0 = callee side

ℹ️ Note: cdr_sdp stores what endpoints agreed to use in SDP; cdr_rtp stores what was actually sent.

cdr_proxy (Proxy Chain)

IP addresses of SIP proxies the call traversed.

cdr_siphistory (SIP Method History)

Stores SIP requests/responses including REFER, BYE, CANCEL, OPTIONS, etc. Requires save_sip_history = all.

cdr_ua / cdr_sip_response (Lookup Tables)

Lookup tables for User-Agent strings and SIP response texts.

Common SQL Queries

Basic CDR Query with IP Conversion

SELECT
    ID, caller, called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip,
    duration
FROM cdr
WHERE calldate >= '2024-01-01'
  AND sipcallerip = INET_ATON('192.168.1.50')
LIMIT 10;

Get PCAP Filename (fbasename)

SELECT c.ID, c.caller, c.called, cn.fbasename
FROM cdr c
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE c.ID = 12345;

Get User-Agent Strings

SELECT c.ID, ua_a.ua AS caller_ua, ua_b.ua AS callee_ua
FROM cdr c
LEFT JOIN cdr_ua ua_a ON c.a_ua_id = ua_a.ID
LEFT JOIN cdr_ua ua_b ON c.b_ua_id = ua_b.ID
WHERE c.calldate >= CURDATE() - INTERVAL 1 DAY
LIMIT 10;

Find Calls Through Specific Proxy

SELECT c.ID, c.calldate, c.caller, c.called,
       INET_NTOA(cp.dst) AS proxy_ip
FROM cdr c
JOIN cdr_proxy cp ON c.ID = cp.cdr_ID
WHERE cp.dst = INET_ATON('192.168.1.100')
LIMIT 100;

Query Video Stream Quality

SELECT cdr_ID, payload,
       INET_NTOA(saddr) AS src_ip,
       received, lost,
       (lost / (received + lost)) * 100 AS loss_pct
FROM cdr_rtp
WHERE payload >= 10000  -- Video streams only
  AND cdr_ID = 12345;

Find Calls with Specific SIP Method (REFER)

SELECT cdr_ID, callid, calldate
FROM cdr_siphistory
WHERE method = 'REFER'
  AND calldate > '2024-01-01'
GROUP BY cdr_ID;

Custom Headers

CDR-Level Custom Headers

CDR custom headers are stored in cdr_next_X tables. Use cdr_custom_headers to find the location:

-- Step 1: Find table/column for header
SELECT dynamic_table, dynamic_column
FROM cdr_custom_headers
WHERE header_field = 'X-Customer-ID';
-- Result: dynamic_table=2, dynamic_column=1 → cdr_next_2.custom_header_1

-- Step 2: Query the data
SELECT c.ID, c.calldate, cn.custom_header_1 AS customer_id
FROM cdr c
JOIN cdr_next_2 cn ON c.ID = cn.cdr_ID
WHERE cn.custom_header_1 = '12345';

ℹ️ Note: The GUI's Reports module does not support grouping by custom headers. Use direct SQL queries.

cdr_custom_headers Table Structure

The cdr_custom_headers table stores the configuration for custom header extraction:

Column	Description
header_field	SIP header name to capture
dynamic_table	Target table number (cdr_next_X)
dynamic_column	Target column number (custom_header_X)
type	Extraction type (first/last/nth)
nth_occurrence	(New in 35.x) Specific occurrence number to extract when type is "nth" (e.g., 2 for second occurrence)

ℹ️ Note: The nth_occurrence column uses packet timestamps for accurate ordering, ensuring correct results even when packet time-based reordering is disabled in the sniffer.

SIP Message Custom Headers

Per-message headers are stored in sip_msg_next_X tables. Use sip_msg_custom_headers to find the location.

System Table

The system table stores configuration including manager_key for sensor operations.

SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G

⚠️ Warning: If sniffer fails with "failed read rsa key", check that mysqlloadconfig is NOT set to no in voipmonitor.conf.

Synchronizing manager_key Between Databases

When multiple GUI instances share sensors (e.g., after database migration), mismatched manager_key values cause PCAP download failures.

Symptoms: "Unable to download PCAP" errors, empty SIP history in one GUI.

Fix:

-- On active database: get the key
SELECT content FROM voipmonitor.`system` WHERE type='manager_key';

-- On old database: update the key
UPDATE voipmonitor.`system`
SET content = '{"key":"<key_from_active>","iv":"<iv_from_active>"}'
WHERE type = 'manager_key';

Users must log out and log back in after the update.

Troubleshooting

Missing sport/dport in cdr_rtp

If RTP streams from overlapping calls are incorrectly merged, add the missing columns:

ALTER TABLE cdr_rtp ADD `sport` smallint unsigned DEFAULT NULL AFTER `saddr`;
ALTER TABLE cdr_rtp ADD `dport` smallint unsigned DEFAULT NULL AFTER `daddr`;

⚠️ Warning: ALTER TABLE can lock large tables for hours. Run during maintenance windows.

To find other recommended schema changes:

journalctl -u voipmonitor | grep ALTER

See Also

• CDR View (GUI)
• Database Troubleshooting
• Database Configuration
• Web API Documentation

AI Summary for RAG

Summary: VoIPmonitor database schema reference. Main table cdr stores one record per call with timing, participants, and quality metrics. Column naming: a_=caller, b_=callee, _multNN=divide by NN. Key tables: cdr_next (1:1, contains fbasename for PCAP linking), cdr_rtp (per-stream stats, video streams have payload≥10000), cdr_proxy (proxy chain IPs), cdr_sdp (SDP-negotiated ports), cdr_siphistory (SIP method history, requires save_sip_history=all). Custom headers stored in cdr_next_X tables; query cdr_custom_headers for mappings. System table stores manager_key; mismatch causes PCAP download failures.

Keywords: database, schema, cdr, mysql, cdr_next, fbasename, cdr_rtp, video payload, cdr_proxy, cdr_sdp, cdr_siphistory, INET_NTOA, INET_ATON, sipcallerip, custom headers, cdr_custom_headers, manager_key, packet loss, jitter, mos, rtcp, a_rtcp_avgrtd_mult10, latency

Key Questions:

• What do a_ and b_ prefixes mean in cdr table columns?
• How do I convert sipcallerip to readable IP address?
• How do I get the PCAP filename (fbasename) for a call?
• How do I query video streams in cdr_rtp (payload >= 10000)?
• How do I find calls that went through a specific proxy?
• How do I find calls with REFER or other SIP methods?
• How do I query CDR custom headers?
• Why does "failed read rsa key" error occur on startup?
• How do I sync manager_key between multiple databases?
• Why can't old GUI download PCAP files after database migration?