Send Netdot-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Netdot-users digest..."
Today's Topics:
1. Geolocating your sites in a map (Nico)
2. Subnet discovery and update last_seen (Christian Meutes)
3. Re: Import scripts (Phil Regnauld)
4. Update device site using ipblock (site) information (Nico)
5. Topology Graphs (Chip Pleasants)
6. Re: Topology Graphs (Carlos Vicente)
----------------------------------------------------------------------
Message: 1
Date: Wed, 11 Dec 2013 12:25:28 +0100
From: Nico <[email protected]>
Subject: [Netdot-users] Geolocating your sites in a map
To: [email protected]
Message-ID:
<CAKXQfmv35c5dp9TW+5QMr=i218=hp5wfmgb7ac90htbulwn...@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Hello,
The idea is to use a geolocation service (in this implementation
google's one) to guess the latitude and longitud corresponding to a
site using the street1, city, state, country and zip fields, then it
will store lat, lng, and the address google thinks that is asigned to
that given lat,lng (for further troubleshooting porpuses) in newly
created fields on the site table. A second script using this latitute
and longitud info will create a kml containing all the sites.
You need to have the stree1, city, state, zip info right for your
sites for this to work, and have a working python 2.7+ environment
with geopy and pykml and lxml libraries installed. I've only tried it
in Ubuntu 12.04.3 LTS and can't remember now how i installed the
libraries, if via apt-get or something else, if someone needs help
i'll try to dig it.
https://code.google.com/p/geopy/
http://pythonhosted.org/pykml/
http://lxml.de/
So first you need to alter the site table to store lat, lng and geodescrip.
>From the mysql promt in your netdot database execute:
alter table site add column geodescrip varchar(255);
alter table site add column lat float(10,6);
alter table site add column lng float(10,6);
The first script to guess the lat and lng of your sites, it a 1 second
sleep timer, becouse google don't likes you hammering their service
like crazy, so it may take a while if you have many sites:
---------------- /usr/local/bin/netdot_getlatlng.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from geopy import geocoders
import time
dbhost="localhost"
dbname="netdot"
dbuser="netdot"
dbpassword="xxx"
db = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpassword,
db=dbname, use_unicode = True, init_command="SET NAMES utf8",
charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
c = db.cursor()
#This is used to discard false positionings outside the area you know
sites should be (in my case on a wide rectangle covering the area of
Asturias (a province in the north of Spain))
west = 7.183347
east = 4.509042
north = 43.669858
south = 42.882111
g = geocoders.GoogleV3()
ultimo = 0
SELECT = "SELECT id, name, street1, city, zip, state from site WHERE
name not like 'REDSARA%' and name not like '00000%' and name != 'None'
and name is not NULL AND name not like 'None%' order by
id;".format(ultimo)
r = c.execute (SELECT)
centros = c.fetchall()
for centro in centros:
print " -",centro['id'],
centro['name'],centro['street1'],centro['city'], centro['state'],
centro['zip']
street1 = u"{} {} {} {}".format( centro['street1'],centro['city'],
centro['zip'] if centro['zip'] != '0' else '',centro['state'])
print street1
places = g.geocode(street1,exactly_one=False)
time.sleep(1)
for place in places:
p, (lat,lng) = place
if lat <= north and lat >= south and abs(lng) <= west and abs(lng) >= east:
print "%s: %.5f, %.5f" % (p, lat, lng)
UPDATE = "UPDATE site SET lat = %s ,lng =%s ,geodescrip = %s
WHERE id = %s"
c.execute(UPDATE, (lat,lng,p,centro['id']))
break
db.commit()
db.close()
------------------------------------
Once the script is done and have inserted all the sites with lat and
lng info, you can run this script to generate a kml with all the sites
positioned on the map (you can later open it with google earth).
Redirect the output to the filename you wish for example:
/usr/local/bin/netdot_create_kml.py > NetdotSites.kml
-------- /usr/local/bin/netdot_create_kml.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from pykml.factory import KML_ElementMaker as KML
from lxml import etree
dbhost="localhost"
dbname="netdot"
dbuser="netdot"
dbpassword="xxx"
db = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpassword,
db=dbname, use_unicode = True, init_command="SET NAMES utf8",
charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
c = db.cursor()
SELECT = "select * from site where lat != '' order by id;"
doc = KML.kml(
KML.Document(
KML.Name("Netdot Sites"),
)
)
r = c.execute (SELECT)
centros = c.fetchall()
for centro in centros:
descrip = u"{}<BR>({})<BR>[{}]".format(centro['name'],centro['street1']+u"
"+centro['zip']+" "+centro['city'],centro['geodescrip'])
pm = KML.Placemark(
KML.name(centro['number']),
KML.description(descrip),
KML.Point(
KML.coordinates("{},{},{}".format(centro['lng'],centro['lat'],"relativeToGround"))
)
)
doc.Document.append(pm)
print etree.tostring(doc,pretty_print=True)
------------------------
Note: The description of each site on the KML will contain both the
street1 and the geodescrip that google thinks corresponds to that lat
and lng, so you can compare.
Use this on your own risk, remember to backup database before trying
stuff (unlike me sometimes).
Greetings,
--
Nico
------------------------------
Message: 2
Date: Wed, 11 Dec 2013 14:00:25 +0200
From: Christian Meutes <[email protected]>
Subject: [Netdot-users] Subnet discovery and update last_seen
To: <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=UTF-8; format=flowed
Hello,
I'am trying to get rid of old subnets which were discovered a long time
ago
but aren't configured anymore on any routers in my network.
Can you confirm that the last_seen field of subnets isn't updated when
updatedevices.pl discovers subnets which are already in the database?
To get rid of old subnets I was thinking about getting updatedevices.pl
to
update also last_seen for subnets and additionally introduce another
flag
for prune_db.pl to delete those in the same way it's done for ARP and
MAC
entries.
Am I right with my assumptions or do I miss anything to accomplish
this? Or
is it maybe already possible and just something is not working properly
in
my setup?
Cheers
Chris
------------------------------
Message: 3
Date: Wed, 11 Dec 2013 13:50:31 +0100
From: Phil Regnauld <[email protected]>
Subject: Re: [Netdot-users] Import scripts
To: Andreas Kunberger <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=utf-8
Andreas Kunberger (andreas.kunberger) writes:
> Yes, I have.
>
> I uploaded them once for coming version, but if needed I can send them again
> to you.
> ?
> Andreas Kunberger
Hi Andreas,
Do you have a link/reference to where those scripts are ? I wouldn't
mind collecting various contributions of this kind, to place on the
netdot wiki. We could also set up a netdot-contrib repo on Github.
Cheers,
Phil
------------------------------
Message: 4
Date: Wed, 11 Dec 2013 15:04:05 +0100
From: Nico <[email protected]>
Subject: [Netdot-users] Update device site using ipblock (site)
information
To: [email protected]
Message-ID:
<CAKXQfmt-1qwb4TCPgn_nUUpgh=rzktmkruzdqnixpcleg4z...@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Hello,
This is a python script that updates device site information using the
network site information.
The script asumes you have your networks already asigned to your
sites, and each network is only in one site (if you have a network
distributed in layer 2 in several locations this may not work for
you).
The script creates temporal table that is a subset of the information
contained in the ipblock table, so it may have a impact on database if
ipblock table is large (in my case it has 17834 records) and it takes
like 3 seconds to run the script. It's written in python and needs the
netaddr library.
What it does is populate the temporal table (ipblocktemp) with the id
of the ipblock, the first ip (network) and the last ip (broadcast) and
the site asociated to that ipblock (if existing in sitesubnet table).
Then for each device it gets the snmp_target address (the snmp ip
address associated to the device), searches in ipblocktemp if there is
a firstip <> lastip that contains the ip, and updates the device site
field to point to the same site that the iplocktemp entry. I've used
root user becouse it didn't allow me to insert into ipblocktemp table
with netdot user, but i guess that should be avoided if possible.
Use it at your own risk and please backup your database before trying
it. I'm using Netdot 1.0.4
----------------
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
import netaddr
dbhost="localhost"
dbname="netdot"
dbuser="root"
dbpassword="xxx"
def create_ipblocktemp (db):
import netaddr
''' Creates a temporal table with fields firstip, lastip, site_id,
ipblock_id for each ocurrence in ipblock table with prefix not 32'''
c=db.cursor()
SQL="DROP TABLE IF EXISTS {}.ipblocktemp;".format(dbname)
c.execute(SQL)
SQL ="""CREATE TABLE `{}`.`ipblocktemp` (
`firstip` BIGINT(10) NOT NULL ,
`lastip` BIGINT(10) NULL ,
`ipblock_id` INT(32) NOT NULL ,
`site_id` INT(32) NOT NULL ,
PRIMARY KEY (`firstip`, `lastip`, `ipblock_id`) );""".format(dbname)
c.execute(SQL)
SQL="SELECT INET_NTOA(ipblock.address) as
address,ipblock.prefix,ipblock.id,sitesubnet.site FROM
ipblock,sitesubnet WHERE ipblock.prefix != '32' and ipblock.id =
sitesubnet.subnet;"
r = c.execute(SQL)
ipblockall = c.fetchall()
for ipblock in ipblockall:
print ipblock
iptemp = "{}/{}".format(ipblock['address'],ipblock['prefix'])
baseip = netaddr.IPNetwork(iptemp)
SQL = "INSERT INTO netdot.ipblocktemp VALUES (%s,%s,%s,%s)";
c.execute(SQL,(int(baseip.network),int(baseip.broadcast),ipblock['id'],ipblock['site']))
db.commit()
c.close()
db = MySQLdb.connect(host=dbhost, user=dbuser,
passwd=dbpassword,db=dbname, use_unicode = True, init_command="SET
NAMES utf8",charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
create_ipblocktemp(db)
SQL_SELECT = "select device.id,ipblock.address from device,ipblock
where device.snmp_target = ipblock.id;"
c = db.cursor()
r = c.execute(SQL_SELECT)
device_list = c.fetchall()
for device in device_list:
SELECT_DEVICE = "SELECT site_id FROM {}.ipblocktemp WHERE firstip <=
{} and lastip >=
{};".format(dbname,device['address'],device['address'])
print SELECT_DEVICE
r = c.execute(SELECT_DEVICE)
if r == 1:
devicetemp = c.fetchone()
UPDATE = "UPDATE device SET site = {} WHERE id =
{};".format(devicetemp['site_id'],device['id'])
c.execute(UPDATE)
print UPDATE
db.commit()
c.close()
db.close()
---------------------------
Greetings,
--
Nico
------------------------------
Message: 5
Date: Wed, 11 Dec 2013 09:25:45 -0500
From: Chip Pleasants <[email protected]>
Subject: [Netdot-users] Topology Graphs
To: [email protected]
Message-ID:
<CAJq5ATp99QU7AH4E0O5UF6A5eaHuBSiFXxd9KyGGhb-=txc...@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
I'm hoping someone gives some pointers to get the topology graphs working?
When trying to see topology I get the following error.
Cannot find root device: myserver.domainname.com
In my Site.conf I have the following line:
NMS_DEVICE => 'myserver.domainname.com',
myserver.domainname.com is my observium server. The server is resolvable
via name internally. What am I doing wrong?
Thanks,
-Chip
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://osl.uoregon.edu/pipermail/netdot-users/attachments/20131211/ddd5a12f/attachment-0001.html
------------------------------
Message: 6
Date: Wed, 11 Dec 2013 07:45:05 -0800
From: Carlos Vicente <[email protected]>
Subject: Re: [Netdot-users] Topology Graphs
To: Chip Pleasants <[email protected]>,
[email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1
Hi Chip,
On 12/11/13, 6:25 AM, Chip Pleasants wrote:
> I'm hoping someone gives some pointers to get the topology graphs
> working? When trying to see topology I get the following error.
>
> Cannot find root device: myserver.domainname.com
> <http://myserver.domainname.com>
>
>
>
> In my Site.conf I have the following line:
>
> NMS_DEVICE => 'myserver.domainname.com <http://myserver.domainname.com>',
>
>
> myserver.domainname.com <http://myserver.domainname.com> is my
> observium server. The server is resolvable via name internally. What
> am I doing wrong?
>
>
>
The NMS_DEVICE must be a device in netdot. There also needs to be a
relationship path (interface neighbors) between that device and the rest
of the network.
Best,
cv
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 61, Issue 7
*******************************************
