Database structure: Difference between revisions

From VoIPmonitor.org
(Add section on filtering CDRs by SIP caller IP or domain)
(VS-1789: Document nth_occurrence column in cdr_custom_headers table)
 
(9 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 (User-Agent string)
}
 
entity "cdr_sip_response" as cdr_sip_response {
  * ID <<PK>>
   --
   --
   ua : varchar
   lastSIPresponse
}
}


cdr ||--|| cdr_next : "1:1"
cdr ||--|| cdr_next : "1:1"
cdr ||--o{ cdr_rtp : "1:N"
cdr ||--o{ cdr_rtp : "1:N"
cdr ||--o{ cdr_dtmf : "1:N"
cdr ||--o{ cdr_proxy : "1:N"
cdr ||--o{ cdr_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> || Primary key
|-
| <code>id_sensor</code> || Sensor ID (from <code>voipmonitor.conf</code>)
|-
|-
| <code>ID</code> || bigint || The unique primary key for the record
| <code>calldate</code> / <code>callend</code> || Start/end timestamps
|-
|-
| <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>duration</code> || Total length in seconds
|-
|-
| <code>calldate</code> || datetime || The start time of the call, measured from the first <code>INVITE</code> packet
| <code>connect_duration</code> || Connected time (after 200 OK)
|-
|-
| <code>callend</code> || datetime || The end time of the call, measured from the last packet associated with the call
| <code>progress_time</code> || Time to first provisional response ([[Glossary#Post_Dial_Delay_.28PDD.29|PDD]])
|-
|-
| <code>bye</code> || tinyint || Indicates how the call was terminated (<code>1</code> = normal termination with <code>BYE</code>)
| <code>bye</code> || <code>1</code> = normal termination with BYE
|-
|-
| <code>whohanged</code> || enum || Indicates which party sent the <code>BYE</code> message first (caller or callee)
| <code>whohanged</code> || Which party sent BYE (caller/callee)
|-
|-
| <code>lastSIPresponse_id</code> || int || Foreign key to <code>cdr_sip_response</code> table for the last final SIP response
| <code>lastSIPresponse_id</code> || FK to <code>cdr_sip_response</code>
|}
|}


=== Call Timing & Duration ===
== Participant Information ==


{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Type !! Description
! Column !! Description
|-
|-
| <code>duration</code> || int || Total call length in seconds (<code>callend - calldate</code>)
| <code>caller</code> / <code>called</code> || Numbers from From/To headers
|-
|-
| <code>connect_duration</code> || int || Connected portion length in seconds (from first <code>200 OK</code> to <code>callend</code>)
| <code>caller_domain</code> / <code>called_domain</code> || Domain parts
|-
|-
| <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>sipcallerip</code> / <code>sipcalledip</code> || IPs stored as integers (use <code>INET_NTOA()</code>)
|-
|-
| <code>first_rtp_time</code> || int || Time from call start to first RTP packet detection
| <code>a_ua_id</code> / <code>b_ua_id</code> || FK to <code>cdr_ua</code> (User-Agent)
|}
|}


=== Participant Information ===
== Quality Metrics ==


=== Packet Loss ===
{| 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_reverse</code> / <code>called_reverse</code> || varchar || Reversed strings for efficient <code>LIKE '%search'</code> queries
|-
|-
| <code>callername</code> || varchar || Display name from the <code>From</code> SIP header
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost
|-
|-
| <code>sipcallerip</code> / <code>sipcalledip</code> || int unsigned || Source/destination IP addresses stored as integers (use <code>INET_NTOA()</code> to convert)
| <code>packet_loss_perc_mult1000</code> || Max loss percentage × 1000
|-
|-
| <code>a_ua_id</code> / <code>b_ua_id</code> || int || Foreign keys to <code>cdr_ua</code> table for User-Agent strings
| <code>a_sl1</code> - <code>a_sl10</code> || Distribution of consecutive loss events
|}
|}


{{Note|
=== Jitter & Delay ===
'''How User-Agent is extracted:'''
* '''Caller (<code>a_ua_id</code>):''' From the <code>User-Agent</code> header in the SIP INVITE request
* '''Callee (<code>b_ua_id</code>):''' From the <code>User-Agent</code> header in the SIP response (typically <code>200 OK</code>)
* Blank values indicate the <code>User-Agent</code> header was not present
}}
 
'''Example: Converting IP addresses in SQL queries:'''
<syntaxhighlight lang="sql">
-- Get calls with readable IP addresses
SELECT
    ID,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip,
    duration
FROM cdr
WHERE calldate >= '2024-01-01'
LIMIT 10;
</syntaxhighlight>
 
=== Filtering CDRs by SIP Caller IP or Domain ===
 
When the standard GUI API filters do not provide the needed functionality (e.g., filtering by SIP caller IP or domain), you can query the database directly using SQL.
 
{{Note|
<strong>Important:</strong> This section describes direct SQL queries against the VoIPmonitor database. For the GUI API (HTTP API with <code>api.php</code>), see the [[WEB_API|WEB API documentation]].}}
 
==== Filtering by SIP Caller IP ===
 
To find calls from a specific SIP caller IP, use the <code>sipcallerip</code> column. Since IP addresses are stored as integers, use <code>INET_ATON()</code> to convert the IP string to an integer for comparison.
 
<syntaxhighlight lang="sql">
-- Find CDRs from a specific SIP caller IP
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND sipcallerip = INET_ATON('192.168.1.50')
ORDER BY calldate DESC
LIMIT 100;
</syntaxhighlight>
 
To filter by either SIP caller IP or SIP called IP:
 
<syntaxhighlight lang="sql">
-- Find CDRs where either SIP caller or called IP matches
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    INET_NTOA(sipcalledip) AS called_ip
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND (sipcallerip = INET_ATON('192.168.1.50')
      OR sipcalledip = INET_ATON('10.0.0.100'))
ORDER BY calldate DESC
LIMIT 100;
</syntaxhighlight>
 
==== Filtering by SIP Domain ===
 
The <code>caller_domain</code> and <code>called_domain</code> columns store the domain part from the SIP <code>From</code> and <code>To</code> headers.
 
<syntaxhighlight lang="sql">
-- Find CDRs from a specific SIP domain
SELECT
    ID,
    calldate,
    caller,
    caller_domain,
    called,
    called_domain
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND caller_domain = 'example.com'
ORDER BY calldate DESC
LIMIT 100;
</syntaxhighlight>
 
To filter by both SIP caller IP and domain:
 
<syntaxhighlight lang="sql">
-- Find CDRs matching IP or domain
SELECT
    ID,
    calldate,
    caller,
    called,
    INET_NTOA(sipcallerip) AS caller_ip,
    caller_domain
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND (sipcallerip = INET_ATON('192.168.1.50')
      OR caller_domain = 'example.com')
ORDER BY calldate DESC
LIMIT 100;
</syntaxhighlight>
 
==== Getting PCAP File Information (fbasename) ===
 
After finding CDR records, you may need to retrieve the associated PCAP file information. The <code>fbasename</code> column (derived from the SIP Call-ID) is stored in the <code>cdr_next</code> table.
 
<syntaxhighlight lang="sql">
-- Step 1: Find CDR IDs matching your criteria
SELECT ID, caller, called, calldate
FROM cdr
WHERE calldate >= '2024-01-01 00:00:00'
  AND sipcallerip = INET_ATON('192.168.1.50');
 
-- Step 2: Use the returned CDR IDs to get fbasename
-- Example: using IDs 12345, 12346, 12347 from Step 1
SELECT cdr_ID, fbasename
FROM cdr_next
WHERE cdr_ID IN (12345, 12346, 12347);
</syntaxhighlight>
 
You can also combine both queries using a JOIN:
 
<syntaxhighlight lang="sql">
-- Single query to get CDR info with fbasename
SELECT
    c.ID,
    c.calldate,
    c.caller,
    c.called,
    INET_NTOA(c.sipcallerip) AS caller_ip,
    cn.fbasename
FROM cdr c
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE c.calldate >= '2024-01-01 00:00:00'
  AND c.sipcallerip = INET_ATON('192.168.1.50')
ORDER BY c.calldate DESC
LIMIT 100;
</syntaxhighlight>
 
{{Tip|
<strong>Using fbasename with the API:</strong> Once you have the fbasename, you can use it with the [[WEB_API|GUI API]] to download or analyze the PCAP file. The fbasename is the filename used for the captured packets.}}
 
==== Performance Considerations ===
 
* Ensure the <code>sipcallerip</code> and <code>sipcalledip</code> columns are indexed for fast queries
* Use date range filters (<code>WHERE calldate >= '...'</code>) to limit the amount of data
* For frequent IP-based filtering, consider creating indexed views or summary tables
 
=== Network Quality Metrics ===
 
==== Packet Loss ====
 
{| class="wikitable"
{| class="wikitable"
|-
|-
! Column !! Description
! Column !! Description
|-
|-
| <code>a_lost</code> / <code>b_lost</code> || Total RTP packets lost (detected by sequence number gaps)
| <code>a_avgjitter_mult10</code> || Average jitter × 10
|-
| <code>lost</code> || Higher value between <code>a_lost</code> and <code>b_lost</code>
|-
|-
| <code>packet_loss_perc_mult1000</code> || Maximum packet loss percentage, multiplied by 1000
| <code>a_maxjitter</code> || Maximum jitter observed
|-
|-
| <code>a_sl1</code> - <code>a_sl10</code> / <code>b_sl1</code> - <code>b_sl10</code> || Distribution of consecutive packet loss events (sl1 = single packet lost, sl5 = 5 packets in a row, etc.)
| <code>a_d50</code> - <code>a_d300</code> || PDV distribution buckets (0-50ms, 50-70ms, etc.)
|}
|}


==== Jitter & Delay (PDV) ====
=== MOS (Mean Opinion Score) ===
 
{| 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_mos_f1_mult10</code> || [[Comprehensive_Guide_to_VoIP_Voice_Quality#MOS_Calculation_in_VoIPmonitor|MOS]] with 50ms fixed buffer × 10
|-
|-
| <code>a_maxjitter</code> / <code>b_maxjitter</code> || Maximum jitter value observed during the call
| <code>a_mos_f2_mult10</code> || MOS with 200ms fixed buffer × 10
|-
|-
| <code>jitter_mult10</code> || Higher <code>maxjitter</code> value between streams, multiplied by 10
| <code>a_mos_adapt_mult10</code> || MOS with adaptive buffer × 10
|-
|-
| <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>mos_min_mult10</code> || Lowest MOS between A/B streams × 10
|}
|}


==== MOS (Mean Opinion Score) ====
=== RTCP-Reported Metrics ===
These come from endpoint RTCP reports, not calculated by the sniffer. See [[Call_Detail_Record_-_CDR#Understanding_Sniffer_vs_RTCP_Packet_Loss|Sniffer vs RTCP Packet Loss]].


{| class="wikitable"
{| class="wikitable"
Line 373: Line 186:
! 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_rtcp_loss</code> || Packets lost (reported by endpoint)
|-
|-
| <code>a_mos_f2_mult10</code> / <code>b_mos_f2_mult10</code> || MOS with 200ms fixed jitter buffer, multiplied by 10
| <code>a_rtcp_avgfr_mult10</code> || Average fraction loss × 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_rtcp_avgjitter_mult10</code> || Average jitter × 10
|-
|-
| <code>mos_min_mult10</code> || Lowest MOS score between A and B streams, multiplied by 10
| <code>a_rtcp_avgrtd_mult10</code> || '''Roundtrip delay (latency)''' in ms × 10
|}
|}


==== RTCP Reported Metrics ====
= Related Tables =
 
== <code>cdr_next</code> (1:1 with cdr) ==


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]].
Extension table linked via <code>cdr_ID</code>.


{| class="wikitable"
{| class="wikitable"
Line 390: Line 205:
! Column !! Description
! Column !! Description
|-
|-
| <code>a_rtcp_loss</code> / <code>b_rtcp_loss</code> || Total packets lost as reported by endpoint via RTCP
| <code>fbasename</code> || Derived from SIP Call-ID; used to link PCAP files. Special characters replaced with underscores (<code>convertchar</code> in config)
|-
| <code>a_rtcp_avgfr_mult10</code> / <code>b_rtcp_avgfr_mult10</code> || Average fraction loss from RTCP, multiplied by 10
|-
|-
| <code>a_rtcp_avgjitter_mult10</code> / <code>b_rtcp_avgjitter_mult10</code> || Average jitter from RTCP, multiplied by 10
| <code>match_header</code> || Content of custom header for linking call legs (<code>matchheader</code> in config)
|}
|}


=== Other Columns ===
== <code>cdr_rtp</code> (Per-Stream RTP Stats) ==
 
One row per RTP stream. Multiple streams per call.


{| class="wikitable"
{| class="wikitable"
Line 403: Line 218:
! Column !! Description
! Column !! Description
|-
|-
| <code>dscp</code> || DSCP values from SIP and RTP packets for QoS analysis
| <code>cdr_ID</code> || FK to <code>cdr</code>
|-
|-
| <code>payload</code> || Codec payload type number used during the call
| <code>ssrc</code> || RTP Synchronization Source identifier
|}
|-
 
| <code>saddr</code> / <code>daddr</code> || Source/destination IP (as integer)
== Important Related Tables ==
 
=== <code>cdr_next</code> ===
 
A 1-to-1 extension of the <code>cdr</code> table for additional data, keeping the main table smaller. This table is linked to <code>cdr</code> via the <code>cdr_ID</code> foreign key (where <code>cdr_next.cdr_ID = cdr.ID</code>).
 
{| class="wikitable"
|-
|-
! Column !! Description
| <code>sport</code> / <code>dport</code> || Source/destination port
|-
|-
| <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>payload</code> || Codec type. '''Video streams: payload ≥ 10000''' (offset applied)
|-
|-
| <code>match_header</code> || Content of custom header (from <code>matchheader</code> in <code>voipmonitor.conf</code>) for linking call legs
| <code>received</code> / <code>lost</code> || Packet counts
|}
|}


'''Example: Finding PCAP filename for a call:'''
{{Tip|To query video streams only: <code>WHERE payload >= 10000</code>}}
<syntaxhighlight lang="sql">
SELECT c.ID, c.caller, c.called, cn.fbasename
FROM cdr c
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE c.ID = 12345;
</syntaxhighlight>
 
{{Note|
'''CDR Custom Headers Storage:'''
CDR-level custom SIP headers (one value per call) are stored dynamically in the <code>cdr_next_X</code> tables, not in the main <code>cdr</code> table. To find where a specific header is stored, you must query the <code>cdr_custom_headers</code> mapping table.
}}


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


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


{| class="wikitable"
{| class="wikitable"
Line 444: Line 241:
! Column !! Description
! Column !! Description
|-
|-
| <code>header_field</code> || The name of the custom SIP header (e.g., <code>'X-IntNum'</code>)
| <code>ip</code> / <code>port</code> || Media IP and port from SDP
|-
| <code>dynamic_table</code> || The table number (e.g., <code>2</code> means <code>cdr_next_2</code>)
|-
|-
| <code>dynamic_column</code> || The column number (e.g., <code>1</code> means <code>custom_header_1</code>)
| <code>is_caller</code> || 1 = caller side, 0 = callee side
|}
|}


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


=== Finding CDR Custom Header Storage ===
== <code>cdr_siphistory</code> (SIP Method History) ==


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


<syntaxhighlight lang="sql">
== <code>cdr_ua</code> / <code>cdr_sip_response</code> (Lookup Tables) ==
-- 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:
Lookup tables for User-Agent strings and SIP response texts.
* <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 ===
= Common SQL Queries =


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.
== Basic CDR Query with IP Conversion ==


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Example: Query cdr_next_2 for custom_header_1
SELECT
SELECT
     cdr.id,
     ID, caller, called,
    cdr.calldate,
     INET_NTOA(sipcallerip) AS caller_ip,
     cdr.caller,
     INET_NTOA(sipcalledip) AS called_ip,
     cdr.called,
     duration
     cdr_next_2.custom_header_1 AS X_IntNum
FROM cdr
FROM cdr
JOIN cdr_next_2 ON cdr.id = cdr_next_2.cdr_ID
WHERE calldate >= '2024-01-01'
WHERE cdr_next_2.custom_header_1 IS NOT NULL
  AND sipcallerip = INET_ATON('192.168.1.50')
ORDER BY cdr.calldate DESC
LIMIT 10;
LIMIT 10;
</syntaxhighlight>
</syntaxhighlight>


=== Complete Example Workflow ===
== Get PCAP Filename (fbasename) ==


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Example: Find CDRs where X-Customer-ID = '12345'
SELECT c.ID, c.caller, c.called, cn.fbasename
 
-- 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
FROM cdr c
JOIN cdr_next_2 cn ON c.ID = cn.cdr_ID
JOIN cdr_next cn ON c.ID = cn.cdr_ID
WHERE cn.custom_header_1 = '12345'
WHERE c.ID = 12345;
ORDER BY c.calldate DESC
LIMIT 100;
</syntaxhighlight>
</syntaxhighlight>


{{Tip|
== Get User-Agent Strings ==
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">
<syntaxhighlight lang="sql">
-- Step 1: Find where X-IntNum is stored
SELECT c.ID, ua_a.ua AS caller_ua, ua_b.ua AS callee_ua
SELECT header_field, dynamic_table, dynamic_column
FROM cdr c
FROM cdr_custom_headers
LEFT JOIN cdr_ua ua_a ON c.a_ua_id = ua_a.ID
WHERE header_field = 'X-IntNum';
LEFT JOIN cdr_ua ua_b ON c.b_ua_id = ua_b.ID
 
WHERE c.calldate >= CURDATE() - INTERVAL 1 DAY
-- Result example: dynamic_table=1, dynamic_column=2
LIMIT 10;
-- 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>
</syntaxhighlight>


To compare between two periods, run the query twice with different date ranges, then compare the results:
== Find Calls Through Specific Proxy ==


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- All X-IntNum values that had minutes in November 2025
SELECT c.ID, c.calldate, c.caller, c.called,
SELECT DISTINCT
      INET_NTOA(cp.dst) AS proxy_ip
    custom_header_2 AS X_IntNum
FROM cdr c
FROM cdr
JOIN cdr_proxy cp ON c.ID = cp.cdr_ID
JOIN cdr_next_1 ON cdr.ID = cdr_next_1.cdr_ID
WHERE cp.dst = INET_ATON('192.168.1.100')
WHERE cdr.calldate >= '2025-11-01 00:00:00'
LIMIT 100;
  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>
</syntaxhighlight>


You can then compare the two result sets manually in a spreadsheet or use SQL to find values that disappeared:
== Query Video Stream Quality ==


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Find X-IntNum values that had activity in November but not in December
SELECT cdr_ID, payload,
SELECT DISTINCT
      INET_NTOA(saddr) AS src_ip,
    nov.X_IntNum,
      received, lost,
    nov.connected_minutes AS november_minutes
      (lost / (received + lost)) * 100 AS loss_pct
FROM (
FROM cdr_rtp
    SELECT
WHERE payload >= 10000  -- Video streams only
        custom_header_2 AS X_IntNum,
  AND cdr_ID = 12345;
        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>
</syntaxhighlight>


{{Note|
== Find Calls with Specific SIP Method (REFER) ==
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"
|-
! Column !! Description
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
|-
| <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>lost</code> || Number of RTP packets lost (detected by sequence number gaps)
|-
| <code>jitter</code> || Jitter value (varies by schema version)
|}
{{Tip|
<strong>Identifying Video Streams:</strong> Video codecs (H.264, VP8, etc.) use dynamic payload types in RTP (typically 96-127). VoIPmonitor adds a <code>+10000</code> offset to video payload values in the database. To query only video streams, use <code>WHERE payload >= 10000</code>.}}
'''Example: Querying video stream quality:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Find calls with high packet loss on video RTP streams
SELECT cdr_ID, callid, calldate
SELECT
FROM cdr_siphistory
    cdr.ID,
WHERE method = 'REFER'
    cdr.calldate,
   AND calldate > '2024-01-01'
    cdr.caller,
GROUP BY cdr_ID;
    cdr.called,
    INET_NTOA(cdr_rtp.saddr) AS source_ip,
    INET_NTOA(cdr_rtp.daddr) AS dest_ip,
    cdr_rtp.payload,
    cdr_rtp.received,
    cdr_rtp.lost,
    (cdr_rtp.lost / (cdr_rtp.received + cdr_rtp.lost)) * 100 AS loss_percentage
FROM cdr
JOIN cdr_rtp ON cdr.ID = cdr_rtp.cdr_ID
-- Filter for video streams (payload >= 10000)
WHERE cdr_rtp.payload >= 10000
   AND cdr.calldate >= '2024-01-01'
ORDER BY loss_percentage DESC
LIMIT 20;
</syntaxhighlight>
 
'''Example: Finding all RTP streams in a call:'''
<syntaxhighlight lang="sql">
-- List all RTP streams (audio and video) for a specific call
SELECT
    payload,
    CASE
        WHEN payload >= 10000 THEN 'Video'
        ELSE 'Audio'
    END AS stream_type,
    INET_NTOA(saddr) AS source_ip,
    INET_NTOA(daddr) AS dest_ip,
    received,
    lost,
    (lost / (received + lost)) * 100 AS loss_perc
FROM cdr_rtp
WHERE cdr_ID = 12345;
</syntaxhighlight>
</syntaxhighlight>


=== <code>cdr_dtmf</code> ===
= Custom Headers =
 
Stores detected DTMF key presses. Requires <code>dtmf2db = yes</code> in <code>voipmonitor.conf</code>.
 
=== <code>cdr_proxy</code> ===
 
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.


{| class="wikitable"
== CDR-Level Custom Headers ==
|-
! 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|
CDR custom headers are stored in <code>cdr_next_X</code> tables. Use <code>cdr_custom_headers</code> to find the location:
<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
-- Step 1: Find table/column for header
SELECT
SELECT dynamic_table, dynamic_column
    cdr_ID,
FROM cdr_custom_headers
    INET_NTOA(dst) AS proxy_ip
WHERE header_field = 'X-Customer-ID';
FROM cdr_proxy
-- Result: dynamic_table=2, dynamic_column=1 → cdr_next_2.custom_header_1
WHERE cdr_ID = 12345;
</syntaxhighlight>


'''Example: Finding calls that used a specific proxy:'''
-- Step 2: Query the data
<syntaxhighlight lang="sql">
SELECT c.ID, c.calldate, cn.custom_header_1 AS customer_id
-- Find all calls that went through a specific proxy server
SELECT
    c.ID,
    c.calldate,
    c.caller,
    c.called,
    INET_NTOA(c.sipcallerip) AS caller_ip,
    INET_NTOA(c.sipcalledip) AS called_ip,
    INET_NTOA(cp.dst) AS proxy_ip
FROM cdr c
FROM cdr c
JOIN cdr_proxy cp ON c.ID = cp.cdr_ID
JOIN cdr_next_2 cn ON c.ID = cn.cdr_ID
WHERE cp.dst = INET_ATON('192.168.1.100')
WHERE cn.custom_header_1 = '12345';
ORDER BY c.calldate DESC
LIMIT 100;
</syntaxhighlight>
 
=== <code>cdr_sip_response</code> ===
 
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">
SELECT
    c.ID,
    c.caller,
    c.called,
    ua_a.ua AS caller_user_agent,
    ua_b.ua AS callee_user_agent
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;
</syntaxhighlight>
</syntaxhighlight>


=== <code>cdr_siphistory</code> ===
{{Note|The GUI's [[Reports|Reports]] module does not support grouping by custom headers. Use direct SQL queries.}}
=== <code>cdr_custom_headers</code> Table Structure ===


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>.
The <code>cdr_custom_headers</code> table stores the configuration for custom header extraction:


{| class="wikitable"
{| class="wikitable"
Line 757: Line 358:
! Column !! Description
! Column !! Description
|-
|-
| <code>cdr_ID</code> || Foreign key to <code>cdr</code> table (links to the primary call record)
| <code>header_field</code> || SIP header name to capture
|-
|-
| <code>method</code> || SIP request method (e.g., INVITE, REFER, BYE, CANCEL, OPTIONS, etc.)
| <code>dynamic_table</code> || Target table number (cdr_next_X)
|-
|-
| <code>calldate</code> || Timestamp of the SIP message
| <code>dynamic_column</code> || Target column number (custom_header_X)
|-
|-
| <code>callid</code> || SIP Call-ID header value
| <code>type</code> || Extraction type (first/last/nth)
|-
|-
| <code>cseq</code> || SIP CSeq (Command Sequence) number
| <code>nth_occurrence</code> || '''(New in 35.x)''' Specific occurrence number to extract when type is "nth" (e.g., 2 for second occurrence)
|}
|}


'''Prerequisite Configuration:'''
{{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.}}
Configure in <code>/etc/voipmonitor.conf</code>:
<syntaxhighlight lang="ini">
save_sip_history = all
</syntaxhighlight>
Restart the voipmonitor sniffer after changing this configuration.
 
'''Example: Find all calls containing a SIP REFER method:'''
<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">
SELECT
    cdr_ID,
    callid,
    method,
    calldate
FROM cdr_siphistory
WHERE calldate > CURDATE() - INTERVAL 1 DAY
  AND method IN ('OPTIONS', 'SUBSCRIBE')
ORDER BY calldate DESC
LIMIT 20;
</syntaxhighlight>
 
== SIP Message Custom Headers ==
== SIP Message Custom Headers ==


VoIPmonitor can capture custom SIP headers from individual SIP messages (not just CDR-level headers). These are stored dynamically in the <code>sip_msg_next_X</code> tables, where each custom header gets its own dedicated column.
Per-message headers are stored in <code>sip_msg_next_X</code> tables. Use <code>sip_msg_custom_headers</code> to find the location.
 
{{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 ===
= System Table =


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.
The <code>system</code> table stores configuration including <code>manager_key</code> for sensor operations.


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Step 1: Find the dynamic table and column for your custom header
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
WHERE header_field = 'X-My-Custom-Header';
</syntaxhighlight>
</syntaxhighlight>


This query returns:
{{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>.}}
* <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 ===
 
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.


<syntaxhighlight lang="sql">
== Synchronizing manager_key Between Databases ==
-- Example: Query sip_msg_next_1 for custom_header_2 = 'desired-value'
-- Step 2: Retrieve SIP messages matching the custom header value
SELECT
    sip_msg_ID,
    time,
    custom_header_2 AS header_value
FROM sip_msg_next_1
WHERE custom_header_2 = 'desired-value'
  AND time > NOW() - INTERVAL 15 MINUTE;
</syntaxhighlight>


{{Tip|
When multiple GUI instances share sensors (e.g., after database migration), mismatched <code>manager_key</code> values cause PCAP download failures.
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 ===
'''Symptoms:''' "Unable to download PCAP" errors, empty SIP history in one GUI.


'''Fix:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Example: Find SIP messages with X-Customer-ID = '12345' in the last 1 hour
-- On active database: get the key
 
SELECT content FROM voipmonitor.`system` WHERE type='manager_key';
-- Step 1: Find where X-Customer-ID is stored
SELECT dynamic_table, dynamic_column
FROM sip_msg_custom_headers
WHERE header_field = 'X-Customer-ID';
 
-- Example result: dynamic_table=3, dynamic_column=1
 
-- Step 2: Query the appropriate table
SELECT
    sm.sip_msg_ID,
    sm.time,
    sm.custom_header_1 AS customer_id
FROM sip_msg_next_3 sm
WHERE sm.custom_header_1 = '12345'
  AND sm.time > NOW() - INTERVAL 1 HOUR
ORDER BY sm.time DESC;
</syntaxhighlight>
 
=== Configuration Requirements ===
 
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:
-- On old database: update the key
<syntaxhighlight lang="bash">
UPDATE voipmonitor.`system`
service voipmonitor restart
SET content = '{"key":"<key_from_active>","iv":"<iv_from_active>"}'
WHERE type = 'manager_key';
</syntaxhighlight>
</syntaxhighlight>


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


The <code>system</code> table stores system-wide configuration and keys used by VoIPmonitor.
= Troubleshooting =


;<code>manager_key</code>
== Missing <code>sport</code>/<code>dport</code> in cdr_rtp ==
: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.
If RTP streams from overlapping calls are incorrectly merged, add the missing columns:


:'''Check manager_key in database:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- Query the manager_key from the system table
ALTER TABLE cdr_rtp ADD `sport` smallint unsigned DEFAULT NULL AFTER `saddr`;
SELECT * FROM voipmonitor.`system` WHERE type='manager_key'\G
ALTER TABLE cdr_rtp ADD `dport` smallint unsigned DEFAULT NULL AFTER `daddr`;
</syntaxhighlight>
</syntaxhighlight>


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


;<code>Synchronizing manager_key Between Multiple Databases</code>
To find other recommended schema changes:
:When you have multiple VoIPmonitor GUI instances sharing the same sensors (e.g., an old GUI and a new GUI both pointing to sensors using Local Processing mode), each database generates its own <code>manager_key</code>. If the <code>manager_key</code> values differ, the GUI that does not have the matching key cannot:
<syntaxhighlight lang="bash">
:* Download PCAP files (downloads fail or return corrupt data)
journalctl -u voipmonitor | grep ALTER
:* View SIP history from the sensor (empty history or errors)
</syntaxhighlight>
:* 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
= See Also =
:<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
* [[Call_Detail_Record_-_CDR|CDR View (GUI)]]
:Users must log out of the old GUI and log back in for the key synchronization to take effect.
* [[Database_troubleshooting|Database Troubleshooting]]
* [[Sniffer_configuration#Database_Configuration|Database Configuration]]
* [[WEB_API|Web API Documentation]]


:;Step 4: Verify the fix
:Attempt to download a PCAP file through the old GUI to verify the fix worked.


:'''Important Notes:'''
= AI Summary for RAG =
:* Do NOT manually generate or modify the <code>key</code> or <code>iv</code> fields - they are encrypted credentials
:* Always backup the <code>system</code> table before making changes
:* This synchronization only needs to happen once - after migration, all GUIs will use the same key
:* Each database maintains its own <code>manager_key</code> in the <code>system</code> table - they are not automatically synchronized


== AI Summary for RAG ==
'''Summary:''' VoIPmonitor database schema reference. Main table <code>cdr</code> stores one record per call with timing, participants, and quality metrics. Column naming: <code>a_</code>=caller, <code>b_</code>=callee, <code>_multNN</code>=divide by NN. Key tables: <code>cdr_next</code> (1:1, contains <code>fbasename</code> for PCAP linking), <code>cdr_rtp</code> (per-stream stats, video streams have payload≥10000), <code>cdr_proxy</code> (proxy chain IPs), <code>cdr_sdp</code> (SDP-negotiated ports), <code>cdr_siphistory</code> (SIP method history, requires <code>save_sip_history=all</code>). Custom headers stored in <code>cdr_next_X</code> tables; query <code>cdr_custom_headers</code> for mappings. System table stores <code>manager_key</code>; mismatch causes PCAP download failures.
'''Summary:''' Reference for VoIPmonitor database schema. The <code>cdr</code> table stores one record per call with timing, participant info, and quality metrics. Column naming: <code>a_</code> = caller stream, <code>b_</code> = callee stream, <code>_multNN</code> = divide by NN. Key metrics include packet loss (<code>a_lost</code>), jitter (<code>a_avgjitter_mult10</code>), MOS scores, and RTCP-reported metrics. 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, a_lost, b_lost, fbasename, 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
'''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 filter CDRs by SIP caller IP?
* What do a_ and b_ prefixes mean in cdr table columns?
* How do I filter CDRs by SIP domain?
* How do I convert sipcallerip to readable IP address?
* What is the correct column name for SIP caller IP in cdr table?
* How do I get the PCAP filename (fbasename) for a call?
* How do I convert IP address to integer for sipcallerip comparison?
* How do I query video streams in cdr_rtp (payload >= 10000)?
* How do I use INET_ATON() to filter by IP address?
* How do I find calls that went through a specific proxy?
* How do I get fbasename for a CDR?
* How do I find calls with REFER or other SIP methods?
* Which table stores the fbasename for PCAP file linking?
* How do I query CDR custom headers?
* How do I join cdr and cdr_next to get fbasename with CDR data?
* Why does "failed read rsa key" error occur on startup?
* How do I filter CDRs by caller_domain or called_domain?
* How do I sync manager_key between multiple databases?
* Where is the manager_key stored in the VoIPmonitor database?
* Why can't old GUI download PCAP files after database migration?
* Why does the sniffer fail to start with "failed read rsa key" error?
* How do I fix the "failed read rsa key" error on startup?
* What does mysqlloadconfig=no do in voipmonitor.conf?
* How do I check if the manager_key exists in the database?
* What is the system table in VoIPmonitor?
* How do I query the manager_key from the system table?
* What do the a_ and b_ prefixes mean in the cdr table?
* How do I convert sipcallerip to a readable IP address?
* How do I get the Call-ID for a call from the database?
* Which table stores the User-Agent of phones?
* How do I query video streams or video call quality using cdr_rtp?
* How do I identify video streams in the database (payload filtering)?
* How do I find calls that went through a specific proxy server?
* How do I find all intermediate proxy IPs for a specific call?
* Which table stores SIP proxy IP addresses a call traversed?
* How do I find calls that transited through a specific IP address?
* How do I query CDRs with a custom header value?
* How do I find which cdr_next_X table stores my custom header?
* How do I query SIP messages with a custom header value?
* Why is my custom SIP header not in the cdr table?
* How do I find the database table and column for a custom SIP header added via web interface?
* A custom SIP header cannot be found in the main cdr table for querying - how do I query it?
* Where are CDR custom headers stored in the database?
* How do I use cdr_custom_headers table to find custom header data?
* How do I synchronize manager_key between multiple VoIPmonitor databases?
* Why cannot the old GUI download PCAP files after migrating to a new database with shared sensors?
* How do I fix PCAP download failures in old GUI after database migration?
* Why is SIP history empty or missing in one GUI but not another?
* How do I copy manager_key from one database to another?
* What causes manager_key mismatch between multiple GUI instances?
* How do I share sensors between old and new GUI instances?
* How do I fix encryption key mismatch between databases?

Latest revision as of 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"