Thanks Bahram,
I copied this to the CVS manual and edited out a few typos etc. (HTML
version attached). It would be nice if somebody could actually run this
in a sample setup and make sure there are no typos left.
Having the same for authentication would also be nice. ;-)
Regards,
Jan
Bahram S. Biria wrote:
> Lucas,
>
> I had the same problem awhile ago and could find nothing on it.
Consequently I did it myself. Here is what I did. Hope it helps you and
anyone else who is (and will be) looking for something like these.
>
> What I did was creating a new database in the name of GUNK. You can do
it using the following command in myself environment.
>
> create database GUNK;
>
> Then I created a table in the name of CDR as follows.
>
> create table GUNK.CDR (
> gatekeeper_name archer(255),
> call_number into zerofill,
> call_duration mediumint unsigned zerofill,
> index duration_idx (call_duration),
> disconnect_cause smallint unsigned zerofill,
> index dcc_idx (disconnect_cause),
> acct_session_id varchar(255),
> h323_id varchar(255),
> gkip varchar(15),
> CallId varchar(255),
> ConfID varchar(255),
> setup_time datetime,
> connect_time datetime,
> disconnect_time datetime,
> caller_ip varchar(15),
> index srcip_idx (caller_ip),
> caller_port smallint unsigned zerofill,
> callee_ip varchar(15),
> index destip_idx (callee_ip),
> callee_port smallint unsigned zerofill,
> src_info varchar(255),
> dest_info varchar(255),
> Calling_Station_Id varchar(255),
> Called_Station_Id varchar(255),
> index dialednumber_idx (Called_Station_Id (20)),
> Dialed_Number varchar(255)
> );
>
>
> You can copy and paste it in the mysql environment.
>
> Then you need to creat a username for accessing the data. You can do
it throught the following command in mysql environment.
>
> GRANT delete,insert,select,update ON GNUGK.* TO
'YourDesireUsername'@'localhost' IDENTIFIED BY 'APassword';
>
> With this command you will permit accessing the data from only the
local server. If you need to access these data from any other computer
then you have to set the proper security options.
>
> Then you can add the following settings ( at the bottom of this email)
into your gnugk.ini file to insert and update the history of the calls
into your database.
>
> Please note the fields that I used were based on version 2.2.1 and I
beleive there are more available data to keep in the latest version
(2.2.3-2) which I haven't added them yet for myself. By the way, I used
the same definitions with the latest version and it worked fine. Please
add the new data fields yourself if you think you need them to keep.
>
> Hope these save a little bit of time for you and whomever who will
possibly use these later.
>
> Good luck,
> Bahram.
>
> P.S. I guess it would have been helpful if something like this was
included in the original manual of the GNUGK to save some time for the
people who are looking for something as a startup.
>
>
> [Gatekeeper::Acct]
> SQLAcct=optional;start,stop,update
> FileAcct=sufficient;stop
>
> [FileAcct]
> DetailFile=Add your desire path here something like /var/log/cdr.log
> StandardCDRFormat=0
>
CDRString=%g|%n|%d|%c|%s|%u|%{gkip}|%{CallId}|%{ConfId}|%{setup-time}|%
{connect-time}|%{disconnect-time}|%{caller-ip}|%{caller-port}|%{callee-
ip}|%{callee-port}|%{src-info}|%{dest-info}|%{Calling-Station-Id}|%{Cal
led-Station-Id}|%{Dialed-Number}
> Rotate=daily
> RotateTime=23:59
>
>
> [SQLAcct]
> Driver=MySQL
> Database=GNUGK
> Username=YourDesireUsername
> Password=APassword
> StartQuery= insert into CDR (gatekeeper_name, call_number,
call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId,
ConfId, setup_time, connect_time, disconnect_time, caller_ip,
caller_port, callee_ip, callee_port, src_info, dest_info,
Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g',
'%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfId}',
'%{setup-time}', '%{connect-time}', '%{disconnect-time}',
'%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}',
'%{src-info}', '%{dest-info}', '%{Calling-Station-Id}',
'%{Called-Station-Id}', '%{Dialed-Number}')
>
> StartQueryAlt= insert into CDR (gatekeeper_name, call_number,
call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId,
ConfID, setup_time, connect_time, disconnect_time, caller_ip,
caller_port, callee_ip, callee_port, src_info, dest_info,
Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g',
'%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfID}',
'%{setup-time}', '%{connect-time}', '%{disconnect-time}',
'%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}',
'%{src-info}', '%{dest-info}', '%{Calling-Station-Id}',
'%{Called-Station-Id}', '%{Dialed-Number}')
>
> UpdateQuery= update CDR set call_duration=%d where
gatekeeper_name='%g' and acct_session_id='%s'
>
> StopQuery= update CDR set call_duration=%d, disconnect_cause=%c,
disconnect_time='%{disconnect-time}' where gatekeeper_name='%g' and
acct_session_id='%s'
>
> StopQueryAlt= insert into CDR (gatekeeper_name, call_number,
call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId,
ConfID, setup_time, connect_time, disconnect_time, caller_ip,
caller_port, callee_ip, callee_port, src_info, dest_info,
Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g STOP
Alt', '%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfID}',
'%{setup-time}', '%{connect-time}', '%{disconnect-time}',
'%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}',
'%{src-info}', '%{dest-info}', '%{Calling-Station-Id}',
'%{Called-Station-Id}', '%{Dialed-Number}')
>
> TimestampFormat=MySQL
>
>
>
>
>
> ----- Original Message -----
> From: Lucas Nishimura
> To: openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
> Sent: Sunday, September 11, 2005 5:24 PM
> Subject: Mysql Schemes
>
>
> Hi All,
> I'm Trying to configure GNUGK with mysql but i cant find the
database schemes could anyone help?
>
> --
> Lucas Nishimura
>
-----------------------------------------------------------------------
---------
> Tel: (43)9911-3440
> Email: lucas.nishimura@xxxxxxxxxx
> GVT - LONDRINA - PR
>
-----------------------------------------------------------------------
---------
>
>
--
Jan Willamowius, jan@xxxxxxxxxxxxxx, http://www.gnugk.org/
Title: OpenH323 Gatekeeper - The GNU Gatekeeper: Accounting Configuration
Sample MySQL Schema
The SQLAcct module is designed to adapt to whatever database structure you already have. You can define all queries so they fit your existing tables. Here is an example what those tables might look like in MySQL and you can use these as a starting point.
Create a new database; here we use the name 'GUNGK':
create database GUNGK;
Then create a table in this database to store you accounting data; we call the table 'CDR'.
create table GUNGK.CDR (
gatekeeper_name varchar(255),
call_number into zerofill,
call_duration mediumint unsigned zerofill,
index duration_idx (call_duration),
disconnect_cause smallint unsigned zerofill,
index dcc_idx (disconnect_cause),
acct_session_id varchar(255),
h323_id varchar(255),
gkip varchar(15),
CallId varchar(255),
ConfID varchar(255),
setup_time datetime,
connect_time datetime,
disconnect_time datetime,
caller_ip varchar(15),
index srcip_idx (caller_ip),
caller_port smallint unsigned zerofill,
callee_ip varchar(15),
index destip_idx (callee_ip),
callee_port smallint unsigned zerofill,
src_info varchar(255),
dest_info varchar(255),
Calling_Station_Id varchar(255),
Called_Station_Id varchar(255),
index dialednumber_idx (Called_Station_Id (20)),
Dialed_Number varchar(255)
);
Then you need to creat a username for accessing the data.
GRANT delete,insert,select,update ON GNUGK.* TO 'YourDesiredUsername'@'localhost' IDENTIFIED BY 'APassword';
With this command you will permit accessing the data from only the local server. If you need to access these data from any other computer then you have to set the proper security options.
Then you can add the following settings into your gnugk.ini file to insert and update the history of the calls into your database.
[Gatekeeper::Acct]
SQLAcct=optional;start,stop,update
FileAcct=sufficient;stop
[FileAcct]
DetailFile=Add your desire path here something like /var/log/cdr.log
StandardCDRFormat=0
CDRString=%g|%n|%d|%c|%s|%u|%{gkip}|%{CallId}|%{ConfId}|%{setup-time}|%{connect-time}|%{disconnect-time}|%{caller-ip}|%{caller-port}|%{callee-ip}|%{callee-port}|%{src-info}|%{dest-info}|%{Calling-Station-Id}|%{Called-Station-Id}|%{Dialed-Number}
Rotate=daily
RotateTime=23:59
[SQLAcct]
Driver=MySQL
Database=GNUGK
Username=YourDesiredUsername
Password=APassword
StartQuery= insert into CDR (gatekeeper_name, call_number, call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId, ConfId, setup_time, connect_time, disconnect_time, caller_ip, caller_port, callee_ip, callee_port, src_info, dest_info, Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g', '%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfId}', '%{setup-time}', '%{connect-time}', '%{disconnect-time}', '%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}', '%{src-info}', '%{dest-info}', '%{Calling-Station-Id}', '%{Called-Station-Id}', '%{Dialed-Number}')
StartQueryAlt= insert into CDR (gatekeeper_name, call_number, call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId, ConfID, setup_time, connect_time, disconnect_time, caller_ip, caller_port, callee_ip, callee_port, src_info, dest_info, Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g', '%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfID}', '%{setup-time}', '%{connect-time}', '%{disconnect-time}', '%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}', '%{src-info}', '%{dest-info}', '%{Calling-Station-Id}', '%{Called-Station-Id}', '%{Dialed-Number}')
UpdateQuery= update CDR set call_duration=%d where gatekeeper_name='%g' and acct_session_id='%s'
StopQuery= update CDR set call_duration=%d, disconnect_cause=%c, disconnect_time='%{disconnect-time}' where gatekeeper_name='%g' and acct_session_id='%s'
StopQueryAlt= insert into CDR (gatekeeper_name, call_number, call_duration, disconnect_cause, acct_session_id, h323_id, gkip, CallId, ConfID, setup_time, connect_time, disconnect_time, caller_ip, caller_port, callee_ip, callee_port, src_info, dest_info, Calling_Station_Id, Called_Station_Id, Dialed_Number) values ('%g STOP Alt', '%n', %d, %c, '%s', '%u', '%{gkip}', '%{CallId}', '%{ConfID}', '%{setup-time}', '%{connect-time}', '%{disconnect-time}', '%{caller-ip}', '%{caller-port}', '%{callee-ip}', '%{callee-port}', '%{src-info}', '%{dest-info}', '%{Calling-Station-Id}', '%{Called-Station-Id}', '%{Dialed-Number}')
TimestampFormat=MySQL
