I’m hoping to figure out what query of the MySQL cdr table will produce the SIP trunk usage minute data that shows up on our SIPStation bill.
My goal is to email myself a daily report that will help me spot excessive usage and possible security issues/abuse.
Our statement date is the 9th of every month so I tried this:
select sum(billsec)/60, sum(duration)/60 from cdr where (length(src)>9 or length(dst)>9) and (calldate between ‘2022-03-08’ and ‘2022-04-09’) and disposition=‘ANSWERED’;
The results I get do not match what is in our bills.
My thinking is:
That there is no billing for calls between extensions so the source or destination has to be a phone number with more than 9 digits. (length(src)>9 or length(dst)>9)
Only calls that go through (disposition=‘ANSWERED’) are billed.
Are you sure that shouldn’t be sum(ceil(billsec/60)) ?
Also, whilst I found it difficult to find useful tariff information, e.g. whether to the second or to the minute, the time dependent part seems to be direction dependent.
Are you sure you are not triggering short call surcharges?
There is also the issue that when you measure times will not be exactly when they do.
The use of ceil() would seem to indicate that some calls are getting rounded up to the next minute. Any idea where to find documentation that explains which ones?
CEIL was speculative. However, historically, at least, it was common to charge per minute or part thereof, so anything between 1 and 60 seconds would get charged the full minute fee. That’s still true for BT domestic rates, in the UK: “All call durations will be rounded up to the next whole minute”.
PS According to How the Division Operator Works in SQL | LearnSQL.com divide will behave differently, on some servers, in particular the Microsoft one, if the billsecs field is defined as integer, so, you may need to force it to float to give something on which the CEIL can work properly.