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

Listing CDR Tables

To list all CDR-related tables in the database, use the following SQL command:

SHOW TABLES LIKE 'cdr%';

This returns all tables with names starting with cdr, including the main cdr table and all related tables such as cdr_next, cdr_rtp, cdr_sdp, cdr_proxy, and custom header tables (cdr_next_2, etc.).

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;

Filtering CDRs by SIP Caller IP or Domain

When the standard GUI API filters do not provide the needed functionality (e.g., filtering by SIP caller IP or domain), you can query the database directly using SQL.

ℹ️ Note: Important: This section describes direct SQL queries against the VoIPmonitor database. For the GUI API (HTTP API with api.php), see the WEB API documentation.

Filtering by SIP Caller IP

To find calls from a specific SIP caller IP, use the sipcallerip column. Since IP addresses are stored as integers, use INET_ATON() to convert the IP string to an integer for comparison.

-- Find CDRs from a specific SIP caller IP
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND sipcallerip = INET_ATON('192.168.1.50')
ORDER BY calldate DESC
LIMIT 100;

To filter by either SIP caller IP or SIP called IP:

-- Find CDRs where either SIP caller or called IP matches
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND (sipcallerip = INET_ATON('192.168.1.50')
       OR sipcalledip = INET_ATON('10.0.0.100'))
ORDER BY calldate DESC
LIMIT 100;

Filtering by SIP Domain

The caller_domain and called_domain columns store the domain part from the SIP From and To headers.

-- Find CDRs from a specific SIP domain
SELECT
    ID,
    calldate,
    caller,
    caller_domain,
    called,
    called_domain
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND caller_domain = 'example.com'
ORDER BY calldate DESC
LIMIT 100;

To filter by both SIP caller IP and domain:

-- Find CDRs matching IP or domain
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    caller_domain
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND (sipcallerip = INET_ATON('192.168.1.50')
       OR caller_domain = 'example.com')
ORDER BY calldate DESC
LIMIT 100;

Getting PCAP File Information (fbasename)

After finding CDR records, you may need to retrieve the associated PCAP file information. The fbasename column (derived from the SIP Call-ID) is stored in the cdr_next table.

-- Step 1: Find CDR IDs matching your criteria
SELECT ID, caller, called, calldate
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND sipcallerip = INET_ATON('192.168.1.50');

-- Step 2: Use the returned CDR IDs to get fbasename
-- Example: using IDs 12345, 12346, 12347 from Step 1
SELECT cdr_ID, fbasename
FROM cdr_next
WHERE cdr_ID IN (12345, 12346, 12347);

You can also combine both queries using a JOIN:

-- Single query to get CDR info with fbasename
SELECT
    c.ID,
    c.calldate,
    c.caller,
    c.called,
    INET_NTOA(c.sipcallerip) AS caller_ip,
    cn.fbasename
FROM cdr c
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE c.calldate >= '2024-01-01 00:00:00'
  AND c.sipcallerip = INET_ATON('192.168.1.50')
ORDER BY c.calldate DESC
LIMIT 100;

💡 Tip: Using fbasename with the API: Once you have the fbasename, you can use it with the GUI API to download or analyze the PCAP file. The fbasename is the filename used for the captured packets.

Performance Considerations

• Ensure the sipcallerip and sipcalledip columns are indexed for fast queries
• Use date range filters (WHERE calldate >= '...') to limit the amount of data
• For frequent IP-based filtering, consider creating indexed views or summary tables

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
a_rtcp_avgrtd_mult10 / b_rtcp_avgrtd_mult10	Average RTCP Roundtrip Delay (also called "packet delay" or "latency" in BI contexts), measured in milliseconds and multiplied by 10. Divide the value by 10 to get the actual delay in milliseconds. This metric represents the network roundtrip time as measured by RTCP, providing a direct measurement of latency between the endpoints and the monitoring point.

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. This table is linked to cdr via the cdr_ID foreign key (where cdr_next.cdr_ID = cdr.ID).

