SQL session start

Alan DeKok aland at deployingradius.com
Fri Sep 13 15:02:59 CEST 2019


> On Sep 12, 2019, at 5:25 PM, Marcin Marszałkowski <m.marszal at wp.pl> wrote:
> 
> As per config files it’s enough to enable sql_session_start in default server and toggle set of queries - start section in queries.conf (freeradius 3.0.20):	
> #  When using "sql_session_start", you should comment out
> #  the previous query, and enable this one.  
> Mainly changing sql query INSERT to UPDATE in queries.conf.
> Is this all or I’m missing something? Description in policy.d/accounting doesn’t say anything more.

  It should work.

> The reason I’m asking is accounting became useless as lots of data is missing (like acctupdatetime, acctstoptime and acctinterval and so on) when this feature is enabled…
> 
> Accounting request debug with sql_session_start on:
> 
> (8) sql: EXPAND UPDATE radacct SET acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), acctupdatetime  = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctinterval    = %{integer:Event-Timestamp} - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '%{Framed-IP-Address}', framedipv6address = '%{Framed-IPv6-Address}', framedipv6prefix = '%{Framed-IPv6-Prefix}', framedinterfaceid = '%{Framed-Interface-Id}', delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'
> (8) sql:    --> UPDATE radacct SET acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), acctupdatetime  = FROM_UNIXTIME(1568321148), acctinterval    = 1568321148 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '172.16.4.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', acctsessiontime = 3985, acctinputoctets = '0' << 32 | '86555066', acctoutputoctets = '0' << 32 | '1008668910' WHERE AcctUniqueId = '6d2b000d2e24857d10699c46ad662bff'
> (8) sql: Executing query: UPDATE radacct SET acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), acctupdatetime  = FROM_UNIXTIME(1568321148), acctinterval    = 1568321148 - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '172.16.4.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', acctsessiontime = 3985, acctinputoctets = '0' << 32 | '86555066', acctoutputoctets = '0' << 32 | '1008668910' WHERE AcctUniqueId = '6d2b000d2e24857d10699c46ad662bff'

  So what's in SQL?  Does the AcctUniqueId match?
> 
> (8) sql: EXPAND UPDATE radacct SET AcctSessionId = '%{Acct-Session-Id}', AcctUniqueId = '%{Acct-Unique-Session-Id}', AcctAuthentic = '%{Acct-Authentic}', ConnectInfo_start = '%{Connect-Info}', ServiceType = '%{Service-Type}', FramedProtocol = '%{Framed-Protocol}', framedipaddress = '%{Framed-IP-Address}', framedipv6address = '%{Framed-IPv6-Address}', framedipv6prefix = '%{Framed-IPv6-Prefix}', framedinterfaceid = '%{Framed-Interface-Id}', delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', AcctUpdateTime = FROM_UNIXTIME(%{integer:Event-Timestamp}), AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', AcctOutputOctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' AND NASPortType = '%{NAS-Port-Type}' AND AcctStopTime IS NULL
> (8) sql:    --> UPDATE radacct SET AcctSessionId = '4989DEE1E63DA03B', AcctUniqueId = '6d2b000d2e24857d10699c46ad662bff', AcctAuthentic = 'RADIUS', ConnectInfo_start = 'CONNECT 0Mbps 802.11b', ServiceType = 'Framed-User', FramedProtocol = '', framedipaddress = '172.16.4.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', AcctUpdateTime = FROM_UNIXTIME(1568321148), AcctSessionTime = 3985, AcctInputOctets = '0' << 32 | '86555066', AcctOutputOctets = '0' << 32 | '1008668910' WHERE UserName = 'Mark' AND NASIPAddress = '172.16.0.4' AND NASPortId = '' AND NASPortType = 'Wireless-802.11' AND AcctStopTime IS NULL
> (8) sql: Executing query: UPDATE radacct SET AcctSessionId = '4989DEE1E63DA03B', AcctUniqueId = '6d2b000d2e24857d10699c46ad662bff', AcctAuthentic = 'RADIUS', ConnectInfo_start = 'CONNECT 0Mbps 802.11b', ServiceType = 'Framed-User', FramedProtocol = '', framedipaddress = '172.16.4.6', framedipv6address = '', framedipv6prefix = '', framedinterfaceid = '', delegatedipv6prefix = '', AcctUpdateTime = FROM_UNIXTIME(1568321148), AcctSessionTime = 3985, AcctInputOctets = '0' << 32 | '86555066', AcctOutputOctets = '0' << 32 | '1008668910' WHERE UserName = 'Mark' AND NASIPAddress = '172.16.0.4' AND NASPortId = '' AND NASPortType = 'Wireless-802.11' AND AcctStopTime IS NULL

  Again, what's in SQL?  If the WHERE clause doesn't find any matching rows, then it won't do the UPDATE.

  What was inserted into SQL when the Access-Accept was sent?

  You're giving us 1/3 of the information, and asking us to solve it.  That's bad.  You're likely doing this because *you're* not looking at the rest of the information.

  The way to fix this is simple.  READ the debug output to see what is inserted during the "post-auth" phase.  CHECK the SQL database to see what data is there for the user.  COMPARE that data to what happens when an accounting packet is received.

  We don't know *any* of that.  So it's impossible for us to help you, just by looking at an accounting packet.

  Alan DeKok.




More information about the Freeradius-Users mailing list