Re: Can i do that?

2007-04-09 Thread Marat Rysbekov 
 IF EXISTS radius.dsl_acct_start $$

CREATE PROCEDURE radius.dsl_acct_start(

IN AcctUniqueId_IN VARCHAR(32),
IN UserName_IN VARCHAR(64),
IN AcctStartTime_IN DATETIME,
IN CallingStationId_IN VARCHAR(50),
IN FramedIPAddress_IN VARCHAR(15))

LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
MODIFIES SQL DATA

BEGIN

INSERT INTO radius.dsl_accounting_details(
AcctUniqueId,
FragStartTime,
FragStopTime,
FragInputOctets,
FragOutputOctets)
VALUES(
AcctUniqueId_IN,
AcctStartTime_IN,
'-00-00 00:00:00',
0,
0);

INSERT INTO radius.dsl_accounting(
AcctUniqueId,
UserName,
AcctStartTime,
AcctStopTime,
AcctSessionTime,
AcctInputOctets,
AcctOutputOctets,
CallingStationId,
FramedIPAddress)
VALUES(
AcctUniqueId_IN,
UserName_IN,
AcctStartTime_IN,
'0',
'0',
'0',
'0',
CallingStationId_IN,
FramedIPAddress_IN);

END $$

DELIMITER ;


2. dsl_acct_update

DELIMITER $$

DROP PROCEDURE IF EXISTS radius.dsl_acct_update $$

CREATE PROCEDURE radius.dsl_acct_update(
IN AcctSessionTime_IN INT(12),
IN AcctInputOctets_IN BIGINT(12),
IN AcctOutputOctets_IN BIGINT(12),
IN AcctUniqueId_IN VARCHAR(32),
IN UserName_IN VARCHAR(64))

LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
MODIFIES SQL DATA

BEGIN

DECLARE SessionStartTime, FragmentStopTime DATETIME;
DECLARE Prev_InputOctets, Prev_OutputOctets, New_InputOctets,
New_OutputOctets BIGINT(12);

# Fetch current values from radacct

SELECT
AcctStartTime,
AcctInputOctets,
AcctOutputOctets
FROM radius.dsl_accounting
WHERE AcctUniqueId = AcctUniqueId_IN
AND UserName = UserName_IN
INTO
SessionStartTime,
Prev_InputOctets,
Prev_OutputOctets;

# Calculate new values

SET New_InputOctets = AcctInputOctets_IN - Prev_InputOctets;
SET New_OutputOctets = AcctOutputOctets_IN - Prev_OutputOctets;

SET FragmentStopTime = SessionStartTime + INTERVAL AcctSessionTime_IN SECOND;

# Update tables

UPDATE radius.dsl_accounting_details
SET
FragStopTime = FragmentStopTime,
FragInputOctets = New_InputOctets,
FragOutputOctets = New_OutputOctets
WHERE AcctUniqueId = AcctUniqueId_IN
AND FragStopTime = '-00-00 00:00:00';

INSERT INTO radius.dsl_accounting_details(
AcctUniqueId,
FragStartTime,
FragStopTime,
FragInputOctets,
FragOutputOctets)
VALUES(
AcctUniqueId_IN,
FragmentStopTime,
'-00-00 00:00:00',
0,
0);

UPDATE radius.dsl_accounting
SET
AcctSessionTime = AcctSessionTime_IN,
AcctInputOctets = AcctInputOctets_IN,
AcctOutputOctets = AcctOutputOctets_IN
WHERE AcctUniqueId = AcctUniqueId_IN
AND UserName = UserName_IN;

END $$

DELIMITER ;


3. dsl_acct_stop

DELIMITER $$

DROP PROCEDURE IF EXISTS radius.dsl_acct_stop $$