Column	Description
fbasename	String derived from SIP Call-ID, used to link PCAP files to CDR. Special characters in the Call-ID are replaced with underscores. This replacement behavior is controlled by the convertchar = : option in voipmonitor.conf
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.

Comparing Custom Header Activity Between Time Periods

A common use case is to find custom header values that had activity in one time period (e.g., last month) but no activity in another time period (e.g., this month), such as identifying call centers that stopped taking calls.

⚠️ Warning: Performance Consideration: Queries with long date ranges (several months of data) on large tables can be slow. Consider using the CDR Summary table for pre-aggregated data, or narrow your date ranges for better performance.

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

-- Result example: dynamic_table=1, dynamic_column=2
-- This means X-IntNum is stored in cdr_next_1.custom_header_2

-- Step 2: Get total connected minutes per X-IntNum value for a specific date range
SELECT
    SUM(cdr.connect_duration) AS connected_minutes,
    COUNT(*) AS call_count,
    cdr_next_1.custom_header_2 AS X_IntNum
FROM cdr
JOIN cdr_next_1 ON cdr_next_1.cdr_ID = cdr.ID
WHERE cdr.calldate >= '2025-11-01 00:00:00'
  AND cdr.calldate < '2025-12-01 00:00:00'
  AND cdr_next_1.calldate >= '2025-11-01 00:00:00'
  AND cdr_next_1.calldate < '2025-12-01 00:00:00'
GROUP BY X_IntNum
ORDER BY connected_minutes DESC;

To compare between two periods, run the query twice with different date ranges, then compare the results:

-- All X-IntNum values that had minutes in November 2025
SELECT DISTINCT
    custom_header_2 AS X_IntNum
FROM cdr
JOIN cdr_next_1 ON cdr.ID = cdr_next_1.cdr_ID
WHERE cdr.calldate >= '2025-11-01 00:00:00'
  AND cdr.calldate < '2025-12-01 00:00:00'
  AND cdr.connect_duration > 0;

-- All X-IntNum values that had minutes in December 2025
SELECT DISTINCT
    custom_header_2 AS X_IntNum
FROM cdr
JOIN cdr_next_1 ON cdr.ID = cdr_next_1.cdr_ID
WHERE cdr.calldate >= '2025-12-01 00:00:00'
  AND cdr.calldate < '2026-01-01 00:00:00'
  AND cdr.connect_duration > 0;

You can then compare the two result sets manually in a spreadsheet or use SQL to find values that disappeared:

-- Find X-IntNum values that had activity in November but not in December
SELECT DISTINCT
    nov.X_IntNum,
    nov.connected_minutes AS november_minutes
FROM (
    SELECT
        custom_header_2 AS X_IntNum,
        SUM(connect_duration) AS connected_minutes
    FROM cdr
    JOIN cdr_next_1 ON cdr.ID = cdr_next_1.cdr_ID
    WHERE cdr.calldate >= '2025-11-01 00:00:00'
      AND cdr.calldate < '2025-12-01 00:00:00'
      AND cdr.connect_duration > 0
    GROUP BY custom_header_2
) AS nov
WHERE nov.X_IntNum NOT IN (
    SELECT DISTINCT
        custom_header_2
    FROM cdr
    JOIN cdr_next_1 ON cdr.ID = cdr_next_1.cdr_ID
    WHERE cdr.calldate >= '2025-12-01 00:00:00'
      AND cdr.calldate < '2026-01-01 00:00:00'
      AND cdr.connect_duration > 0
)
ORDER BY november_minutes DESC;

ℹ️ Note: The GUI's Reports module (Call Summary and CDR Summary) does not currently support grouping by CDR Custom Headers. It is limited to standard fields such as sipcallerip, sipcalledip, payload, and last_sipresponse_id. Therefore, generating aggregated reports based on arbitrary SIP headers like X-IntNum requires direct SQL queries as shown above.

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)
sport	Source port number (smallint unsigned). See Troubleshooting section below if this column is missing.
dport	Destination port number (smallint unsigned). Used with sport for more precise stream identification. See Troubleshooting section below if this column is missing.
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;

