Database structure

This document provides an overview of the most important tables and columns in the VoIPmonitor database schema. It is intended as a reference for administrators, developers, or anyone directly querying the database.

The core of the VoIPmonitor database is the cdr (Call Detail Record) table, which stores one primary record for each monitored call. Many other tables are linked to cdr to store additional, more detailed information.

Database Schema Overview

The following diagram shows the relationships between the main VoIPmonitor database tables:

Understanding Column Naming Conventions

Before diving into the columns, it's important to understand these common naming patterns:

Pattern	Meaning	Example
a_ prefix	Caller's media stream (A-leg, RTP sent from caller)	a_lost, a_mos_f1_mult10
b_ prefix	Callee's media stream (B-leg, RTP sent from callee)	b_lost, b_mos_f1_mult10
_mult10	Divide stored value by 10 to get real value	jitter_mult10 = 15 means jitter = 1.5
_mult100	Divide stored value by 100 to get real value	Used for percentage values
_mult1000	Divide stored value by 1000 to get real value	packet_loss_perc_mult1000

The cdr Table

This is the main table containing the primary information for every call.

General Call Information

Column	Type	Description
ID	bigint	The unique primary key for the record
id_sensor	int	The ID of the sensor that processed this call (corresponds to id_sensor in voipmonitor.conf)
calldate	datetime	The start time of the call, measured from the first INVITE packet
callend	datetime	The end time of the call, measured from the last packet associated with the call
bye	tinyint	Indicates how the call was terminated (1 = normal termination with BYE)
whohanged	enum	Indicates which party sent the BYE message first (caller or callee)
lastSIPresponse_id	int	Foreign key to cdr_sip_response table for the last final SIP response

Call Timing & Duration

Column	Type	Description
duration	int	Total call length in seconds (callend - calldate)
connect_duration	int	Connected portion length in seconds (from first 200 OK to callend)
progress_time	int	Time from INVITE to first provisional response (e.g., 180 Ringing). Also known as Post-Dial Delay (PDD)
first_rtp_time	int	Time from call start to first RTP packet detection

Participant Information

Column	Type	Description
caller / called	varchar	User part of the number from From and To SIP headers
caller_domain / called_domain	varchar	Domain part from From and To SIP headers
caller_reverse / called_reverse	varchar	Reversed strings for efficient LIKE '%search' queries
callername	varchar	Display name from the From SIP header
sipcallerip / sipcalledip	int unsigned	Source/destination IP addresses stored as integers (use INET_NTOA() to convert)
a_ua_id / b_ua_id	int	Foreign keys to cdr_ua table for User-Agent strings

ℹ️ Note: How User-Agent is extracted:

• Caller (a_ua_id): From the User-Agent header in the SIP INVITE request
• Callee (b_ua_id): From the User-Agent header in the SIP response (typically 200 OK)
• Blank values indicate the User-Agent header was not present

Example: Converting IP addresses in SQL queries:

-- Get calls with readable IP addresses
SELECT
    ID,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip,
    duration
FROM cdr
WHERE calldate >= '2024-01-01'
LIMIT 10;

Network Quality Metrics

Packet Loss

Column	Description
a_lost / b_lost	Total RTP packets lost (detected by sequence number gaps)
lost	Higher value between a_lost and b_lost
packet_loss_perc_mult1000	Maximum packet loss percentage, multiplied by 1000
a_sl1 - a_sl10 / b_sl1 - b_sl10	Distribution of consecutive packet loss events (sl1 = single packet lost, sl5 = 5 packets in a row, etc.)

Jitter & Delay (PDV)

Column	Description
a_avgjitter_mult10 / b_avgjitter_mult10	Average jitter per RFC 3550, multiplied by 10
a_maxjitter / b_maxjitter	Maximum jitter value observed during the call
jitter_mult10	Higher maxjitter value between streams, multiplied by 10
a_d50 - a_d300 / b_d50 - b_d300	Packet Delay Variation (PDV) distribution buckets (d50 = 0-50ms, d70 = 50-70ms, etc.)

