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. For a detailed explanation of the difference between sniffer-calculated and RTCP-reported metrics, see Understanding Sniffer vs RTCP Packet Loss.

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

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;

SIP Message Custom Headers

VoIPmonitor can capture custom SIP headers from individual SIP messages (not just CDR-level headers). These are stored dynamically in the sip_msg_next_X tables, where each custom header gets its own dedicated column.

ℹ️ Note: CDR vs SIP Message Headers:

• CDR custom headers are stored in the cdr_next table for call-level data (one value per call)
• SIP message custom headers are stored in sip_msg_next_X tables for individual SIP messages (one value per message)

Finding the Table and Column for a Custom Header

Because custom SIP headers are stored in dynamically created tables, you must first query the sip_msg_custom_headers table to find which table and column contains your header.

-- Step 1: Find the dynamic table and column for your custom header
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
WHERE header_field = 'X-My-Custom-Header';

This query returns:

• dynamic_table: The table number (e.g., 1 means sip_msg_next_1)
• dynamic_column: The column number (e.g., 2 means custom_header_2)

Querying SIP Messages with Custom Header Values

Once you have the table and column information, you can query the sip_msg_next_X table for SIP messages containing the desired header value.

-- Example: Query sip_msg_next_1 for custom_header_2 = 'desired-value'
-- Step 2: Retrieve SIP messages matching the custom header value
SELECT
    sip_msg_ID,
    time,
    custom_header_2 AS header_value
FROM sip_msg_next_1
WHERE custom_header_2 = 'desired-value'
  AND time > NOW() - INTERVAL 15 MINUTE;

💡 Tip: You can also join this with other SIP message tables if needed. The SIP messages are linked to CDRs through the sip_msg_ID column and related message tables.

Complete Example Workflow

-- Example: Find SIP messages with X-Customer-ID = '12345' in the last 1 hour

-- Step 1: Find where X-Customer-ID is stored
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
WHERE header_field = 'X-Customer-ID';

-- Example result: dynamic_table=3, dynamic_column=1

-- Step 2: Query the appropriate table
SELECT
    sm.sip_msg_ID,
    sm.time,
    sm.custom_header_1 AS customer_id
FROM sip_msg_next_3 sm
WHERE sm.custom_header_1 = '12345'
  AND sm.time > NOW() - INTERVAL 1 HOUR
ORDER BY sm.time DESC;

Configuration Requirements

To capture custom SIP headers, configure them in /etc/voipmonitor.conf:

[general]
# List custom headers to capture from SIP messages
sip_headers = X-Customer-ID, X-Branch-Code, X-Transaction-ID

After modifying the configuration, restart the sensor:

service voipmonitor restart

The system will automatically create new columns in the appropriate sip_msg_next_X table and update the sip_msg_custom_headers mapping table.

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), MOS scores, and RTCP-reported metrics. Related tables: cdr_next (Call-ID via fbasename), cdr_rtp (per-stream stats), cdr_ua (User-Agent lookup). SIP message custom headers require querying sip_msg_custom_headers to find dynamic table/column mappings, then querying sip_msg_next_X tables.

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

Key Questions:

• What do the a_ and b_ prefixes mean in the cdr table?
• How do I convert sipcallerip to a readable IP address?
• How do I get the Call-ID for a call from the database?
• Which table stores the User-Agent of phones?
• How do I query SIP messages with a custom header value?
• How do I find which table stores a custom SIP header?