Inner identity in accounting logs

[Jonathan Gazeley]

Thanks for your reply.

I've just got round to looking at your SQL statement - I take it you've 
had to edit your queries in dialup.conf to get it to insert some extra 
fields? If you wouldn't mind, could you post your changes to the 
query/queries?

Thanks a lot,
Jonathan


Alexander Clouter wrote:
> 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
>