[unisog] Tracking usage of dynamic IP

[Russell Fulton]

Hi Folks

We have largely static IP address allocations with some meaningful (for
varying values of meaningful) information in PTR records indicating who
is using it or in some cases where the machine physically is.

With wireless networks and a small but increasing increasing mobile
population (people with laptops who genuinely need to plug in in a
number of different places) we are coming to grips with tracking these
dynamic addresses.

A couple of years ago we set up a mysql data base with tables for the
DHCP logs and Radius logs.  This was less than ideal since, in most
cases we only had a login time and no record when the machine
disconnected from the network.  To find out who was using an IP at a
particular time we did a query on the join (on mac address) of the two
tables and selected the first record for that IP after the given time. 
This works OK for small tables but once you get several million entries
the finding the 'first after the time' gets really painful.  The pain
can be reduced significantly by adding a lower bound on the search time
(say 24 hours) but it still is not ideal.  I can think of other ways of
tackling this problems but I won't prejudice the discussion at this point.

Anyway we are revisiting the whole issue and I really don't want to
reinvent the wheel here so I am asking how others deal with this.  What
data are you collecting from DHCP and authentication systems?  How are
you storing it and how are you doing queries on it.

Has anyone bent standard software (e.g security consoles) to report user
names instead of domain names for dynamic IPs  .

Russell.