Asterisk :: Asterisk CDR Changes in 1.4.5

[index] [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15]

Woah, look at all these calls! (20-Jun-2007)

I've been logging Asterisk's call history (CDR) to a PostgreSQL database for a few months now and it's been working out quite well. It looks like Digium made some changes to CDR handling in 1.4.5, though, that caused me a lot of grief. Asterisk now creates CDR records for a huge number of events that did not previously generate CDR activity. For example, now when an inbound caller is placed in a queue, there's a CDR record for each agent phone that rings even if that agent doesn't ultimately take the call.

I can understand why Digium decided to go this route -- it's easier for people like me to just filter out the extraneous rows than it would be for the people who want this information to add it in. I did want to do the filtering in an intelligent and aesthetic way, though, so I did it with a trigger on the table in the database.

Here's a little bit of sql that did the trick quite nicely:

CREATE OR REPLACE FUNCTION cdr_filter() RETURNS trigger AS $$ 
  BEGIN
    IF NEW.src = '' THEN
      RETURN NULL;
    END IF;
    IF NEW.dst = 's' THEN
      RETURN NULL;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cdr_filter BEFORE INSERT ON cdr FOR EACH ROW EXECUTE PROCEDURE cdr_filter();

So let me take this opportunity to once again advocate PostgreSQL. There's a reason lots of folks don't give MySQL much credit and it has a lot to do with things like this. Triggers didn't exist in MySQL until version 5.02. Even now they're not well-tested and suffer from some pretty staggering limitations.

For every person who tells you that they don't need all the "fancy" features in PostgreSQL I can show you a person who would have had to solve this with some nasty cleanup hygiene script and still had to code around the transient presence of the unwanted rows in the database. Features like stored procedures and triggers are tremendously valuable tools even in the simplest of databases (which the CDR certainly is).