Database structure: Difference between revisions

From VoIPmonitor.org
(Review: opraveno formátování - backticks na <code> v nadpisech, <pre> na <syntaxhighlight lang=sql>)
(Rewrite: consolidated structure, removed redundancy, improved organization)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
{{DISPLAYTITLE:CDR Database Table Schema}}
{{DISPLAYTITLE:CDR Database Table Schema}}


'''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.'''
'''Database schema reference for VoIPmonitor. The <code>cdr</code> table stores one record per call; related tables store RTP statistics, custom headers, and metadata.'''


The core of the VoIPmonitor database is the <code>cdr</code> (Call Detail Record) table, which stores one primary record for each monitored call. Many other tables are linked to <code>cdr</code> to store additional, more detailed information.
= Schema Overview =
 
== Database Schema Overview ==
 
=== Listing CDR Tables ===
 
To list all CDR-related tables in the database, use the following SQL command:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- List all CDR-related tables
SHOW TABLES LIKE 'cdr%';
SHOW TABLES LIKE 'cdr%';
</syntaxhighlight>
</syntaxhighlight>
This returns all tables with names starting with <code>cdr</code>, including the main <code>cdr</code> table and all related tables such as <code>cdr_next</code>, <code>cdr_rtp</code>, <code>cdr_sdp</code>, <code>cdr_proxy</code>, and custom header tables (<code>cdr_next_2</code>, etc.).
The following diagram shows the relationships between the main VoIPmonitor database tables:


<kroki lang="plantuml">
<kroki lang="plantuml">
Line 28: Line 19:
   * ID : bigint <<PK>>
   * ID : bigint <<PK>>
   --
   --
   id_sensor : int
   id_sensor, calldate, callend
  calldate : datetime
   caller, called, duration
  callend : datetime
   sipcallerip, sipcalledip
   caller : varchar
   a_lost, b_lost, a_mos_f1_mult10
  called : varchar
   lastSIPresponse_id <<FK>>
   sipcallerip : int
   a_ua_id, b_ua_id <<FK>>
  sipcalledip : int
   duration : int
   connect_duration : int
  a_ua_id : int <<FK>>
   b_ua_id : int <<FK>>
  lastSIPresponse_id : int <<FK>>
  a_lost / b_lost : int
  a_mos_f1_mult10 : tinyint
  ...
}
}


entity "cdr_next" as cdr_next {
entity "cdr_next" as cdr_next {
   * cdr_ID : bigint <<PK,FK>>
   * cdr_ID <<PK,FK>>
   --
   --
   fbasename : varchar
   fbasename, match_header
  match_header : varchar
  ...
}
}


entity "cdr_rtp" as cdr_rtp {
entity "cdr_rtp" as cdr_rtp {
   * ID : bigint <<PK>>
   cdr_ID <<FK>>
   --
   --
  cdr_ID : bigint <<FK>>
   saddr, daddr, ssrc
   saddr : int
   received, lost, payload
  daddr : int
  ssrc : bigint
   received : int
  lost : int
  ...
}
}


entity "cdr_dtmf" as cdr_dtmf {
entity "cdr_proxy" as cdr_proxy {
   * ID : bigint <<PK>>
   cdr_ID <<FK>>
   --
   --
   cdr_ID : bigint <<FK>>
   dst (proxy IP)
  dtmf : char
  ts : bigint
  ...
}
}


entity "cdr_proxy" as cdr_proxy {
entity "cdr_sdp" as cdr_sdp {
   * ID : bigint <<PK>>
   cdr_id <<FK>>
   --
   --
   cdr_ID : bigint <<FK>>
   ip, port, is_caller
  dst : int
  ...
}
}


entity "cdr_sdp" as cdr_sdp {
entity "cdr_siphistory" as cdr_siphistory {
   * ID : bigint <<PK>>
   cdr_ID <<FK>>
   --
   --
   cdr_id : bigint <<FK>>
   method, calldate, cseq
  calldate : datetime
  ip : int unsigned
  port : smallint unsigned
  is_caller : tinyint
  ...
}
}


entity "cdr_sip_response" as cdr_sip_response {
entity "cdr_ua" as cdr_ua {
   * ID : int <<PK>>
   * ID <<PK>>
   --
   --
   lastSIPresponse : varchar
   ua (User-Agent string)
}
}


entity "cdr_ua" as cdr_ua {
entity "cdr_sip_response" as cdr_sip_response {
   * ID : int <<PK>>
   * ID <<PK>>
   --
   --
   ua : varchar
   lastSIPresponse
}
}


