Adding call quality parameters to Asterisk CDR

Following presentation from AsterConf 2016 (yes, I know, it was 5 years ago), I've decided to collect similar info on CDR. But this presentation was given a long ago and many things have changed. 

So, a bit updated version.

I'm omitting here connection procedure to the database via odbc, don't want to write the same procedure once again.

And few things to mention - it's for PJSIP. And I'm not using setvar= routine, insead doing it all explicitly in the dialplan.

So...

cdr_adaptive_odbc.conf

[asterisk]
connection=asterisk
table=cdr_extra
usegmtime=no
alias start => calldate
alias sip_callid => sip_callid
alias rtcp_a_all => rtcp_a_all
alias rtcp_a_all_jitter => rtcp_a_all_jitter
alias rtcp_a_all_loss => rtcp_a_all_loss
alias rtcp_a_all_rtt => rtcp_a_all_rtt
alias rtcp_a_txjitter => rtcp_a_txjitter
alias rtcp_a_rxjitter => rtcp_a_rxjitter
alias rtcp_a_txploss => rtcp_a_txploss
alias rtcp_a_rxploss => rtcp_a_rxploss
alias rtcp_a_rtt => rtcp_a_rtt
alias rtcp_b_all => rtcp_b_all
alias rtcp_b_all_jitter => rtcp_b_all_jitter
alias rtcp_b_all_loss => rtcp_b_all_loss
alias rtcp_b_all_rtt => rtcp_b_all_rtt
alias rtcp_b_txjitter => rtcp_b_txjitter
alias rtcp_b_rxjitter => rtcp_b_rxjitter
alias rtcp_b_txploss => rtcp_b_txploss
alias rtcp_b_rxploss => rtcp_b_rxploss
alias rtcp_b_rtt => rtcp_b_rtt

database_cdr_extra.sql

 CREATE TABLE cdr_extra (
        calldate datetime NOT NULL default '0000-00-00 00:00:00',
        clid varchar(80) NOT NULL default '',
        src varchar(80) NOT NULL default '',
        dst varchar(80) NOT NULL default '',
        dcontext varchar(80) NOT NULL default '',
        channel varchar(80) NOT NULL default '',
        dstchannel varchar(80) NOT NULL default '',
        lastapp varchar(80) NOT NULL default '',
        lastdata varchar(80) NOT NULL default '',
        duration int(11) NOT NULL default '0',
        billsec int(11) NOT NULL default '0',
        disposition varchar(45) NOT NULL default '',
        amaflags int(11) NOT NULL default '0',
        accountcode varchar(20) NOT NULL default '',
        uniqueid varchar(32) NOT NULL default '',
        userfield varchar(255) NOT NULL default '',
        sip_callid varchar(255) NOT NULL default '',
        rtcp_a_all varchar(255) NOT NULL default '',
        rtcp_a_all_jitter varchar(255) NOT NULL default '',
        rtcp_a_all_loss varchar(255) NOT NULL default '',
        rtcp_a_all_rtt varchar(255) NOT NULL default '',
        rtcp_a_txjitter decimal(10,6),
        rtcp_a_rxjitter decimal(10,6),
        rtcp_a_txploss int(11) NOT NULL default '0',
        rtcp_a_rxploss int(11) NOT NULL default '0',
        rtcp_a_rtt decimal(10,6),
        rtcp_b_all varchar(255) NOT NULL default '',
        rtcp_b_all_jitter varchar(255) NOT NULL default '',
        rtcp_b_all_loss varchar(255) NOT NULL default '',
        rtcp_b_all_rtt varchar(255) NOT NULL default '',
        rtcp_b_txjitter decimal(10,6),
        rtcp_b_rxjitter decimal(10,6),
        rtcp_b_txploss int(11) NOT NULL default '0',
        rtcp_b_rxploss int(11) NOT NULL default '0',
        rtcp_b_rtt decimal(10,6)
);

 

extensions.conf

 ...

; usual Dial

same => n,Set(CHANNEL(hangup_handler_push)=qos_leg_a,s,1)

same => n,Dial(${destinationStr},,b(set_qos_handler_b^s^1))
...

[qos_leg_a]
exten => s,1,Noop(Channel: ${CHANNEL(name)}, QoS stats RTCP: ${CHANNEL(rtcp,all)})
    same => n,Set(CDR(sip_callid)=${CHANNEL(pjsip,call-id)})
    same => n,GotoIf($["${CHANNEL(rtcp,all)}"==""]?end)
    same => n,Set(CDR(rtcp_a_all)=${CHANNEL(rtcp,all)})
    same => n,Set(CDR(rtcp_a_all_jitter)=${CHANNEL(rtcp,all_jitter)})
    same => n,Set(CDR(rtcp_a_all_loss)=${CHANNEL(rtcp,all_loss)})
    same => n,Set(CDR(rtcp_a_all_rtt)=${CHANNEL(rtcp,all_rtt)})
    same => n,Set(CDR(rtcp_a_txjitter)=${CHANNEL(rtcp,txjitter)})
    same => n,Set(CDR(rtcp_a_rxjitter)=${CHANNEL(rtcp,rxjitter)})
    same => n,Set(CDR(rtcp_a_txploss)=${CHANNEL(rtcp,txploss)})
    same => n,Set(CDR(rtcp_a_rxploss)=${CHANNEL(rtcp,rxploss)})
    same => n,Set(CDR(rtcp_a_rtt)=${CHANNEL(rtcp,rtt)})
    same => n(end),Return()

[qos_leg_b]
exten => s,1,Noop(Channel: ${CHANNEL(name)}, QoS stats RTCP: ${CHANNEL(rtcp,all)})
    same => n,GotoIf($["${CHANNEL(rtcp,all)}"==""]?end)
    same => n,Set(CDR(rtcp_b_all)=${CHANNEL(rtcp,all)})
    same => n,Set(CDR(rtcp_b_all_jitter)=${CHANNEL(rtcp,all_jitter)})
    same => n,Set(CDR(rtcp_b_all_loss)=${CHANNEL(rtcp,all_loss)})
    same => n,Set(CDR(rtcp_b_all_rtt)=${CHANNEL(rtcp,all_rtt)})
    same => n,Set(CDR(rtcp_b_txjitter)=${CHANNEL(rtcp,txjitter)})
    same => n,Set(CDR(rtcp_b_rxjitter)=${CHANNEL(rtcp,rxjitter)})
    same => n,Set(CDR(rtcp_b_txploss)=${CHANNEL(rtcp,txploss)})
    same => n,Set(CDR(rtcp_b_rxploss)=${CHANNEL(rtcp,rxploss)})
    same => n,Set(CDR(rtcp_b_rtt)=${CHANNEL(rtcp,rtt)})
    same => n(end),Return()

[set_qos_handler_b]

exten => s,1,Set(CHANNEL(hangup_handler_push)=qos_leg_b,s,1)
    same => n,Return()

As a bonus you have the SIP Call-ID in this CDR, which is useful for debugging SIP traces.