Database structure

cdr

• ID - unique primary key
• id_sensor - match id_sensor in voipmonitor.conf or NULL if not set
• calldate - start of the call (counted from first INVITE
• callend - end of the call (counted from the last seen packet)
• duration - callend - calldate in seconds
• connect_duration - callend - time of 200 OK
• progress_time - lenght of ringing
• first_rtp_time - number of seconds since beginning of the call to first rtp packet
• caller - number part of the caller from "From" sip header
• caller_domain - domain part from "From" sip header
• caller_reverse - reversed caller column used to do index searching %search
• callername - number part of the caller from "From" sip header
• callername_reverse - reversed callername used for index searching %search
• called - number part of the called from "To" sip header
• called_domain - domain part from "To" sip header
• called_reverse - reversed called column
• sipcallerip - source IP address from the first INVITE. Use INET_NTOA() to get string
• sipcalledip - destination IP address from the last INVITE.
• whohanged - indication who sent the BYE first
• bye - indication if the call was successfully terminated with BYE - ACK
• lastSIPresponse_id - last SIP response reference to table cdr_sip_response
• lastSIPresponseNum - last SIP response number
• sighup - indication if the call was written during HUP signal sent to sniffer (incomplete CDR)
• dscp - dscp bits

columns starting with a_ represents information for caller RTP stream (RTP sent from caller). Columns starting with b_ represents informatino for called RTP stream (RTP sent from called)

columns which ends with multNN where NN is number 10 or 100 or 1000 represents values which has to be divided by NN to get real number.

• ab_index - index of RTP stream. used to get correct graph file
• ab_payload - number representing codec type
• ab_saddr - source IP address of the RTP stream
• ab_received - number of received RTP packets
• ab_lost - number of lost RTP packets detected by holes in sequence numbers according to rfc3550
• lost - max (a_lost, b_lost)
• ab_ua_id - useragent reference to table cdr_ua
• ab_avgjitter_mult10 - average jitter calculated according to rfc3550
• ab_maxjitter - maximum jitter
• ab_sl1 - ab_sl10 - number of consecutive packet loss. sl1 - number of one packet loss occurences. sl5 - number of 5 consecutive packet loss occurences.
• ab_d50-300 - number of delay occurences within delay range. d50: 0-50ms, d70: 50-70ms, etc.
• ab_mos_f1_mult10 - MOS score for fixed jitterbuffer 50ms. Divide it by 10 to get real number.
• ab_mos_f2_mult10 - MOS score for fixed jitterbuffer 200ms. Divide it by 10 to get real number.
• a_mos_adapt_mult10 - MOS score for adaptive jitterbuffer. Divide it by 10 to get real number.
• ab_rtcp_loss - loss from RTCP
• ab_rtcp_maxfr - max fraction loss from RTCP
• ab_rtcp_avgfr_mult10 - average fraction loss - devidie it by 10 to get real number
• ab_rtcp_maxjitter - maximum jitter from RTCP
• a_rtcp_avgjitter_mult10 - average jitter from RTCP
• payload - codec used for this call
• jitter_mult10 - max(a_jitter, b_jitter)
• mos_min_mult10 - minimum jitter from caller or called stream
• a_mos_min_mult10 - minimum caller jitter
• b_mos_min_mult10 - maximum called jitter
• packet_loss_perc_mult1000 - maximum packet loss from caller or called
• a_packet_loss_perc_mult1000 - maximum packet loss from caller
• b_packet_loss_perc_mult1000 - maximum packet loss from called
• delay_sum - sum of all delays (PDV) from caller or called
• a_delay_sum - sum of all delays (PDV) from caller
• b_delay_sum - sum of all delays (PDV) from called

• delay_avg_mult100 - max average PDV from caller or called
• a_delay_avg_mult100 - average PDV from caller
• a_delay_avg_mult100 - average PDV from called

• rtcp_avgfr_mult10 - max(a_avgfr, b_avgfr)
• rtcp_avgjitter_mult10 - max(a_avgjitter, b_avgjitter)
• delay_cnt -

• a_delay_cnt -
• b_delay_cnt -

cdr_next

this table contains additional data for CDR. The relation between cdr and cdr_next is 1:1

• cdr_ID - reference to cdr.ID table
• calldate - same as cdr.calldate. this is used to partition table
• fbasename - string from SIP.Call-ID
• GeoPosition - string from sip geo tag
• match_header - see match header in sniffer configuration

=