cdr ||--|| cdr_next : "1:1"
cdr ||--|| cdr_next : "1:1"
cdr ||--o{ cdr_rtp : "1:N"
cdr ||--o{ cdr_rtp : "1:N"
cdr ||--o{ cdr_dtmf : "1:N"
cdr ||--o{ cdr_proxy : "1:N"
cdr ||--o{ cdr_sdp : "1:N"
cdr ||--o{ cdr_sdp : "1:N"
cdr ||--o{ cdr_proxy : "1:N"
cdr ||--o{ cdr_siphistory : "1:N"
cdr }o--|| cdr_ua : "N:1"
cdr }o--|| cdr_sip_response : "N:1"
cdr }o--|| cdr_sip_response : "N:1"
cdr }o--|| cdr_ua : "N:1 (a_ua_id)"
cdr }o--|| cdr_ua : "N:1 (b_ua_id)"
@enduml
@enduml
</kroki>
</kroki>


== Understanding Column Naming Conventions ==
== Column Naming Conventions ==
 
Before diving into the columns, it's important to understand these common naming patterns:


{| class="wikitable"
{| class="wikitable"
Line 124: Line 86:
! Pattern !! Meaning !! Example
! Pattern !! Meaning !! Example
|-
|-
| <code>a_</code> prefix || Caller's media stream (A-leg, RTP sent from caller) || <code>a_lost</code>, <code>a_mos_f1_mult10</code>
| <code>a_</code> || Caller's stream (A-leg) || <code>a_lost</code>, <code>a_mos_f1_mult10</code>
|-
|-
| <code>b_</code> prefix || Callee's media stream (B-leg, RTP sent from callee) || <code>b_lost</code>, <code>b_mos_f1_mult10</code>
| <code>b_</code> || Callee's stream (B-leg) || <code>b_lost</code>, <code>b_mos_f1_mult10</code>
|-
|-
| <code>_mult10</code> || Divide stored value by 10 to get real value || <code>jitter_mult10 = 15</code> means jitter = 1.5
| <code>_mult10</code> || Divide by 10 || <code>jitter_mult10 = 15</code> → jitter = 1.5
|-
|-
| <code>_mult100</code> || Divide stored value by 100 to get real value || Used for percentage values
| <code>_mult100</code> || Divide by 100 || Percentage values
|-
|-
| <code>_mult1000</code> || Divide stored value by 1000 to get real value || <code>packet_loss_perc_mult1000</code>
| <code>_mult1000</code> || Divide by 1000 || <code>packet_loss_perc_mult1000</code>
|}
|}


== The <code>cdr</code> Table ==
= The <code>cdr</code> Table =


This is the main table containing the primary information for every call.
== Call Information & Timing ==
 
=== General Call Information ===


{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Type !! Description
! Column !! Description
|-
|-
| <code>ID</code> || bigint || The unique primary key for the record
| <code>ID</code> || Primary key
|-
|-
| <code>id_sensor</code> || int || The ID of the sensor that processed this call (corresponds to <code>id_sensor</code> in <code>voipmonitor.conf</code>)
| <code>id_sensor</code> || Sensor ID (from <code>voipmonitor.conf</code>)
|-
|-
| <code>calldate</code> || datetime || The start time of the call, measured from the first <code>INVITE</code> packet
| <code>calldate</code> / <code>callend</code> || Start/end timestamps
|-
|-
| <code>callend</code> || datetime || The end time of the call, measured from the last packet associated with the call
| <code>duration</code> || Total length in seconds
|-
|-
| <code>bye</code> || tinyint || Indicates how the call was terminated (<code>1</code> = normal termination with <code>BYE</code>)
| <code>connect_duration</code> || Connected time (after 200 OK)
|-
|-
| <code>whohanged</code> || enum || Indicates which party sent the <code>BYE</code> message first (caller or callee)
| <code>progress_time</code> || Time to first provisional response ([[Glossary#Post_Dial_Delay_.28PDD.29|PDD]])
|-
|-
| <code>lastSIPresponse_id</code> || int || Foreign key to <code>cdr_sip_response</code> table for the last final SIP response
| <code>bye</code> || <code>1</code> = normal termination with BYE
|}
 
=== Call Timing & Duration ===
 
{| class="wikitable"
|-
|-
! Column !! Type !! Description
| <code>whohanged</code> || Which party sent BYE (caller/callee)
|-
|-
| <code>duration</code> || int || Total call length in seconds (<code>callend - calldate</code>)
| <code>lastSIPresponse_id</code> || FK to <code>cdr_sip_response</code>
|-
| <code>connect_duration</code> || int || Connected portion length in seconds (from first <code>200 OK</code> to <code>callend</code>)
|-
| <code>progress_time</code> || int || Time from <code>INVITE</code> to first provisional response (e.g., <code>180 Ringing</code>). Also known as [[Glossary#Post_Dial_Delay_.28PDD.29|Post-Dial Delay (PDD)]]
|-
| <code>first_rtp_time</code> || int || Time from call start to first RTP packet detection
|}
|}


=== Participant Information ===
== Participant Information ==
 
{| class="wikitable"
|-
! Column !! Type !! Description
|-
| <code>caller</code> / <code>called</code> || varchar || User part of the number from <code>From</code> and <code>To</code> SIP headers
|-
| <code>caller_domain</code> / <code>called_domain</code> || varchar || Domain part from <code>From</code> and <code>To</code> SIP headers
|-
| <code>caller_reverse</code> / <code>called_reverse</code> || varchar || Reversed strings for efficient <code>LIKE '%search'</code> queries
|-
| <code>callername</code> || varchar || Display name from the <code>From</code> SIP header
|-
| <code>sipcallerip</code> / <code>sipcalledip</code> || int unsigned || Source/destination IP addresses stored as integers (use <code>INET_NTOA()</code> to convert)
|-
| <code>a_ua_id</code> / <code>b_ua_id</code> || int || Foreign keys to <code>cdr_ua</code> table for User-Agent strings
|}
 
{{Note|
'''How User-Agent is extracted:'''
* '''Caller (<code>a_ua_id</code>):''' From the <code>User-Agent</code> header in the SIP INVITE request
* '''Callee (<code>b_ua_id</code>):''' From the <code>User-Agent</code> header in the SIP response (typically <code>200 OK</code>)
* Blank values indicate the <code>User-Agent</code> header was not present
}}
 
'''Example: Converting IP addresses in SQL queries:'''
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
=== 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|
<strong>Important:</strong> This section describes direct SQL queries against the VoIPmonitor database. For the GUI API (HTTP API with <code>api.php</code>), see the [[WEB_API|WEB API documentation]].}}
 
==== Filtering by SIP Caller IP ====
 
To find calls from a specific SIP caller IP, use the <code>sipcallerip</code> column. Since IP addresses are stored as integers, use <code>INET_ATON()</code> to convert the IP string to an integer for comparison.
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
To filter by either SIP caller IP or SIP called IP:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
==== Filtering by SIP Domain ====
 
The <code>caller_domain</code> and <code>called_domain</code> columns store the domain part from the SIP <code>From</code> and <code>To</code> headers.
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
To filter by both SIP caller IP and domain:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
==== Getting PCAP File Information (fbasename) ====
 
After finding CDR records, you may need to retrieve the associated PCAP file information. The <code>fbasename</code> column (derived from the SIP Call-ID) is stored in the <code>cdr_next</code> table.
 
<syntaxhighlight lang="sql">
-- 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);
</syntaxhighlight>
 
You can also combine both queries using a JOIN:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
{{Tip|
<strong>Using fbasename with the API:</strong> Once you have the fbasename, you can use it with the [[WEB_API|GUI API]] to download or analyze the PCAP file. The fbasename is the filename used for the captured packets.}}
 
==== Performance Considerations ====
 
* Ensure the <code>sipcallerip</code> and <code>sipcalledip</code> columns are indexed for fast queries
* Use date range filters (<code>WHERE calldate >= '...'</code>) to limit the amount of data
* For frequent IP-based filtering, consider creating indexed views or summary tables
 
=== Network Quality Metrics ===
 
==== Packet Loss ====


{| class="wikitable"
{| class="wikitable"
Line 355: Line 130:
! Column !! Description
! Column !! Description
|-
|-
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost (detected by sequence number gaps)
| <code>caller</code> / <code>called</code> || Numbers from From/To headers
|-
|-
| <code>lost</code> || Higher value between <code>a_lost</code> and <code>b_lost</code>
| <code>caller_domain</code> / <code>called_domain</code> || Domain parts
|-
|-
| <code>packet_loss_perc_mult1000</code> || Maximum packet loss percentage, multiplied by 1000
| <code>sipcallerip</code> / <code>sipcalledip</code> || IPs stored as integers (use <code>INET_NTOA()</code>)
|-
|-
| <code>a_sl1</code> - <code>a_sl10</code> / <code>b_sl1</code> - <code>b_sl10</code> || Distribution of consecutive packet loss events (sl1 = single packet lost, sl5 = 5 packets in a row, etc.)
| <code>a_ua_id</code> / <code>b_ua_id</code> || FK to <code>cdr_ua</code> (User-Agent)
|}
|}


==== Jitter & Delay (PDV) ====
== Quality Metrics ==


=== Packet Loss ===
{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Description
! Column !! Description
|-
|-
| <code>a_avgjitter_mult10</code> / <code>b_avgjitter_mult10</code> || Average jitter per RFC 3550, multiplied by 10
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost
|-
|-
| <code>a_maxjitter</code> / <code>b_maxjitter</code> || Maximum jitter value observed during the call
| <code>packet_loss_perc_mult1000</code> || Max loss percentage × 1000
|-
|-
| <code>jitter_mult10</code> || Higher <code>maxjitter</code> value between streams, multiplied by 10
| <code>a_sl1</code> - <code>a_sl10</code> || Distribution of consecutive loss events
|-
| <code>a_d50</code> - <code>a_d300</code> / <code>b_d50</code> - <code>b_d300</code> || Packet Delay Variation (PDV) distribution buckets (d50 = 0-50ms, d70 = 50-70ms, etc.)
|}
|}


==== MOS (Mean Opinion Score) ====
=== Jitter & Delay ===
 
{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Description
! Column !! Description
|-
|-
| <code>a_mos_f1_mult10</code> / <code>b_mos_f1_mult10</code> || [[Comprehensive_Guide_to_VoIP_Voice_Quality#MOS_Calculation_in_VoIPmonitor|Parametric MOS]] with 50ms fixed jitter buffer, multiplied by 10
| <code>a_avgjitter_mult10</code> || Average jitter × 10
|-
| <code>a_mos_f2_mult10</code> / <code>b_mos_f2_mult10</code> || MOS with 200ms fixed jitter buffer, multiplied by 10
|-
|-
| <code>a_mos_adapt_mult10</code> / <code>b_mos_adapt_mult10</code> || MOS with adaptive jitter buffer (up to 500ms), multiplied by 10
| <code>a_maxjitter</code> || Maximum jitter observed
|-
|-
| <code>mos_min_mult10</code> || Lowest MOS score between A and B streams, multiplied by 10
| <code>a_d50</code> - <code>a_d300</code> || PDV distribution buckets (0-50ms, 50-70ms, etc.)
|}
|}


==== RTCP Reported Metrics ====
=== MOS (Mean Opinion Score) ===
 
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 [[Call_Detail_Record_-_CDR#Understanding_Sniffer_vs_RTCP_Packet_Loss|Understanding Sniffer vs RTCP Packet Loss]].
 
{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Description
! Column !! Description
|-
|-
| <code>a_rtcp_loss</code> / <code>b_rtcp_loss</code> || Total packets lost as reported by endpoint via RTCP
| <code>a_mos_f1_mult10</code> || [[Comprehensive_Guide_to_VoIP_Voice_Quality#MOS_Calculation_in_VoIPmonitor|MOS]] with 50ms fixed buffer × 10
|-
|-
| <code>a_rtcp_avgfr_mult10</code> / <code>b_rtcp_avgfr_mult10</code> || Average fraction loss from RTCP, multiplied by 10
| <code>a_mos_f2_mult10</code> || MOS with 200ms fixed buffer × 10
|-
|-
| <code>a_rtcp_avgjitter_mult10</code> / <code>b_rtcp_avgjitter_mult10</code> || Average jitter from RTCP, multiplied by 10
| <code>a_mos_adapt_mult10</code> || MOS with adaptive buffer × 10
|-
|-
| <code>a_rtcp_avgrtd_mult10</code> / <code>b_rtcp_avgrtd_mult10</code> || 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.
| <code>mos_min_mult10</code> || Lowest MOS between A/B streams × 10
|}
|}


=== Other Columns ===
=== RTCP-Reported Metrics ===
These come from endpoint RTCP reports, not calculated by the sniffer. See [[Call_Detail_Record_-_CDR#Understanding_Sniffer_vs_RTCP_Packet_Loss|Sniffer vs RTCP Packet Loss]].


{| class="wikitable"
{| class="wikitable"
Line 417: Line 186:
! Column !! Description
! Column !! Description
|-
|-
| <code>dscp</code> || DSCP values from SIP and RTP packets for QoS analysis
| <code>a_rtcp_loss</code> || Packets lost (reported by endpoint)
|-
|-
| <code>payload</code> || Codec payload type number used during the call
| <code>a_rtcp_avgfr_mult10</code> || Average fraction loss × 10
|}
 
== Important Related Tables ==
 
=== <code>cdr_next</code> ===
 
A 1-to-1 extension of the <code>cdr</code> table for additional data, keeping the main table smaller. This table is linked to <code>cdr</code> via the <code>cdr_ID</code> foreign key (where <code>cdr_next.cdr_ID = cdr.ID</code>).
 
{| class="wikitable"
|-
|-
! Column !! Description
| <code>a_rtcp_avgjitter_mult10</code> || Average jitter × 10
|-
|-
| <code>fbasename</code> || String derived from SIP <code>Call-ID</code>, used to link PCAP files to CDR. Special characters in the Call-ID are replaced with underscores. This replacement behavior is controlled by the <code>convertchar = :</code> option in <code>voipmonitor.conf</code>
| <code>a_rtcp_avgrtd_mult10</code> || '''Roundtrip delay (latency)''' in ms × 10
|-
| <code>match_header</code> || Content of custom header (from <code>matchheader</code> in <code>voipmonitor.conf</code>) for linking call legs
|}
|}


'''Example: Finding PCAP filename for a call:'''
= Related Tables =
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>
 
{{Note|
'''CDR Custom Headers Storage:'''
CDR-level custom SIP headers (one value per call) are stored dynamically in the <code>cdr_next_X</code> tables, not in the main <code>cdr</code> table. To find where a specific header is stored, you must query the <code>cdr_custom_headers</code> mapping table.
}}


=== <code>cdr_custom_headers</code> ===
== <code>cdr_next</code> (1:1 with cdr) ==


Lookup table that maps custom SIP header names to their storage locations in the <code>cdr_next_X</code> tables. You must query this table first to find the correct table and column for your custom header.
Extension table linked via <code>cdr_ID</code>.


{| class="wikitable"
{| class="wikitable"
Line 458: Line 205:
! Column !! Description
! Column !! Description
|-
|-
| <code>header_field</code> || The name of the custom SIP header (e.g., <code>'X-IntNum'</code>)
| <code>fbasename</code> || Derived from SIP Call-ID; used to link PCAP files. Special characters replaced with underscores (<code>convertchar</code> in config)
|-
| <code>dynamic_table</code> || The table number (e.g., <code>2</code> means <code>cdr_next_2</code>)
|-
|-
| <code>dynamic_column</code> || The column number (e.g., <code>1</code> means <code>custom_header_1</code>)
| <code>match_header</code> || Content of custom header for linking call legs (<code>matchheader</code> in config)
|}
|}


{{Tip|
== <code>cdr_rtp</code> (Per-Stream RTP Stats) ==
The system automatically creates new <code>cdr_next_X</code> 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 <code>cdr_custom_headers</code> table to find the correct table and column.
 
<syntaxhighlight lang="sql">
-- 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';
</syntaxhighlight>
 
This query returns:
* <code>dynamic_table</code>: The table number (e.g., <code>2</code> means <code>cdr_next_2</code>)
* <code>dynamic_column</code>: The column number (e.g., <code>1</code> means <code>custom_header_1</code>)
 
=== Querying CDRs with Custom Header Values ===
 
Once you have the table and column information, you can query the <code>cdr_next_X</code> table for calls containing the desired header value.
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
=== Complete Example Workflow ===
 
<syntaxhighlight lang="sql">
-- 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
One row per RTP stream. Multiple streams per call.
-- 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;
</syntaxhighlight>
 
{{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|
<strong>Performance Consideration:</strong> Queries with long date ranges (several months of data) on large tables can be slow. Consider using the [[CDR_Summary|CDR Summary]] table for pre-aggregated data, or narrow your date ranges for better performance.
}}
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
To compare between two periods, run the query twice with different date ranges, then compare the results:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
You can then compare the two result sets manually in a spreadsheet or use SQL to find values that disappeared:
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
{{Note|
The GUI's [[Reports|Reports]] module (Call Summary and CDR Summary) does not currently support grouping by CDR Custom Headers. It is limited to standard fields such as <code>sipcallerip</code>, <code>sipcalledip</code>, <code>payload</code>, and <code>last_sipresponse_id</code>. Therefore, generating aggregated reports based on arbitrary SIP headers like <code>X-IntNum</code> requires direct SQL queries as shown above.
}}
 
=== <code>cdr_rtp</code> ===
 
Stores detailed statistics for each individual RTP stream within a call. Multiple rows per call (one per stream).


{| class="wikitable"
{| class="wikitable"
Line 631: Line 218:
! Column !! Description
! Column !! Description
|-
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
| <code>cdr_ID</code> || FK to <code>cdr</code>
|-
| <code>ssrc</code> || RTP Synchronization Source identifier (unique identifier for the stream)
|-
| <code>saddr</code> || Source IP address (stored as integer, use <code>INET_NTOA()</code> to convert)
|-
| <code>daddr</code> || Destination IP address (stored as integer, use <code>INET_NTOA()</code> to convert)
|-
|-
| <code>sport</code> || Source port number (smallint unsigned). See [[#Missing sport_or dport Columns in cdr_rtp|Troubleshooting section below]] if this column is missing.
| <code>ssrc</code> || RTP Synchronization Source identifier
|-
|-
| <code>dport</code> || Destination port number (smallint unsigned). Used with <code>sport</code> for more precise stream identification. See [[#Missing sport_or dport Columns in cdr_rtp|Troubleshooting section below]] if this column is missing.
| <code>saddr</code> / <code>daddr</code> || Source/destination IP (as integer)
|-
|-
| <code>payload</code> || Codec payload type number. Video streams are stored with an offset of <strong>+10000</strong> to distinguish them from audio streams
| <code>sport</code> / <code>dport</code> || Source/destination port
|-
|-
| <code>received</code> || Number of RTP packets received
| <code>payload</code> || Codec type. '''Video streams: payload ≥ 10000''' (offset applied)
|-
|-
| <code>lost</code> || Number of RTP packets lost (detected by sequence number gaps)
| <code>received</code> / <code>lost</code> || Packet counts
|-
| <code>jitter</code> || Jitter value (varies by schema version)
|}
|}


{{Tip|
{{Tip|To query video streams only: <code>WHERE payload >= 10000</code>}}
<strong>Identifying Video Streams:</strong> Video codecs (H.264, VP8, etc.) use dynamic payload types in RTP (typically 96-127). VoIPmonitor adds a <code>+10000</code> offset to video payload values in the database. To query only video streams, use <code>WHERE payload >= 10000</code>.}}
 
'''Example: Querying video stream quality:'''
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
'''Example: Finding all RTP streams in a call:'''
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>
 
=== Troubleshooting: Missing Columns in <code>cdr_rtp</code> ===
 
The <code>cdr_rtp</code> table schema evolves over time as new features are added to VoIPmonitor. When upgrading to a newer version, certain columns may be missing, causing incorrect behavior or values (e.g., <code>packetization = 0</code> when <code>sdp_ptime</code> and <code>rtp_ptime</code> columns are missing).
 
{{Warning|1=<strong>Performance Warning:</strong> <code>ALTER TABLE</code> operations can lock the table for several hours on large databases, preventing new CDRs from being inserted. It is highly recommended to perform these actions during off-peak hours or a maintenance window. This warning applies to ALL missing column additions, not just the examples below.}}
 
=== Troubleshooting: Missing <code>sport</code> or <code>dport</code> Columns in <code>cdr_rtp</code> ===
 
By default, the <code>cdr_rtp</code> table stores RTP stream identifiers using only <code>saddr</code> (source IP) and <code>daddr</code> (destination IP). However, some environments require port numbers (<code>sport</code> and <code>dport</code>) for more precise stream identification.
 
==== Symptoms ====
 
If the <code>sport</code> column is missing from <code>cdr_rtp</code>, 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:
 
<syntaxhighlight lang="sql">
EXPLAIN cdr_rtp;
</syntaxhighlight>
 
Look for <code>sport</code> and <code>dport</code> columns in the output. If missing, you need to add them.
 
==== Solution: Adding the Missing Columns ====
 
To add the missing <code>sport</code> column and/or <code>dport</code> column:
 
Add the <code>sport</code> column:
<syntaxhighlight lang="sql">
ALTER TABLE cdr_rtp ADD `sport` smallint unsigned DEFAULT NULL AFTER `saddr`;
</syntaxhighlight>
 
Add the <code>dport</code> column (if also missing):
<syntaxhighlight lang="sql">
ALTER TABLE cdr_rtp ADD `dport` smallint unsigned DEFAULT NULL AFTER `daddr`;
</syntaxhighlight>
 
After adding the columns, restart the VoIPmonitor service:
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
 
==== Finding Other Recommended Schema Changes ====
 
To find other recommended schema changes for your database version, restart the VoIPmonitor service and check the syslog for <code>ALTER</code> commands:
 
<syntaxhighlight lang="bash">
journalctl -u voipmonitor | grep ALTER
</syntaxhighlight>
 
The service will log <code>ALTER TABLE</code> commands for any columns that should be added to match the current schema version.
 
==== Impact on Historical Data ====
 
* Existing records will have <code>NULL</code> values for the newly added <code>sport</code> and <code>dport</code> columns
* New calls will populate these columns correctly
* Historical statistics and reports may display inaccuracies until the data refreshes
 
=== <code>cdr_dtmf</code> ===
 
Stores detected DTMF key presses. Requires <code>dtmf2db = yes</code> in <code>voipmonitor.conf</code>.


=== <code>cdr_proxy</code> ===
== <code>cdr_sdp</code> (SDP-Negotiated Ports) ==


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.
Stores media endpoints declared in SDP. Requires <code>save_sdp_ipport = yes</code>.


{| class="wikitable"
{| class="wikitable"
Line 772: Line 241:
! Column !! Description
! Column !! Description
|-
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
| <code>ip</code> / <code>port</code> || Media IP and port from SDP
|-
|-
| <code>dst</code> || Proxy IP address (stored as integer, use <code>INET_NTOA()</code> to convert)
| <code>is_caller</code> || 1 = caller side, 0 = callee side
|}
|}


{{Tip|
{{Note|<code>cdr_sdp</code> stores what endpoints '''agreed to use''' in SDP; <code>cdr_rtp</code> stores what was '''actually sent'''.}}
<strong>Proxy Chain Tracing:</strong> 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:'''
== <code>cdr_proxy</code> (Proxy Chain) ==
<syntaxhighlight lang="sql">
-- List all proxies a call passed through
SELECT
    cdr_ID,
    INET_NTOA(dst) AS proxy_ip
FROM cdr_proxy
WHERE cdr_ID = 12345;
</syntaxhighlight>


'''Example: Finding calls that used a specific proxy:'''
IP addresses of SIP proxies the call traversed.
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>


=== <code>cdr_sdp</code> ===
== <code>cdr_siphistory</code> (SIP Method History) ==


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 <code>save_sdp_ipport = yes</code> in <code>voipmonitor.conf</code>.
Stores SIP requests/responses including REFER, BYE, CANCEL, OPTIONS, etc. Requires <code>save_sip_history = all</code>.


{{Note|
== <code>cdr_ua</code> / <code>cdr_sip_response</code> (Lookup Tables) ==
'''Difference from <code>cdr_rtp</code>:'''
* <code>cdr_sdp</code>: Stores ports and IPs declared in SDP (the negotiated media endpoints). This reflects what endpoints <strong>agreed to use</strong>.
* <code>cdr_rtp</code>: Stores actual observed RTP streams with packet statistics (loss, jitter, MOS, etc.). This reflects what endpoints <strong>actually sent on the wire</strong>.
}}


{| class="wikitable"
Lookup tables for User-Agent strings and SIP response texts.
|-
! Column !! Type !! Description
|-
| <code>cdr_id</code> || bigint || Foreign key to <code>cdr</code> table (links to the primary call record)
|-
| <code>calldate</code> || datetime || Timestamp when this SDP entry was captured
|-
| <code>ip</code> || int unsigned || Media IP address (stored as integer, use <code>INET_NTOA()</code> to convert)
|-
| <code>port</code> || smallint unsigned || Media port number negotiated in SDP
|-
| <code>is_caller</code> || tinyint || Indicates which side this SDP record belongs to (0 = called/callee, 1 = caller)
|}


{{Tip|
= Common SQL Queries =
<strong>Use Case - Viewing All Negotiated Media Ports:</strong>
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:'''
== Basic CDR Query with IP Conversion ==
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>


'''Example: Comparing SDP-negotiated vs. actual RTP ports:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Compare declared SDP ports with actual observed RTP ports
SELECT
SELECT
     'SDP declared' AS source,
     ID, caller, called,
     INET_NTOA(sdp.ip) AS media_ip,
     INET_NTOA(sipcallerip) AS caller_ip,
     sdp.port AS media_port,
     INET_NTOA(sipcalledip) AS called_ip,
     sdp.is_caller AS side
     duration
FROM cdr_sdp sdp
FROM cdr
WHERE sdp.cdr_id = 31
WHERE calldate >= '2024-01-01'
 
  AND sipcallerip = INET_ATON('192.168.1.50')
UNION ALL
LIMIT 10;
 
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;
</syntaxhighlight>
</syntaxhighlight>


'''Prerequisite Configuration:'''
== Get PCAP Filename (fbasename) ==
<syntaxhighlight lang="bash">
# Add to /etc/voipmonitor.conf
save_sdp_ipport = yes


# Restart service to apply
<syntaxhighlight lang="sql">
systemctl restart voipmonitor
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;
</syntaxhighlight>
</syntaxhighlight>


=== <code>cdr_sip_response</code> ===
== Get User-Agent Strings ==
 
Lookup table for unique SIP response texts (e.g., "404 Not Found"). The <code>cdr</code> table references by ID to save space.
 
=== <code>cdr_ua</code> ===
 
Lookup table for <code>User-Agent</code> strings of SIP devices.


'''Example: Getting User-Agent strings for calls:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT
SELECT c.ID, ua_a.ua AS caller_ua, ua_b.ua AS callee_ua
    c.ID,
    c.caller,
    c.called,
    ua_a.ua AS caller_user_agent,
    ua_b.ua AS callee_user_agent
FROM cdr c
FROM cdr c
LEFT JOIN cdr_ua ua_a ON c.a_ua_id = ua_a.ID
LEFT JOIN cdr_ua ua_a ON c.a_ua_id = ua_a.ID
Line 904: Line 296:
</syntaxhighlight>
</syntaxhighlight>


=== <code>cdr_siphistory</code> ===
== Find Calls Through Specific Proxy ==


Stores detailed SIP request and response history for each call, including subdialog methods such as <code>REFER</code>, <code>BYE</code>, <code>CANCEL</code>, <code>OPTIONS</code>, <code>SUBSCRIBE</code>, <code>NOTIFY</code>, and <code>MESSAGE</code>. This table enables filtering CDRs by specific SIP methods via the GUI filter dropdown. Requires <code>save_sip_history = all</code> in <code>voipmonitor.conf</code>.
<syntaxhighlight lang="sql">
SELECT c.ID, c.calldate, c.caller, c.called,
      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')
LIMIT 100;
</syntaxhighlight>


{| class="wikitable"
== Query Video Stream Quality ==
|-
! Column !! Description
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
|-
| <code>method</code> || SIP request method (e.g., INVITE, REFER, BYE, CANCEL, OPTIONS, etc.)
|-
| <code>calldate</code> || Timestamp of the SIP message
|-
| <code>callid</code> || SIP Call-ID header value
|-
| <code>cseq</code> || SIP CSeq (Command Sequence) number
|}


'''Prerequisite Configuration:'''
<syntaxhighlight lang="sql">
Configure in <code>/etc/voipmonitor.conf</code>:
SELECT cdr_ID, payload,
<syntaxhighlight lang="ini">
      INET_NTOA(saddr) AS src_ip,
save_sip_history = all
      received, lost,
      (lost / (received + lost)) * 100 AS loss_pct
FROM cdr_rtp
WHERE payload >= 10000  -- Video streams only
  AND cdr_ID = 12345;
</syntaxhighlight>
</syntaxhighlight>
Restart the voipmonitor sniffer after changing this configuration.


'''Example: Find all calls containing a SIP REFER method:'''
== Find Calls with Specific SIP Method (REFER) ==
 
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT
SELECT cdr_ID, callid, calldate
    cdr_ID,
    callid,
    calldate
FROM cdr_siphistory
FROM cdr_siphistory
WHERE calldate > '2024-01-01 00:00:00'
WHERE method = 'REFER'
  AND method = 'REFER'
  AND calldate > '2024-01-01'
GROUP BY cdr_ID;
GROUP BY cdr_ID;
</syntaxhighlight>
</syntaxhighlight>


'''Example: Find calls with OPTIONS or SUBSCRIBE messages (for troubleshooting qualify pings):'''
= Custom Headers =
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>
 
== 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 <code>sip_msg_next_X</code> tables, where each custom header gets its own dedicated column.
== CDR-Level Custom Headers ==


{{Note|
CDR custom headers are stored in <code>cdr_next_X</code> tables. Use <code>cdr_custom_headers</code> to find the location:
'''CDR vs SIP Message Headers:'''
* <strong>CDR custom headers</strong> are stored in the <code>cdr_next</code> table for call-level data (one value per call)
* <strong>SIP message custom headers</strong> are stored in <code>sip_msg_next_X</code> 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 <code>sip_msg_custom_headers</code> table to find which table and column contains your header.


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Step 1: Find the dynamic table and column for your custom header
-- Step 1: Find table/column for header
SELECT dynamic_table, dynamic_column
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
FROM cdr_custom_headers
WHERE header_field = 'X-My-Custom-Header';
WHERE header_field = 'X-Customer-ID';
-- Result: dynamic_table=2, dynamic_column=1 → cdr_next_2.custom_header_1
 
-- Step 2: Query the data
SELECT c.ID, c.calldate, 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';
</syntaxhighlight>
</syntaxhighlight>


This query returns:
{{Note|The GUI's [[Reports|Reports]] module does not support grouping by custom headers. Use direct SQL queries.}}
* <code>dynamic_table</code>: The table number (e.g., <code>1</code> means <code>sip_msg_next_1</code>)
* <code>dynamic_column</code>: The column number (e.g., <code>2</code> means <code>custom_header_2</code>)


=== Querying SIP Messages with Custom Header Values ===
== SIP Message Custom Headers ==


Once you have the table and column information, you can query the <code>sip_msg_next_X</code> table for SIP messages containing the desired header value.
Per-message headers are stored in <code>sip_msg_next_X</code> tables. Use <code>sip_msg_custom_headers</code> to find the location.
 
<syntaxhighlight lang="sql">
-- 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;
</syntaxhighlight>


{{Tip|
= System Table =
You can also join this with other SIP message tables if needed. The SIP messages are linked to CDRs through the <code>sip_msg_ID</code> column and related message tables.
}}


=== Complete Example Workflow ===
The <code>system</code> table stores configuration including <code>manager_key</code> for sensor operations.


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Example: Find SIP messages with X-Customer-ID = '12345' in the last 1 hour
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
 
</syntaxhighlight>
-- 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
{{Warning|1=If sniffer fails with "failed read rsa key", check that <code>mysqlloadconfig</code> is NOT set to <code>no</code> in <code>voipmonitor.conf</code>.}}


-- Step 2: Query the appropriate table
== Synchronizing manager_key Between Databases ==
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;
</syntaxhighlight>


=== Configuration Requirements ===
When multiple GUI instances share sensors (e.g., after database migration), mismatched <code>manager_key</code> values cause PCAP download failures.


To capture custom SIP headers, configure them in <code>/etc/voipmonitor.conf</code>:
'''Symptoms:''' "Unable to download PCAP" errors, empty SIP history in one GUI.


<syntaxhighlight lang="ini">
'''Fix:'''
[general]
<syntaxhighlight lang="sql">
# List custom headers to capture from SIP messages
-- On active database: get the key
sip_headers = X-Customer-ID, X-Branch-Code, X-Transaction-ID
SELECT content FROM voipmonitor.`system` WHERE type='manager_key';
</syntaxhighlight>


After modifying the configuration, restart the sensor:
-- On old database: update the key
<syntaxhighlight lang="bash">
UPDATE voipmonitor.`system`
systemctl restart voipmonitor
SET content = '{"key":"<key_from_active>","iv":"<iv_from_active>"}'
WHERE type = 'manager_key';
</syntaxhighlight>
</syntaxhighlight>


The system will automatically create new columns in the appropriate <code>sip_msg_next_X</code> table and update the <code>sip_msg_custom_headers</code> mapping table.
Users must log out and log back in after the update.


=== <code>system</code> Table ===
= Troubleshooting =


The <code>system</code> table stores system-wide configuration and keys used by VoIPmonitor.
== Missing <code>sport</code>/<code>dport</code> in cdr_rtp ==


;<code>manager_key</code>
If RTP streams from overlapping calls are incorrectly merged, add the missing columns:
:The <code>system</code> table contains a <code>manager_key</code> entry (identified by <code>type='manager_key'</code>) 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 <code>mysqlloadconfig</code> option in <code>voipmonitor.conf</code> (documented in [[Sniffer_configuration#Database_Configuration|Sniffer Configuration]]). If <code>mysqlloadconfig=no</code> 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:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Query the manager_key from the system table
ALTER TABLE cdr_rtp ADD `sport` smallint unsigned DEFAULT NULL AFTER `saddr`;
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
ALTER TABLE cdr_rtp ADD `dport` smallint unsigned DEFAULT NULL AFTER `daddr`;
</syntaxhighlight>
</syntaxhighlight>


:'''Troubleshooting manager_key errors:'''
{{Warning|1=<code>ALTER TABLE</code> can lock large tables for hours. Run during maintenance windows.}}
:# Check that <code>mysqlloadconfig</code> is NOT set to <code>no</code> in <code>/etc/voipmonitor.conf</code>
:# Query the <code>system</code> table to verify the manager_key exists using the SQL above
:# Check database connectivity permissions (VoIPmonitor needs read access to the <code>system</code> table)
:# Review syslog or journalctl for additional database error messages around the time of the startup failure
:# Ensure both probe and server have consistent configuration in <code>/etc/voipmonitor.conf</code> files


;<code>Synchronizing manager_key Between Multiple Databases</code>
To find other recommended schema changes:
: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 <code>manager_key</code>. If the <code>manager_key</code> values differ, the GUI that does not have the matching key cannot:
<syntaxhighlight lang="bash">
:* Download PCAP files (downloads fail or return corrupt data)
journalctl -u voipmonitor | grep ALTER
:* 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 <code>manager_key</code> 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
:<syntaxhighlight lang="sql">
-- On the database server that sensors are actively connected to
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
</syntaxhighlight>
:Note the values for <code>key</code> and <code>iv</code> from the <code>content</code> field (JSON format).
 
:;Step 2: Update the manager_key on the old database
:<syntaxhighlight lang="sql">
-- On the old database server
UPDATE voipmonitor.`system` SET content = '{"key":"<key_from_step_1>","iv":"<iv_from_step_1>"}' WHERE type = 'manager_key';
</syntaxhighlight>
</syntaxhighlight>


:;Step 3: Log out and log back in
= See Also =
:Users must log out of the old GUI and log back in for the key synchronization to take effect.


:;Step 4: Verify the fix
* [[Call_Detail_Record_-_CDR|CDR View (GUI)]]
:Attempt to download a PCAP file through the old GUI to verify the fix worked.
* [[Database_troubleshooting|Database Troubleshooting]]
* [[Sniffer_configuration#Database_Configuration|Database Configuration]]
* [[WEB_API|Web API Documentation]]


:'''Important Notes:'''
= AI Summary for RAG =
:* Do NOT manually generate or modify the <code>key</code> or <code>iv</code> fields - they are encrypted credentials
:* Always backup the <code>system</code> 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 <code>manager_key</code> in the <code>system</code> table - they are not automatically synchronized


== AI Summary for RAG ==
'''Summary:''' VoIPmonitor database schema reference. Main table <code>cdr</code> stores one record per call with timing, participants, and quality metrics. Column naming: <code>a_</code>=caller, <code>b_</code>=callee, <code>_multNN</code>=divide by NN. Key tables: <code>cdr_next</code> (1:1, contains <code>fbasename</code> for PCAP linking), <code>cdr_rtp</code> (per-stream stats, video streams have payload≥10000), <code>cdr_proxy</code> (proxy chain IPs), <code>cdr_sdp</code> (SDP-negotiated ports), <code>cdr_siphistory</code> (SIP method history, requires <code>save_sip_history=all</code>). Custom headers stored in <code>cdr_next_X</code> tables; query <code>cdr_custom_headers</code> for mappings. System table stores <code>manager_key</code>; mismatch causes PCAP download failures.
'''Summary:''' Reference for VoIPmonitor database schema. The <code>cdr</code> table stores one record per call with timing, participant info, and quality metrics. Column naming: <code>a_</code> = caller stream, <code>b_</code> = callee stream, <code>_multNN</code> = divide by NN. Key metrics include packet loss (<code>a_lost</code>), jitter (<code>a_avgjitter_mult10</code>), MOS scores, and RTCP-reported metrics. RTCP-reported metrics include <code>a_rtcp_loss</code>/<code>b_rtcp_loss</code> (endpoints' loss reports), <code>a_rtcp_avgfr_mult10</code>/<code>b_rtcp_avgfr_mult10</code> (average fraction loss), <code>a_rtcp_avgjitter_mult10</code>/<code>b_rtcp_avgjitter_mult10</code> (jitter from RTCP), and <strong><code>a_rtcp_avgrtd_mult10</code>/<code>b_rtcp_avgrtd_mult10</code></strong> (RTCP Roundtrip Delay, also called "packet delay" or "latency" in BI contexts - measured in milliseconds, divide by 10 for actual value). Related tables: <code>cdr_next</code> (1-to-1 with <code>cdr</code>, linked via <code>cdr_ID</code> foreign key; contains <code>fbasename</code> from SIP Call-ID for PCAP file linking, where special characters are replaced with underscores via <code>convertchar</code> option in <code>voipmonitor.conf</code>), <code>cdr_rtp</code> (per-stream RTP stats), <code>cdr_sdp</code> (RTP media ports negotiated in SDP - query with `select calldate,INET_NTOA(ip),port,is_caller from cdr_sdp where cdr_id=31;`), <code>cdr_proxy</code> (intermediate SIP proxy IPs), <code>cdr_ua</code> (User-Agent lookup). The <code>cdr_rtp</code> table stores individual RTP stream statistics; <strong>video streams are identified by <code>payload >= 10000</code></strong> (VoIPmonitor adds +10000 offset to distinguish video from audio). The <code>cdr_proxy</code> table stores proxy IP addresses a call traversed; query with <code>select INET_NTOA(dst) from cdr_proxy where cdr_ID=123;</code> or <code>join cdr_proxy on cdr.ID = cdr_proxy.cdr_ID</code> to find all calls going through a specific proxy. CDR custom headers require querying <code>cdr_custom_headers</code> to find dynamic table/column mappings, then querying <code>cdr_next_X</code> tables. SIP message custom headers use <code>sip_msg_custom_headers</code> and <code>sip_msg_next_X</code> tables. The <code>system</code> table stores system-wide configuration including <code>manager_key</code> (type='manager_key') which is required for manager/sensor operations. If the sniffer fails to start with "failed read rsa key" error, check that <code>mysqlloadconfig</code> in <code>voipmonitor.conf</code> is NOT set to <code>no</code> 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 <code>SHOW TABLES LIKE 'cdr%';</code> 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
'''Keywords:''' database, schema, cdr, mysql, cdr_next, fbasename, cdr_rtp, video payload, cdr_proxy, cdr_sdp, cdr_siphistory, INET_NTOA, INET_ATON, sipcallerip, custom headers, cdr_custom_headers, manager_key, packet loss, jitter, mos, rtcp, a_rtcp_avgrtd_mult10, latency


'''Key Questions:'''
'''Key Questions:'''
* How do I query all RTP media ports negotiated in SDP for a call?
* What do a_ and b_ prefixes mean in cdr table columns?
* Which table stores SDP-negotiated RTP ports and IPs?
* How do I convert sipcallerip to readable IP address?
* What is the cdr_sdp table used for?
* How do I get the PCAP filename (fbasename) for a call?
* How do I view all media ports for a call using cdr_sdp table?
* How do I query video streams in cdr_rtp (payload >= 10000)?
* What is the difference between cdr_sdp and cdr_rtp tables?
* How do I find calls that went through a specific proxy?
* How do I filter CDRs by SIP caller IP?
* How do I find calls with REFER or other SIP methods?
* How do I filter CDRs by SIP domain?
* How do I query CDR custom headers?
* What is the correct column name for SIP caller IP in cdr table?
* Why does "failed read rsa key" error occur on startup?
* How do I convert IP address to integer for sipcallerip comparison?
* How do I sync manager_key between multiple databases?
* How do I use INET_ATON() to filter by IP address?
* Why can't old GUI download PCAP files after database migration?
* 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?

Latest revision as of 16:49, 8 January 2026


Database schema reference for VoIPmonitor. The cdr table stores one record per call; related tables store RTP statistics, custom headers, and metadata.

Schema Overview

-- List all CDR-related tables
SHOW TABLES LIKE 'cdr%';

Column Naming Conventions

Pattern Meaning Example
a_ Caller's stream (A-leg) a_lost, a_mos_f1_mult10
b_ Callee's stream (B-leg) b_lost, b_mos_f1_mult10
_mult10 Divide by 10 jitter_mult10 = 15 → jitter = 1.5
_mult100 Divide by 100 Percentage values
_mult1000 Divide by 1000 packet_loss_perc_mult1000

The cdr Table

Call Information & Timing

Column Description
ID Primary key
id_sensor Sensor ID (from voipmonitor.conf)
calldate / callend Start/end timestamps
duration Total length in seconds
connect_duration Connected time (after 200 OK)
progress_time Time to first provisional response (PDD)
bye 1 = normal termination with BYE
whohanged Which party sent BYE (caller/callee)
lastSIPresponse_id FK to cdr_sip_response

Participant Information

Column Description
caller / called Numbers from From/To headers
caller_domain / called_domain Domain parts
sipcallerip / sipcalledip IPs stored as integers (use INET_NTOA())
a_ua_id / b_ua_id FK to cdr_ua (User-Agent)

Quality Metrics

Packet Loss

Column Description
a_lost / b_lost Total RTP packets lost
packet_loss_perc_mult1000 Max loss percentage × 1000
a_sl1 - a_sl10 Distribution of consecutive loss events

Jitter & Delay

Column Description
a_avgjitter_mult10 Average jitter × 10
a_maxjitter Maximum jitter observed
a_d50 - a_d300 PDV distribution buckets (0-50ms, 50-70ms, etc.)

MOS (Mean Opinion Score)

Column Description
a_mos_f1_mult10 MOS with 50ms fixed buffer × 10
a_mos_f2_mult10 MOS with 200ms fixed buffer × 10
a_mos_adapt_mult10 MOS with adaptive buffer × 10
mos_min_mult10 Lowest MOS between A/B streams × 10

RTCP-Reported Metrics

These come from endpoint RTCP reports, not calculated by the sniffer. See Sniffer vs RTCP Packet Loss.

Column Description
a_rtcp_loss Packets lost (reported by endpoint)
a_rtcp_avgfr_mult10 Average fraction loss × 10
a_rtcp_avgjitter_mult10 Average jitter × 10
a_rtcp_avgrtd_mult10 Roundtrip delay (latency) in ms × 10

Related Tables

cdr_next (1:1 with cdr)

Extension table linked via cdr_ID.

Column Description
fbasename Derived from SIP Call-ID; used to link PCAP files. Special characters replaced with underscores (convertchar in config)
match_header Content of custom header for linking call legs (matchheader in config)

cdr_rtp (Per-Stream RTP Stats)

One row per RTP stream. Multiple streams per call.

Column Description
cdr_ID FK to cdr
ssrc RTP Synchronization Source identifier
saddr / daddr Source/destination IP (as integer)
sport / dport Source/destination port
payload Codec type. Video streams: payload ≥ 10000 (offset applied)
received / lost Packet counts

💡 Tip:

cdr_sdp (SDP-Negotiated Ports)

Stores media endpoints declared in SDP. Requires save_sdp_ipport = yes.

Column Description
ip / port Media IP and port from SDP
is_caller 1 = caller side, 0 = callee side

ℹ️ Note: cdr_sdp stores what endpoints agreed to use in SDP; cdr_rtp stores what was actually sent.

cdr_proxy (Proxy Chain)

IP addresses of SIP proxies the call traversed.

cdr_siphistory (SIP Method History)

Stores SIP requests/responses including REFER, BYE, CANCEL, OPTIONS, etc. Requires save_sip_history = all.

cdr_ua / cdr_sip_response (Lookup Tables)

Lookup tables for User-Agent strings and SIP response texts.

Common SQL Queries

Basic CDR Query with IP Conversion

SELECT
    ID, caller, called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip,
    duration
FROM cdr
WHERE calldate >= '2024-01-01'
  AND sipcallerip = INET_ATON('192.168.1.50')
LIMIT 10;

Get PCAP Filename (fbasename)

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;

Get User-Agent Strings

SELECT c.ID, ua_a.ua AS caller_ua, ua_b.ua AS callee_ua
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;

Find Calls Through Specific Proxy

SELECT c.ID, c.calldate, c.caller, c.called,
       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')
LIMIT 100;

Query Video Stream Quality

SELECT cdr_ID, payload,
       INET_NTOA(saddr) AS src_ip,
       received, lost,
       (lost / (received + lost)) * 100 AS loss_pct
FROM cdr_rtp
WHERE payload >= 10000  -- Video streams only
  AND cdr_ID = 12345;

Find Calls with Specific SIP Method (REFER)

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

Custom Headers

CDR-Level Custom Headers

CDR custom headers are stored in cdr_next_X tables. Use cdr_custom_headers to find the location: 

-- Step 1: Find table/column for header
SELECT dynamic_table, dynamic_column
FROM cdr_custom_headers
WHERE header_field = 'X-Customer-ID';
-- Result: dynamic_table=2, dynamic_column=1 → cdr_next_2.custom_header_1

-- Step 2: Query the data
SELECT c.ID, c.calldate, 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';

ℹ️ Note: The GUI's Reports module does not support grouping by custom headers. Use direct SQL queries.

SIP Message Custom Headers

Per-message headers are stored in sip_msg_next_X tables. Use sip_msg_custom_headers to find the location.

System Table

The system table stores configuration including manager_key for sensor operations. 

SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G

⚠️ Warning: If sniffer fails with "failed read rsa key", check that mysqlloadconfig is NOT set to no in voipmonitor.conf.

Synchronizing manager_key Between Databases

When multiple GUI instances share sensors (e.g., after database migration), mismatched manager_key values cause PCAP download failures.

Symptoms: "Unable to download PCAP" errors, empty SIP history in one GUI.

Fix: 

-- On active database: get the key
SELECT content FROM voipmonitor.`system` WHERE type='manager_key';

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

Users must log out and log back in after the update.

Troubleshooting

Missing sport/dport in cdr_rtp

If RTP streams from overlapping calls are incorrectly merged, add the missing columns: 

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

⚠️ Warning: ALTER TABLE can lock large tables for hours. Run during maintenance windows.

To find other recommended schema changes: 

journalctl -u voipmonitor | grep ALTER

See Also

AI Summary for RAG

Summary: VoIPmonitor database schema reference. Main table cdr stores one record per call with timing, participants, and quality metrics. Column naming: a_=caller, b_=callee, _multNN=divide by NN. Key tables: cdr_next (1:1, contains fbasename for PCAP linking), cdr_rtp (per-stream stats, video streams have payload≥10000), cdr_proxy (proxy chain IPs), cdr_sdp (SDP-negotiated ports), cdr_siphistory (SIP method history, requires save_sip_history=all). Custom headers stored in cdr_next_X tables; query cdr_custom_headers for mappings. System table stores manager_key; mismatch causes PCAP download failures.

Keywords: database, schema, cdr, mysql, cdr_next, fbasename, cdr_rtp, video payload, cdr_proxy, cdr_sdp, cdr_siphistory, INET_NTOA, INET_ATON, sipcallerip, custom headers, cdr_custom_headers, manager_key, packet loss, jitter, mos, rtcp, a_rtcp_avgrtd_mult10, latency

Key Questions:

  • What do a_ and b_ prefixes mean in cdr table columns?
  • How do I convert sipcallerip to readable IP address?
  • How do I get the PCAP filename (fbasename) for a call?
  • How do I query video streams in cdr_rtp (payload >= 10000)?
  • How do I find calls that went through a specific proxy?
  • How do I find calls with REFER or other SIP methods?
  • How do I query CDR custom headers?
  • Why does "failed read rsa key" error occur on startup?
  • How do I sync manager_key between multiple databases?
  • Why can't old GUI download PCAP files after database migration?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Database_structure&oldid=10147"