Re: Inner identity in accounting logs

16 Jan 2009

      Hi,

* Jonathan Gazeley <jonathan.gazeley@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."