โดย voip4share » 20 ธ.ค. 2009 13:14
11.4 Section [SQLAcct]
This accounting module stores accounting information directly to a SQL database. Many configuration settings are common with other SQL modules.
Driver=MySQL | PostgreSQL | Firebird | ODBC | SQLite
Default: N/A
SQL database driver to use. Currently, MySQL, PostgreSQL, Firebird, ODBC and SQLite drivers are implemented.
Host=DNS[:PORT] | IP[:PORT]
Default: localhost
SQL server host address. Can be in the form of DNS[:PORT] or IP[:PORT]. Examples: sql.mycompany.com or sql.mycompany.com:3306 or 192.168.3.100.
Database=billing
Default: N/A
The database name to connect to.
Username=gnugk
The username used to connect to the database.
Password=secret
The password used to connect to the database. If the password is not specified, a database connection attempt without any password will be made. If EncryptAllPasswords is enabled, or a KeyFilled variable is defined in this section, the password is in an encrypted form and should be created using the addpasswd utility.
StartQuery=INSERT ...
Default: N/A
Defines SQL query used to insert a new call record to the database. The query is parametrized - that means parameter replacement is made before each query is executed. Parameter placeholders are denoted by % character and can be one letter (like %u) or whole strings (like %{src-info}). Specify %% to embed a percent character inside the query string (like %%). For SQLAcct the following parameters are defined:
%g - gatekeeper name
%n - call number (not unique after gatekeeper restart)
%d - call duration (seconds)
%t - total call duration (from Setup to Release Complete)
%c - Q.931 disconnect cause (hexadecimal integer)
%r - who disconnected the call (-1 - unknown, 0 - the gatekeeper, 1 - the caller, 2 - the callee)
%p - PDD (Post Dial Delay) in seconds
%s - unique (for this gatekeeper) call (Acct-Session-Id)
%u - H.323 ID of the calling party
%{gkip} - IP address of the gatekeeper
%{CallId} - H.323 call identifier (16 hex 8-bit digits)
%{ConfId} - H.323 conference identifier (16 hex 8-bit digits)
%{setup-time} - timestamp string for Q.931 Setup message
%{alerting-time} - timestamp string for Q.931 Alerting message
%{connect-time} - timestamp string for a call connected event
%{disconnect-time} - timestamp string for a call disconnect event
%{ring-time} - time a remote phone was ringing for (from Alerting till Connect or Release Complete)
%{caller-ip} - signaling IP address of the caller
%{caller-port} - signaling port of the caller
%{callee-ip} - signaling IP address of the called party
%{callee-port} - signaling port of the called party
%{src-info} - a colon separated list of source aliases
%{dest-info} - a colon separated list of destination aliases
%{Calling-Station-Id} - calling party number
%{Called-Station-Id} - called party number (rewritten Dialed-Number)
%{Dialed-Number} - dialed number (as received from the calling party)
%{caller-epid} - endpoint identifier of the calling party
%{callee-epid} - endpoint identifier of the called party
%{call-attempts} - number of attempts to establish the calls (with failover this can be > 1)
%{last-cdr} - is this the last CDR for this call ? (0 / 1) only when using failover this can be 0
%{media-oip} - caller's RTP media IP (only for H.245 routed/tunneled calls)
%{codec} - audio codec used during the call (only for H.245 routed/tunneled calls)
%{bandwidth} - bandwidth for this call
%{client-auth-id} - an ID provided to GnuGk when authenticating the call (through SqlAuth)
Sample query string:
INSERT INTO call (gkname, sessid, username, calling, called)
VALUES ('%g', '%s', '%u', '%{Calling-Station-Id}', '%{Called-Station-Id}')
StartQueryAlt=INSERT ...
Default: N/A
Defines a SQL query used to insert a new call record to the database in case the StartQuery failed for some reason (the call already exists, for example). The syntax and parameters are the same as for StartQuery.
UpdateQuery=UPDATE ...
Default: N/A
Defines a SQL query used to update a call record in the database with the current call state. The syntax and parameters are the same as for StartQuery.
Sample query string:
UPDATE call SET duration = %d WHERE gkname = '%g' AND sessid = '%s'
StopQuery=UPDATE ...
Default: N/A
Defines SQL query used to update a call record in the database when the call is finished (disconnected). The syntax and parameters are the same as for StartQuery.
Sample query string:
UPDATE call SET duration = %d, dtime = '%{disconnect-time}' WHERE gkname = '%g' AND sessid = '%s'
StopQueryAlt=INSERT ...
Default: N/A
Defines a SQL query used to update call record in the database when the call is finished (disconnected) in case the regular StopQuery failed (because the call record does not yet exist, for example). The syntax and parameters are the same as for StartQuery.
Sample query string:
INSERT INTO call (gkname, sessid, username, calling, called, duration)
VALUES ('%g', '%s', '%u', '%{Calling-Station-Id}', '%{Called-Station-Id}', %d)
TimestampFormat=MySQL
Default: N/A
Format of timestamp strings used in queries. If this setting is not specified, the global one from the main gatekeeper section is used.
MinPoolSize=5
Default: 1
Number of concurrent SQL connections in the pool. The first available connection in the pool is used to store accounting data.
A 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 of what those tables might look like in MySQL and which you can use as a starting point.
Create a new database; here we use the name 'GNUGK':
create database GNUGK;
Then create a table in this database to store you accounting data; we call the table 'CDR'.
create table GNUGK.CDR (
gatekeeper_name varchar(255),
call_number int 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 create a username for accessing the data.
mysql> GRANT delete,insert,select,update ON GNUGK.* TO 'YourDesiredUsername'@'localhost' IDENTIFIED BY 'APassword';
mysql> flush privileges;
With this command you will permit access to the data only from the local server. If you need to access the data from any other computer then you have to set the proper security options.
For example, to permit access from the 192.168.1.0/24 network:
mysql> GRANT delete,insert,select,update ON GNUGK.* TO 'YourDesiredUsername'@'192.168.1.%' IDENTIFIED BY 'APassword';
mysql> flush privileges;
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