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;

ℹ️ Note: CDR Custom Headers Storage: CDR-level custom SIP headers (one value per call) are stored dynamically in the cdr_next_X tables, not in the main cdr table. To find where a specific header is stored, you must query the cdr_custom_headers mapping table.

cdr_custom_headers

Lookup table that maps custom SIP header names to their storage locations in the cdr_next_X tables. You must query this table first to find the correct table and column for your custom header.

Column	Description
header_field	The name of the custom SIP header (e.g., 'X-IntNum')
dynamic_table	The table number (e.g., 2 means cdr_next_2)
dynamic_column	The column number (e.g., 1 means custom_header_1)

💡 Tip: The system automatically creates new cdr_next_X tables when needed and updates this mapping table. Each header gets its own dedicated column, not each call.

Finding CDR Custom Header Storage

Because CDR custom headers are stored in dynamically created tables, you must use the cdr_custom_headers table to find the correct table and column.

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

This query returns:

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

Querying CDRs with Custom Header Values

Once you have the table and column information, you can query the cdr_next_X table for calls containing the desired header value.

-- Example: Query cdr_next_2 for custom_header_1
SELECT
    cdr.id,
    cdr.calldate,
    cdr.caller,
    cdr.called,
    cdr_next_2.custom_header_1 AS X_IntNum
FROM cdr
JOIN cdr_next_2 ON cdr.id = cdr_next_2.cdr_ID
WHERE cdr_next_2.custom_header_1 IS NOT NULL
ORDER BY cdr.calldate DESC
LIMIT 10;

Complete Example Workflow

-- Example: Find CDRs where X-Customer-ID = '12345'

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

-- Result example: dynamic_table=2, dynamic_column=1
-- This means the header is stored in cdr_next_2.custom_header_1

-- Step 2: Query CDRs matching the custom header value
SELECT
    c.ID,
    c.calldate,
    c.caller,
    c.called,
    c.duration,
    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'
ORDER BY c.calldate DESC
LIMIT 100;

💡 Tip: You can use variables or dynamic SQL to combine both steps into a single query when working programmatically.

cdr_rtp

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

Column	Description
cdr_ID	Foreign key to cdr table (links to the primary call record)
ssrc	RTP Synchronization Source identifier (unique identifier for the stream)
saddr	Source IP address (stored as integer, use INET_NTOA() to convert)
daddr	Destination IP address (stored as integer, use INET_NTOA() to convert)
payload	Codec payload type number. Video streams are stored with an offset of +10000 to distinguish them from audio streams
received	Number of RTP packets received
lost	Number of RTP packets lost (detected by sequence number gaps)
jitter	Jitter value (varies by schema version)

💡 Tip:

Example: Querying video stream quality:

-- Find calls with high packet loss on video RTP streams
SELECT
    cdr.ID,
    cdr.calldate,
    cdr.caller,
    cdr.called,
    INET_NTOA(cdr_rtp.saddr) AS source_ip,
    INET_NTOA(cdr_rtp.daddr) AS dest_ip,
    cdr_rtp.payload,
    cdr_rtp.received,
    cdr_rtp.lost,
    (cdr_rtp.lost / (cdr_rtp.received + cdr_rtp.lost)) * 100 AS loss_percentage
FROM cdr
JOIN cdr_rtp ON cdr.ID = cdr_rtp.cdr_ID
-- Filter for video streams (payload >= 10000)
WHERE cdr_rtp.payload >= 10000
  AND cdr.calldate >= '2024-01-01'
ORDER BY loss_percentage DESC
LIMIT 20;

Example: Finding all RTP streams in a call:

-- List all RTP streams (audio and video) for a specific call
SELECT
    payload,
    CASE
        WHEN payload >= 10000 THEN 'Video'
        ELSE 'Audio'
    END AS stream_type,
    INET_NTOA(saddr) AS source_ip,
    INET_NTOA(daddr) AS dest_ip,
    received,
    lost,
    (lost / (received + lost)) * 100 AS loss_perc
FROM cdr_rtp
WHERE cdr_ID = 12345;

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 between caller and callee. Each proxy hop creates one row, allowing you to trace the full call path including intermediate servers.

