[RADIATOR] Insert Accounting to DB Table.
Hugh Irvine
hugh at open.com.au
Mon Jun 1 00:45:34 CDT 2015
Hello again -
I’m not sure I understand what you are trying to do.
The AcctColumnDef that you show below is only for RADIUS accounting requests, not an access request which is what you show in the debug.
I see that your AuthSelect is looking for a column called “EXPIRATION” which again from the debug appears to contain a date prior to “now”, so it is failing.
Note that “Timestamp” is the UNIX integer number of seconds since the epoch (start of time at January 1, 1970).
regards
Hugh
> On 1 Jun 2015, at 14:12, Mohammed Alhaj Ali <m.alhaj at itc.sa> wrote:
>
> Hi Hugh
>
> I'm using the same line in my configuration
>
> "AcctColumnDef TIME_STAMP,Timestamp,integer", below is trace 4 output for account named testhuawei at 2048.itc.net.sa,
>
>
>
>
> Code: Access-Request
> Identifier: 114
> Authentic: <197><189>Qv<215>#<10><184><140><192><249>g<218><210><217><165>
> Attributes:
> User-Name = "testhuawei at 2048.itc.net.sa"
> CHAP-Password = <1>w<233><9>r<144><169>tI<15><29><14>+w<206><162><139>
> CHAP-Challenge = <197><189>Qv<215>#<10><184><140><192><249>g<218><210><217><165>
> NAS-Port = 33554442
> NAS-IP-Address = 87.101.255.184
> Service-Type = Framed-User
> Framed-Protocol = PPP
> Calling-Station-Id = "c4:6e:1f:a5:72:3e"
> NAS-Identifier = "Jeddah-ME60"
> NAS-Port-Type = Ethernet
> NAS-Port-Id = "Jeddah-ME60 eth 0/2/0/0:10"
> Acct-Session-Id = "Jeddah-0120200100000042f0f7184912"
> Connect-Info = "1000000000"
> Huawei-Startup-Stamp = 1422959894
> Huawei-IPHost-Addr = "255.255.255.255 c4:6e:1f:a5:72:3e"
> Huawei-Connect-ID = 184912
> Huawei-Version = "Huawei ME60"
> Huawei-Product-ID = "ME60"
> Huawei-Domain-Name = "2048.itc.net.sa"
> Huawei-User-Mac = "c4:6e:1f:a5:72:3e"
>
> Sun May 31 08:57:47 2015: DEBUG: Handling request with Handler 'Realm=/^(512|1024|2048)\.itc\.net\.sa$/'
> Sun May 31 08:57:47 2015: DEBUG: Deleting session for testhuawei at 2048.itc.net.sa, 87.101.255.184, 33554442
> Sun May 31 08:57:47 2015: DEBUG: Handling with Radius::AuthSQL: dpool_H
> Sun May 31 08:57:47 2015: DEBUG: Handling with Radius::AuthSQL: dpool_H
> Sun May 31 08:57:47 2015: DEBUG: Query is: 'select PASSWORD, to_char(EXPIRATION, 'yyyy-mm-dd HH24:MI:SS') Expiration, MAXSESSIONS, EXPIRATION_D "Huawei-Domain-Name" , Session_Timeout "Session-Timeout" from ITC_ACCOUNTS_H where upper(USERNAME)=upper('testhuawei at 2048.itc.net.sa')':
> Sun May 31 08:57:47 2015: ERR: Bad attribute=value pair: 3600
> Sun May 31 08:57:47 2015: DEBUG: Radius::AuthSQL looks for match with testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
> Sun May 31 08:57:47 2015: DEBUG: Expiration date converted to: 1427835600
> Sun May 31 08:57:47 2015: DEBUG: Radius::AuthSQL REJECT: Expiration date has passed: testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
> Sun May 31 08:57:47 2015: DEBUG: Query is: 'select PASSWORD, to_char(EXPIRATION, 'yyyy-mm-dd HH24:MI:SS') Expiration, MAXSESSIONS, EXPIRATION_D "Huawei-Domain-Name" , Session_Timeout "Session-Timeout" from ITC_ACCOUNTS_H where upper(USERNAME)=upper('DEFAULT')':
> Sun May 31 08:57:47 2015: DEBUG: AuthBy SQL result: REJECT, Expiration date has passed
> Sun May 31 08:57:47 2015: DEBUG: Handling with Radius::AuthFILE: flat
> Sun May 31 08:57:47 2015: DEBUG: Radius::AuthFILE looks for match with testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
> Sun May 31 08:57:47 2015: DEBUG: Radius::AuthFILE REJECT: No such user: testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
> Sun May 31 08:57:47 2015: DEBUG: AuthBy FILE result: REJECT, No such user
> Sun May 31 08:57:47 2015: INFO: Access rejected for testhuawei at 2048.itc.net.sa: No such user
> Sun May 31 08:57:47 2015: DEBUG: Packet dump:
> *** Sending to 87.101.255.184 port 1812 ....
>
> Packet length = 36
> 03 72 00 24 2f f5 e8 46 d5 1d 46 78 62 5e a1 1c
> 04 0f 93 b2 12 10 52 65 71 75 65 73 74 20 44 65
> 6e 69 65 64
> Code: Access-Reject
> Identifier: 114
> Authentic: <197><189>Qv<215>#<10><184><140><192><249>g<218><210><217><165>
> Attributes:
> Reply-Message = "Request Denied"
>
> Sun May 31 08:57:47 2015: DEBUG: Timed out, retransmitting
> Sun May 31 08:57:47 2015: DEBUG: Packet dump:
> *** Sending to 172.31.14.34 port 1813 ....
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Hugh Irvine [mailto:hugh at open.com.au]
> Sent: Sunday, May 31, 2015 8:32 AM
> To: Mohammed Alhaj Ali
> Cc: Sami Keski-Kasari; radiator at open.com.au
> Subject: Re: [RADIATOR] Insert Accounting to DB Table.
>
>
> Hello -
>
> The Radiator timestamp is an attribute called “Timestamp” which is added to the accounting requests.
>
> See “goodies/sql.cfg” in the Radiator distribution.
>
> regards
>
> Hugh
>
>
>> On 31 May 2015, at 15:00, Mohammed Alhaj Ali <m.alhaj at itc.sa> wrote:
>>
>> Hi Hugh,
>>
>> Actually as you said I was trying to use Radiator server timestamp,
>> but I'm not sure about syntax and where to pass it, can you help
>> please
>>
>>
>> Regards,
>>
>>
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: Hugh Irvine [mailto:hugh at open.com.au]
>> Sent: Friday, May 29, 2015 9:54 AM
>> To: Mohammed Alhaj Ali
>> Cc: Sami Keski-Kasari; radiator at open.com.au
>> Subject: Re: [RADIATOR] Insert Accounting to BD Table.
>>
>>
>> Hello -
>>
>> You should check your accounting requests to see if Event-Timestamp is present (I suspect it is not).
>>
>> A trace 4 debug will show you what you are receiving in the accounting requests.
>>
>> You may need additional configuration on your Huawei equipment, or you may need to use something else like the Radiator Timestamp.
>>
>> regards
>>
>> Hugh
>>
>>
>>
>>> On 28 May 2015, at 22:09, Mohammed Alhaj Ali <m.alhaj at itc.sa> wrote:
>>>
>>> Hi Sami,
>>>
>>> System calculate the Session-Timeout biased on the account first
>>> login which rely on the Event-Timestamp, when it inserted on the
>>> TIME_STAMP column on the DB table, then it will check the account
>>> number of date to calculate account expiry and then it return this
>>> value to Session-Timeout,
>>>
>>> Note that there's no problem for the account already active and having session-timeout configured, but for new subscription we did not get Event-Timestamp to be insert on the DB table.
>>>
>>> Please let me know if you need any other information.
>>>
>>> Thank you!
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: radiator-bounces at open.com.au
>>> [mailto:radiator-bounces at open.com.au] On Behalf Of Sami Keski-Kasari
>>> Sent: Thursday, May 28, 2015 1:54 PM
>>> To: radiator at open.com.au
>>> Subject: Re: [RADIATOR] Insert Accounting to BD Table.
>>>
>>> Hello Mohammed,
>>>
>>> I think that the error message is due your SQL query doesn't return anything to Expiration Check item and you have AddToReply Session-Timeout = "until Expiration" in configuration.
>>>
>>> Could you tell us more how the system should work?
>>> Who should/will update EXPIRATION field in database?
>>>
>>> Best Regards,
>>> Sami
>>>
>>> On 05/27/2015 11:32 AM, Mohammed Alhaj Ali wrote:
>>>> Dears,
>>>>
>>>>
>>>>
>>>> Recently we had some change on our network, as we replaced cisco
>>>> platform with Huawei BRAS, now we're unable to get prober accounting
>>>> specially, when customer account are newly created so we can't get
>>>> account activation on the first logging in order to calculate
>>>> Session-timeout, below are the error logs plus the part of the
>>>> configuration:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ################################
>>>>
>>>>
>>>>
>>>> <AuthBy SQL>
>>>>
>>>> AccountingTable DSL_ACCOUNTING
>>>>
>>>> AcctColumnDef USERNAME,User-Name,%A
>>>>
>>>> AcctColumnDef TIME_STAMP,Timestamp,integer
>>>>
>>>> AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
>>>>
>>>> AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
>>>>
>>>> AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
>>>>
>>>> AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
>>>>
>>>> AcctColumnDef ACCTSESSIONID,Acct-Session-Id
>>>>
>>>> AcctColumnDef ACCTSESSIONTIME,Acct-Session-Time,integer
>>>>
>>>> AcctColumnDef acctterminatecause, Acct-Terminate-Cause
>>>>
>>>> AcctColumnDef NASIDENTIFIER,NAS-Identifier
>>>>
>>>> AcctColumnDef NASPORT,NAS-Port,integer
>>>>
>>>> AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
>>>>
>>>> #AcctInsertQuery insert into %0 (%1) values (%2)
>>>>
>>>> AuthColumnDef 0,User-Password, check
>>>>
>>>> AuthColumnDef 1,Expiration, check
>>>>
>>>> AuthColumnDef 2,Simultaneous-Use, check
>>>>
>>>> AuthColumnDef 3,Huawei-Domain-Name, reply
>>>>
>>>> AuthColumnDef 4,GENERIC, reply
>>>>
>>>> AuthSelect select PASSWORD, to_char(EXPIRATION, 'yyyy-mm-dd
>>>> HH24:MI:SS') Expiration, MAXSESSIONS, EXPIRATION_D "Huawei-Domain-Name" ,
>>>> Session_Timeout "Session-Timeout" from ITC_ACCOUNTS_H where
>>>> upper(USERNAME)=upper('%n')
>>>>
>>>> CachePasswordExpiry 86400
>>>>
>>>> AddToReply Service-Type=Framed-User, Framed-Protocol=PPP,
>>>> Framed-MTU=1492, Session-Timeout = "until Expiration"
>>>>
>>>> ConnectionAttemptFailedHook sub {my $self = shift;my $dbsource =
>>>> shift;my $dbusername = shift;my $dbauth =
>>>> shift;$self->log($main::LOG_ERR, "Could not connect to SQL database
>>>> with
>>>> DBI->connect $dbsource, $dbusername, $dbauth: $@ $DBI::errstr");}
>>>>
>>>> DBSource dbi:ODBC:ORADB
>>>>
>>>> DBUsername user
>>>>
>>>> DBAuth password
>>>>
>>>> DateFormat %b %e, %Y %H:%M
>>>>
>>>> EAPAnonymous anonymous
>>>>
>>>> EAPContextTimeout 1000
>>>>
>>>> EAPFAST_PAC_Lifetime 7776000
>>>>
>>>> EAPFAST_PAC_Reprovision 2592000
>>>>
>>>> EAPTLS_MaxFragmentSize 2048
>>>>
>>>> EAPTLS_PEAPVersion 1
>>>>
>>>> EAPTLS_SessionResumption 1
>>>>
>>>> EAPTLS_SessionResumptionLimit 43200
>>>>
>>>> EAPTLS_VerifyDepth 1
>>>>
>>>> FailureBackoffTime 600
>>>>
>>>> Identifier HUW_POOL
>>>>
>>>> NoConnectionsHook sub { my $self = shift;$self->log($main::LOG_ERR,
>>>> "Could not connect to any SQL database. Request is ignored. Backing
>>>> off for $self- >{FailureBackoffTime} seconds");}
>>>>
>>>> NullPasswordMatchesAny 1
>>>>
>>>> PasswordPrompt password
>>>>
>>>> SIPDigestRealm DefaultSipRealm
>>>>
>>>> Timeout 60
>>>>
>>>> </AuthBy>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> LOG:
>>>>
>>>>
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Handling request with Handler
>>>> 'Realm=/^(512|1024|2048)\.itc\.net\.sa$/'
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Deleting session for
>>>> testhuawei at 2048.itc.net.sa, 87.101.255.184, 33554442
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Handling with Radius::AuthSQL:
>>>> HUW_POOL
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Handling with Radius::AuthSQL:
>>>> HUW_POOL
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Query is: 'select PASSWORD,
>>>> to_char(EXPIRATION, 'yyyy-mm-dd HH24:MI:SS') Expiration,
>>>> MAXSESSIONS, EXPIRATION_D "Huawei-Domain-Name" , Session_Timeout "Session-Timeout"
>>>> from ITC_ACCOUNTS_H where
>>>> upper(USERNAME)=upper('testhuawei at 2048.itc.net.sa')':
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Radius::AuthSQL looks for match
>>>> with testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Radius::AuthSQL ACCEPT: :
>>>> testhuawei at 2048.itc.net.sa [testhuawei at 2048.itc.net.sa]
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Session-Timeout="until ValidTo" was
>>>> specified, but there was no ValidTo or Expiration check item for
>>>> this user. Ignored.
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: AuthBy SQL result: ACCEPT,
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Access accepted for
>>>> testhuawei at 2048.itc.net.sa <mailto:testhuawei at 2048.itc.net.sa>
>>>>
>>>>
>>>>
>>>> Wed May 27 09:09:39 2015: ERR: There is no value named until
>>>> Expiration for attribute Session-Timeout. Using 0.
>>>>
>>>>
>>>>
>>>> Wed May 27 09:09:39 2015: DEBUG: Packet dump:
>>>>
>>>> *** Sending to 87.101.255.184 port 1812 ....
>>>>
>>>>
>>>>
>>>> Mohammed Alhaj Ali
>>>> Integrated Telecom Co. Ltd.
>>>> Tel : +966(11) 406-2222 Ext.2384
>>>> Fax : +966(11) 406-2221
>>>> GSM :
>>>> m.alhaj at itc.sa <mailto:m.alhaj at itc.sa>
>>>>
>>>> <http://www.execloud.net>
>>>>
>>>> www.itc.sa <http://www.itc.sa>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> radiator mailing list
>>>> radiator at open.com.au
>>>> http://www.open.com.au/mailman/listinfo/radiator
>>>>
>>>
>>>
>>> --
>>> Sami Keski-Kasari <samikk at open.com.au>
>>>
>>> Radiator: the most portable, flexible and configurable RADIUS server anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER etc. Full source on Unix, Windows, MacOSX, Solaris, VMS, NetWare etc.
>>> _______________________________________________
>>> radiator mailing list
>>> radiator at open.com.au
>>> http://www.open.com.au/mailman/listinfo/radiator
>>> _______________________________________________
>>> radiator mailing list
>>> radiator at open.com.au
>>> http://www.open.com.au/mailman/listinfo/radiator
>>
>>
>> --
>>
>> Hugh Irvine
>> hugh at open.com.au
>>
>> Radiator: the most portable, flexible and configurable RADIUS server anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER, SIM, etc.
>> Full source on Unix, Linux, Windows, MacOSX, Solaris, VMS, NetWare etc.
>>
>
>
> --
>
> Hugh Irvine
> hugh at open.com.au
>
> Radiator: the most portable, flexible and configurable RADIUS server anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP, DIAMETER, SIM, etc.
> Full source on Unix, Linux, Windows, MacOSX, Solaris, VMS, NetWare etc.
>
--
Hugh Irvine
hugh at open.com.au
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER, SIM, etc.
Full source on Unix, Linux, Windows, MacOSX, Solaris, VMS, NetWare etc.
More information about the radiator
mailing list