Details
-
Type:
Task
-
Status: Done
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: None
-
Labels:None
Description
Not sure it will work with < 3.0
But here is a SQL statement that will count call channel overlaps (number of paths in use at one time) by an account. This is ALL channels, IN/OUT it could be modified to show individuals counts as well.
In order for it to work efficiently, you need to make sure an index on created on callstart and end_stamp. Note: If you database is LARGE it could talk a LONG LONG time for it to index the first time.
SELECT group_concat(v2.accountid) as Overlaps, count(v2.accountid)+1 as OverlapCount, v1.accountid, v1.callstart, v2.accountid, least(v1.end_stamp,v2.end_stamp) AS end_stamp, v1.uniqueid, v2.uniqueid
FROM cdrs v1
JOIN cdrs v2 ON v1.uniqueid <> v2.uniqueid and v1.callstart >= v2.callstart and v1.callstart < v2.end_stamp and v1.accountid = v2.accountid
group by v1.accountid,
DATE_FORMAT(
v1.callstart,'%Y-%m-%d'),
DATE_FORMAT(
v1.end_stamp,'%Y-%m-%d')
ORDER BY v1.accountid, v1.callstart;
This shows counts by Date.
'%Y-%m-%d %H') - will show by date/hour
'%Y-%m-%d %H') - will show by date/hour/minute if you need go that granular