Asterisk Concurrent Calls from MySQL

Question

I am trying to get concurrent calls from my CDR for asterisk reporting via MySQL Query. I have seen other suggestions and they included JOINs and ONs and some other calls that I didnt fully understand what they were doing so I couldnt adjust for my application.

I have multiple departments with their own set of DIDs which I need individual reports for. My current script is wrong as it grabs the number of calls that occurred while a call was in progress meaning that if a call was connected from 1pm to 2pm, it will count all the subsequent calls that occurred between 1pm and 2pm which is not what I want. I want the number of concurrent calls not how many calls occurred.

My Dataset

calldate            | callsource  | calldestination | duration | callended
--------------------| ------------| ----------------| -------- | ---------
2016-12-01 15:45:00 | 12121111111 | 12031234567     | 120      | 2016-12-01 15:47:00          
--------------------| ------------| ----------------| -------- | ---------
2016-12-01 15:45:10 | 12122332223 | 12121111111     |  15      | 2016-12-01 15:45:25 
--------------------| ------------| ----------------| -------- | ---------
2016-12-01 15:45:12 | 12121111111 | 12033254678     | 15       | 2016-12-01 15:45:27 
--------------------| ------------| ----------------| -------- | ---------
2016-12-01 15:45:20 | 12123333333 | 12031223244     | 50       | 2016-12-01 15:47:10 
--------------------| ------------| ----------------| -------- | ---------
2016-12-01 15:45:26 | 12121111111 | 12031231222     | 10       | 2016-12-01 15:45:33 
--------------------|-------------|-----------------|----------|----------
2016-12-01 15:45:27 | 12120433322 | 12031456544     | 10       | 2016-12-01 15:45:37 

I want to find out the billing department's max concurrent calls:

Billing Department's DIDs:
1212111111
1212333333

So the above would output:
Max Concurrent Calls: 3

I am trying to do this with MySQL and PHP so the formatting and all is irrelevant on the web page side. I just need to get the 3 out of the MySQL query.

My current attempt would return 5 since they all occurred within the first calls start and end time.

The only way I see doing this currently is to loop through every line with an inner loop of every line seeing if it occurred during the outer loop and incrementing max concurrent if surpassed.

Thoughts?

Thanks,
--E

Answer 1

If you have access to Asterisk CLI then you may just fire one command on Asterisk server console and you will get number of concurrent calls:

asterisk -rx "core show calls" | grep "active" | cut -d' ' -f1

this command you may use in PHP script also like:

<?php

$cmd = `asterisk -rx "core show calls" | grep "active" | cut -d' ' -f1`; //getting current active calls on asterisk server
echo "ConCurrentCalls:".$cmd."\n";

?>

Answer 2

There are no easy way do it.

Simplest way is do something like this:

select max(c) from 
 ( select a.uniqueid,a.calldata,
    (select count(*) from cdr as b
       where  
        b.calldate <date_add(a.calldate,interval a.duration second) and  
        date_add(b.calldate,interval  b.duration second) >a.calldate
    ) as c    
 from cdr as a ) as res 

But this query will be VERY VERY bad optimized.

Optimization: add calldate limits for each internal query

You can do using php. just sort out all queries by calldate, after that put same set with stopdate=calldate+duration. Mark such records as end records.

After that sort both sets, loop via result. When you see start record, do +1. When you see stop record, check if current value>max(if yes, set max=c) and decrease count.

Or do same using pl-sql. This one variant very likly will be much faster.