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" [EMAIL PROTECTED]
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
@: [EMAIL PROTECTED]
Http://www.globtel.ro
T:+40344880100
F:+40344880113
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
