[unisog] Tracking usage of dynamic IP

[Sealey, Adam L.]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Russell,

We run a somewhat similar environment, but our campus is moving towards 100% DHCP controlled IP addresses.  We have a home-grown system for locking down access based on MAC address, which gives us the hooks into who is using the device (standard MAC-spoofing arguments aside, it has worked pretty well for us).

We ran into a similar situation where the DHCP logs weren't granular enough for us.  I wrote another homegrown MySQL database application that scrapes the arp tables from all routers and firewalls once every 30 minutes (our accepted granularity).  This gives us the MAC-IP combo at that time period, and we can then use the MAC address into either our DHCP system, or one of the MAC-reg based applications we use to front non-faculty/staff networks.

The database has 2 tables, one that is current information, and one that is historical.  The current information holds an entry for every IP in our class B (quite large, and contains more than we use...but it is guaranteed not to grow).  The historical table contains the deltas from the first table (Every time the MAC Address changes, that change is recorded into the history).  This gives us a range on who is using IP's at specific intervals.

Let me know if you want more details, or have questions.
Adam

- -----Original Message-----
From: unisog-bounces at lists.dshield.org [mailto:unisog-bounces at lists.dshield.org] On Behalf Of Russell Fulton
Sent: Sunday, November 11, 2007 4:47 PM
To: unisog at lists.sans.org
Subject: [unisog] Tracking usage of dynamic IP

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.

_______________________________________________
unisog mailing list
unisog at lists.dshield.org
https://lists.sans.org/mailman/listinfo/unisog

-----BEGIN PGP SIGNATURE-----
Version: 9.6.1 (Build 1012)

wsBVAwUBRzhm5EK/1jqypG4fAQjFtwf7BZoD/QVg4soej+hOeqf1CbotrIi3l/Xg
4B9L8NADuE2gVEj23BzoHlQvSRuzSz+v90MfTD/Yg1rcfgRcHUQ/3bV1Ex3h9UZY
kAbWuoBHkYDoxVhSi2VmHxCdiGCh1R/QAbxH91S8f9Ez9qkbic0a8FoI9MFpBCEm
YVLVMy87gAhKbGgnMojw3nMsysDOh4jQYNlwcR/USxjb/cNsVeEcIxxj4NZnoNEF
zjA6MI6Z2tPtzXVZKoUfP0/KVeINOc1XNtDfL7P55uhl+dsSbep2pKpo991vhbjU
2AfGmHecajSkUzrg02MJYoq6kB9XV5DlUCo34J7paoCCaeTDWygbMQ==
=4T82
-----END PGP SIGNATURE-----