<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>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.<br><br>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.<br><br><br>Benoit.<br><br><div><hr id="stopSpelling">Date: Sat, 12 Apr 2014 10:08:11 +0200<br>Subject: Re: nas id in radacct table<br>From: ryandekock1988@gmail.com<br>To: freeradius-users@lists.freeradius.org<br><br><div dir="ltr"><div><div><div>Here is a query that I'm using right now and is working<br><br> accounting_start_query = " \<br> INSERT INTO ${acct_table1} \<br> (acctsessionid, acctuniqueid, username, \<br>
realm, nasipaddress, nasportid, \<br> nasporttype, acctstarttime, acctstoptime, \<br> acctsessiontime, acctauthentic, connectinfo_start, \<br> connectinfo_stop, acctinputoctets, acctoutputoctets, \<br>
calledstationid, callingstationid, acctterminatecause, \<br> servicetype, framedprotocol, framedipaddress, \<br> NASIdentifier, \<br> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \<br>
VALUES \<br> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<br> '%{SQL-User-Name}', \<br> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<br>
'%{NAS-Port-Type}', '%S', NULL, \<br> '0', '%{Acct-Authentic}', '%{Connect-Info}', \<br> '', '0', '0', \<br> '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \<br>
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<br> '%{NAS-Identifier}', \<br> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"<br>
<br><br></div>Test it and see if it works for you, if it doesn't the problem is elsewhere.<br></div>When you run "radiusd -X" do you see what you expect in the DB as the NAS-Identifier?<br><br></div>Have you created the nas-id table in the db, what did you make it varchar,int?<br>
</div><div class="ecxgmail_extra"><br><br><div class="ecxgmail_quote">On 12 April 2014 09:58, Benoit <span dir="ltr"><<a href="mailto:goony@hotmail.fr" target="_blank">goony@hotmail.fr</a>></span> wrote:<br><blockquote class="ecxgmail_quote" style="border-left:1px #ccc solid;padding-left:1ex;">
<div><div dir="ltr">Thank you Ryan for the reply.<br><br>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.<br><br>I tried first to do this anywhere we INSERT rows in the radacct table, which are those queries:<br>
<br>- accounting_update_query_alt<br>- accounting_start_query<br>- accounting_stop_query_alt<br><br>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:<br>
<br>- accounting_onoff_query<br>- accounting_update_query<br>- accounting_start_query_alt<br>- accounting_stop_query<br><br><br>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.<br>
<br><br><br><br><div><hr>Date: Sat, 12 Apr 2014 08:34:24 +0200<div><br>Subject: Re: nas id in radacct table<br></div>From: <a href="mailto:ryandekock1988@gmail.com" target="_blank">ryandekock1988@gmail.com</a><br>
To: <a href="mailto:freeradius-users@lists.freeradius.org" target="_blank">freeradius-users@lists.freeradius.org</a><div><div class="h5"><br><br><div dir="ltr">The attribute is not NAS-Id its NAS-Identifier so try<br><br>
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<br> '%{SQL-User-Name}', \<br> '%{Realm}', <b>%{NAS-Identifier}</b>, '%{NAS-IP-Address}', '%{NAS-Port}', \<br>
'%{NAS-Port-Type}', '%S', NULL, \<br> '0', '%{Acct-Authentic}', '%{Connect-Info}', \<br> '', '0', '0', \<br> '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \<br>
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<br> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')<div dir="ltr">
"</div><br></div><div><br><br><div>On 12 April 2014 05:20, Benoit <span dir="ltr"><<a href="mailto:goony@hotmail.fr" target="_blank">goony@hotmail.fr</a>></span> wrote:<br>
<blockquote style="border-left:1px #ccc solid;padding-left:1ex;">
<div><div dir="ltr">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 = <a href="http://nas.id" target="_blank">nas.id</a>). <br>
<br>In the dialup.conf file, I see the queries that insert lines in the radacct table. So I could add<br><br>INSERT INTO ${acct_table1} \<br> (acctsessionid, acctuniqueid, username, \<br> realm, <b> nasid</b>, nasipaddress, nasportid, \<br>
nasporttype, acctstarttime, acctstoptime, \<br> acctsessiontime, acctauthentic, connectinfo_start, \<br> connectinfo_stop, acctinputoctets, acctoutputoctets, \<br> calledstationid, callingstationid, acctterminatecause, \<br>
servicetype, framedprotocol, framedipaddress, \<br> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \<br>VALUES \<br> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<br>
'%{SQL-User-Name}', \<br> '%{Realm}', <b>%{NAS-Id}</b>, '%{NAS-IP-Address}', '%{NAS-Port}', \<br> '%{NAS-Port-Type}', '%S', NULL, \<br>
'0', '%{Acct-Authentic}', '%{Connect-Info}', \<br>
'', '0', '0', \<br> '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \<br> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<br>
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"<br><br><br>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.<br>
<br>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.<br>
<br>Any help would be very appreciated.<br><br>Benoit.<br><br><br><br><div>Subject: Re: nas id in radacct table<br>From: <a href="mailto:a.cudbardb@freeradius.org" target="_blank">a.cudbardb@freeradius.org</a><br>Date: Wed, 9 Apr 2014 07:42:51 +0100<br>
To: <a href="mailto:freeradius-users@lists.freeradius.org" target="_blank">freeradius-users@lists.freeradius.org</a><div><div><br><br><pre> <br>> Am I missing a reference that can identify the NAS used for a connection, and other than the nas IP?<br>
<br>rfc2865 speaketh of a legendary second identifier. The NAS-Identifier, which may be used to identify<br>a NAS, even when it hopeth between ipeth.<br> <br>> 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.<br>
<br>The queries are editable... look in the appropriate dialup.conf or queries.conf (v3.0.x) for your SQL dialect.<br> <br>Arran Cudbard-Bell <<a href="mailto:a.cudbardb@freeradius.org" target="_blank">a.cudbardb@freeradius.org</a>><br>
FreeRADIUS Development Team<br> <br>FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2<br> <br></pre><br></div></div>-
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a></div> </div></div>
<br>-<br>
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a><br></blockquote></div><br></div>
<br>-
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a></div></div></div> </div></div>
<br>-<br>
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/list/users.html</a><br></blockquote></div><br></div>
<br>-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html</div> </div></body>
</html>