Tracking usage

[digitalb]

I have been tasked with setting up an automated way to track the usage of our hosted PBX systems.

The requirements are:
Automated/scripted
Completed Monthly
Tracks only call that go across the SIP trunks. Don’t care about extension to extension calls, unless one of the extensions are set to forward/followme to an outside number

What I have done so far is manually export the CDR reports from each PBX system and run a script against them removing 0 duration calls, and filtering out the inbound across the trunks and outbound across the trunks. This seems to work ok, but I get a bunch of duplicates because of the ring groups we have setup and how the duration occurs. Also, the data collection is manual.

I tried to export the CDR reports via a MySQL dump but the headers didn’t match up and skewed my numbers greatly (all calls were 300 sec long…) and again it appears there were duplicates in there.

I tried to manually export the CEL reports, but for some of our systems we have over 11K calls and it appears the CEL reports stop at 1600 entries.

Anyone have any suggestions on a cost affective way to pull the usage across the trunks?

[dicko]

A mysql query will be more precise and customizable than a mysql dump.

Do you care to share your ‘filter’ ?

[digitalb]

I am no expert, this is what I was able to piece together

mysql asteriskcdrdb -e 'SELECT * FROM cdr WHERE calldate BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() '|tr ‘\t’ ‘,’ > /tmp/cdr.csv

I know it will not report the “last month”, it reports exactly 1 months of data, so it depends on when I run the cmd.

When I look at the data it seems to add two columns of data but not move the headers

duration	billsec	disposition	amaflags	accountcode
300	Ttb(func-apply-sipheaders^s^1	(3))U(sub-send-obro	19	11

The amaflags and accountcode are the true duration and billsec.

[digitalb]

Ok figured out the actual “last month” filter

mysql asteriskcdrdb -e 'SELECT * FROM cdr WHERE YEAR(calldate) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(calldate) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) '|tr ‘\t’ ‘,’ > /tmp/cdr.csv

Dug a bit more of into the raw export (before my scripts filter it even more) and it appears not all the lines are out of order. Only some,does not appear to have a common factor. Some are inbound, some are outbound, others are internal to internal.

[dicko]

add ORDER BY and any WHERE clauses to so ‘order’ and ‘filter’ by statement

SELECT *  FROM cdr WHERE MONTH(calldate) =  MONTH(now() - interval 1 month) AND   YEAR(calldate) =  YEAR(now() - interval 1 month)
AND disposition =  'ANSWERED'  
 ORDER BY  calldate 

would get you all answered calls from last month. Use paired LENGTH(src) and LENGTH(dst) to filter likely trunk calls.