Re: (RADIATOR) logging everything with sql into database
some misunderstandings occurred. i already solved the problem, but it
may be useful for others also. for the result i need my database look like
this:
SQL select * from radius_tbl;
ID KEY VALUE
---
1 User-Nameragnar
1 Service-Type Framed-User
1 NAS-IP-Address 203.63.154.1
1 NAS-Port 1234
1 NAS-Port-TypeAsync
1 Acct-Session-Id 1234
1 Acct-Status-Type Start
1 Timestamp936347798
2 User-Nameragnar
2 Service-Type Framed-User
2 NAS-IP-Address 203.63.154.1
2 NAS-Port 1234
2 NAS-Port-TypeAsync
2 Acct-Session-Id 1234
2 Acct-Status-Type Stop
2 Acct-Delay-Time 1
2 Acct-Session-Time1
2 Acct-Input-Octets2
2 Acct-Output-Octets 3
2 Timestamp936347799
20 rows selected.
SQL
as i understood, it is not possible trivially. radiator did need slight
patching. my configuration file finally contains following section:
AuthBy SQL
AuthSelect
DBSource dbi:Oracle:MY_DATABASE_SID
DBUsername ragnar
DBAuth
AcctSQLFormat sub { \
my( $p ) = @_; \
my $str = join "", map { \
my( $key, $value ) = @$_; \
$key =~ s/([^a-zA-Z0-9])/sprintf("%%%d%%",ord($1))/ego; \
$value =~ s/([^a-zA-Z0-9])/sprintf("%%%d%%",ord($1))/ego; \
"$key=$value;"; \
} @{$p-{Attributes}}; \
"begin radius.log('$str'); end;"; \
}
/Auth
as you see i use AcctSQLFormat option. it just returns appropriate sql
statement, which will be executed by radiator.
this example constructs sql function which will be executed by database.
function takes one string as an argument. string is made up from encoded
key-value pairs.
see attatchment for patch. i use radiator version 2.13.1 so the patch is
also for it.
as this logs huge amount of data into database it requires a lot database
tuning.
i have not heavily tested this, but my first attempts were successful.
so it may contain bugs.
ragnar
Radiator already provides funtionality to log everything to
an SQL database. There's no hacking required.
Look at the AuthBy SQL and the Log SQL directives.
The docs are at http://www.open.com.au/radiator/ref.html
I've even posted an example implementation to log accounting
packets into an SQL database while retaining authentication
using another method. Search the mailing list archives.
i'm trying to make radiator to log everything into my database.
my database table would contain following fields: id, key, value.
one log event would be grouped by id. how can this be achieved?
nearest solution i can think of is following:
* hack radiator to provide a perl code to access all event attributes
* encode them into a long string
* call database procedure and pass the string
* the procedure would decode the string and insert key-value pairs
with shared id into table
is there more elegant way to do this? especially should i hack radiator or
does it provide facilities to satisfy my needs without hacking one?
*** AuthSQL.pm.orig Thu Sep 2 14:47:50 1999
--- AuthSQL.pm Thu Sep 2 15:27:01 1999
***
*** 100,105
--- 100,112
{
push(@{$self-{AcctSQLStatement}}, $value);
}
+ elsif ($keyword eq 'AcctSQLFormat')
+ {
+ $self-{AcctSQLFormat} = eval($value);
+ main::log($main::LOG_ERR,
+ "Compilation error in AcctSQLFormat(): $@")
+ if $@;
+ }
else
{
# Multiple inheritance
***
*** 147,153
return ($main::ACCEPT)
if (!defined $self-{AcctColumnDef}
|| $self-{AccountingTable} eq '')
!!defined $self-{AcctSQLStatement};
# If AccountingStartsOnly is set, only process Starts
# Acknowledge and drop anything else
--- 154,161
return ($main::ACCEPT)
if (!defined $self-{AcctColumnDef}
|| $self-{AccountingTable} eq '')
!!defined $self-{AcctSQLStatement}
!!defined $self-{AcctSQLFormat};
# If AccountingStartsOnly is set, only process Starts
# Acknowledge and drop anything else
***
*** 163,168
--- 171,187
$p-getAttrByNum($Radius::Radius::ACCT_STATUS_TYPE)
ne 'Stop';
+ # If AcctSQLFormat is set, execute it.
+ if(defined $self-{AcctSQLFormat})
+ {
+ # We use an eval so an error in the hook wont kill us.
+ my $sql = eval{ {$self-{AcctSQLFormat}}($p); };
+ main::log( $main::LOG_ERR, "Error in AcctSQLFormat(): $@" )
+ if $@;
+ defined($sql)
+
Re: (RADIATOR) logging everything with sql into database
Radiator already provides funtionality to log everything to an SQL database. There's no hacking required. Look at the AuthBy SQL and the Log SQL directives. The docs are at http://www.open.com.au/radiator/ref.html I've even posted an example implementation to log accounting packets into an SQL database while retaining authentication using another method. Search the mailing list archives. At 02:29 PM 9/2/99 +0300, Ragnar Kurm wrote: i'm trying to make radiator to log everything into my database. my database table would contain following fields: id, key, value. one log event would be grouped by id. how can this be achieved? nearest solution i can think of is following: * hack radiator to provide a perl code to access all event attributes * encode them into a long string * call database procedure and pass the string * the procedure would decode the string and insert key-value pairs with shared id into table is there more elegant way to do this? especially should i hack radiator or does it provide facilities to satisfy my needs without hacking one? ragnar === Archive at http://www.thesite.com.au/~radiator/ To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message. === Archive at http://www.thesite.com.au/~radiator/ To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
Re: (RADIATOR) logging everything with sql into database
Hello Ragnar - On Thu, 02 Sep 1999, Ragnar Kurm wrote: i'm trying to make radiator to log everything into my database. my database table would contain following fields: id, key, value. one log event would be grouped by id. how can this be achieved? nearest solution i can think of is following: * hack radiator to provide a perl code to access all event attributes * encode them into a long string * call database procedure and pass the string * the procedure would decode the string and insert key-value pairs with shared id into table is there more elegant way to do this? especially should i hack radiator or does it provide facilities to satisfy my needs without hacking one? Yes there is: Log SQL DBSource ... DBUsername ... DBAuth ... ... /Log or # Use an SQL table specified by AccountingTable # Columns are defined by AcctColumnDef AuthBy SQL DBSource ... DBUsername ... DBAuth ... AccountingTable ... AcctColumnDef ... ... /AuthBy or # Use an SQL table specified by AccountingTable # Define an SQL statement to perform inserts with AcctSQLStatement AuthBy SQL DBSource ... DBUsername ... DBAuth ... AccountingTable ... AcctSQLStatement ... /AuthBy There are a number of excellent examples in the distributed goodies directory. hth Hugh -- Radiator: the most portable, flexible and configurable RADIUS server anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, Platypus, Freeside, TACACS+, PAM, external, etc etc on Unix, Win95/8, NT, Rhapsody === Archive at http://www.thesite.com.au/~radiator/ To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
