Inner identity in accounting logs
Alexander Clouter
alex at digriz.org.uk
Fri Jan 16 11:00:11 CET 2009
Hi,
* Jonathan Gazeley <jonathan.gazeley at bristol.ac.uk> [Thu, 15 Jan 2009 15:31:19 +0000]:
>
> I have an existing FreeRadius setup for an 802.1x wireless network.
> Currently the accounting is done to a MySQL database. Presently, the
> username appearing in these records is the outer identity. I want to use
> the authenticated inner identity, such that I can rely on my accounting
> data e.g. for billing.
>
> I know that accounting packets are not sent through the inner-tunnel and
> so I can't simply move my accounting to the inner-tunnel. I suppose what
> I'm after is a way to tie a session at authentication to the associated
> session in accounting. Is there any sort of ID that is used both in
> inner-tunnel authentication and accounting, that can be relied upon?
> Otherwise am I looking at using something like the MAC address of the
> client?
>
> The other option might be using vendor supplied attributes from the
> Cisco controllers. Has anyone done this before?
>
As you will at some stage probably was to do this to a wired network
too, I would ignore anything the horrible WLC is willing to spit out,
keep to standard and common attributes.
I use a honking great SQL statement to associate *live* users accounting
data to an authentication:
----
SELECT user_name, tunnel_private_group_id, nas_ip_address, nas_port_type, nas_port_id
FROM postauth
WHERE (
packet_type = 'Access-Accept'
AND calling_station_id = ?
AND (
(
calling_station_id, called_station_id, nas_ip_address,
nas_port, nas_port_id, nas_port_type, client_ip_address
) = (
SELECT calling_station_id, called_station_id, nas_ip_address,
nas_port, nas_port_id, nas_port_type,
client_ip_address
FROM acct
WHERE acct_status_type = 'Start'
AND calling_station_id = ?
ORDER BY start_timestamp DESC
LIMIT 1 )
)
)
ORDER BY timestamp DESC
LIMIT 1
----
Now the trick is to fine a clued DBA person (and not a packet pusher
like me) to sanitise that query and also use it to query historical
data. I keep meaning to get around to it...one day. I have been told a
SQL view would be a good start. :)
Cheers
--
Alexander Clouter
.sigmonster says: QOTD:
"Like this rose, our love will wilt and die."
More information about the Freeradius-Users
mailing list