MOS (Mean Opinion Score)

Column	Description
a_mos_f1_mult10 / b_mos_f1_mult10	Parametric MOS with 50ms fixed jitter buffer, multiplied by 10
a_mos_f2_mult10 / b_mos_f2_mult10	MOS with 200ms fixed jitter buffer, multiplied by 10
a_mos_adapt_mult10 / b_mos_adapt_mult10	MOS with adaptive jitter buffer (up to 500ms), multiplied by 10
mos_min_mult10	Lowest MOS score between A and B streams, multiplied by 10

RTCP Reported Metrics

These metrics come from RTCP Receiver Reports sent by the endpoints, not calculated by the sniffer.

Column	Description
a_rtcp_loss / b_rtcp_loss	Total packets lost as reported by endpoint via RTCP
a_rtcp_avgfr_mult10 / b_rtcp_avgfr_mult10	Average fraction loss from RTCP, multiplied by 10
a_rtcp_avgjitter_mult10 / b_rtcp_avgjitter_mult10	Average jitter from RTCP, multiplied by 10

Understanding Sniffer vs RTCP Packet Loss

VoIPmonitor provides two different packet loss metrics measured from different network locations:

Interpretation Guide:

Scenario	Likely Cause
RTCP loss > Sniffer loss	Network issue after the sniffer (Wi-Fi, last-mile, endpoint network)
Sniffer loss > RTCP loss	Monitoring point dropping packets (CPU overload, interface saturation) or RTCP reports not captured
High RTCP loss, good MOS	Packet Loss Concealment (PLC) effectively hiding scattered losses

Troubleshooting tips:

• Trust RTCP loss for user experience issues - it reflects what the device actually received
• Use sniffer loss for network health monitoring in the segment you control
• When RTCP loss is higher, investigate the network path between sniffer and endpoint

Other Columns

Column	Description
dscp	DSCP values from SIP and RTP packets for QoS analysis
payload	Codec payload type number used during the call

Important Related Tables

cdr_next

A 1-to-1 extension of the cdr table for additional data, keeping the main table smaller.

Column	Description
fbasename	String derived from SIP Call-ID, used to link PCAP files to CDR
match_header	Content of custom header (from matchheader in voipmonitor.conf) for linking call legs

Example: Finding PCAP filename for a call:

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;

cdr_rtp

Stores detailed statistics for each individual RTP stream within a call. Multiple rows per call (one per stream).

cdr_dtmf

Stores detected DTMF key presses. Requires dtmf2db = yes in voipmonitor.conf.

cdr_proxy

Stores IP addresses of all SIP proxies the call traversed.

cdr_sip_response

Lookup table for unique SIP response texts (e.g., "404 Not Found"). The cdr table references by ID to save space.

cdr_ua

Lookup table for User-Agent strings of SIP devices.

Example: Getting User-Agent strings for calls:

SELECT
    c.ID,
    c.caller,
    c.called,
    ua_a.ua AS caller_user_agent,
    ua_b.ua AS callee_user_agent
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;

AI Summary for RAG

Summary: Reference for VoIPmonitor database schema. The cdr table stores one record per call with timing, participant info, and quality metrics. Column naming: a_ = caller stream, b_ = callee stream, _multNN = divide by NN. Key metrics include packet loss (a_lost), jitter (a_avgjitter_mult10), and MOS scores. RTCP metrics (a_rtcp_loss) show endpoint-reported loss vs sniffer-calculated loss. Related tables: cdr_next (Call-ID via fbasename), cdr_rtp (per-stream stats), cdr_ua (User-Agent lookup).

Keywords: database, schema, cdr, mysql, mariadb, packet loss, jitter, mos, rtcp, a_lost, b_lost, fbasename, user-agent, cdr_next, cdr_rtp

Key Questions:

• What do the a_ and b_ prefixes mean in the cdr table?
• How do I convert sipcallerip to a readable IP address?
• What is the difference between sniffer loss and RTCP loss?
• How do I get the Call-ID for a call from the database?
• Which table stores the User-Agent of phones?