[freeside-users] How to prevent duplicate CDRs

Grant Emsley grant at techplanit.com
Wed Mar 31 06:32:57 PDT 2010


One major problem I've come across with Freeside is that my CDRs often contain duplicates.  Our VOIP services are hosted elsewhere, and I download the CDRs every day.  But since there are days where the CDRs are late getting added, I have to have a bit of overlap between each import.   Rather than have to filter the CDR records before I could import them, I found a simple hack to prevent them from being added.  This should apply to both the command line and webui methods of importing CDR records.

For the record, I'm using the VMWare Freeside-Appliance-1.9.1.zip, and that's the only system I have tested this on.

Run the following two commands in postgresql. (I installed phppgadmin for easier access).

-------------------------------------------
ALTER TABLE ONLY cdr ADD CONSTRAINT forceunique UNIQUE (calldate, src, dst, charged_party, billsec);

CREATE RULE ignoreduplicates2 AS ON INSERT TO cdr WHERE (EXISTS (SELECT 1 FROM cdr WHERE ((((cdr.calldate = new.calldate) AND ((cdr.src)::text = (new.src)::text)) AND ((cdr.charged_party)::text = (new.charged_party)::text)) AND (cdr.billsec = new.billsec)))) DO INSTEAD SELECT nextval('cdr_acctid_seq'::regclass) AS nextval;
--------------------------------------------

The first line checks that all records have a unique combination of calldate, source, destination, charged party and billing seconds. You might need to adjust those columns if your CDRs fill out different fields.  For my purposes, I'm reasonably sure that if all those are identical, it's a duplicate record.

The second is a rule that prevents Freeside's inserts from actually inserting anything on a duplicate record.  It checks if the about to be inserted record is already in the cdr table, and if it is, calls nextval instead.  I originally tried using DO NOTHING, but Freeside expects nextval to have been called when the record was inserted, and if it wasn't, the call to currval fails and stops the import process.

I have NOT extensively tested this, but it's working on my test server so far.  It will skip over any duplicate records, and still add all the new records from my csv file.

I'm posting it here so anyone else who's having this problem might benefit from it, and save themselves the few hours I spent banging my head against the desk :).  If anyone has any better ideas for doing this, let me know.

Hope it helps,

Grant Emsley



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://420.am/pipermail/freeside-users/attachments/20100331/7d12210d/attachment.htm>


More information about the freeside-users mailing list