[Netdisco] Help with SQL query to Netdisco DB

Discussion:

Kevin Ratcliff

2009-12-16 04:45:06 UTC

Greetings,

I would like to be able to do a SELECT query that will be given a
known MAC address and return the IP address, subnet, and subnet mask
from the postgresql database. All of this information is in the
Netdisco web interface so I presume it can be extracted via a SQL
query, but I don't know enough about the relationships between tables
to do this myself.

I don't need instructions for actually connecting to postgresql, as I
can already do that, I just need to know what SELECT query to run.
Could anyone help with this?

The reason I would like this information is so that I can run a
wake-on-lan script to wake up a group of computers (of which I know
the MAC addresses, but nothing else). They're on different subnets,
and if I understand WOL correctly I need to know the subnet
information to send the "magic packet". I could be mistaken though.

Maybe Netdisco is the wrong tool to use in this case to get the
information, in which case I'm open to suggestions.

Thanks for any assistance!

Kevin

PS - a few months ago I set up the "stolen device tracking" feature in
Netdisco with the MAC address of a computer that was missing from
inventory, and then promptly forgot about it. Today I was surprised to
get an email alert from Netdisco telling me the computer had been
found, along with it's device switch port location! Way cool! It
wasn't stolen, just temporarily misplaced. I don't think this new
feature got very widely publicized on the mailing lists, so if anyone
hasn't checked it out yet you may wish to look into it.

j***@utwente.nl

2009-12-16 10:18:08 UTC

Permalink

Hi Kevin,

If you want to be sure to get only the most recent result, try the
following:

select
host(ip),
network(net),
netmask(net)
from
node_ip,
subnets
where
ip <<=net
and time_last = (select max(time_last) from node_ip where mac='<insert
mac here>')
and mac = '<insert mac here>';

Regards,

Jeroen van Ingen
ICT Service Centre
University of Twente, P.O.Box 217, 7500 AE Enschede, The Netherlands

----Original Message----
From: Kevin Ratcliff [mailto:***@kevinratcliff.com]
Sent: woensdag 16 december 2009 5:45
To: netdisco-***@lists.sourceforge.net
Subject: [Netdisco] Help with SQL query to Netdisco DB

Post by Kevin Ratcliff
Greetings,
I would like to be able to do a SELECT query that will be given a
known MAC address and return the IP address, subnet, and subnet mask
from the postgresql database. All of this information is in the
Netdisco web interface so I presume it can be extracted via a SQL
query, but I don't know enough about the relationships between tables
to do this myself.
I don't need instructions for actually connecting to postgresql, as I
can already do that, I just need to know what SELECT query to run.
Could anyone help with this?
The reason I would like this information is so that I can run a
wake-on-lan script to wake up a group of computers (of which I know
the MAC addresses, but nothing else). They're on different subnets,
and if I understand WOL correctly I need to know the subnet
information to send the "magic packet". I could be mistaken though.
Maybe Netdisco is the wrong tool to use in this case to get the
information, in which case I'm open to suggestions.
Thanks for any assistance!
Kevin
PS - a few months ago I set up the "stolen device tracking" feature in
Netdisco with the MAC address of a computer that was missing from
inventory, and then promptly forgot about it. Today I was surprised to
get an email alert from Netdisco telling me the computer had been
found, along with it's device switch port location! Way cool! It
wasn't stolen, just temporarily misplaced. I don't think this new
feature got very widely publicized on the mailing lists, so if anyone
hasn't checked it out yet you may wish to look into it.

------------------------------------------------------------------------
------

Post by Kevin Ratcliff
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast
and easy Join now and get one step closer to millions of Verizon
customers http://p.sf.net/sfu/verizon-dev2dev
_______________________________________________
Netdisco mailing list
https://lists.sourceforge.net/lists/listinfo/netdisco-users

Oliver Gorwits

2009-12-16 10:24:30 UTC

Permalink

Hi Kevin,

Just some further ideas in addition to Jeroen's reply.

Post by Kevin Ratcliff
I would like to be able to do a SELECT query that will be given a
known MAC address and return the IP address, subnet, and subnet mask
from the postgresql database.

We do a number of similar tasks with our Netdisco installation (for
example a kind of Looking Glass service). The way I implement them
is to set up the query as a VIEW in the Netdisco database. This is
efficient and means multiple applications can share the intelligence.

Post by Kevin Ratcliff
All of this information is in the
Netdisco web interface so I presume it can be extracted via a SQL
query, but I don't know enough about the relationships between tables
to do this myself.

There are no explicit relations (i.e. foreign key constraints)
defined in the Netdisco database, simply that tables share key data
(e.g. device_*).

Post by Kevin Ratcliff
The reason I would like this information is so that I can run a
wake-on-lan script

We recently implemented Wake on LAN here and I believe you cannot
send the magic packets over Layer 3 as they are a kind of Layer 2
broadcast. We use relay stations on each subnet which receieve
unicast instructions from a central server and create the magic
packets for their broadcast domain.

regards,

--
Oliver Gorwits, Network and Telecommunications Group,
Oxford University Computing Services

j***@utwente.nl

2009-12-16 10:50:15 UTC

Permalink

Hi Kevin, Oliver,

Post by Oliver Gorwits
We do a number of similar tasks with our Netdisco installation (for
example a kind of Looking Glass service). The way I implement them
is to set up the query as a VIEW in the Netdisco database. This is
efficient and means multiple applications can share the intelligence.

Good idea, that also enables you to change the query in only one place,
should the database scheme be altered in some way.

Post by Oliver Gorwits

Post by Kevin Ratcliff
The reason I would like this information is so that I can run a
wake-on-lan script

Well... the magic packets don't actually have to be L2 broadcasts; what
really matters is that a packet with a specific payload (anywhere in the
data stream) arrives at the NIC of the station that should wake up.
Usually a Layer2 broadcast or directed L3 broadcast is used, but
(flooded) unicast or multicast is also possible.

We used specially crafted BOOTP packets for WOL for a while, since we
could send those to a (Cisco) router and based on a few fields in the
packet, the router could select the correct interface to forward the
packet on.

Later we configured the routers to allow L3 directed broadcasts, but
only originating from one management station which is used to send WOL
packets. I guess any proper router will support directed broadcast, but
it's not a good idea to turn this on unless the feature can be protected
by an ACL.

So, there are a couple of methods by which you can do WOL without being
in the same L2 broadcast domain as the client.

Regards,

Jeroen van Ingen
ICT Service Centre
University of Twente, P.O.Box 217, 7500 AE Enschede, The Netherlands

Oliver Gorwits

2009-12-16 10:54:16 UTC

Permalink

Post by j***@utwente.nl
We used specially crafted BOOTP packets for WOL for a while, since we
could send those to a (Cisco) router and based on a few fields in the
packet, the router could select the correct interface to forward the
packet on.
Later we configured the routers to allow L3 directed broadcasts, but
only originating from one management station which is used to send WOL
packets. I guess any proper router will support directed broadcast, but
it's not a good idea to turn this on unless the feature can be protected
by an ACL.
So, there are a couple of methods by which you can do WOL without being
in the same L2 broadcast domain as the client.

Thanks! I will pass this on to a colleague and see if either
solution is feasible for us as an alternative to the relay devices.

regards,

--
Oliver Gorwits, Network and Telecommunications Group,
Oxford University Computing Services

Kevin Ratcliff

2009-12-16 10:58:07 UTC

Permalink

On Wed, Dec 16, 2009 at 5:50 AM, <***@utwente.nl> wrote:

Thanks Jeroen and Oliver for your help!

Post by j***@utwente.nl
Later we configured the routers to allow L3 directed broadcasts, but
only originating from one management station which is used to send WOL
packets. I guess any proper router will support directed broadcast, but
it's not a good idea to turn this on unless the feature can be protected
by an ACL.

Our network admin here just finished telling me the very same thing
and mentioned smurf attacks.

Post by j***@utwente.nl
So, there are a couple of methods by which you can do WOL without being
in the same L2 broadcast domain as the client.

I will discuss these options with the network admin here. Thanks again
to both of you for your ideas!

Kevin

Max Baker

2009-12-16 17:42:03 UTC

Permalink

Hi Kevin,

There is a debug hook you might find interesting.

Add the following line just after an <%init> statement in one of the
.html files ...
$netdisco::SQLCARP=1;

Then do a cold restart of apache. This is `apachectl stop && apachectl
start` or `service httpd stop && service httpd start` depending on
your distro.

Now go do the thing you want to do in the front-end like search for a
MAC or IP or whatever. Then check your apache logs and in one of them,
depending on your setup, you will find the SQL statement dumped
out. You can then use those queries as a starting point for your
script.

Make sure to comment that statement out and do a cold restart soon after
or your log file will get very very big.

Also note that for scripts it's easiest to let netdisco do the
databasing for you. See this script for an example of a small utility
that uses the config file and database back-end :
http://netdisco.cvs.sourceforge.net/viewvc/netdisco/netdisco/bin/device_to_csv?view=markup

-m

John Kokidko

2009-12-16 20:06:06 UTC

Permalink

Kevin

Could you point to where the config is for this "stolen device tracking"
feature resides.

Thanks John

Post by j***@utwente.nl
Hi Kevin,
There is a debug hook you might find interesting.
Add the following line just after an <%init> statement in one of the
.html files ...
$netdisco::SQLCARP=1;
Then do a cold restart of apache. This is `apachectl stop && apachectl
start` or `service httpd stop && service httpd start` depending on
your distro.
Now go do the thing you want to do in the front-end like search for a
MAC or IP or whatever. Then check your apache logs and in one of them,
depending on your setup, you will find the SQL statement dumped
out. You can then use those queries as a starting point for your
script.
Make sure to comment that statement out and do a cold restart soon after
or your log file will get very very big.
Also note that for scripts it's easiest to let netdisco do the
databasing for you. See this script for an example of a small utility
http://netdisco.cvs.sourceforge.net/viewvc/netdisco/netdisco/bin/device_to_csv?view=markup
-m

------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev
_______________________________________________
Netdisco mailing list
https://lists.sourceforge.net/lists/listinfo/netdisco-users

Kevin Ratcliff

2009-12-18 10:07:26 UTC

Permalink

Post by Max Baker
There is a debug hook you might find interesting.

Thanks, that sounds like a useful feature.

I have one more Netdisco SQL-related question: we have one switch port
that shows 225784 MAC addresses on it! This was due to a flaky network
cable, not an attack on the switch.

Can I just delete those rows for that port from the 'node' table, or
will that cause issues in Netdisco? Just want to be sure I don't break
something! Of course I will do a database backup first.

Thanks again.

Kevin

j***@utwente.nl

2009-12-18 13:48:56 UTC

Permalink

Kevin,

Post by Kevin Ratcliff
I have one more Netdisco SQL-related question: we have one switch port
that shows 225784 MAC addresses on it! This was due to a flaky network
cable, not an attack on the switch.
Can I just delete those rows for that port from the 'node' table, or
will that cause issues in Netdisco? Just want to be sure I don't break
something! Of course I will do a database backup first.

You can safely perform that delete, it won't cause any issues.

Regards,

Jeroen van Ingen
ICT Service Centre
University of Twente, P.O.Box 217, 7500 AE Enschede, The Netherlands