CREATE PROCEDURE radius.dsl_acct_stop(

IN AcctStopTime_IN DATETIME,
IN AcctSessionTime_IN INT(12),
IN AcctInputOctets_IN BIGINT(12),
IN AcctOutputOctets_IN BIGINT(12),
IN AcctUniqueId_IN VARCHAR(32),
IN UserName_IN VARCHAR(64))

LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
MODIFIES SQL DATA

BEGIN

DECLARE SessionStartTime DATETIME;
DECLARE Prev_InputOctets, Prev_OutputOctets, New_InputOctets,
New_OutputOctets BIGINT(12);

# Fetch current values from radacct

SELECT
AcctStartTime,
AcctInputOctets,
AcctOutputOctets
FROM radius.dsl_accounting
WHERE AcctUniqueId = AcctUniqueId_IN
AND UserName = UserName_IN
INTO
SessionStartTime,
Prev_InputOctets,
Prev_OutputOctets;

# Calculate new values

SET New_InputOctets = AcctInputOctets_IN - Prev_InputOctets;
SET New_OutputOctets = AcctOutputOctets_IN - Prev_OutputOctets;

# Update tables

UPDATE radius.dsl_accounting_details
SET
FragStopTime = AcctStopTime_IN,
FragInputOctets = New_InputOctets,
FragOutputOctets = New_OutputOctets
WHERE AcctUniqueId = AcctUniqueId_IN
AND FragStopTime = '-00-00 00:00:00';

UPDATE radius.dsl_accounting
SET
AcctStopTime = AcctStopTime_IN,
AcctSessionTime = AcctSessionTime_IN,
AcctInputOctets = AcctInputOctets_IN,
AcctOutputOctets = AcctOutputOctets_IN
WHERE AcctUniqueId = AcctUniqueId_IN
AND UserName = UserName_IN;

END $$

DELIMITER ;


To actually use these procedures you should replace the accounting
query strings in your sql.conf with the following:

accounting_start_query = CALL dsl_acct_start
('%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%S',
'%{Calling-Station-Id}', '%{Framed-IP-Address}')

accounting_update_query = CALL dsl_acct_update
('%{Acct-Session-Time}', '%{Acct-Input-Octets}',
'%{Acct-Output-Octets}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}')

accounting_stop_query = CALL dsl_acct_stop('%S',
'%{Acct-Session-Time}', '%{Acct-Input-Octets}',
'%{Acct-Output-Octets}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}')


Any questions/comments/suggestions are welcome. :)

Best regards,
Marat Rysbekov
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Can i do that?

2007-04-08 Thread Marat Rysbekov 
Hello, Kostas.

I believe the best way to do that is to use stored procedures, if your
DB server supports them. Then you can replace the accounting query
strings in sql.conf to something like: CALL
accounting_start_procedure(attributes_you_want_to_pass), and compose
a stored procedure that will process the passed attributes and write
to whatever tables you need.

I can give you a MySQL example, if you're interested.
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Re: Radius Packet Simulator

2007-04-04 Thread Marat Rysbekov 
NTRadPing may be useful, too: http://www.dialways.com/download/
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Blank usernames

2006-11-19 Thread Marat Rysbekov 

Good day, everyone.

I'm trying to setup FreeRadius 1.1.3 to assing IP-addresses based on
what NAS the authorization request came from, no matter what
username or password was supplied.

Some of my configuration:

huntgroups
...
Alpha  NAS-IP-Address == 192.168.10.1

BetaNAS-IP-Address == 192.168.20.1
...

users
...
DEFAULTAuth-Type := Accept, Huntgroup-Name == Alpha, Pool-Name := PoolA

DEFAULTAuth-Type := Accept, Huntgroup-Name == Beta, Pool-Name := PoolB
...

I specify the IP-pools in radiusd.conf, and it works fine, except
for one thing:
I get no Access-Accept reply whenever the username is left blank (any non-empty
username is accepted by the server).

So, my question: is there a way to make FreeRadius use the DEFAULT entries
when the username is not supplied at all?

Thanks.
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html