I want to be able to track the number of calls per DID we have configured and am unable to figure out how to accomplish this.
I’m sure I could write an AGI to filter all my call and write it to the CDR but am trying to avoid that much work.
I just need a number of calls per DID by date.
Any thoughts?
Thanks in advance,
Paul
Nobody needs this? I would love this feature!
A possible starting point:-
mysql -u USER -pPASSWORD -D asteriskcdrdb -e “SELECT YEAR(calldate) as Year, MONTH(calldate) AS Month, DAY(calldate) as Day, COUNT(*) as calls, SUM(duration) as seconds, dst as DID from cdr WHERE dst REGEXP ‘^[0-9][0-9][0-9][0-9]$|^V$’ group by year, month, day, dst ;”
make sure your REGEXP covers all the “endpoints” (this example all 4 digit numbers, and voicemail) your DID’s land on, a little more work and you can map the endpoints to DID’s (inbound routes) directly from the asterisk.incoming table fields extension to destination, depending on yiour setup. (The association of extensions (DID) to answering point is not simple in FreePBX)
But is there a way to modify something I the src code so it starts tracking the calls rcvd on DIDs and can be pulled from the reports tab?
Not that I know of. (The association of extensions (DID) to answering point is not simple in FreePBX), further what a “call” is considered by the CDR engine equally unsimple. But anything is possible if you want to rewrite the code.
Do you want some sort of call centre wallboard type system? If so you can get addon software to accomplish this.
Perhaps:-
mysql -H -u USER -pPASSWORD -e "SELECT DATE_FORMAT(calldate,’%b %D, %Y - %a ‘) as Date,COUNT(*) as Calls, CEIL(SUM(duration)/60) as Minutes, extension as DID, SUBSTRING_INDEX(destination ,’,’,1) as Destination, dst as EndPoint,description as Description FROM asterisk.incoming DID LEFT JOIN asteriskcdrdb.cdr CDR ON DID.destination=CONCAT(CDR.dcontext,’,’,CDR.dst,’,1’) WHERE calldate > ADDDATE(DATE(now()), INTERVAL -1 MONTH) GROUP by YEAR(calldate), MONTH(calldate), DAY(calldate),DID " > /var/www/html/admin/DIDusage.html
is more useful to get the last month’s stats.
http://yourbox/admin/DIDusage.html
and Bob’s your uncle.
With the caveat that CDR’s are ‘really messed up’ in Asterisk/FreePBX and thus the numbers may be misleading, there is a group by option in the call reports module.
We’ve added DIDs (in 2.10 or was that 2.11?) but … I noticed that grouping by DIDs is not one of the pull down options.
I suspect if you open a ticket as a reminder it would be fairly trivial to add in though it’ll be in 2.11 that it gets added. But do get the ticket in to help us remember.
nm … r14966
will get it published fairly soon
Unfortunately tho, it doesn’t show much other than calls rcvd by application 
Sadly I’m still in 2.8 (as part of Elastix)
Sorry, Elastix decided a couple of years ago to go their own way with their hacked up old FreePBX 2.8. You are on your own, think about moving to a mainline distro before Elastix drop FreePBX altogether in their version 3 
Unless I am totally wrong, my mysql query should solidly and effectively tie all your DID calls to the endpoint they were directed to by associating asterisk.inbound to asteriskcdrdb.cdr.dst , it works for me on old Elastix Boxii I still have working.
The only reason why I’m sticking with Elastix is their callcenter applet that’s so easily integrated and various other goodies.
I have a PIAF box ready to be deployed tho - just waiting on moving everything over from the 2.8 Elastix settings to 2.9 PIAF.
Hm, not sure why mine isn’t working. I was too excited cuz the format is just simply perfect. Just shows a few things out of the full list of DIDs - http://postimage.org/image/kbthusluz/
So how many inbound DID’s do you have apart from *7770 and *7778? it will only report calls to extant DID’s maybe post an obfuscated:-
mysql -u USER -pPASSWORD -e “SELECT * from asterisk.incoming”
I have three more and those are the important ones.
That mysql command listed all the DIDs but no reporting on how many calls rcvd
Well. you can post what I asked for, I assume that no-one called those numbers or those asterisk.incoming DID is not covered. Sorry , I’m not a mind-reader.
Then you have run into the “not-simple” problem when you use the second derivative “timeconditions” which need another JOIN into another mysql table probably quite simple but you are on your own from here, I suggest you upgrade to a legitimate FreePBX while you wait for Philippe’s patches to be applied to that legitimacy.
Sorry
What do you think about PIAF?