Menu
▾
▴
Re: [Netdisco] using command line
|
From: Olivier R. <oli...@ad...> - 2005-02-15 10:48:14
|
Many thanks for this, this seems to correspond to our needs. We'll post here if we get something from this. Oliver Max Baker wrote: > I think the data you guys want is already in the database... for example this is > the answer to the question, "What switch ports have we seen this MAC address > at?" > > netdisco=> select * from node where mac='00:03:47:be:ef:ee' order by time_last desc; > mac | switch | port | active | time_first | time_last | oui > -------------------+-----------------+------+--------+-------------------------------+------------------------+---------- > 00:03:47:be:ef:ee | 192.168.2.208 | C3 | t | 2004-03-23 08:01:36.30201-08 | 2004-07-22 14:04:22-07 | 00:03:47 > 00:03:47:be:ef:ee | 192.168.2.209 | C5 | f | 2003-05-19 14:07:12.350052-07 | 2003-11-06 11:04:38-08 | 00:03:47 > 00:03:47:be:ef:ee | 192.168.2.209 | C6 | f | 2003-09-24 14:04:37.593939-07 | 2003-09-24 14:04:37-07 | 00:03:47 > 00:03:47:be:ef:ee | 192.168.2.209 | A5 | f | 2003-05-05 10:06:02.657231-07 | 2003-05-15 10:04:37-07 | 00:03:47 > 00:03:47:be:ef:ee | 192.168.2.208 | A8 | f | 2003-03-17 14:06:06.738676-08 | 2003-03-17 14:06:06-08 | 00:03:47 > 00:03:47:be:ef:ee | 192.168.133.242 | D4 | f | 2003-01-08 11:02:29.576105-08 | 2003-01-28 14:03:04-08 | 00:03:47 > > If you want to find by IP address it's a little more tricky because if people > are on DHCP they tend to have a variety of IPs. You would have to use the > node_ip table and try to find the MAC address of the offender. > > So this query answers "Show me the MAC addresses that used a certain IP on a > certain date": > > netdisco=> select * from node_ip where ip='192.168.133.163' and ((time_first,time_last) OVERLAPS (DATE '2003-04-20', DATE '2003-04-20')); > > mac | ip | active | time_first | time_last > -------------------+-----------------+--------+-------------------------------+------------------------ > 00:03:47:be:ef:ee | 192.168.133.163 | f | 2003-04-17 12:02:48.599335-07 | 2003-05-01 12:07:11-07 > > There is no guarantee that you'll get only one MAC address for that day, so > you'll just have to pick the most reasonable looking one? Perhaps the one with > the smallest interval? After that plug the resulting MAC address into the first > query above. > > >>FTR, a history is something UC Berkeley'd like to see. Is there some feature >>of postgres that you could do savepoints on updates or something just to >>create a history? I keep thinking "database people must have a way to do >>this" but I don't know what it is. > > > You could add an archive entry in daily or something, but that would create an > extreme amount of data. That data might be harvestable from the daily data/arp > logs... hmm. > > -m > > On Tue, Feb 08, 2005 at 09:01:35AM -0800, Mike Hunter wrote: > >>On Feb 08, "Robert Kerr" wrote: >> >> >>>>b) the same, specifing a date? >>> >>>This might be more difficult, netdisco records a first and last seen >>>time, but doesn't seem to record history between the two. You might be >>>able to do it by working through the archived data. >> >>FTR, a history is something UC Berkeley'd like to see. Is there some feature >>of postgres that you could do savepoints on updates or something just to >>create a history? I keep thinking "database people must have a way to do >>this" but I don't know what it is. >> >>Mike > > > > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click > _______________________________________________ > Netdisco mailing list > net...@li... > https://lists.sourceforge.net/lists/listinfo/netdisco-users > |