Troubleshooting: Missing `sport` or `dport` Columns in `cdr_rtp`

By default, the cdr_rtp table stores RTP stream identifiers using only saddr (source IP) and daddr (destination IP). However, some environments require port numbers (sport and dport) for more precise stream identification.

Symptoms

If the sport column is missing from cdr_rtp, RTP streams from different, overlapping calls may be incorrectly merged in the database and GUI. This occurs when calls share the same source IP and port for one side of the RTP stream.

Common symptoms:

• Audio from one call appears in the graph or recording of another call
• GUI displays warnings about RTP streams being used in another call
• CDRs show mixed RTP statistics between unrelated calls

Verification

Connect to the VoIPmonitor database and verify the column existence:

EXPLAIN cdr_rtp;

Look for sport and dport columns in the output. If missing, you need to add them.

Solution: Adding the Missing Columns

To add the missing sport column and/or dport column:

Warning: ALTER TABLE operations can lock the table for several hours on large databases, preventing new CDRs from being inserted. It is highly recommended to perform this action during off-peak hours or a maintenance window.

Add the sport column:

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

Add the dport column (if also missing):

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

After adding the columns, restart the VoIPmonitor service:

systemctl restart voipmonitor

Finding Other Recommended Schema Changes

To find other recommended schema changes for your database version, restart the VoIPmonitor service and check the syslog for ALTER commands:

journalctl -u voipmonitor | grep ALTER

The service will log ALTER TABLE commands for any columns that should be added to match the current schema version.

Impact on Historical Data

• Existing records will have NULL values for the newly added sport and dport columns
• New calls will populate these columns correctly
• Historical statistics and reports may display inaccuracies until the data refreshes

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_sdp

Stores RTP media port and IP address information negotiated via SDP (Session Description Protocol) in SIP messages. This table captures the media endpoints as declared in the SDP Offer/Answer exchange, which is useful for understanding the exact media paths negotiated by endpoints before actual RTP flow begins. Requires save_sdp_ipport = yes in voipmonitor.conf.

ℹ️ Note: Difference from cdr_rtp:

• cdr_sdp: Stores ports and IPs declared in SDP (the negotiated media endpoints). This reflects what endpoints agreed to use.
• cdr_rtp: Stores actual observed RTP streams with packet statistics (loss, jitter, MOS, etc.). This reflects what endpoints actually sent on the wire.

Column	Type	Description
cdr_id	bigint	Foreign key to cdr table (links to the primary call record)
calldate	datetime	Timestamp when this SDP entry was captured
ip	int unsigned	Media IP address (stored as integer, use INET_NTOA() to convert)
port	smallint unsigned	Media port number negotiated in SDP
is_caller	tinyint	Indicates which side this SDP record belongs to (0 = called/callee, 1 = caller)

💡 Tip: Use Case - Viewing All Negotiated Media Ports: A single call may involve multiple streams (e.g., Audio + Video, or multiple re-INVITEs). This table shows all media ports negotiated in SDP, which can differ from actual RTP ports due to NAT or ICE renegotiations.

Example: Viewing all SDP-negotiated media ports for a call:

-- List all media ports negotiated in SDP for a specific call
SELECT
    calldate,
    INET_NTOA(ip) AS media_ip,
    port AS media_port,
    is_caller AS side
FROM cdr_sdp
WHERE cdr_id = 31;

Example: Comparing SDP-negotiated vs. actual RTP ports:

-- Compare declared SDP ports with actual observed RTP ports
SELECT
    'SDP declared' AS source,
    INET_NTOA(sdp.ip) AS media_ip,
    sdp.port AS media_port,
    sdp.is_caller AS side
FROM cdr_sdp sdp
WHERE sdp.cdr_id = 31

UNION ALL

SELECT
    'RTP observed',
    INET_NTOA(rtp.saddr),
    rtp.sport,
    1 AS side