Column	Description
cdr_ID	Foreign key to cdr table (links to the primary call record)
dst	Proxy IP address (stored as integer, use INET_NTOA() to convert)

💡 Tip: Proxy Chain Tracing: Multiple proxies may be involved in a single call (e.g., caller → SBC → gateway → callee). This table records each intermediate IP, allowing you to troubleshoot call routing issues or filter calls by specific proxy servers.

Example: Finding all proxy IPs for a specific call:

-- List all proxies a call passed through
SELECT
    cdr_ID,
    INET_NTOA(dst) AS proxy_ip
FROM cdr_proxy
WHERE cdr_ID = 12345;

Example: Finding calls that used a specific proxy:

-- Find all calls that went through a specific proxy server
SELECT
    c.ID,
    c.calldate,
    c.caller,
    c.called,
    INET_NTOA(c.sipcallerip) AS caller_ip,
    INET_NTOA(c.sipcalledip) AS called_ip,
    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')
ORDER BY c.calldate DESC
LIMIT 100;

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;

cdr_siphistory

Stores detailed SIP request and response history for each call, including subdialog methods such as `REFER`, `BYE`, `CANCEL`, `OPTIONS`, `SUBSCRIBE`, `NOTIFY`, and `MESSAGE`. This table enables filtering CDRs by specific SIP methods via the GUI filter dropdown. Requires save_sip_history = all in voipmonitor.conf.

Column	Description
cdr_ID	Foreign key to cdr table (links to the primary call record)
method	SIP request method (e.g., INVITE, REFER, BYE, CANCEL, OPTIONS, etc.)
calldate	Timestamp of the SIP message
callid	SIP Call-ID header value
cseq	SIP CSeq (Command Sequence) number

Prerequisite Configuration: Configure in /etc/voipmonitor.conf:

save_sip_history = all

Restart the voipmonitor sniffer after changing this configuration.

Example: Find all calls containing a SIP REFER method:

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

Example: Find calls with OPTIONS or SUBSCRIBE messages (for troubleshooting qualify pings):

SELECT
    cdr_ID,
    callid,
    method,
    calldate
FROM cdr_siphistory
WHERE calldate > CURDATE() - INTERVAL 1 DAY
  AND method IN ('OPTIONS', 'SUBSCRIBE')
ORDER BY calldate DESC
LIMIT 20;

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 RTP stats), cdr_proxy (intermediate SIP proxy IPs), cdr_ua (User-Agent lookup). The cdr_rtp table stores individual RTP stream statistics; video streams are identified by payload >= 10000 (VoIPmonitor adds +10000 offset to distinguish video from audio). The cdr_proxy table stores proxy IP addresses a call traversed; query with select INET_NTOA(dst) from cdr_proxy where cdr_ID=123; or join cdr_proxy on cdr.ID = cdr_proxy.cdr_ID to find all calls going through a specific proxy. CDR custom headers require querying cdr_custom_headers to find dynamic table/column mappings, then querying cdr_next_X tables. SIP message custom headers use sip_msg_custom_headers and 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, cdr_proxy, proxy ip, intermediate proxy, sip proxy chain, sip_msg, custom headers, cdr_custom_headers, cdr_next_X, sip_msg_custom_headers, sip_msg_next, video stream, payload, ssrc, saddr, daddr

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 video streams or video call quality using cdr_rtp?
• How do I identify video streams in the database (payload filtering)?
• How do I find calls that went through a specific proxy server?
• How do I find all intermediate proxy IPs for a specific call?
• Which table stores SIP proxy IP addresses a call traversed?
• How do I find calls that transited through a specific IP address?
• How do I query CDRs with a custom header value?
• How do I find which cdr_next_X table stores my custom header?
• How do I query SIP messages with a custom header value?
• Why is my custom SIP header not in the cdr table?
• How do I find the database table and column for a custom SIP header added via web interface?
• A custom SIP header cannot be found in the main cdr table for querying - how do I query it?
• Where are CDR custom headers stored in the database?
• How do I use cdr_custom_headers table to find custom header data?