CDR JSON MYSQL readable

[Rodolphe]

Hello,

I just tested the implementation of CDRs in a MySQL database via PHP script available here: http://wiki.pbxnsip.com/index.php?title=JSON_CDR and everything works well. Each call is recorded in the database.

But I have trouble reading the data and understand them.

Indeed, for one call there may be three entries and the type is both O and I

My goal is to make a script statistics (number of incoming calls, number of outgoing calls, average length, etc ...), can you help me to understand these values ​​and how to use them.

Thank you.

Rudwolf

[Vodia PBX]

You have to keep in mind that those CDR are call leg CDR. That means you typically get two records for each telephone call. They contain the same Call-ID, so that you can group them together if you want.

 

Most users want to see only the CDR for the trunks for billing purposes. When then trunk is empty, those users can just discard the record.

[Rodolphe]

thx for your reply

 

so what the difference between "primarycallid" and "callid" ? because for one call, there are 3 entries with the same "primarycallid" but 3 different "callid", and sometimes primarycallid = callid.

 

If i check where trunk is not empty i could have 2 entries, one with type O and one with type I

 

but i think it will be when snomONE call cellphone extension (type O)

 

hard to do script in this condition

[Rodolphe]

if i select entries like this :

 

SELECT * FROM `cdr` WHERE trunkname <> ''

 

and when i have two entries with same "primarycallid" i only keep the entrie where "primarycallid" = "callid" (in case of cellphone call)

 

should i be good ?

[Rodolphe]

or

 

SELECT * FROM `cdr` WHERE trunkname <> ''

 

and keep only entries with "ipadr" field not empty

 

what do you think ?

 

thx in advance

 

rudwolf

[Vodia PBX]

If you are only interested in trunk calls, you can forget about primarycallid and also callid. "SELECT * FROM cdr WHERE trunkid > 0" would be my first guess.

[Rodolphe]

thx

 

il will do some tests

[Rodolphe]

Indeed, the result seems correct

 

but all call to cellphone when you have your cellphone in "Accounts / Redirection / Cell Phone Number" and "Include the cell phone in calls to extension" is set to "Immediatly" are recorded.

 

I have to find a better request

 

Thx for your help

[Vodia PBX]

Well calls to the cell phone are trunk calls, and they do cost money (if connected). If the purpose of your report is to find out what costs were caused, then this is correct.

[Rodolphe]

exactly

 

after more tests, the best request i found was :

 

SELECT * FROM `cdr` WHERE trunkid > 0 and extension = ''

 

and i have to look after "remoteparty" field to know for Outbound Call the callee and for Inbound Call the caller

 

If during the call, there was a lot of : hold, transfer, *52 for cellphone, etc... the results will be the summary of the call but the "from" and "to" field will be not correct afterall, so i check the "remoteparty".

 

In case of call in huntgroup or ivr node, with this request, il will not know which extension pick up the phone. In these cases, i have to do another request to have more detail.

 

What do you think about all this ???

 

i'll continue my tests and let you know if i'm good

 

Thx for your answers

 

Rudwolf