(RADIATOR) AddQuery in Session Databse SQL

[Mariano Absatz]

El 29 May 2001, a las 13:19, julio.prada at bt.es escribió:

> Hello,
> 
> as the manual documentation says I put default addQuery:
> 
> 
> insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, \
> ACCTSESSIONID, TIME_STAMP, FRAMEDADDRESS, PORTTYPE, \
> SERVICETYPE) values ('%n', '%N', %{NAS-Port}, '%{Acct-Session-Id}', \
> %{Timestamp}, '%{Framed-IP-Address}', '%{Port-Type}', '%{Service-Type}')
> 
> A mySQl database is used, and it returns the error in the statement.
> 
> Is this sentence correct?
It's quite possible that some of the values you are trying to insert don't 
exist in the request... maybe from some specific NAS.

You should do a Trace 4 and check the Access-Request packet and it's 
attributes as well as how the "INSERT" sql statement is generated.

I bet you might be using some kind of tunnel terminator that don't use the 
NAS-Port attribute so the actual insert statement might look something like 
this:

> insert into RADONLINE (USERNAME,NASIDENTIFIER, NASPORT, ACCTSESSIONID,
> TIME_STAMP,FRAMEDIPADDRESS, NASPORTTYPE, SERVICETYPE) values ('user at realm',
> '1.2.3.4',, 'b500ff91', 991145023,'4.3.2.1', '', 'Annex-Framed-Tunnel')

where 1.2.3.4 is the NAS IP identifier, 4.3.2.1 is the Framed-IP assignted to 
the user and b500ff91 is the Session-Id.

You should note the two commas after the '1.2.3.4' and the 'b500ff91'... that 
is the place to put the NAS-Port. Since it's a numeric value, you can add a 
number 0 without quotes just in front of the value to insert. Since it's a 
numeric value, it's value will be identical to the original, or 0 if it 
doesn't exist.

But... if that attribute is allways 0, you will definitively have a problem 
with the default delete query, that uses the NAS-Port as part of the key to 
delete... since every row has a NAS-Port of 0 you will be actually deleting 
all the connections from that NAS :-(

Luckily enough, I did pass this very same problem some months ago and you can 
have the portion of my config file for free :-) see the bottom of the message.

For this to work, your NAS should send the Acct-Session-Id in the Access-
Request packet. Otherwise, you should need the the Framed-IP-Address in the 
Access-Request packet.

The Nortel Shasta I use with this config does send the Acct-Session-Id with 
the Access-Request, but not the Framed-IP-Address.

One more piece of advice... (this is a brand new one :-)... IF you are using 
a NasType statement in the client section of the NAS that will use this 
SessionDatabase... your DeleteQuery will fail miserably when trying to delete 
a stale record in RADONLINE that it detected through the isOnline method of 
that NasType.

The DeleteQuery will try to erase a record not for the Session-Id (or Framed-
IP-Address) just found in the CountQuery, but the one from the CURRENT 
request packet.

I patched SessSQL.pm so it's able to do this and sent it to the list last 
week. If you can't find it, I can send you an updated SessSQL.pm (for version 
2.17.1 or 2.18.1) or the diff so you figure it out.

BTW, IF you were using a Nortel Shasta AND you are using a NasType... how do 
you do it? Do you have a MIB for the Shasta that allows me to query the box 
for a Session-Id and find out if it's still valid or not? (that would be my 
price for the config & patches ;-)

If you have the MIB AND the SNMP NasType doesn't work, I could fiddle with it 
and patch isOnline so it does... that would also be included for the same 
price... with a little luck, Mike would put it in the next release ;-)

I've come to an age where I'm too old to program but not so as not to patch 
:-)

I've been using Ping for some time but if the final user has a personal 
firewall that doesn't reply ICMP, I'm done.



> 
> regards,
> jules
> 

##################################################################
#                     ON LINE USERS SECTION                      #
##################################################################

#Manejo de usuarios actuales y sesiones simultaneas
<SessionDatabase SQL>
    # This database spec usually should be exactly the same
    # as in <AuthBy RADMIN> above
    DBSource    dbi:mysql:radius:localhost
    #DBUsername  xxxxxxx
    DBAuth      yyyyy

    AddQuery        insert into RADONLINE (USERNAME,\
                         NASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP,\
                         FRAMEDIPADDRESS, NASPORTTYPE, SERVICETYPE, NASCLASS) 
\
                         values ('%n', '%N',\
                         0%{NAS-Port}, '%{Acct-Session-Id}', %{Timestamp},\
                         '%{Framed-IP-Address}', '%{NAS-Port-Type}', \
                         '%{Service-Type}', '%{NASClass}')
   #Como el Shasta no envia Nas-Port, se borran sesiones solo en base
   #al session ID
   DeleteQuery delete from RADONLINE \
               where NASIDENTIFIER='%N' and ACCTSESSIONID='%{Acct-Session-Id}'
    #Agregado por Baby 17/11/2000 porque permite simultaneous use con ip fija
    CountQuery      select NASIDENTIFIER, NASPORT, ACCTSESSIONID, \
                FRAMEDIPADDRESS from RADONLINE where USERNAME = '%n'

</SessionDatabase>


Mariano Absatz
El Baby
----------------------------------------------------------
Suicidal twin kills sister by mistake! 

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