(RADIATOR) Question about "Calls" table

Hugh Irvine hugh at open.com.au
Wed Nov 6 01:51:01 CST 2002


Hello Lin -

This is because the default format for integer-date is being used.

If you want to change it you should specify your own DateFromat.

See sections 6.28.14 and 6.3 in the Radiator 3.3.1 reference manual.

regards

Hugh


On Wednesday, November 6, 2002, at 07:55 AM, Huaikun Lin wrote:

> Hi Hugh
>
> Here is my radius configuration:
> Foreground
> LogStdout
> LogDir          /var/log/radius
> DbDir           /local/etc/radius
> PidFile         /var/run/radiusd.pid
> SnmpgetProg     /local/bin/snmpget
> AuthPort        1645
> AcctPort        1646
> Trace 3
>
> <client xxx>
> ...
> </client>
>
> <Handler Request-Type = Accounting-Request>
>         RewriteUsername s/^([^@]+).*/$1/
>         <AuthBy EMERALD>
>                 # You can use this to force Radiator to limit
>                 # maximum session times to how many minutes
>                 # are left in subaccounts.timeleft
>                 DefaultSimultaneousUse 1
>                 TimeBanking
>                 # Change DBSource, DBUsername, DBAuth for your database
>                 # See the reference manual
>                 DBSource        dbi:Sybase:server=xxx;database=xxx
>                 DBUsername      xxxx
>                 DBAuth          xxxx
>
>                 # You can add to or change these if you want.
>                 AccountingTable Calls
>                 AcctColumnDef   UserName,User-Name
>                 AcctColumnDef   CallDate,Timestamp,integer-date
>                 AcctColumnDef   AcctStatusType,Acct-Status-Type,integer
>                 AcctColumnDef   AcctDelayTime,Acct-Delay-Time,integer
>                 AcctColumnDef    
> AcctOutputOctets,Acct-Output-Octets,integer
>                 AcctColumnDef   AcctSessionId,Acct-Session-Id
>                 AcctColumnDef    
> AcctSessionTime,Acct-Session-Time,integer
>                 AcctColumnDef  
> AcctTerminateCause,Ascend-Disconnect-Cause,integ
> er
> #               AcctColumnDef    
> AcctTerminateCause,Acct-Terminate-Cause,integer
> #               AcctColumnDef   NASIdentifier,NAS-Identifier
>                 AcctColumnDef   NASIdentifier,NAS-IP-Address
>                 AcctColumnDef   NASPort,NAS-Port,integer
>
>                 AddATDefaults
>                 AuthSelect ,sa.LoginLimit
>                 AuthColumnDef 0,Simultaneous-Use,check
>         </AuthBy>
> # Log all accounting into daily log files
> AcctLogFileName /var/log/radius/log/%Y%m%d.act
> </Handler>
>
> <Handler>
>         RewriteUsername s/^([^@]+).*/$1/
>         # If Platypus rejects the login, forward it to the old Radius  
> server
>         #AuthByPolicy ContinueUntilAccept
>         PasswordLogFileName %L/password.log
>
>
>         <AuthBy EMERALD>
>                 # You can use this to force Radiator to limit
>                 # maximum session times to how many minutes
>                 # are left in subaccounts.timeleft
>                 DefaultSimultaneousUse 1
>                 TimeBanking
>                 # Change DBSource, DBUsername, DBAuth for your database
>                 # See the reference manual
>                 DBSource        dbi:Sybase:server=xxxx;database=xxx
>                 DBUsername      xxxxxx
>                 DBAuth          xxxxxx
>                 AddATDefaults
>                 AuthSelect ,sa.LoginLimit
>                 AuthColumnDef 0,Simultaneous-Use,check
>         </AuthBy>
>
>         <AuthBy DBFILE>
>                 Filename %D/users
>         </AuthBy>
>         <AuthBy FILE>
>                 Filename %D/users
>         </AuthBy>
>
> </Handler>
>
>
> The following is one example of trace 4 debug:
>
> Thu Sep  5 23:00:03 2002: DEBUG: Packet dump:
> *** Received from 203.96.xx.xx port 39577 ....
> Code:       Access-Request
> Identifier: 167
> Authentic:  <0><0>X<242><0><0><t<0><0>,4<0><0>B<173>
> Attributes:
>         Proxy-Action = "AUTHENTICATE"
>         User-Name = "andy.hema"
>         User-Password =  
> "<184>/<213><9>gG<19><237>4<177><232><252><250><2><23> "
>         NAS-IP-Address = 192.168.8.253
>         NAS-Port = 278
>         Acct-Session-Id = "18159809"
>         USR-Interface-Index = 1534
>         Service-Type = Framed-User
>         Framed-Protocol = PPP
>         Chassis-Call-Slot = 2
>         Chassis-Call-Span = 1
>         Chassis-Call-Channel = 22
>         Calling-Station-Id = ""
>         Called-Station-Id = "1900"
>         Connect-Speed = NONE
>         NAS-Port-Type = Async
>         User-Id = "andy.hema"
>         NAS-Identifier = "ipw1-n1-15.ipnet.telecom.co.nz"
>         User-Realm = "actrix"
>         Proxy-State = 0
>
> Thu Sep  5 23:00:03 2002: DEBUG: Rewrote user name to andy.hema
> Thu Sep  5 23:00:03 2002: DEBUG: Check if Handler Realm=twor.ac.nz  
> should be use
> d to handle this request
> Thu Sep  5 23:00:03 2002: DEBUG: Check if Handler Request-Type =  
> Accounting-Requ
> est should be used to handle this request
> Thu Sep  5 23:00:03 2002: DEBUG: Check if Handler  should be used to  
> handle this
>  request
> Thu Sep  5 23:00:03 2002: DEBUG: Handling request with Handler ''
> Thu Sep  5 23:00:03 2002: DEBUG: Rewrote user name to andy.hema
> Thu Sep  5 23:00:03 2002: DEBUG:  Deleting session for andy.hema,  
> 192.168.8.253,
>  278
> Thu Sep  5 23:00:03 2002: DEBUG: do query is: delete from RADONLINE  
> where NASIDE
> NTIFIER='192.168.8.253' and NASPORT=0278
>
> Thu Sep  5 23:00:03 2002: DEBUG: Handling with Radius::AuthEMERALD
> Thu Sep  5 23:00:03 2002: DEBUG: Handling with Radius::AuthEMERALD
> Thu Sep  5 23:00:03 2002: DEBUG: Query is: select DateAdd(Day,  
> ma.extension+ma.o
> verdue, maExpireDate),
> DateAdd(Day, sa.extension, saExpireDate), sa.AccountID, sa.AccountType,
> sa.password, sa.login, sa.shell, sa.TimeLeft ,sa.LoginLimit
> from masteraccounts ma, subaccounts sa
> where (sa.login = 'andy.hema' or sa.shell = 'andy.hema')
> and ma.customerid = sa.customerid
> and sa.active <> 0 and ma.active <> 0
>
> Thu Sep  5 23:00:03 2002: DEBUG: Select results: Dec 31 2030 12:00A,  
> Sep 05 2003
>  10:57P, 28523, PPP, hema23, andy.hema, , , 1
> Thu Sep  5 23:00:03 2002: DEBUG: Query is: select ra.RadAttributeID,  
> ra.RadVendo
> rID, ra.RadVendorType,
> Data, Value, Type, RadCheck
> from RadConfigs rc, RadAttributes ra
> where ra.RadAttributeID = rc.RadAttributeID
> and ra.RadVendorID = rc.RadVendorID
> and ra.RadVendorType = rc.RadVendorType
> and rc.AccountID=28523
>
> Thu Sep  5 23:00:03 2002: DEBUG: Query is: select ra.RadAttributeID,  
> ra.RadVendo
> rID, ra.RadVendorType,
> Data, Value, Type, RadCheck
> from RadATConfigs rc, RadAttributes ra
> where ra.RadAttributeID = rc.RadAttributeID
> and ra.RadVendorID = rc.RadVendorID
> and ra.RadVendorType = rc.RadVendorType
> and rc.AccountType='PPP'
>
> Thu Sep  5 23:00:03 2002: DEBUG: Radius::AuthEMERALD looks for match  
> with andy.h
> ema
> Thu Sep  5 23:00:03 2002: DEBUG: Expiration date converted to:  
> 1924858800
> Thu Sep  5 23:00:03 2002: DEBUG: Expiration date converted to:  
> 1062676800
> Thu Sep  5 23:00:03 2002: DEBUG: Query is: select NASIDENTIFIER,  
> NASPORT, ACCTSE
> SSIONID, FRAMEDIPADDRESS from RADONLINE where USERNAME='andy.hema'
>
> Thu Sep  5 23:00:03 2002: DEBUG: Radius::AuthEMERALD ACCEPT:
> Thu Sep  5 23:00:03 2002: DEBUG: Access accepted for andy.hema
> Thu Sep  5 23:00:03 2002: DEBUG: Packet dump:
> *** Sending to 203.96.xx.xx port 39577 ....
> Code:       Access-Accept
> Identifier: 167
> Authentic:  <0><0>X<242><0><0><t<0><0>,4<0><0>B<173>
> Attributes:
>         Proxy-State = 0
>         Proxy-Action = "AUTHENTICATE"
>         Idle-Timeout = 1200
>         Framed-IP-Netmask = 255.255.255.255
>         Service-Type = 2
>         Framed-Protocol = 1
>         Framed-Compression = 1
>
> Thu Sep  5 23:00:04 2002: DEBUG: Packet dump:
> *** Received from 203.96.xx.xx port 39577 ....
> Code:       Accounting-Request
> Identifier: 163
> Authentic:  8<189><206>!O<131><162>*N<194><29><136><3>qP<137>
> Attributes:
>         Proxy-Action = "LAS_ACCT"
>         User-Name = "andy.hema"
>         NAS-IP-Address = 192.168.8.253
>         Acct-Status-Type = Start
>         Acct-Session-Id = "18159809"
>         Acct-Delay-Time = 0
>         Acct-Authentic = RADIUS
>         Service-Type = Framed-User
>         NAS-Port-Type = Async
>         NAS-Port = 278
>         USR-Interface-Index = 1534
>         Chassis-Call-Slot = 2
>         Chassis-Call-Span = 1
>         Chassis-Call-Channel = 22
>         Unauthenticated-Time = 1
>         Calling-Station-Id = ""
>         Called-Station-Id = "1900"
>         Modulation-Type = v90Digital
>         Simplified-MNP-Levels = ccittV42
>         Simplified-V42bis-Usage = ccittV42bis
>         Connect-Speed = 48000_BPS
>         Framed-Protocol = PPP
>         Framed-IP-Address = 203.96.62.246
>         User-Id = "andy.hema"
>         NAS-Identifier = "ipw1-n1-15.ipnet.telecom.co.nz"
>         User-Realm = "actrix"
>         Proxy-State = 0
>         Timestamp = 1031223164
>
> Thu Sep  5 23:00:04 2002: DEBUG: Rewrote user name to andy.hema
> Thu Sep  5 23:00:04 2002: DEBUG: Check if Handler Realm=twor.ac.nz  
> should be use
> d to handle this request
> Thu Sep  5 23:00:04 2002: DEBUG: Check if Handler Request-Type =  
> Accounting-Requ
> est should be used to handle this request
> Thu Sep  5 23:00:04 2002: DEBUG: Handling request with Handler  
> 'Request-Type = A
> ccounting-Request'
> Thu Sep  5 23:00:04 2002: DEBUG: Rewrote user name to andy.hema
> Thu Sep  5 23:00:04 2002: DEBUG: Query is: checkcallstop  
> 'andy.hema','192.168.8.
> 253',278
>
> Thu Sep  5 23:00:04 2002: DEBUG:  Adding session for andy.hema,  
> 192.168.8.253, 2
> 78
> Thu Sep  5 23:00:04 2002: DEBUG: do query is: delete from RADONLINE  
> where NASIDE
> NTIFIER='192.168.8.253' and NASPORT=0278
>
> Thu Sep  5 23:00:04 2002: DEBUG: do query is: insert into RADONLINE  
> (USERNAME, N
> ASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS,  
> NASPORTTYPE,
> SERVICETYPE) values ('andy.hema', '192.168.8.253', 0278, '18159809',  
> 1031223164,
>  '203.96.62.246', 'Async', 'Framed-User')
>
> Thu Sep  5 23:00:04 2002: DEBUG: do query is: delete from Stopoffline  
> where user
> name='andy.hema'
>
> Thu Sep  5 23:00:04 2002: DEBUG: Handling with Radius::AuthEMERALD
> Thu Sep  5 23:00:04 2002: DEBUG: Handling accounting with  
> Radius::AuthEMERALD
> Thu Sep  5 23:00:04 2002: DEBUG: do query is: insert into Calls
>                 (UserName, CallDate, AcctStatusType, AcctDelayTime,  
> AcctSessionI
> d, NASIdentifier, NASPort)
>                 values
>                 ('andy.hema', 'Sep  5, 2002 22:52', 1, 0, '18159809',  
> '192.168.8
> .253', 278)
>
> Thu Sep  5 23:00:04 2002: DEBUG: Accounting accepted
> Thu Sep  5 23:00:04 2002: DEBUG: Packet dump:
> *** Sending to 203.96.xx.xx port 39577 ....
> Code:       Accounting-Response
> Identifier: 163
> Authentic:  8<189><206>!O<131><162>*N<194><29><136><3>qP<137>
> Attributes:
>         Proxy-State = 0
>         Proxy-Action = "LAS_ACCT"
>
>
> Please look at the CallDate field in "insert calls" statement:
> 'Sep  5, 2002 22:52'------------------------has no "second" there.
>
> Lin
>
> At 05:51 PM 11/5/2002 +1100, Hugh Irvine wrote:
>
>
>> Hello Lin -
>>
>> I will need to see a copy of your Radiator configuration file (no
>> secrets) together with a trace 4 debug showing what is happening.
>>
>> regards
>>
>> Hugh
>>
>>
>> On Tuesday, November 5, 2002, at 10:21 AM, Huaikun Lin wrote:
>>
>>> Hi
>>>
>>> I am using SQL server and radiator 3.1 radius.
>>>
>>> In SQL server,we have a table "calls" which records the accounting
>>> data.
>>>
>>> Recently,I found the data in one of the field "CallDate" (data
>>> type:datetime) is not exactly
>>> acurate. This data type should record data like "2002-11-05  
>>> 12:05:23".
>>> But the actual
>>> data is something like "2002-11-05 12:05:00" which the "second" field
>>> is alway 0.
>>> (The year,month,day,hour,min are right.)
>>>
>>> When I check the radius debug, I can see the "Insert" statement
>>> doesn't include
>>> the "second" field and make the data is not 100% acurate:
>>>
>>> insert into Calls
>>> (UserName,AcctStatusType,NASPort,CallDate,AcctSessionId,AcctInputOcte 
>>> ts ,AcctSessionTime,
>>> AcctOutputOctets) values ('test',2,541262250,'Nov  5, 2002
>>> 12:10','204301AA-3DC6C5C6',25 45013,14466,66242572)
>>>
>>> I think the insert statement in radius script causes the inacurate.
>>>
>>> If there is no "second" field in there,the SQL database makes it to  
>>> be
>>> "00".
>>>
>>> Is there anywhere to fix that?
>>>
>>> How to fix it.
>>>
>>> Lin
>>>
>>> ---
>>> Outgoing mail is certified Virus Free.
>>> Checked by AVG anti-virus system (http://www.grisoft.com).
>>> Version: 6.0.410 / Virus Database: 231 - Release Date: 10/31/2002
>>
>> NB: I am travelling this week, so there may be delays in our
>> correspondence.
>>
>> --
>> Radiator: the most portable, flexible and configurable RADIUS server
>> anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
>> -
>> Nets: internetwork inventory and management - graphical, extensible,
>> flexible with hardware, software, platform and database independence.
>>
>>
>>
>>
>>
>> ---
>> Incoming mail is certified Virus Free.
>> Checked by AVG anti-virus system (http://www.grisoft.com).
>> Version: 6.0.410 / Virus Database: 231 - Release Date: 10/31/2002
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.410 / Virus Database: 231 - Release Date: 10/31/2002
>

NB: I am travelling this week, so there may be delays in our  
correspondence.

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.

===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.


More information about the radiator mailing list