If your business runs several departments through one PBX and one set of trunks, finance will eventually ask the question every telecom admin dreads: "How much did each department spend on calls last month?" The raw call detail record (CDR) table doesn't answer that on its own.
This guide walks through the full pipeline for turning raw Asterisk/FreePBX CDR data into a per-department phone bill — the same process Q5000 automates — so you understand exactly what's involved whether you build it yourself or use a tool.
What's in the CDR table
On FreePBX/Asterisk, the cdr table already holds the raw material:
calldate— when the call happened (stored in the PBX's local time)src/dst— source and destination numbersduration— total call length including ringingbillsec— talk time only (this is what you cost)disposition—ANSWERED,NO ANSWER,BUSY,FAILEDaccountcode— the key to department attribution, if you use it
Tip: cost calls off billsec, not duration, and filter to disposition = 'ANSWERED'
if you only want billable, connected calls. Forgetting this is the most common reason call
counts and costs come out inflated.
Step 1 — Attribute each call to a department
This is the hard part, and it's where most homemade solutions get messy. You need to map something on each call to a department. The two reliable approaches:
Account codes / PINs (recommended)
If callers enter a PIN, or you set accountcode per extension, that's your cleanest key. Map
it through three levels: PIN → user → department. Modelling the person in the middle
matters — when someone changes department you re-point one person record instead of editing
every code.
Extension (src)
Works only when extensions map 1:1 to people. It breaks the moment you introduce shared phones or hot-desking.
Keep this mapping in its own table (or a proper tool), never buried in the dialplan, so HR changes don't require a PBX reload.
Step 2 — Cost the calls with rate rules
Build a rate table keyed by destination prefix — local, national, mobile, international,
premium — then match the longest prefix of dst and multiply by billsec / 60.
Watch out for:
- Mobile numbers that look national — order rules most-specific first.
- Premium-rate ranges hidden inside otherwise-normal number blocks.
- Overlapping international/national prefixes.
- Number formatting — normalise
+27…,0…, and raw formats before prefix-matching, or your rate rules silently miss.
Step 3 — Aggregate and report
GROUP BY department, month gives finance the chargeback figures. But a raw SQL table rarely
gets read. Export to PDF or Excel with a chart so managers actually engage with it — a
one-page per-department summary beats a 5,000-row dump every time.
The gotchas that cost the most time
- Timezones: CDR
calldateis stored in PBX-local time. Report in a different timezone and your month boundaries drift, so totals never reconcile with the carrier bill. - Unanswered calls inflating counts if you forget the disposition filter.
- Number normalisation breaking rate matching.
- Unmapped account codes silently dropping calls — surface them as an "unassigned" bucket instead. That's often where unusual (or fraudulent) usage hides.
Do it yourself, or automate it
For a single small site, SQL plus a spreadsheet is perfectly fine. Past a few departments, regular staff churn, and finance wanting monthly PDFs, the maintenance adds up.
Q5000 automates this entire pipeline for FreePBX, Issabel, and VitalPBX: PIN/account-code → department mapping, prefix-based cost rules, per-department PDF/Excel reports, and budget alerts — connecting to your CDR database over a secure SSH tunnel with a read-only user. See how Q5000 handles call cost reporting →
Frequently asked questions
What's the difference between duration and billsec in Asterisk CDR?
duration is the total call length including ring time; billsec is the connected talk time.
Cost calls off billsec — using duration overcharges for calls that rang before answering.
How do I attribute calls to a department if extensions are shared? Use account codes or dial-in PINs rather than the source extension. Map PIN → user → department so shared phones and hot-desking still attribute correctly.
Can I generate per-department call reports without writing SQL? Yes. Tools like Q5000 read your CDR database directly and produce per-department PDF/Excel cost reports without manual queries.
Why don't my call totals match the carrier bill?
The usual causes are timezone mismatches shifting month boundaries, counting duration
instead of billsec, or not filtering unanswered calls.