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.

Understanding Column Naming Conventions

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

`a_` prefix: Refers to the "A-leg" or the caller's media stream (RTP sent from the caller).
`b_` prefix: Refers to the "B-leg" or the callee's media stream (RTP sent from the callee).
`_mult10` / `_mult100` / `_mult1000`: Indicates that the stored integer value must be divided by this number to get the real decimal value. For example, a `jitter_mult10` value of `15` represents a real jitter of `1.5`.

The `cdr` Table

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

General Call Information

`ID`: The unique primary key for the record.
`id_sensor`: The ID of the sensor that processed this call, corresponding to `id_sensor` in `voipmonitor.conf`.
`calldate`: The start time of the call, typically measured from the first `INVITE` packet.
`callend`: The end time of the call, measured from the last packet associated with the call.
`bye`: An indicator of how the call was terminated. `1` typically means a normal termination with a `BYE` request.
`whohanged`: Indicates which party sent the `BYE` message first (caller or callee).
`lastSIPresponse_id`: A reference to the `cdr_sip_response` table, linking to the last final SIP response for the call.

Call Timing & Duration

`duration`: The total length of the call in seconds (`callend` - `calldate`).
`connect_duration`: The length of the connected portion of the call in seconds, measured from the first `200 OK` to the `callend`.
`progress_time`: The time in seconds from the initial `INVITE` to the first provisional response (e.g., `180 Ringing`). This is often referred to as Post-Dial Delay (PDD).
`first_rtp_time`: The time in seconds from the start of the call to the detection of the very first RTP packet.

Participant Information

`caller` / `called`: The user part of the number from the `From` and `To` SIP headers, respectively.
`caller_domain` / `called_domain`: The domain part from the `From` and `To` SIP headers.
`caller_reverse` / `called_reverse`: Reversed versions of the `caller` and `called` fields, used internally for efficient `LIKE '%search'` queries in the database.
`callername`: The display name part from the `From` SIP header.
`sipcallerip` / `sipcalledip`: The source and destination IP addresses from the SIP signaling. These are stored as integers; use the `INET_NTOA()` or `INET6_NTOA()` SQL function to convert them to a string representation.
`a_ua_id` / `b_ua_id`: A reference to the `cdr_ua` table, linking to the User-Agent string of the caller's and callee's device.

NOTE: How User-Agent is extracted from SIP packets

Caller (a_ua_id): Extracted from the User-Agent header in the SIP INVITE request sent by the caller
Callee (b_ua_id): Extracted from the User-Agent header in the SIP response (typically 200 OK) sent by the called party
If the field is blank, it indicates that the User-Agent header was not present in the corresponding SIP packet

Network Quality Metrics

Packet Loss

`a_lost` / `b_lost`: The total number of RTP packets lost, detected by sequence number gaps.
`lost`: The higher value between `a_lost` and `b_lost`.
`packet_loss_perc_mult1000`: The maximum packet loss percentage from either stream, multiplied by 1000.
`a_sl1` - `a_sl10` / `b_sl1` - `b_sl10`: A distribution of consecutive packet loss events. `a_sl1` counts the number of times a single packet was lost, `a_sl5` counts the number of times 5 packets were lost in a row, etc.

Jitter & Delay (PDV)

`a_avgjitter_mult10` / `b_avgjitter_mult10`: The average jitter calculated according to RFC3550, multiplied by 10.
`a_maxjitter` / `b_maxjitter`: The maximum jitter value observed during the call.
`jitter_mult10`: The higher `maxjitter` value between the two streams, multiplied by 10.
`a_d50` - `a_d300` / `b_d50` - `b_d300`: A distribution of Packet Delay Variation (PDV). `a_d50` counts packets with a delay between 0-50ms, `a_d70` counts packets with a delay between 50-70ms, etc.

MOS (Mean Opinion Score)

`a_mos_f1_mult10` / `b_mos_f1_mult10`: The calculated parametric MOS score based on a simulated 50ms fixed jitter buffer, multiplied by 10.
`a_mos_f2_mult10` / `b_mos_f2_mult10`: MOS score based on a simulated 200ms fixed jitter buffer, multiplied by 10.
`a_mos_adapt_mult10` / `b_mos_adapt_mult10`: MOS score based on a simulated adaptive jitter buffer, multiplied by 10.
`mos_min_mult10`: The lowest MOS score recorded between the A and B streams, multiplied by 10.

RTCP Reported Metrics

These metrics are sourced from RTCP reports sent by the endpoints themselves, as opposed to being calculated by the sniffer.

`a_rtcp_loss` / `b_rtcp_loss`: Total packets lost as reported by RTCP.
`a_rtcp_avgfr_mult10` / `b_rtcp_avgfr_mult10`: Average fraction loss reported by RTCP, multiplied by 10.
`a_rtcp_avgjitter_mult10` / `b_rtcp_avgjitter_mult10`: Average jitter reported by RTCP, multiplied by 10.

Understanding the Difference Between Sniffer and RTCP Packet Loss

VoIPmonitor provides two different packet loss metrics that measure the same concept (lost VoIP packets) from two fundamentally different locations in the network. Understanding the distinction is critical for troubleshooting call quality issues.

The Two Perspectives:

Sniffer Loss (`a_lost` / `b_lost`): The VoIPmonitor sniffer watches the traffic at its monitoring point (e.g., on the PBX switch) and counts missing packets based on RTP sequence number gaps. This represents what the network path up to the monitoring point experienced.
RTCP Loss (`a_rtcp_loss` / `b_rtcp_loss`): The receiving endpoint (phone, gateway, or SBC) sends RTP Control Protocol (RTCP) Receiver Reports back to the transmitter, listing the cumulative count of packets it failed to receive or decode. This represents what the actual end-user device experienced.

Common Scenarios and Interpretations:

1. RTCP Loss is Higher Than Sniffer Loss

This indicates a network issue exists in the segment after the VoIPmonitor sniffer. Typical causes:

Poor Wi-Fi connectivity at the end-user's home/office
Network issues in the last-mile connection
Problems between the monitoring point (e.g., PBX) and the actual receiver device

In this case, the sniffer sees all packets on the wire (low loss), but some packets are lost downstream before reaching the user's phone.

2. Sniffer Loss is Higher Than RTCP Loss

This is unusual and typically indicates:

The monitoring point itself is dropping packets (e.g., due to high CPU load or interface saturation)
The RTCP reports from the endpoint are not being captured or transmitted properly

3. High RTCP Loss but MOS is Still Good

This can occur when:

Packet Loss Concealment (PLC) is effectively hiding scattered losses
The endpoint's jitter buffer is successfully absorbing jitter-related delays
The MOS calculation (based on sniffer loss) doesn't fully represent the user's actual experience

Troubleshooting Guidance:

Trust RTCP loss for user experience: For calls where users report quality issues, `a_rtcp_loss`/`b_rtcp_loss` is more authoritative because it reflects what the device actually received.
Use sniffer loss for network health: Monitoring `a_lost`/`b_lost` is useful for capacity planning and detecting issues in the network segment you control.
Downstream issues: When RTCP loss is higher, investigate the network segment between the monitoring point and the end-user device (check Wi-Fi, local network, ISP issues).

Other Information

`dscp`: Stores the DSCP values from SIP and RTP packets for Quality of Service (QoS) analysis.
`payload`: The codec payload type number used during the call.

Important Related Tables

`cdr_next`

A 1-to-1 extension of the `cdr` table that stores additional data to keep the main `cdr` table smaller and faster.

fbasename: A string derived from the SIP `Call-ID`, used to link PCAP files to the CDR.
match_header: Stores the content of a custom header (defined by `matchheader` in `voipmonitor.conf`) used for linking call legs.

`cdr_rtp`: Stores detailed statistics for each individual RTP stream within a call, used by the GUI to render stream information in the call history view.

`cdr_dtmf`: Stores detected DTMF key presses (if `dtmf2db` is enabled).

`cdr_proxy`: Stores the IP addresses of all SIP proxies that the call traversed.

`cdr_sip_response`: A lookup table storing the text of unique SIP responses (e.g., "404 Not Found"). The `cdr` table references this table by ID to save space.

`cdr_ua`: A lookup table storing the `User-Agent` strings of SIP devices.

AI Summary for RAG

Summary: This document provides a reference for the VoIPmonitor database schema, focusing on the main `cdr` table and its most important related tables. It explains the column naming conventions, such as the `a_` (caller) and `b_` (callee) prefixes for RTP streams and the `_multNN` suffix indicating a value needs to be divided. The `cdr` table columns are logically grouped into categories: General Call Information (ID, calldate), Call Timing (duration, connect_duration, PDD), Participant Information (caller, called, IPs, User-Agents), and detailed Network Quality Metrics. The quality metrics section details columns for Packet Loss (lost counts, distribution), Jitter (average, max, PDV distribution), Mean Opinion Score (MOS), and RTCP-reported statistics. A dedicated section explains the conceptual difference between sniffer-calculated packet loss (based on RTP sequence gaps at the monitoring point) and RTCP-reported loss (from the endpoint device), including troubleshooting scenarios for when values differ. It also explains how User-Agent information is extracted: the caller's User-Agent (a_ua_id) comes from the SIP INVITE request User-Agent header, while the callee's User-Agent (b_ua_id) comes from the SIP response User-Agent header. Finally, it briefly describes the purpose of key related tables like `cdr_next` (for Call-ID), `cdr_rtp`, `cdr_dtmf`, `cdr_proxy`, and the lookup tables `cdr_sip_response` and `cdr_ua`. Keywords: database, schema, table, column, cdr, cdr_next, cdr_rtp, database schema, sql, mysql, mariadb, calldate, duration, packet loss, jitter, mos, rtcp, pdd, a_lost, b_lost, a_avgjitter, mos_f1, a_mos_adapt, fbasename, user-agent, rtcp_loss, troubleshooting, downstream issues, endpoint reported loss, a_ua_id, b_ua_id, SIP INVITE, SIP response, User-Agent header Key Questions:

What do the `a_` and `b_` prefixes mean in the `cdr` table?
How are jitter and packet loss stored in the VoIPmonitor database?
What is the difference between `duration` and `connect_duration`?
How can I get the Call-ID for a specific call from the database?
Which table stores the User-Agent of the phone?
How do I convert the `sipcallerip` integer back to an IP address in SQL?
What is the purpose of the `cdr_next` table?
How are MOS scores stored in the database?
What is the difference between sniffer loss (`a_lost`) and RTCP loss (`a_rtcp_loss`)?
Why might RTCP loss be higher than sniffer loss?
What does it mean when RTCP loss is high but MOS is still good?