Database structure: Difference between revisions

From VoIPmonitor.org
(Add RTCP Roundtrip Delay columns (a_rtcp_avgrtd_mult10, b_rtcp_avgrtd_mult10) for packet delay/latency metrics)
(VS-1789: Document nth_occurrence column in cdr_custom_headers table)
 
(8 intermediate revisions 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_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_sip_response" as cdr_sip_response {
entity "cdr_siphistory" as cdr_siphistory {
   * ID : int <<PK>>
   cdr_ID <<FK>>
   --
   --
   lastSIPresponse : varchar
   method, calldate, cseq
}
}


entity "cdr_ua" as cdr_ua {
entity "cdr_ua" as cdr_ua {
   * ID : int <<PK>>
   * ID <<PK>>
   --
   --
   ua : varchar
   ua (User-Agent string)
}
 
entity "cdr_sip_response" as cdr_sip_response {
  * ID <<PK>>
  --
  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_proxy : "1:N"
cdr ||--o{ cdr_sdp : "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 112: 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>whohanged</code> || enum || Indicates which party sent the <code>BYE</code> message first (caller or callee)
|-
| <code>lastSIPresponse_id</code> || int || Foreign key to <code>cdr_sip_response</code> table for the last final SIP response
|}
 
=== Call Timing & Duration ===
 
{| class="wikitable"
|-
|-
! Column !! Type !! Description
| <code>connect_duration</code> || Connected time (after 200 OK)
|-
|-
| <code>duration</code> || int || Total call length in seconds (<code>callend - calldate</code>)
| <code>progress_time</code> || Time to first provisional response ([[Glossary#Post_Dial_Delay_.28PDD.29|PDD]])
|-
|-
| <code>connect_duration</code> || int || Connected portion length in seconds (from first <code>200 OK</code> to <code>callend</code>)
| <code>bye</code> || <code>1</code> = normal termination with BYE
|-
|-
| <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>whohanged</code> || Which party sent BYE (caller/callee)
|-
|-
| <code>first_rtp_time</code> || int || Time from call start to first RTP packet detection
| <code>lastSIPresponse_id</code> || FK to <code>cdr_sip_response</code>
|}
|}


=== Participant Information ===
== Participant Information ==


{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Type !! Description
! Column !! 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</code> / <code>called</code> || Numbers from From/To headers
|-
|-
| <code>caller_reverse</code> / <code>called_reverse</code> || varchar || Reversed strings for efficient <code>LIKE '%search'</code> queries
| <code>caller_domain</code> / <code>called_domain</code> || Domain parts
|-
|-
| <code>callername</code> || varchar || Display name from the <code>From</code> SIP header
| <code>sipcallerip</code> / <code>sipcalledip</code> || IPs stored as integers (use <code>INET_NTOA()</code>)
|-
|-
| <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> || FK to <code>cdr_ua</code> (User-Agent)
|-
| <code>a_ua_id</code> / <code>b_ua_id</code> || int || Foreign keys to <code>cdr_ua</code> table for User-Agent strings
|}
|}


{{Note|
== Quality Metrics ==
'''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 ====


=== Packet Loss ===
{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Description
! Column !! Description
|-
|-
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost (detected by sequence number gaps)
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost
|-
|-
| <code>lost</code> || Higher value between <code>a_lost</code> and <code>b_lost</code>
| <code>packet_loss_perc_mult1000</code> || Max loss percentage × 1000
|-
|-
| <code>packet_loss_perc_mult1000</code> || Maximum packet loss percentage, multiplied by 1000
| <code>a_sl1</code> - <code>a_sl10</code> || Distribution of consecutive loss events
|-
| <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.)
|}
|}


==== Jitter & Delay (PDV) ====
=== Jitter & Delay ===
 
{| 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_avgjitter_mult10</code> || Average jitter × 10
|-
| <code>a_maxjitter</code> / <code>b_maxjitter</code> || Maximum jitter value observed during the call
|-
|-
| <code>jitter_mult10</code> || Higher <code>maxjitter</code> value between streams, multiplied by 10
| <code>a_maxjitter</code> || Maximum jitter observed
|-
|-
| <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.)
| <code>a_d50</code> - <code>a_d300</code> || PDV distribution buckets (0-50ms, 50-70ms, etc.)
|}
|}


==== MOS (Mean Opinion Score) ====
=== MOS (Mean Opinion Score) ===
 
{| 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_mos_f1_mult10</code> || [[Comprehensive_Guide_to_VoIP_Voice_Quality#MOS_Calculation_in_VoIPmonitor|MOS]] with 50ms fixed buffer × 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_f2_mult10</code> || MOS with 200ms fixed buffer × 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_mos_adapt_mult10</code> || MOS with adaptive buffer × 10
|-
|-
| <code>mos_min_mult10</code> || Lowest MOS score between A and B streams, multiplied by 10
| <code>mos_min_mult10</code> || Lowest MOS between A/B streams × 10
|}
|}


==== RTCP Reported Metrics ====
=== 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]].
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"
Line 390: Line 186:
! 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_rtcp_loss</code> || Packets lost (reported by endpoint)
|-
|-
| <code>a_rtcp_avgfr_mult10</code> / <code>b_rtcp_avgfr_mult10</code> || Average fraction loss from RTCP, multiplied by 10
| <code>a_rtcp_avgfr_mult10</code> || Average fraction loss × 10
|-
|-
| <code>a_rtcp_avgjitter_mult10</code> / <code>b_rtcp_avgjitter_mult10</code> || Average jitter from RTCP, multiplied by 10
| <code>a_rtcp_avgjitter_mult10</code> || Average jitter × 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>a_rtcp_avgrtd_mult10</code> || '''Roundtrip delay (latency)''' in ms × 10
|}
|}


=== Other Columns ===
= Related Tables =
 
== <code>cdr_next</code> (1:1 with cdr) ==
 
Extension table linked via <code>cdr_ID</code>.


{| class="wikitable"
{| class="wikitable"
Line 405: Line 205:
! Column !! Description
! Column !! Description
|-
|-
| <code>dscp</code> || DSCP values from SIP and RTP packets for QoS analysis
| <code>fbasename</code> || Derived from SIP Call-ID; used to link PCAP files. Special characters replaced with underscores (<code>convertchar</code> in config)
|-
|-
| <code>payload</code> || Codec payload type number used during the call
| <code>match_header</code> || Content of custom header for linking call legs (<code>matchheader</code> in config)
|}
|}


== Important Related Tables ==
== <code>cdr_rtp</code> (Per-Stream RTP Stats) ==


=== <code>cdr_next</code> ===
One row per RTP stream. Multiple streams per call.
 
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"
{| class="wikitable"
Line 420: Line 218:
! Column !! Description
! Column !! Description
|-
|-
| <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>cdr_ID</code> || FK to <code>cdr</code>
|-
|-
| <code>match_header</code> || Content of custom header (from <code>matchheader</code> in <code>voipmonitor.conf</code>) for linking call legs
| <code>ssrc</code> || RTP Synchronization Source identifier
|}
 
'''Example: Finding PCAP filename for a call:'''
<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> ===
 
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.
 
{| class="wikitable"
|-
|-
! Column !! Description
| <code>saddr</code> / <code>daddr</code> || Source/destination IP (as integer)
|-
|-
| <code>header_field</code> || The name of the custom SIP header (e.g., <code>'X-IntNum'</code>)
| <code>sport</code> / <code>dport</code> || Source/destination port
|-
|-
| <code>dynamic_table</code> || The table number (e.g., <code>2</code> means <code>cdr_next_2</code>)
| <code>payload</code> || Codec type. '''Video streams: payload ≥ 10000''' (offset applied)
|-
|-
| <code>dynamic_column</code> || The column number (e.g., <code>1</code> means <code>custom_header_1</code>)
| <code>received</code> / <code>lost</code> || Packet counts
|}
|}


{{Tip|
{{Tip|To query video streams only: <code>WHERE payload >= 10000</code>}}
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 ===
== <code>cdr_sdp</code> (SDP-Negotiated Ports) ==


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.
Stores media endpoints declared in SDP. Requires <code>save_sdp_ipport = yes</code>.
 
<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
-- 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 619: 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>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>payload</code> || Codec payload type number. Video streams are stored with an offset of <strong>+10000</strong> to distinguish them from audio streams
|-
|-
| <code>received</code> || Number of RTP packets received
| <code>is_caller</code> || 1 = caller side, 0 = callee side
|-
| <code>lost</code> || Number of RTP packets lost (detected by sequence number gaps)
|-
| <code>jitter</code> || Jitter value (varies by schema version)
|}
|}


{{Tip|
{{Note|<code>cdr_sdp</code> stores what endpoints '''agreed to use''' in SDP; <code>cdr_rtp</code> stores what was '''actually sent'''.}}
<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:'''
== <code>cdr_proxy</code> (Proxy Chain) ==
<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:'''
IP addresses of SIP proxies the call traversed.
<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>


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


Stores detected DTMF key presses. Requires <code>dtmf2db = yes</code> in <code>voipmonitor.conf</code>.
Stores SIP requests/responses including REFER, BYE, CANCEL, OPTIONS, etc. Requires <code>save_sip_history = all</code>.


=== <code>cdr_proxy</code> ===
== <code>cdr_ua</code> / <code>cdr_sip_response</code> (Lookup Tables) ==


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.
Lookup tables for User-Agent strings and SIP response texts.


{| class="wikitable"
= Common SQL Queries =
|-
! Column !! Description
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
|-
| <code>dst</code> || Proxy IP address (stored as integer, use <code>INET_NTOA()</code> to convert)
|}


{{Tip|
== Basic CDR Query with IP Conversion ==
<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:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- List all proxies a call passed through
SELECT
SELECT
     cdr_ID,
     ID, caller, called,
     INET_NTOA(dst) AS proxy_ip
     INET_NTOA(sipcallerip) AS caller_ip,
FROM cdr_proxy
    INET_NTOA(sipcalledip) AS called_ip,
WHERE cdr_ID = 12345;
    duration
FROM cdr
WHERE calldate >= '2024-01-01'
  AND sipcallerip = INET_ATON('192.168.1.50')
LIMIT 10;
</syntaxhighlight>
</syntaxhighlight>


'''Example: Finding calls that used a specific proxy:'''
== Get PCAP Filename (fbasename) ==
 
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Find all calls that went through a specific proxy server
SELECT c.ID, c.caller, c.called, cn.fbasename
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
FROM cdr c
JOIN cdr_proxy cp ON c.ID = cp.cdr_ID
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE cp.dst = INET_ATON('192.168.1.100')
WHERE c.ID = 12345;
ORDER BY c.calldate DESC
LIMIT 100;
</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 751: 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 `REFER`, `BYE`, `CANCEL`, `OPTIONS`, `SUBSCRIBE`, `NOTIFY`, and `MESSAGE`. 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,
{| class="wikitable"
      INET_NTOA(cp.dst) AS proxy_ip
|-
FROM cdr c
! Column !! Description
JOIN cdr_proxy cp ON c.ID = cp.cdr_ID
|-
WHERE cp.dst = INET_ATON('192.168.1.100')
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
LIMIT 100;
|-
| <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:'''
Configure in <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
save_sip_history = all
</syntaxhighlight>
</syntaxhighlight>
Restart the voipmonitor sniffer after changing this configuration.


'''Example: Find all calls containing a SIP REFER method:'''
== Query Video Stream Quality ==
<syntaxhighlight lang="sql">
SELECT
    cdr_ID,
    callid,
    calldate
FROM cdr_siphistory
WHERE calldate > '2024-01-01 00:00:00'
  AND method = 'REFER'
GROUP BY cdr_ID;
</syntaxhighlight>


'''Example: Find calls with OPTIONS or SUBSCRIBE messages (for troubleshooting qualify pings):'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT
SELECT cdr_ID, payload,
    cdr_ID,
      INET_NTOA(saddr) AS src_ip,
    callid,
      received, lost,
    method,
      (lost / (received + lost)) * 100 AS loss_pct
    calldate
FROM cdr_rtp
FROM cdr_siphistory
WHERE payload >= 10000  -- Video streams only
WHERE calldate > CURDATE() - INTERVAL 1 DAY
   AND cdr_ID = 12345;
   AND method IN ('OPTIONS', 'SUBSCRIBE')
ORDER BY calldate DESC
LIMIT 20;
</syntaxhighlight>
</syntaxhighlight>


== SIP Message Custom Headers ==
== Find Calls with Specific SIP Method (REFER) ==
 
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.
 
{{Note|
'''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
SELECT cdr_ID, callid, calldate
SELECT dynamic_table, dynamic_column
FROM cdr_siphistory
FROM sip_msg_custom_headers
WHERE method = 'REFER'
WHERE header_field = 'X-My-Custom-Header';
  AND calldate > '2024-01-01'
GROUP BY cdr_ID;
</syntaxhighlight>
</syntaxhighlight>


This query returns:
= Custom Headers =
* <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 ===
== CDR-Level 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.
CDR custom headers are stored in <code>cdr_next_X</code> tables. Use <code>cdr_custom_headers</code> to find the location:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Example: Query sip_msg_next_1 for custom_header_2 = 'desired-value'
-- Step 1: Find table/column for header
-- 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|
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 ===
 
<syntaxhighlight lang="sql">
-- Example: Find SIP messages with X-Customer-ID = '12345' in the last 1 hour
 
-- Step 1: Find where X-Customer-ID is stored
SELECT dynamic_table, dynamic_column
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
FROM cdr_custom_headers
WHERE header_field = 'X-Customer-ID';
WHERE header_field = 'X-Customer-ID';
-- Result: dynamic_table=2, dynamic_column=1 → cdr_next_2.custom_header_1


-- Example result: dynamic_table=3, dynamic_column=1
-- Step 2: Query the data
 
SELECT c.ID, c.calldate, cn.custom_header_1 AS customer_id
-- Step 2: Query the appropriate table
FROM cdr c
SELECT
JOIN cdr_next_2 cn ON c.ID = cn.cdr_ID
    sm.sip_msg_ID,
WHERE cn.custom_header_1 = '12345';
    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 ===
 
To capture custom SIP headers, configure them in <code>/etc/voipmonitor.conf</code>:
 
<syntaxhighlight lang="ini">
[general]
# List custom headers to capture from SIP messages
sip_headers = X-Customer-ID, X-Branch-Code, X-Transaction-ID
</syntaxhighlight>
 
After modifying the configuration, restart the sensor:
<syntaxhighlight lang="bash">
service voipmonitor restart
</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.
 
=== <code>system</code> Table ===
 
The <code>system</code> table stores system-wide configuration and keys used by VoIPmonitor.
 
;<code>manager_key</code>
: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">
-- Query the manager_key from the system table
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
</syntaxhighlight>
</syntaxhighlight>


:'''Troubleshooting manager_key errors:'''
{{Note|The GUI's [[Reports|Reports]] module does not support grouping by custom headers. Use direct SQL queries.}}
:# Check that <code>mysqlloadconfig</code> is NOT set to <code>no</code> in <code>/etc/voipmonitor.conf</code>
=== <code>cdr_custom_headers</code> Table Structure ===
:# 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>
: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:
:* Download PCAP files (downloads fail or return corrupt data)
:* View SIP history from the sensor (empty history or errors)
:* Access encrypted data from sensors
 
:This issue commonly occurs during database migration when sensors write to a new database but an old GUI remains in use.
 
:'''Symptoms of manager_key mismatch:'''
:* Old GUI users cannot download PCAP files even though files exist
:* "Unable to download PCAP" errors in browser or GUI
:* SIP history appears empty or incomplete
:* No explicit "failed read rsa key" error (this only occurs on sniffer startup)
 
:'''Solution: Synchronize manager_key across databases'''
 
:The fix is to copy the <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
:<pre>
-- On the database server that sensors are actively connected to
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
</pre>
: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
:<pre>
-- On the old database server
UPDATE voipmonitor.`system` SET content = '{"key":"<key_from_step_1>","iv":"<iv_from_step_1>"}' WHERE type = 'manager_key';
</pre>
 
:;Step 3: Log out and log back in
:Users must log out of the old GUI and log back in for the key synchronization to take effect.
 
:;Step 4: Verify the fix
:Attempt to download a PCAP file through the old GUI to verify the fix worked.
 
:'''Important Notes:'''
:* Do NOT manually generate or modify the <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:''' 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_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_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, pcab 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
The <code>cdr_custom_headers</code> table stores the configuration for custom header extraction:
 
'''Key Questions:'''
* How do I filter CDRs by SIP caller IP?
* How do I filter CDRs by SIP domain?
* What is the correct column name for SIP caller IP in cdr table?
* How do I convert IP address to integer for sipcallerip comparison?
* How do I use INET_ATON() to filter by IP address?
* How do I get fbasename for a CDR?
* Which table stores the fbasename for PCAP file linking?
* How do I join cdr and cdr_next to get fbasename with CDR data?
* How do I filter CDRs by caller_domain or called_domain?
* Where is the manager_key stored in the VoIPmonitor database?
* Why does the sniffer fail to start with "failed read rsa key" error?
* How do I fix the "failed read rsa key" error on startup?
* What does mysqlloadconfig=no do in voipmonitor.conf?
* How do I check if the manager_key exists in the database?
* What is the system table in VoIPmonitor?
* How do I query the manager_key from the system table?
* What do the a_ and b_ prefixes mean in the cdr table?
* How do I convert sipcallerip to a readable IP address?
* How do I get the Call-ID for a call from the database?
* Which table stores the User-Agent of phones?
* How do I query video streams or video call quality using cdr_rtp?
* How do I identify video streams in the database (payload filtering)?
* How do I find calls that went through a specific proxy server?
* How do I find all intermediate proxy IPs for a specific call?
* Which table stores SIP proxy IP addresses a call traversed?
* How do I find calls that transited through a specific IP address?
* How do I query CDRs with a custom header value?
* How do I find which cdr_next_X table stores my custom header?
* How do I query SIP messages with a custom header value?
* Why is my custom SIP header not in the cdr table?
* How do I find the database table and column for a custom SIP header added via web interface?
* A custom SIP header cannot be found in the main cdr table for querying - how do I query it?
* Where are CDR custom headers stored in the database?
* How do I use cdr_custom_headers table to find custom header data?
* How do I synchronize manager_key between multiple VoIPmonitor databases?
* Why cannot the old GUI download PCAP files after migrating to a new database with shared sensors?
* How do I fix PCAP download failures in old GUI after database migration?
* Why is SIP history empty or missing in one GUI but not another?
* How do I copy manager_key from one database to another?
* What causes manager_key mismatch between multiple GUI instances?
* How do I share sensors between old and new GUI instances?
* How do I fix encryption key mismatch between databases?mail plain text || Enable to force HTML-only emails. Useful for mail clients that display only plain text incorrectly (e.g., older Outlook versions)
|}
 
=== License ===
 
License and notification email configuration.
 
[[File:settings-syslicense.png]]


{| class="wikitable"
{| class="wikitable"
|-
|-
! Setting !! Description
! Column !! Description
|-
|-
| License token || Short text token for retrieving license key from VoIPmonitor portal
| <code>header_field</code> || SIP header name to capture
|-
| License key || Full license key content (multi-line text)
|-
| get/update license key || Button to fetch or update the license from the VoIPmonitor portal
|-
| License email || Email address for receiving license issue and overage notification emails
|}
 
'''Updating License After Payment:'''
 
After purchasing or renewing a license through the voipmonitor.org portal, update your GUI license as follows:
 
# Navigate to '''Settings > License'''
# Click the '''get/update license key''' button
 
The GUI will automatically fetch your updated license from the portal if the payment has been processed. You do not need to manually enter a license key or token unless automatic retrieval fails.
 
'''Manually Retrieving License from Portal:'''
 
If the automatic update does not work, retrieve your license directly from the portal:
 
# Log in to https://www.voipmonitor.org
# Navigate to '''Services > My services'''
# Locate your VoIPmonitor license
# Click to view the license details
# Copy the full license key content
# Paste it into the '''License key''' field in '''Settings > License'''
 
'''Note:''' The license key is displayed as multi-line text. Ensure you copy the entire content when pasting.
 
To configure the license expiry notification for multiple recipients, enter all email addresses separated by a comma (e.g., <code>user1@example.com,user2@example.com</code>).
 
'''Disabling License Notification Emails:'''
 
To stop receiving license issue and overage notification emails:
# Navigate to '''Settings > License'''
# Remove the email address from the "License email" field
# Save the changes
 
'''Note:''' This only disables license-related notification emails. Other automated emails (QoS alerts, daily reports, sensor health alerts) will continue to function.
 
=== GeoIP ===
 
Configuration for GeoIP services used in the CDR Map view.
 
[[File:settings-sysgeoip.png]]
 
{| class="wikitable"
|-
|-
! Setting !! Description
| <code>dynamic_table</code> || Target table number (cdr_next_X)
|-
|-
| Use GeoIP local database || Enable/disable the internal GeoIP database (if loaded via [[#Load_GeoIP_Data|Load GeoIP Data]])
| <code>dynamic_column</code> || Target column number (custom_header_X)
|-
|-
| GeoIP maxmind.com KEY || API key for MaxMind GeoIP service
| <code>type</code> || Extraction type (first/last/nth)
|-
|-
| GeoIP ipinfodb.com KEY || API key for IPInfoDB service
| <code>nth_occurrence</code> || '''(New in 35.x)''' Specific occurrence number to extract when type is "nth" (e.g., 2 for second occurrence)
|}
|}


=== Advanced ===
{{Note|The <code>nth_occurrence</code> column uses packet timestamps for accurate ordering, ensuring correct results even when packet time-based reordering is disabled in the sniffer.}}
== SIP Message Custom Headers ==


Advanced configuration options for power users and specific use cases.
Per-message headers are stored in <code>sip_msg_next_X</code> tables. Use <code>sip_msg_custom_headers</code> to find the location.


[[File:settings-sysadvanced.png]]
= System Table =


{| class="wikitable"
The <code>system</code> table stores configuration including <code>manager_key</code> for sensor operations.
|-
! Setting !! Description
|-
| Enable CDR group panel || Show/hide the group panel at the bottom of the CDR view
|-
| ENABLE_CDR_FORCE_INDEX_CALLDATE || Force use of the calldate index on CDR queries. Enable only for unoptimized MySQL installations experiencing slow queries
|-
| ENABLE_CSRF_CHECK || Enable CSRF (Cross-Site Request Forgery) protection in the GUI. When enabled, the GUI validates CSRF tokens for state-changing operations, increasing session security and mitigating certain types of web attacks. Set to <code>TRUE</code> to enable. Recommended for production environments
|-
| Enable database IP reverse lookup || Resolve IP addresses to names using the internal IP lookup table
|-
| Enable DNS reverse lookup || Resolve IP addresses to names using DNS
|-
| Enable database number lookup || Resolve phone numbers to names using the internal prefix lookup table
|-
| Disable rtpfirstleg param || Disable the <code>--rtp-firstleg</code> parameter for PCAP audio decoding. Enable only if experiencing audio issues
|-
| Disable URL wav protection || Skip session authentication for WAV file downloads. Use with '''WAV download key''' for secure external access
|-
| WAV download key || Secret key required for WAV downloads when URL protection is disabled
|-
| Hide SIP domain in CDR || Hide SIP domains in the CDR display
|-
| Hide live play || Hide live playback buttons in [[Active_calls|Active calls]]
|-
| Hide WAV play || Hide WAV playback buttons in CDR view
|-
| Upload sniffer conf path || Path to voipmonitor.conf for PCAP upload functionality
|-
| CDR share key || Secret string used to generate unique hashes for CDR share URLs
|-
| Folder for export CSV || Directory where CSV files from [[Reports#CSV_Export_via_Crontab_Scheduler|crontab scheduler tasks]] are saved
|-
| CSV name prefix || Optional prefix for CSV filenames generated by crontab tasks
|-
| Delete CSV after X days || Auto-delete CSV files older than specified days
|-
| Pcap deduplication before download || Enable to remove duplicate and retransmitted SIP/RTP packets when downloading PCAP files from the GUI. This may cause a mismatch between the packet count shown in the GUI SIP History and the packet count in the downloaded PCAP file. Disable to ensure the downloaded PCAP contains all captured packets including duplicates
|-
| Http proxy (for upgrades) || Proxy server address and port for automatic GUI and sniffer upgrades via the web interface. Required when the VoIPmonitor server is behind a corporate firewall or proxy and cannot connect directly to download.voipmonitor.org or github.com. Format: <code>http://proxy-server-ip:port</code> or <code>http://username:password@proxy-server-ip:port</code> for authenticated proxies
|-
| Enable GUI to run in iframe || Allow the GUI to be loaded in an <code>iframe</code> (embed the VoIPmonitor interface in other web applications). Set to <code>true</code> to enable. This is required when hosting the GUI in subfolders (e.g., <code>/ucloud</code>, <code>/unite</code>) within an iframe. By default, the GUI sends security headers that prevent iframe embedding for clickjacking protection
|}


=== Troubleshooting: GUI Upgrades Behind Proxy Servers ===
<syntaxhighlight lang="sql">
 
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
If the GUI or sensor upgrade process fails due to network restrictions or firewall blocking direct internet access:
 
'''Solution 1: Configure HTTP Proxy in GUI (Recommended)'''
# Navigate to '''Settings > System Configuration > Advanced'''
# Find the '''Http proxy (for upgrades)''' field
# Enter your proxy server address: <code>http://proxy-server-ip:port</code>
# If authentication is required, include credentials: <code>http://username:password@proxy-server-ip:port</code>
# Save the settings
# Retry the GUI upgrade (Settings > System > Upgrade) or sensor upgrade (Settings > Sensors)
 
'''Solution 2: Proxy for Remote Sensors (curlproxy)'''
For remote sensors that need to download packages independently, configure the <code>curlproxy</code> parameter directly on the sensor:
# SSH into the remote sensor server
# Edit the sensor configuration: <code>sudo nano /etc/voipmonitor.conf</code>
# Add or modify the <code>curlproxy</code> line in the <code>[general]</code> section:
<syntaxhighlight lang="ini">
[general]
curlproxy = http://proxy-server-ip:port
</syntaxhighlight>
</syntaxhighlight>
# Restart the sensor service: <code>sudo systemctl restart voipmonitor</code>
# Retry the upgrade from the GUI (Settings > Sensors)


'''References:'''
{{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>.}}
* [[FAQ#How_do_I_troubleshoot_internet_connectivity_issues|FAQ: Troubleshooting Internet Connectivity]]
* [[GUI_Installation|GUI Installation Guide]]


=== Troubleshooting: GUI in iframe Not Loading Properly ===
== Synchronizing manager_key Between Databases ==


If the VoIPmonitor GUI is embedded in an <code>iframe</code> (e.g., from another web application) and fails to load or shows 301 redirect errors:
When multiple GUI instances share sensors (e.g., after database migration), mismatched <code>manager_key</code> values cause PCAP download failures.


'''Symptoms:'''
'''Symptoms:''' "Unable to download PCAP" errors, empty SIP history in one GUI.
* Iframe displays error messages or blank content
* 301 redirect when accessing subfolder URLs (e.g., <code>/ucloud</code>, <code>/unite</code>)
* Browser console shows refused to load in iframe errors


'''Solution: Enable iframe Support in System Configuration'''
'''Fix:'''
<syntaxhighlight lang="sql">
-- On active database: get the key
SELECT content FROM voipmonitor.`system` WHERE type='manager_key';


The GUI sends security headers (such as <code>X-Frame-Options</code>) by default to prevent clickjacking attacks. To allow the GUI to run in an iframe:
-- On old database: update the key
UPDATE voipmonitor.`system`
SET content = '{"key":"<key_from_active>","iv":"<iv_from_active>"}'
WHERE type = 'manager_key';
</syntaxhighlight>


# Navigate to '''Settings > System Configuration > Advanced'''
Users must log out and log back in after the update.
# Find the '''Enable GUI to run in iframe''' setting
# Set it to <code>true</code>
# Save the settings
# Re-test the iframe functionality


Apply this setting to all relevant GUI installations/folders if you have multiple instances (e.g., <code>/ucloud</code> and <code>/unite</code>).
= Troubleshooting =


'''Additional Notes:'''
== Missing <code>sport</code>/<code>dport</code> in cdr_rtp ==
* This is a GUI-level setting for security header configuration, not a web server configuration
* After enabling, the browser should be able to load the GUI content within the iframe
* For security reasons, only enable this if you trust the parent application hosting the iframe


=== Troubleshooting: License Key Renewal Failures ===
If RTP streams from overlapping calls are incorrectly merged, add the missing columns:


If the "get/update license key" button fails or the license cannot be renewed automatically, the most common cause is a firewall blocking outbound HTTPS connections to the VoIPmonitor portal.
<syntaxhighlight lang="sql">
ALTER TABLE cdr_rtp ADD `sport` smallint unsigned DEFAULT NULL AFTER `saddr`;
ALTER TABLE cdr_rtp ADD `dport` smallint unsigned DEFAULT NULL AFTER `daddr`;
</syntaxhighlight>


'''Primary Solution: Configure Firewall Rules'''
{{Warning|1=<code>ALTER TABLE</code> can lock large tables for hours. Run during maintenance windows.}}
 
The GUI requires outbound access to the VoIPmonitor portal servers via HTTPS (TCP port 443). Configure your firewall to allow:


To find other recommended schema changes:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Example: Allow outbound HTTPS to voipmonitor.org
journalctl -u voipmonitor | grep ALTER
sudo firewall-cmd --permanent --add-service=https
</syntaxhighlight>
# Or allow specific traffic
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" destination address="voipmonitor.org" port protocol="tcp" port="443" accept'
sudo firewall-cmd --reload</syntaxhighlight>
 
For iptables-based firewalls:
<syntaxhighlight lang="bash">
# Allow outbound HTTPS to voipmonitor.org
sudo iptables -A OUTPUT -p tcp -d voipmonitor.org --dport 443 -j ACCEPT
sudo iptables -A INPUT -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT
# Save rules (distribution-specific)
sudo iptables-save | sudo tee /etc/iptables/rules.v4</syntaxhighlight>
 
For corporate environments with restrictive firewalls or outbound web proxies, contact your network administrator to:
* Allow outbound HTTPS (port 443) to <code>voipmonitor.org</code>
* Allow access to <code>download.voipmonitor.org</code> and <code>github.com</code> (for upgrades)
* Configure any necessary proxy settings (see [[#Troubleshooting:_GUI_Upgrades_Behind_Proxy_Servers|GUI Upgrades Behind Proxy Servers]])
 
'''Alternative Solutions'''
 
If you cannot configure firewall rules or have no internet access:
 
*'''Option 1: Use HTTP Proxy'''
Configure the <code>Http proxy (for upgrades)</code> setting in [[#Advanced|Settings > System Configuration > Advanced]] if a corporate proxy is available. This setting routes HTTPS requests including license renewal through an approved proxy server.
 
*'''Option 2: Offline Activation (Last Resort)'''
If network access is completely blocked, use offline activation:
# Log in to https://www.voipmonitor.org
# Navigate to '''Services > My services'''
# Locate your VoIPmonitor license
# Click the '''license''' button
# Copy the '''full license key content''' (multi-line text including Expires, id, hwid, maxcalls, etc.)
# Paste the full content into the '''License key''' field in Settings > License
# Click '''Save'''
 
'''Verification'''
After configuring firewall rules or updating the license:
# Return to '''Settings > License'''
# Click the '''get/update license key''' button
# Verify the license status shows as current and not expired
 
== Localization ==
 
Create custom translations for the GUI interface. Localizations are not 100% complete; please report missing translation items.
 
[[File:settings-localisationform.png]]
 
[[File:settings-localisationgrid.png]]
 
* Red numbers indicate untranslated items, which is useful after upgrading to identify new strings
* Changes take effect after logout/login
 
== CDR View Custom URL ==
 
Add custom hyperlinks to the CDR view Commands column. This is useful for integrating external monitoring or CRM systems.
 
=== Configuration ===
 
Navigate to '''GUI > Settings > CDR view custom URL'''.
 
[[File:cdr_view_custom_url.png]]
 
You can include CDR parameters in the URL using two methods:


# '''Via Parameters and Custom headers items:''' Values are appended as query parameters (e.g., <code>?paramName=value</code>)
= See Also =
# '''Directly in URL:''' Use <nowiki>{{paramName}}</nowiki> syntax, which is replaced with the actual value


=== Display ===
* [[Call_Detail_Record_-_CDR|CDR View (GUI)]]
* [[Database_troubleshooting|Database Troubleshooting]]
* [[Sniffer_configuration#Database_Configuration|Database Configuration]]
* [[WEB_API|Web API Documentation]]


Configured custom URLs appear as links in the Commands column of the CDR view.


== AI Summary for RAG ==
= AI Summary for RAG =


'''Summary:''' This article documents VoIPmonitor GUI settings including sensor configuration (with SSL/TLS parameters for decrypting encrypted SIP traffic), sensor health monitoring via RRD charts (buffer usage and packet drops graphs to diagnose sniffer host overload), troubleshooting server instances that do not appear in the GUI after hardware replacement, disabling sensors to stop data collection, CDR custom headers with their limitations and performance optimization (database index creation), distributed architecture troubleshooting for custom headers not visible in central GUI, GeoIP data loading, system configuration (basic, database, national, intervals, email, license, GeoIP, advanced), localization, and custom CDR URLs. Advanced settings include Http proxy (for upgrades) for configuring proxy servers behind firewalls, Pcap deduplication before download which can cause packet count mismatches between GUI SIP History and downloaded PCAP files, and ENABLE_CSRF_CHECK for security hardening by enabling CSRF (Cross-Site Request Forgery) protection tokens in the GUI to mitigate web attacks. Key topics: Enable GUI to run in iframe setting in Settings > System Configuration > Advanced allows embedding the VoIPmonitor GUI in iframes for subfolders like /ucloud or /unite - by default the GUI sends security headers (X-Frame-Options) to prevent iframe embedding for clickjacking protection; GUI in iframe not loading properly with 301 redirect errors can be fixed by enabling the "Enable GUI to run in iframe" setting to true; ENABLE_CSRF_CHECK in Settings > System Configuration > Advanced enables CSRF token validation for state-changing operations to increase session security and mitigate web attacks including XSS vulnerabilities - set to TRUE to enable and recommended for production environments; LICENSE UPDATE: After purchasing or renewing a license through voipmonitor.org portal, navigate to Settings > License and click the "get/update license key" button - the GUI automatically fetches your updated license if payment has been processed (no manual entry needed); if automatic retrieval fails, retrieve license manually from portal: log in to voipmonitor.org, navigate to Services > My services, locate license, view details, copy full license key content (multi-line text), paste into License key field in Settings > License; license token is a short text string for retrieving license key; license key field accepts full multi-line license key content; "get/update license key" button fetches license from VoIPmonitor portal; SENSOR DISABLE: Disabling a sensor in the GUI does NOT stop it from collecting data - the sensor process continues to capture and write to the database regardless of GUI status. To stop data collection permanently, run "systemctl stop voipmonitor" on the sensor host machine. Alternatively, use capture rules with SKIP=ON to prevent CDRs from being written to the database while keeping the service running for selective blocking; custom headers store raw SIP header values without regexp extraction or delimiter aggregation; DATABASE INDEX OPTIMIZATION: Custom header columns in cdr_next_X tables are NOT indexed by default to maintain high database write performance - to optimize performance for frequent or large-scale queries on custom SIP headers, manually create a database index by first querying cdr_custom_headers to find dynamic_table and dynamic_column (e.g., header 'X-Custom-Header' stored in cdr_next_2, column custom_header_3), then execute CREATE INDEX idx_custom_header_3 ON cdr_next_2 (custom_header_3) - adding indexes increases database load during CDR insertion so only create indexes on frequently queried headers; use EXPLAIN to verify the database uses your index (type: ref is index lookup, type: ALL is full table scan); if using cdr_partition_by_hours or daily partitioning, index is automatically created on all partitions; sensor SSL settings (ssl_key, ssl_cert, ssl_ipport) can be configured via GUI wrench icon; sensor health monitoring via RRD charts (Settings > Sensors > chart icon) showing buffer usage and packet drops to diagnose sniffer overload vs network quality issues; sensor troubleshooting for non-deletable local sensors in server/probe deployments; server instance troubleshooting when replacing hardware (server/SBC) and reusing IP address - requires manually creating sensor entry in Settings > Sensors and configuring managerip/managerport in voipmonitor.conf on the new server instance; DISTRIBUTED ARCHITECTURE: Custom headers not visible in central GUI for specific sensor despite being configured - check sensor's server_destination, server_destination_port, and server_password in /etc/voipmonitor.conf; delete sensor entry from GUI Settings > Sensors and restart sniffer service to re-register; without correct server_destination configuration, sensor captures headers locally but does not transmit CDRs to central database; GUI and sensor upgrades behind proxy servers can be configured via Settings > System Configuration > Advanced > Http proxy (for upgrades) or via curlproxy in voipmonitor.conf for remote sensors; license notification emails can be disabled by removing the email address in Settings > License; license email supports multiple recipients by entering email addresses separated by a comma (e.g., user1@example.com,user2@example.com); troubleshooting CDR custom headers not being captured or showing truncated content - check snaplen parameter (increase to 3200 or higher for long SIP headers like STIR/SHAKEN P-Asserted-Identity with verstat parameters) and custom_headers_max_size parameter (increase from default 1024 if header content is truncated); CDR timezone configuration - use "Sensors Timezone" setting in Settings > System Configuration > National to fix CDR times displaying in UTC instead of local timezone; the regular "Timezone" setting is for GUI host (reports, alerts), not for CDR data; all sensors sending data to the same database should use the same timezone; sensor OS timezone can be overridden via timezone or utc options in voipmonitor.conf.
'''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.


'''Keywords:''' GUI settings, sensors, disable sensor, stop sensor, capture rules SKIP, skip CDRs, stop data collection, duplicate records, sensor disabled GUI, CDR custom headers, header limitations, GeoIP, system configuration, timezone, national prefix, date format, intervals, email settings, license settings, license notification emails, overage notification, multiple recipients, comma-separated emails, LICENSE UPDATE, update license after payment, get/update license key button, Settings > License, license token, license key, retrieve license from portal, voipmonitor.org portal, Services > My services, copy license key, paste license key, multi-line license key, license not received after payment, license update automatically, manual license update, advanced settings, localization, custom URL, CSV export, ssl_key, ssl_cert, ssl_ipport, tls decryption, ssl configuration, pcap deduplication, packet count mismatch, missing packets in PCAP, RRD charts, sensor health monitoring, buffer usage, packet drops, sniffer overload, choppy audio, poor call quality, MOS score, sensor capacity limits, server instance troubleshooting, hardware replacement, SBC replacement, reusing IP address, managerip, managerport, server not appearing in GUI, CDR warning about unknown sensor, proxy, http proxy, proxy server, firewall, corporate proxy, GUI upgrade, sensor upgrade, curlproxy, proxy authentication, custom headers troubleshooting, snaplen, custom_headers_max_size, header capture issues, SIP packet capture, STIR, SHAKEN, P-Asserted-Identity, verstat, truncated headers, long SIP headers, CDR timezone, sensors timezone, UTC time display, BST time display, incorrect CDR times, iframe, embed, X-Frame-Options, clickjacking, GUI in iframe, iframe 301 redirect, ucloud, unite, subfolder, security headers, distributed architecture, custom headers not visible central GUI, sensor not sending CDRs to central database, server_destination, server_destination_port, server_password, delete sensor from GUI, re-register sensor, sensor configuration wrong, headers captured locally not in central GUI, CSRF, ENABLE_CSRF_CHECK, CSRF protection, cross-site request forgery, web security, XSS vulnerability mitigation, session security, security hardening, database index, index custom header, database performance, cdr_custom_headers table, dynamic_table, dynamic_column, CREATE INDEX, query optimization, custom header queries slow, EXPLAIN query, database indexes not indexed by default, partitioned tables index, full table scan, index lookup performance
'''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 disable a sensor in the GUI?
* What do a_ and b_ prefixes mean in cdr table columns?
* Why is the sensor still collecting data when disabled in the GUI?
* How do I convert sipcallerip to readable IP address?
* How do I stop a sensor from writing to the database?
* How do I get the PCAP filename (fbasename) for a call?
* How do I use capture rules with SKIP to stop data collection?
* How do I query video streams in cdr_rtp (payload >= 10000)?
* What is the difference between disabling a sensor in GUI vs systemctl stop?
* How do I find calls that went through a specific proxy?
* How do I prevent duplicate records from a disabled sensor?
* How do I find calls with REFER or other SIP methods?
* How do I temporarily stop a sensor without SSH access?
* How do I query CDR custom headers?
* What happens when I click Disable on a sensor in Settings?
* Why does "failed read rsa key" error occur on startup?
* Why does deleting a sensor in GUI not stop data collection?
* How do I sync manager_key between multiple databases?
* How do I stop voipmonitor service on sensor machine?
* Why can't old GUI download PCAP files after database migration?
* Can I use capture rules to selectively stop data from a sensor?
* How do I update my license after payment in VoIPmonitor GUI?
* Where do I click to get/update license key after purchasing a license?
* How do I manually retrieve my license key from the VoIPmonitor portal?
* My license key was not received after payment, what should I do?
* How do I use the get/update license key button in Settings > License?
* Do I need to manually enter the license key after payment?
* How do I paste the full license key content in Settings > License?
* What is the license token field used for in Settings > License?
* How do I copy my license key from voipmonitor.org portal?
* How do I configure sensors in the VoIPmonitor GUI?
* How do I enable CSRF protection in the GUI?
* Where do I find the ENABLE_CSRF_CHECK setting?
* How do I enable CSRF token validation to mitigate XSS vulnerabilities?
* What is ENABLE_CSRF_CHECK and how does it increase session security?
* How do I enable the GUI to run in an iframe?
* How do I fix 301 redirect errors when loading the GUI in an iframe?
* Where is the "Enable GUI to run in iframe" setting located?
* Why does the GUI not load when embedded in an iframe from another application?
* How do I enable iframe support for subfolders like /ucloud or /unite?
* How do I fix CDR times displaying in UTC instead of local timezone?
* Where do I set the sensors timezone for CDR data?
* What is the difference between "Timezone" and "Sensors Timezone" in Settings?
* How do I configure timezone for multiple sensors sending to the same database?
* How do I configure SSL/TLS settings for a sensor using the GUI?
* How do I enable TLS decryption for specific trunks through the web interface?
* Where do I find SSL/TLS parameters (ssl_key, ssl_cert, ssl_ipport) in the GUI?
* How do I access sensor RRD charts for health monitoring?
* How do I check if the sniffer host is overloaded using RRD charts?
* What do buffer usage and packet drops graphs indicate in sensor RRD charts?
* How can I tell if poor call quality is caused by the sniffer host vs network issues?
* What does buffer usage at 100% mean for VoIPmonitor sensor performance?
* How do I delete a non-deletable local sensor in a server/probe deployment?
* How do I create and configure CDR custom headers?
* Can I extract part of a SIP header using regexp in custom headers?
* Can I aggregate multiple occurrences of a SIP header?
* What are the limitations of CDR custom headers?
* Why are my custom SIP headers not being captured or showing truncated content?
* How do I capture long SIP headers like P-Asserted-Identity for STIR/SHAKEN?
* What is the snaplen parameter in voipmonitor.conf and how does it affect custom header capture?
* What is the custom_headers_max_size parameter in voipmonitor.conf?
* How do I fix truncated custom header content in the database?
* How do I create alerts based on CDR custom headers?
* How do I add a database index to a custom SIP header for better query performance?
* Why are queries on custom SIP headers slow?
* Are custom header columns in the database indexed by default?
* How do I create a database index on custom_header_3 in cdr_next_2?
* How do I find which table and column stores my custom SIP header?
* What is the cdr_custom_headers table used for?
* How does adding indexes on custom headers affect database performance?
* How do I verify the database is using an index for custom header queries?
* What is the difference between type: ref and type: ALL in EXPLAIN results?
* Should I create indexes on all custom SIP headers?
* How does partitioning affect database indexes on custom headers?
* How do I load GeoIP data for the map view?
* What system configuration options are available?
* How do I set national prefixes and date/time formats?
* What interval settings control CDR and dashboard filters?
* How do I configure GeoIP services?
* How do I create GUI localizations?
* How do I add custom URLs to the CDR view?
* How do I export CDRs to CSV via crontab scheduler?
* Why are custom SIP headers not visible in the central GUI for a specific sensor in a distributed deployment?
* How do I fix custom headers not appearing in central GUI when they capture locally on the sensor?
* Where are server_destination, server_destination_port, and server_password configured in distributed architecture?
* How do I re-register a sensor with the central GUI after configuration changes?
* When should I delete and recreate a sensor entry in Settings > Sensors?
* Why does the downloaded PCAP file have fewer packets than shown in the GUI SIP History?
* How do I disable Pcap deduplication before download to include all packets in downloaded PCAP?
* Where do I configure license notification email addresses?
* How do I configure multiple recipients for license expiry notification emails?
* How do I stop receiving license issue and overage notification emails?
* What should I do if a server/SBC does not appear in the GUI after replacing hardware and reusing the IP address?
* How do I manually create a server instance entry in the GUI?
* What are managerip and managerport configuration options in voipmonitor.conf?
* After replacing a server/SBC, why does data appear in CDRs with warnings but the sensor is not visible in Settings > Sensors?
* How do I configure network connectivity for server instances that connect directly to MySQL database?
* Can I switch from the old server instance architecture to modern Client-Server mode?
* How do I configure HTTP proxy for GUI and sensor upgrades?
* Where is the Http proxy (for upgrades) setting in the GUI?
* How do I configure proxy server in Settings > System Configuration?
* How do I fix GUI upgrade failures behind a corporate proxy or firewall?
* How do I configure curlproxy for remote sensors?
* What is the format for HTTP proxy configuration in VoIPmonitor?

Latest revision as of 11:29, 19 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.

cdr_custom_headers Table Structure

The cdr_custom_headers table stores the configuration for custom header extraction:

Column Description
header_field SIP header name to capture
dynamic_table Target table number (cdr_next_X)
dynamic_column Target column number (custom_header_X)
type Extraction type (first/last/nth)
nth_occurrence (New in 35.x) Specific occurrence number to extract when type is "nth" (e.g., 2 for second occurrence)

ℹ️ Note: The nth_occurrence column uses packet timestamps for accurate ordering, ensuring correct results even when packet time-based reordering is disabled in the sniffer.

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=10753"