[ASTPPCOM-306] Max Channels Used Report (Overlap Counts). Created: 05/Jan/18  Updated: 24/Feb/18  Resolved: 24/Feb/18

Status: Done
Project: ASTPP Community
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Task
Reporter: countrdd Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 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



 Comments   
Comment by countrdd [ 07/Jan/18 ]

The other option is to write the DID, AccountID, TYPE and data and time to when a LIMIT is hit. This may be a better more efficient reporting option

Comment by Samir Doshi [ 02/Feb/18 ]

Hi [~countrdd]

Can you please explain bit more about this report?
What is the use of it?

Comment by countrdd [ 15/Feb/18 ]

To see how many active channles an acount has at a give time.
Would be very handy for upselling channels to customers.

Comment by (Community) DN (Inactive) [ 24/Feb/18 ]

[~countrdd] You can control CC per customer but not have GUI feature to see exact CC at any moment.
However you can use live call report.

Generated at Sat Feb 10 07:15:58 CET 2024 using Jira 8.13.3#813003-sha1:22ebedbb75c99b147c66f14e031dd8a2d214753a.