(RADIATOR) Re: Values Disappearing

Wed Apr 2 00:45:26 CST 2003

Hugh,

I HAVE JUST FIXED THE PROBLEM

I AM STILL SENDING THIS EMAIL INCASE ANYONE ELSE RUNS INTO THE SAME PROBLEM

BELOW IS A FIX FOR THE PROBLEM

We are using Radius Interim Packets (Live Packets),
Looking through the logs I have found the problem why it was failing but it
still doesnt solve my current delimer,

The reason why they are failing are becuase they are account start records,
so these attributes are not being parsed. So the first insert will always
fail, and then the rest of the add querys will run.

Is there anyway I can fix this.

Below is extract of the log so you can see what it is doing,
Wed Apr  2 16:12:46 2003: DEBUG: Packet dump:
*** Received from 202.22.160.2 port 1646 ....
Code:       Accounting-Request
Identifier: 190
Authentic:  ~<137><180><133>`<30>w<30><179><247><235><27><201><17><224><244>
Attributes:
 NAS-IP-Address = 202.22.160.2
 NAS-Port = 119
 NAS-Port-Type = Async
 User-Name = "tsn191414"
 Called-Station-Id = "65608120"
 Calling-Station-Id = "265690655"
 Acct-Status-Type = Start
 Acct-Authentic = RADIUS
 Service-Type = Framed-User
 Acct-Session-Id = "000194B8"
 Framed-Protocol = PPP
 Framed-IP-Address = 202.22.162.226
 Acct-Delay-Time = 35

Wed Apr  2 16:12:46 2003: DEBUG: Handling request with Handler ''
Wed Apr  2 16:12:46 2003: DEBUG: TSN Adding session for tsn191414,
202.22.160.2, 119
Wed Apr  2 16:12:46 2003: DEBUG: do query is: delete from RADONLINE where
USERNAME='tsn191414' and NASIDENTIFIER='202.22.160.2' and NASPORT='119'

Wed Apr  2 16:12:47 2003: DEBUG: do query is: insert into RADONLINE
(USERNAME, NASIDENTIFIER, NASPORT, ACCTSESSIONID, FRAMEDADDRESS,
AccInputOctets, AccOutputOctets, SERVICETYPE,NASPortDNIS,callerid,AscendKey)
values ('tsn191414', '202.22.160.2', 119, '000194B8', '202.22.162.226', , ,
'Framed-User','65608120','265690655','')

Wed Apr  2 16:12:47 2003: ERR: do failed for 'insert into RADONLINE
(USERNAME, NASIDENTIFIER, NASPORT, ACCTSESSIONID, FRAMEDADDRESS,
AccInputOctets, AccOutputOctets, SERVICETYPE,NASPortDNIS,callerid,AscendKey)
values ('tsn191414', '202.22.160.2', 119, '000194B8', '202.22.162.226', , ,
'Framed-User','65608120','265690655','')': [Microsoft][ODBC SQL Server
Driver][SQL Server]Line 1: Incorrect syntax near ','. (SQL-37000)(DBD:
Execute immediate failed err=-1)
Wed Apr  2 16:12:47 2003: ERR: do failed for 'insert into RADONLINE
(USERNAME, NASIDENTIFIER, NASPORT, ACCTSESSIONID, FRAMEDADDRESS,
AccInputOctets, AccOutputOctets, SERVICETYPE,NASPortDNIS,callerid,AscendKey)
values ('tsn191414', '202.22.160.2', 119, '000194B8', '202.22.162.226', , ,
'Framed-User','65608120','265690655','')': [Microsoft][ODBC SQL Server
Driver][SQL Server]Line 1: Incorrect syntax near ','. (SQL-37000)(DBD:
Execute immediate failed err=-1)
Wed Apr  2 16:12:47 2003: DEBUG: Handling with Radius::AuthEMERALD
Wed Apr  2 16:12:47 2003: DEBUG: Handling accounting with
Radius::AuthEMERALD
Wed Apr  2 16:12:47 2003: DEBUG: do query is: insert into radCalls
(CallerID,AcctSessionId,NASPort,UserName,NASIdentifier,CallDate,FramedAddres
s,NASPortDNIS,AcctStatusType,AcctDelayTime) values
('265690655','000194B8',119,'tsn191414','202.22.160.2','Apr  2, 2003
16:12','202.22.162.226','65608120',1,35)

Wed Apr  2 16:12:47 2003: DEBUG: Accounting accepted
Wed Apr  2 16:12:47 2003: DEBUG: Packet dump:

Here is an extract of our config file
AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, \
ACCTSESSIONID, FRAMEDADDRESS, AccInputOctets, AccOutputOctets, \
SERVICETYPE,NASPortDNIS,callerid,AscendKey) values ('%n', '%N', %{NAS-Port},
'%{Acct-Session-Id}', \
'%{Framed-IP-Address}', %{Acct-Input-Octets}, %{Acct-Output-Octets}, \
'%{Service-Type}','%{Called-Station-Id}','%{Calling-Station-Id}','%{Ascend-S
ession-Svr-Key}')

DeleteQuery delete from RADONLINE where USERNAME='%n' and \
NASIDENTIFIER='%N' and NASPORT='%{NAS-Port}'

ClearNasQuery delete from RADONLINE where NASIDENTIFIER='%N'

CountQuery select NASIDENTIFIER, NASPORT, ACCTSESSIONID from RADONLINE \
where USERNAME='%n'

HERE IS THE FIX TO THE PROBLEM
AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, \
ACCTSESSIONID, FRAMEDADDRESS, AccInputOctets, AccOutputOctets, \
SERVICETYPE,NASPortDNIS,callerid,AscendKey) values ('%n', '%N', %{NAS-Port},
'%{Acct-Session-Id}', \
'%{Framed-IP-Address}', case '%{Acct-Input-Octets}' when '' then 0 else
cast('%{Acct-Input-Octets}' as numeric) end, case '%{Acct-Output-Octets}'
when '' then 0 else cast('%{Acct-Output-Octets}' as numeric) end, \
'%{Service-Type}','%{Called-Station-Id}','%{Calling-Station-Id}','%{Ascend-S
ession-Svr-Key}')

I replaced my AddQUery to the above statment

Have a good night list and hugh

Kind Regards
Nathan Franklin
TSN Internet nathan at tsn.cc
MSN: nathanfranko at hotmail.com
'When doing something, the toughest and quite obviously, the most important
step is always the first one. We all suffer from the problem of inertia...
getting started is all it takes.'

----- Original Message -----
From: "Hugh Irvine" <hugh at open.com.au>
To: "Nathan 'Franko' Franklin" <radiator_tsn at tsn.cc>
Cc: <radiator at open.com.au>
Sent: Wednesday, April 02, 2003 3:41 PM
Subject: (RADIATOR) Re: Values Disappearing

>
> Hello Nathan -
>
> I am surprised that you are having a problem with the AddQuery, as it
> is only used when an accounting start is received, and the counters
> should be zero.
>
> Could you please send me a complete copy of the configuration file (no
> secrets) together with a trace 4 debug showing what is happening?
>
> regards
>
> Hugh
>
>
> On Wednesday, Apr 2, 2003, at 09:24 Australia/Melbourne, Nathan
> 'Franko' Franklin wrote:
>
> > Hello List And Hugh
> >
> > I have a problem, I am trying to set up our session database without
> > much
> > success.
> > I am using SessionDatabase SQL.
> >
> > The current field in my Online table for input and output octects is
> > BIGINT,
> > (SQL SERVER)
> > This is working fine except for the fact when we get a user that is
> > download
> > over 2 gig in any one session.
> > After this we get an Arithimic overflow error.
> >
> > This is my query below:
> > AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, \
> > ACCTSESSIONID, FRAMEDADDRESS, AccInputOctets, AccOutputOctets, \
> > SERVICETYPE,NASPortDNIS,callerid,AscendKey) values ('%n', '%N',
> > %{NAS-Port},
> > '%{Acct-Session-Id}', \
> > '%{Framed-IP-Address}', '%{Acct-Input-Octets}',
> > '%{Acct-Output-Octets}', \
> > '%{Service-Type}','%{Called-Station-Id}','%{Calling-Station-
> > Id}','%{Ascend-S
> > ession-Svr-Key}')
> >
> > notice how %{Acct-Input-Octets}and %{Acct-Output-Octets} are enclosed
> > by '',
> > This is a problem,
> > I tried converting our fields in the database to numeric so we could
> > handle
> > the data coming through, but then the sql statement errored as i
> > thought it
> > would saying error converting varchar to numeric. So i took away the ''
> > around the two values, and after I did that the values disappeared.
> > Without
> > the quotes %{Acct-Input-Octets} and %{Acct-Output-Octets} return empty
> > values.
> >
> > Even if I try to cast the value as numeric it still comes back as
> > empty.
> >
> > IN casting numeric I mean this
> >
> > AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, \
> > ACCTSESSIONID, FRAMEDADDRESS, AccInputOctets, AccOutputOctets, \
> > SERVICETYPE,NASPortDNIS,callerid,AscendKey) values ('%n', '%N',
> > %{NAS-Port},
> > '%{Acct-Session-Id}', \
> > '%{Framed-IP-Address}', cast('%{Acct-Input-Octets}' as numeric),
> > cast('%{Acct-Output-Octets}' as numeric), \
> > '%{Service-Type}','%{Called-Station-Id}','%{Calling-Station-
> > Id}','%{Ascend-S
> > ession-Svr-Key}')
> >
> > this Still causes the values to return empty.
> >
> > The values are returning empty becuase I print out the sql string
> > before
> > execution....
> >
> > Any help in this matter would be very much appreciated.
> >
> > Thanks everyone
> >
> >
> > Kind Regards
> >
> > Nathan Franklin
> > TSN Internet
> > nathan at tsn.cc
> > MSN: nathanfranko at hotmail.com
> >
> > 'I do not regret the things I have done, but those I have left undone.'
> >
> >
>
> NB: have you included a copy of your configuration file (no secrets),
> together with a trace 4 debug showing what is happening?
>
> --
> 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.
>

===
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.