FROM cdr_rtp rtp
WHERE rtp.cdr_ID = 31
ORDER BY side, source;

Prerequisite Configuration:

# Add to /etc/voipmonitor.conf
save_sdp_ipport = yes

# Restart service to apply
systemctl restart voipmonitor

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.

system Table

The system table stores system-wide configuration and keys used by VoIPmonitor.

manager_key

The system table contains a manager_key entry (identified by type='manager_key') which is required for manager/sensor operations. If the sniffer fails to start with the error "failed read rsa key," this indicates the manager key cannot be loaded from the database.

Important: Loading configuration from database (including the manager_key) is controlled by the mysqlloadconfig option in voipmonitor.conf (documented in Sniffer Configuration). If mysqlloadconfig=no is set, VoIPmonitor will NOT load the manager_key from the database, causing the "failed read rsa key" error on startup.

Check manager_key in database:

-- Query the manager_key from the system table
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G

Troubleshooting manager_key errors:

1. Check that mysqlloadconfig is NOT set to no in /etc/voipmonitor.conf
2. Query the system table to verify the manager_key exists using the SQL above
3. Check database connectivity permissions (VoIPmonitor needs read access to the system table)
4. Review syslog or journalctl for additional database error messages around the time of the startup failure
5. Ensure both probe and server have consistent configuration in /etc/voipmonitor.conf files

Synchronizing manager_key Between Multiple Databases

When you have multiple VoIPmonitor GUI instances sharing the same sensors (e.g., an old GUI and a new GUI both pointing to sensors using Local Processing mode), each database generates its own manager_key. If the manager_key values differ, the GUI that does not have the matching key cannot:

• Download PCAP files (downloads fail or return corrupt data)
• View SIP history from the sensor (empty history or errors)
• Access encrypted data from sensors

This issue commonly occurs during database migration when sensors write to a new database but an old GUI remains in use.

Symptoms of manager_key mismatch:

• Old GUI users cannot download PCAP files even though files exist
• "Unable to download PCAP" errors in browser or GUI
• SIP history appears empty or incomplete
• No explicit "failed read rsa key" error (this only occurs on sniffer startup)

Solution: Synchronize manager_key across databases

The fix is to copy the manager_key from the active database (where sensors are currently writing) to any database that needs GUI access to the same sensors.

Step 1

Retrieve the manager_key from the active database

-- On the database server that sensors are actively connected to SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G

Note the values for key and iv from the content field (JSON format).

Step 2

Update the manager_key on the old database

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

Step 3

Log out and log back in

Users must log out of the old GUI and log back in for the key synchronization to take effect.

Step 4

Verify the fix

Attempt to download a PCAP file through the old GUI to verify the fix worked.

Important Notes:

• Do NOT manually generate or modify the key or iv fields - they are encrypted credentials
• Always backup the system table before making changes
• This synchronization only needs to happen once - after migration, all GUIs will use the same key
• Each database maintains its own manager_key in the system table - they are not automatically synchronized

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. RTCP-reported metrics include a_rtcp_loss/b_rtcp_loss (endpoints' loss reports), a_rtcp_avgfr_mult10/b_rtcp_avgfr_mult10 (average fraction loss), a_rtcp_avgjitter_mult10/b_rtcp_avgjitter_mult10 (jitter from RTCP), and a_rtcp_avgrtd_mult10/b_rtcp_avgrtd_mult10 (RTCP Roundtrip Delay, also called "packet delay" or "latency" in BI contexts - measured in milliseconds, divide by 10 for actual value). Related tables: cdr_next (1-to-1 with cdr, linked via cdr_ID foreign key; contains fbasename from SIP Call-ID for PCAP file linking, where special characters are replaced with underscores via convertchar option in voipmonitor.conf), cdr_rtp (per-stream RTP stats), cdr_sdp (RTP media ports negotiated in SDP - query with `select calldate,INET_NTOA(ip),port,is_caller from cdr_sdp where cdr_id=31;`), 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. The system table stores system-wide configuration including manager_key (type='manager_key') which is required for manager/sensor operations. If the sniffer fails to start with "failed read rsa key" error, check that mysqlloadconfig in voipmonitor.conf is NOT set to no and query the manager_key with `SELECT * FROM voipmonitor.system WHERE type='manager_key'\G`. SYNCHRONIZING MANAGER_KEY BETWEEN MULTIPLE DATABASES: When you have multiple VoIPmonitor GUI instances sharing the same sensors (e.g., old GUI and new GUI after database migration), each database generates its own manager_key. If manager_key values differ, the GUI without the matching key cannot download PCAP files or view SIP history. Common symptom: "Unable to download PCAP" errors in old GUI after migration to new database with shared sensors. FIX: Query `SELECT * FROM voipmonitor.system WHERE type='manager_key'\G` on the active database (where sensors write), copy the key and iv values from the content field, then update on the old database with `UPDATE voipmonitor.system SET content = '{"key":"<key_from_active>","iv":"<iv_from_active>"}' WHERE type = 'manager_key';`. After updating, users must log out and log back in to the old GUI for synchronization to take effect. Use SHOW TABLES LIKE 'cdr%'; to list all CDR-related tables.

Keywords: database, schema, cdr, mysql, mariadb, packet loss, jitter, mos, rtcp, rtcp_loss, rtcp_avgjitter, rtcp_avgrtd, rtcp roundtrip delay, roundtrip delay, a_rtcp_avgrtd_mult10, b_rtcp_avgrtd_mult10, packet delay, latency, network latency, bi data, business intelligence, sip call id, fbasename, a_lost, b_lost, convertchar, user-agent, cdr_next, cdr_rtp, cdr_sdp, sdp, rtp ports, media ports, 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, system table, manager_key, manager_key type, failed read rsa key, mysqlloadconfig, mysqlloadconfig=no, manager_key synchronization, sync manager_key, multiple databases, shared sensors, old gui new gui, database migration, unable to download pcap, sip history empty, pcap download failure, encryption key mismatch, filter by sip caller ip, filter by sip domain, sipcallerip, sipcalledip, caller_domain, called_domain, INET_ATON, INET_NTOA, sip ip filtering, domain filtering, save_sdp_ipport

Key Questions:

• How do I query all RTP media ports negotiated in SDP for a call?
• Which table stores SDP-negotiated RTP ports and IPs?
• What is the cdr_sdp table used for?
• How do I view all media ports for a call using cdr_sdp table?
• What is the difference between cdr_sdp and cdr_rtp tables?
• How do I filter CDRs by SIP caller IP?
• How do I filter CDRs by SIP domain?
• What is the correct column name for SIP caller IP in cdr table?
• How do I convert IP address to integer for sipcallerip comparison?
• How do I use INET_ATON() to filter by IP address?
• How do I get fbasename for a CDR?
• Which table stores the fbasename for PCAP file linking?
• How do I join cdr and cdr_next to get fbasename with CDR data?
• How do I filter CDRs by caller_domain or called_domain?
• Where is the manager_key stored in the VoIPmonitor database?
• Why does the sniffer fail to start with "failed read rsa key" error?
• How do I fix the "failed read rsa key" error on startup?
• What does mysqlloadconfig=no do in voipmonitor.conf?
• How do I check if the manager_key exists in the database?
• What is the system table in VoIPmonitor?
• How do I query the manager_key from the system table?
• 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?
• How do I synchronize manager_key between multiple VoIPmonitor databases?
• Why cannot the old GUI download PCAP files after migrating to a new database with shared sensors?
• How do I fix PCAP download failures in old GUI after database migration?
• Why is SIP history empty or missing in one GUI but not another?
• How do I copy manager_key from one database to another?
• What causes manager_key mismatch between multiple GUI instances?
• How do I share sensors between old and new GUI instances?
• How do I fix encryption key mismatch between databases?