Database structure

From VoIPmonitor.org
Revision as of 16:49, 8 January 2026 by Admin (talk | contribs) (Rewrite: consolidated structure, removed redundancy, improved organization)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


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.

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

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?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Database_structure&oldid=10147"