CDR Extract Call Numbers On Interval On Unique Callers

Home » Asterisk Users » CDR Extract Call Numbers On Interval On Unique Callers
Asterisk Users 3 Comments

hi,

we want to extract the information when the most callers are entering our phone system based on an interval of 15 minutes. this is quite simple (although not perfect) with select calldate, count(*) as anzahl from cdr where calldate >
‘2019-10-12’ group by unix_timestamp(calldate) DIV 900 having ;

Unfortunately we have lots of callers who calls multiple times when they are forwarded to a queue instead of being answered by a human immediately. But to know when we need more people I want to count same caller-ids within an interval as one call.

Any ideas how to do this?

kind regards, andre

—

3 thoughts on - CDR Extract Call Numbers On Interval On Unique Callers

  • thanks john,

    that is a good idea and really easy. I selected both values to have a good comparison:

    select calldate, count(distinct(clid)), count(clid) from cdr where calldate > ‘2019-10-12’ group by unix_timestamp(calldate) DIV 900 ;

    now it would be nice to have intervals starting always in the same manner. currently the output is like:

    MariaDB [asteriskcdrdb]> select calldate, count(distinct(clid)), count(clid) from cdr where calldate > ‘2019-10-12’ group by unix_timestamp(calldate) DIV 900 ;
    +———————+———————–+————-+
    | calldate            | count(distinct(clid)) | count(clid) |
    +———————+———————–+————-+
    | 2019-10-14 08:04:36 |                     5 |          24 |
    | 2019-10-14 08:16:42 |                     6 |          14 |
    | 2019-10-14 08:30:55 |                     7 |          29 |
    | 2019-10-14 08:45:10 |                     3 |           6 |
    | 2019-10-14 09:00:46 |                     6 |          19 |
    | 2019-10-14 09:35:57 |                     4 |           5 |
    | 2019-10-14 09:45:05 |                     4 |          19 |
    | 2019-10-14 10:01:12 |                     6 |          45 |

    […]

    would be better to have dates starting with “2019-10-14 08:00:00”,
    “2019-10-14 08:15:00” etc… any quick idea? i will search for that anyway.

    regards, andre

    —

  • i’ve got it:

    select from_unixtime(round((ceiling(unix_timestamp(calldate)/ 900)
    *900))) as intervall, count(distinct(clid)), count(clid) from cdr where calldate > ‘2019-09-01’ group by intervall;

    Am 12.11.19 um 15:16 schrieb Andre Gronwald:

    —