nas id in radacct table

Benoit goony at hotmail.fr
Tue Apr 15 08:13:59 CEST 2014


Well that's it I was using an Integer because I wanted to get the row id for the nas table, the one auto-incremented by SQL.

But since I changed to varchar, and using your code, I get the other nas identifier, which is a varchar. I can deal with this value actually. Thank you for your help.


Benoit.

Date: Sat, 12 Apr 2014 10:08:11 +0200
Subject: Re: nas id in radacct table
From: ryandekock1988 at gmail.com
To: freeradius-users at lists.freeradius.org

Here is a query that I'm using right now and is working

    accounting_start_query = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,     username, \

             realm,            nasipaddress,     nasportid, \
             nasporttype,      acctstarttime,    acctstoptime, \
             acctsessiontime,  acctauthentic,    connectinfo_start, \
             connectinfo_stop, acctinputoctets,  acctoutputoctets, \

             calledstationid,  callingstationid, acctterminatecause, \
             servicetype,      framedprotocol,   framedipaddress, \
             NASIdentifier, \
             acctstartdelay,   acctstopdelay,    xascendsessionsvrkey) \

          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \

             '%{NAS-Port-Type}', '%S', NULL, \
             '0', '%{Acct-Authentic}', '%{Connect-Info}', \
             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \

             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
             '%{NAS-Identifier}', \
             '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"



Test it and see if it works for you, if it doesn't the problem is elsewhere.
When you run "radiusd -X" do you see what you expect in the DB as the NAS-Identifier?

Have you created the nas-id table in the db, what did you make it varchar,int?



On 12 April 2014 09:58, Benoit <goony at hotmail.fr> wrote:




Thank you Ryan for the reply.

If I use ${NAS-Identifier}  the value inserted in the raddacct row is 0. But the client I am using has the id=3 in the nas table.

I tried first to do this anywhere we INSERT rows in the radacct table, which are those queries:


- accounting_update_query_alt
- accounting_start_query
- accounting_stop_query_alt

And I got 0 inserted all the time. Then I though maybe during the steps of the process we insert the radacct lines before querying the nas table. So I also added my new field even in all the update queries on radacct:


- accounting_onoff_query
- accounting_update_query
- accounting_start_query_alt
- accounting_stop_query


But I still get the same result: the value "0" is inserted. So the variable exists but does not seem to contain my nas_id.





Date: Sat, 12 Apr 2014 08:34:24 +0200
Subject: Re: nas id in radacct table
From: ryandekock1988 at gmail.com

To: freeradius-users at lists.freeradius.org

The attribute is not NAS-Id its NAS-Identifier so try


            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', %{NAS-Identifier}, '%{NAS-IP-Address}', '%{NAS-Port}', \


             '%{NAS-Port-Type}', '%S', NULL, \
             '0', '%{Acct-Authentic}', '%{Connect-Info}', \
             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \


             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
             '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')

"


On 12 April 2014 05:20, Benoit <goony at hotmail.fr> wrote:




Sorry I have to come back with my problem. I tried to figure out some way to get what I want but with no success. What I want is basically to add the row_id of the nas in the radacct table. So OK I added the field nasid in the radacct table (radacct.nasid = nas.id). 



In the dialup.conf file, I see the queries that insert lines in the radacct table. So I could add

INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,     username, \
             realm,            nasid,         nasipaddress,     nasportid, \


             nasporttype,      acctstarttime,    acctstoptime, \
             acctsessiontime,  acctauthentic,    connectinfo_start, \
             connectinfo_stop, acctinputoctets,  acctoutputoctets, \
             calledstationid,  callingstationid, acctterminatecause, \


             servicetype,      framedprotocol,   framedipaddress, \
             acctstartdelay,   acctstopdelay,    xascendsessionsvrkey) \
VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \


             '%{SQL-User-Name}', \
             '%{Realm}', %{NAS-Id}, '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', '%S', NULL, \

             '0', '%{Acct-Authentic}', '%{Connect-Info}', \

             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \


             '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"


But of course the %{NAS-Id}  variable does not exist. Is there any variable where this information is stored? Seems like all the NAS-related vars are coming from the client and not from a local query on the database.



I have little understanding yet about the freeradius system but hope to get it. This file for me is just a list of SQL queries, but I don't even get how and where they are ever executed... Each query name is used in this file only, which makes me clueless on how to retrieve anything from those.



Any help would be very appreciated.

Benoit.



Subject: Re: nas id in radacct table
From: a.cudbardb at freeradius.org
Date: Wed, 9 Apr 2014 07:42:51 +0100


To: freeradius-users at lists.freeradius.org

 
> Am I missing a reference that can identify the NAS used for a connection, and other than the nas IP?


 
rfc2865 speaketh of a legendary second identifier. The NAS-Identifier, which may be used to identify
a NAS, even when it hopeth between ipeth.
 
> Do you guys know a workaround for me? Even modifying the code a little bit so that I can save the NAS (database) id in the radacct table would not bother me but I would appreciate some help to locate this part of the code if it comes to this.


 
The queries are editable... look in the appropriate dialup.conf or queries.conf (v3.0.x) for your SQL dialect.
 
Arran Cudbard-Bell <a.cudbardb at freeradius.org>


FreeRADIUS Development Team
 
FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
 

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

-

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



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

-

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



-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20140415/34e17dfc/attachment-0001.html>


More information about the Freeradius-Users mailing list