CDR with SIP Trunk

Discussion in 'Ideas' started by Ricardo Copriva, Mar 24, 2018.

CDR with SIP Trunk 5 5 2votes
5/5, 2 votes

  1. Ricardo Copriva

    Joined:
    Jul 27, 2017
    Messages:
    5
    Likes Received:
    0
    Hey, guys, good night. I have a need to identify which SIP Trunk the link is coming out on the CDR ticket. I have tried to make this markup in many ways and I have not found a solution. This need is valid for billing software to correctly calculate the expenses per carrier. Often the value of one carrier is different from another and would not want to let the user's charge activate the billing code.
     
  2. Silly English Kniggit

    Joined:
    Sep 13, 2017
    Messages:
    217
    Likes Received:
    78
    For outbound PSTN calls? The trunk ID is in the CDR. The field is "to-dn". To see trunk IDs - export the inbound rules table. For inbound calls it's in the "from-dn" field.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 Silly English Kniggit, Mar 30, 2018
    Last edited: Mar 30, 2018
  3. Ricardo Copriva

    Joined:
    Jul 27, 2017
    Messages:
    5
    Likes Received:
    0
    Hi,

    That's not the way it would be. Imagine I have two SIP trunks. The first SIP trunk has the cost "X" and the second SIP trunk has the cost "Y ".

    How do I identify the SIP trunk in the CDR?
     
  4. voiptoys

    voiptoys Active Member

    Joined:
    Feb 13, 2013
    Messages:
    569
    Likes Received:
    92
    Actually, I concur with Silly English Kniggit's suggestion. Here is an example of the CDR data pulled from my SQL database for an outbound call:

    cdrid: 7
    historyid: 00000BF92D49FB1B_318
    callid: Call 18976
    duration: 00:24:18 2018-03-06 18:29:45.000
    time_start: 2018-03-06 18:29:46.000
    time_answered: 2018-03-06 18:54:04.000
    Reason_terminated: TerminatedBySrc
    From_No: Ext.6002
    To_No: 240454XXXX
    from_dn: 6002
    To_dn: 10003
    dial_no: 240454XXXX
    reason_changed:
    bill_code:
    bill_rate: 1.0000
    bill_cost: 24.3000
    bill_name: default
    call_chain: Chain: Ext.6002;240454XXXX; Call 18977

    The field you are looking for is "to_dn" (in red). In this example, "10003" is the trunk ID. Keep in mind that each trunk has a unique ID. So, if you have multiple trunks the value in this field will change based on which trunk was used for the outbound call.

    Further, In this example you see the bill rate of 1.000. It is pretty easy math to take this rate, and the associated cost, and apply a multiplier to arrive at a cost based on which trunk you use. You will simply need to create a table that cross references the trunk ID with the appropriate multiplier and perform the calculation. Super simple.

    Our Exporter tool can automatically process the 3CX CDR files and place the data in a SQL or MySQL database, in real-time. Once the data is in a database, you can do with it whatever you need.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Ricardo Copriva

    Joined:
    Jul 27, 2017
    Messages:
    5
    Likes Received:
    0
    Perfect. I'm going to test it again.
     
  6. timmbo

    Joined:
    Aug 25, 2017
    Messages:
    88
    Likes Received:
    31
    Hi,
    it would be great if i can choose All Extensions; outgoing; SIP-Trunk, so i can control the Providers invoice.

    Bye
    Timm
     
  7. voiptoys

    voiptoys Active Member

    Joined:
    Feb 13, 2013
    Messages:
    569
    Likes Received:
    92
    Once the data in a database, you will be able to craft the necessary query to generate the information you need. Take a look at our 3CX Exporter tool. It can parse the CDR files and place the data into a database automatically. If you need some help with the necessary queries, let me know. We can help.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...