Freeradius + postgresql for cisco voip

[Claudiu Filip]

Hi nix,
Wednesday, August 1, 2007, 2:56:13 PM, you wrote:
> inserted in the database.  Now the problem here is how to retrieve
> the data from the database.  Is there any pre-made front end of this
> kind of things?  I need to calculate daily/weekly/monthly voip CDR.

Daily run at 12:01 can be something like
#!/bin/bash
DAY=`date -d yesterday +"%Y/%m/%d"`
psql -d voipdb -c "SELECT count,sum... WHERE h323connecttime > '$DAY 00:00'" | mail -s "Daily traffic for $DAY" gigi at kent.com


Replace h323connecttime with h323disconnecttime or h323setuptime for
the info you need or the db index used.

For weekly use
    date -d "last week" +"%Y/%m/%d 00:00"
and for monthly use
    date -d "last month" +"%Y/%m/%d 00:00"


If you need to sumarize by destinations (let say first 3 digits), you
can change the SELECT into something like
SELECT substr(CalledStationId, 0, 3), count(.... GROUP BY
substr(CalledStationId, 0, 3);


This is not the complex and powerful front end you were looking for,
but maybe it gives you an ideea to make yourself exactly what you
need.

> One more thing can i insert my syslog cisco CDR to above database?

Yes, you can do that. Pay attention to duplicates.
It will be better to create a unique index based on h323-conf-id,
called-station-id and a timestamp.
Check for relevant info between syslog entries and db.

Good luck,

Claudiu Filip
@: claudiu at globtel.ro
Http://www.globtel.ro
T:+40344880100
F:+40344880113