Database structure

From VoIPmonitor.org
Jump to navigation Jump to search


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?