Concurrent / Simultaneous calls

[cloidhamer]

I was searching the 3CX forums for a way to get the Concurrent / Simultaneous calls on my lines, but there wasn't much. So I figured it out myself. Here is the SQL code to get the results. It can probably be made more efficient. It basically gets a table of all your calls in the date range and trunk you input and checks the answer and end times against a itself. So it is a pig when it comes to processing large tables because each record geometrically increases the processing.

So just change the dates to the time period you want to query and the trunk line you want it for.

with
            Rings as (
                
SELECT s.call_id, si.dn AS src_dn, dp.answer_time AS dst_answer_time,
    dp.end_time AS dst_end_time, di.dn_type AS dst_dn_type, di.dn AS dst_dn
FROM cl_segments s
   JOIN cl_participants sp ON sp.id = s.src_part_id
   JOIN cl_participants dp ON dp.id = s.dst_part_id
   JOIN cl_party_info si ON si.id = sp.info_id
   JOIN cl_party_info di ON di.id = dp.info_id
WHERE dp.end_time BETWEEN '01/01/20' and '01/31/20'
      and si.dn = '10000'
      and dp.answer_time is not null

            )

Select Max(SimultaneousCalls) As Max_Simultaneous_Calls
From
(           
Select Call1, src_dn, Count(Call2) as SimultaneousCalls
From
(Select Distinct A.call_ID as Call1, A.src_dn, B.call_ID as Call2
From Rings A
    JOIN
     Rings B on
     ((A.dst_answer_time BETWEEN B.dst_answer_time AND B.dst_end_time)
    OR (A.dst_end_time BETWEEN B.dst_answer_time AND B.dst_end_time))
Where A.call_ID != B.call_ID
) as Calls
Group By Call1, src_dn
Order by Call1
) as SimCall
;

 

[Clément Rousseau]

thx for sharing!

 

[AWS2P]

How do you use this SQL, are you requesting 3CX database directly ?

 

[edossantos]

Please not that directly accessing the 3CX database might cause serious issues to the PBX if not done properly. You might block a table, and freeze the PBX while your query is running. This is absolutely NOT recommended.

 

[cloidhamer]

Yes I am accessing the database directly. In regards to the 3CX comment, like I said it is a pig for CPU cycles so make sure you have multiple processors or run it during a slow call time as it could impact performance.