Inner identity in accounting logs

[Alexander Clouter]

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."