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.

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.

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. 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 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?