<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Hi All,<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>I’m wondering if you can advise on the best way to deal with the below.<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>On all NAS’s apart from Mikrotik routers, when the radius accounting packets are sent, the correct figures are in the Acct-Input-Octets and Acct-Output-Octets respectively.<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>When using Mikrotik, they switch those around (i.e. the download octets are upload and vice versa), and because I use an SQL DB, it means that the values are also stored the wrong way round, as the queries in dialup.conf are fixed to insert in to the same columns.<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>So, I’m looking to detect a Mikrotik accounting request and then change the columns round so that the DB is always correct.<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>I’ve tried doing this inside the dialup.conf but not sure it accepts conditional statements inside there. (I use other conditional statements in sites-enabled/default etc, so have an understanding on that in general).<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>---------------------------------<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>if (Mikrotik-Host-IP) {<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>accounting_start_query = " \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> INSERT INTO ${acct_table1} \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> (acctsessionid, acctuniqueid, username, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> realm, nasipaddress, nasportid, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> nasporttype, acctstarttime, acctstoptime, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> acctsessiontime, acctauthentic, connectinfo_start, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> connectinfo_stop, acctoutputoctets, acctinputoctets, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> calledstationid, callingstationid, acctterminatecause, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> servicetype, framedprotocol, framedipaddress, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> VALUES \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{SQL-User-Name}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{NAS-Port-Type}', '%S', NULL, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '0', '%{Acct-Authentic}', '%{Connect-Info}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '', '0', '0', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> REPLACE(SUBSTRING('%{Called-Station-Id}',1,17),':','-'), \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> REPLACE(SUBSTRING('%{Calling-Station-Id}',1,17),':','-'), '', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>} else {<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>accounting_start_query = " \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> INSERT INTO ${acct_table1} \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> (acctsessionid, acctuniqueid, username, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> realm, nasipaddress, nasportid, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> nasporttype, acctstarttime, acctstoptime, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> acctsessiontime, acctauthentic, connectinfo_start, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> connectinfo_stop, acctinputoctets, acctoutputoctets, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> calledstationid, callingstationid, acctterminatecause, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> servicetype, framedprotocol, framedipaddress, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> VALUES \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{SQL-User-Name}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{NAS-Port-Type}', '%S', NULL, \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '0', '%{Acct-Authentic}', '%{Connect-Info}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '', '0', '0', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> REPLACE(SUBSTRING('%{Called-Station-Id}',1,17),':','-'), \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> REPLACE(SUBSTRING('%{Calling-Station-Id}',1,17),':','-'), '', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>}<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>-------------------------------------<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>This doesn’t work; it throws a config error strangely, which goes away if I comment out the if statement.<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>[…]<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Wed Oct 22 07:59:28 2014 : Debug: including configuration file /usr/local/etc/raddb/sql.conf<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Wed Oct 22 07:59:28 2014 : Debug: including configuration file /usr/local/etc/raddb/sql/mysql/dialup.conf<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Wed Oct 22 07:59:28 2014 : Debug: WARNING: No such configuration item acct_table1<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Wed Oct 22 07:59:28 2014 : Error: /usr/local/etc/raddb/sql/mysql/dialup.conf[165]: Reference " UPDATE ${acct_table1} SET framedipaddress = '%{Framed-IP-Address}', acctsessiontime = '%{Acct-Session-Time}', acctoutputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctinputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}'" not found<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Wed Oct 22 07:59:28 2014 : Error: Errors reading /usr/local/etc/raddb/radiusd.conf<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>If I’m doing it wrong can you point me in the direction of how else to change the incoming request so that I can switch the download/upload values for Mikrotik?<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Thanks,<o:p></o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p><p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>James<o:p></o:p></span></font></p></div></body></html>