We use a Windows Server 2003 IAS server to authenticate users to our wireless network. We also have a Microsoft DHCP server that gives out dynamic IP addresses to clients on our wireless VLAN. We use tools such as Argus to watch for "interesting" occurances on the network. When these occur, we like to be able to trace them to a user. To do this, we log events from our DHCP and IAS server into a central database to match users to IP addresses at certain times.
A mysql server collects all the logging information. We have one table for RADIUS events and one for DHCP events. Here's what the table structure looks like.
Each of the IAS servers has SQL logging enabled. Each server runs an instance of MSDE to collect the SQL logging from the RADIUS server. A good explanation of RADIUS logging can be found here. Interestingly, that page says that MSDE can't be used for this purpose, but it appears to work fine. Go figure... The report_event stored procedure in MSDE looks like this. report_event extracts the info we're interested in and sends its to a linked server called tieke_mysql, which is an OLE DB provider for an ODBC DSN. MyODBC is set up on each server to provide an ODBC connection to the mysql server.
Currently we only log logins. At some point we may use this system to do dial-in and VPN connections, in which case we'll want accounting, but for now it's fine (and it'd be a fair bit of work to make it deal with VPN/dialup packets). Due to the data contained in each type of RADIUS packet, we do a two step process. The initial access-request from the client includes the MAC address (which we use to tie the user to an IP in DHCP). The subsequent access-accept or access-reject specifies whether the request was allowed, but doesn't include the MAC address. Consequentially, we log every access-request, and then delete those that don't result in an access-accept (why don't we keep them? we have lots of incorrectly configured laptops spewing many access-requests at us per second. We ended up logging tens of thousands of access-requests per hour).
We have a callout DLL tied to our DHCP servers which logs DHCP requests to the same ODBC DSN that the IAS server uses. Simple, really. The source (in VC++ 2k3 format) for the callout DLL is here. It could be a lot more robust and efficient, but it works fine for our volumes.
select upi from radius, dhcp where ip_address = '127.0.0.1' and radius.mac_address = dhcp.mac_address and radius.timestamp<='2004-05-04 12:00:00' and dhcp.timestamp>=radius.timestamp order by radius.timestamp desc limit 1
This small bit of SQL basically says "Find the last entry in the radius table before the specified time, and then find the first event in the dhcp table after the radius event happened. Now tie the MAC address in the DHCP table with that in the RADIUS table, and tell me who the radius table says the user was".
That was more confusing to write in English than in SQL. I also think the SQL is more accurate.
All of the above provides us with a very quick way of working out who the authenticated user was on a specific IP address at a specific time. If you're doing something like this too, let me know - there's a lot more still to be done in this area.