We were an early adopter of supporting Google map integration in our NetFlow collector system. Since then, we have had customers asked that we pull in the location information from SNMP (syslocation), IPAM solutions and other GPS coordinate applications in order to avoid a manual process.

GPS Coordinates

Traditionally, our customers would type in an address into the device that was added to Scrutinizer and the system would find the GPS coordinates and add it to the google map for them.  This process can be tedious and we needed to automate it for customers.  This is especially important when you consider adds moves and changes on thousands of devices under network management.

A couple years ago a customer  gave us a great idea to get the address from sysLocation which we were already collecting using SNMP.  Along comes another customer that has a 3rd party application that can export the IP addresses and GPS coordinates (or address) and we can pull that information to auto populate the Google maps. The process for doing this is really pretty straight forward with a perl script.

 

CSV GPS Update Script:

The script below reads in a file named exporter-gps.csv which contains a comma delimited file which assumes the order of CSV is Exporter IP, Latitude and Longitude.

 

################

## BEGIN SCRIPT ##

################

 

#!perl

use DBI;

use DBD::mysql;

 

# lines beginning with a pound sign ‘#’ are comments that help you understand the script.

# use the DBI module above to connect to the Scrutinizer database locally with the credentials.

my $dbh = DBI->connect("dbi:mysql:database=plixer;host=127.0.0.1;port=3306","root", "root");

 

# open a file handle called FILE and it opens the csv file. Note, the script and the file must be in the same directory.

open (FILE, "exporter-gps.csv");

while (<FILE>) {

# line by line go through the csv file, suck out the latitude and longitude which is separated by a comma.

chomp;

($exporterIP, $lat, $lon) = split(",");

# print the results to the local computer display when it runs for debug purposes

print "Exporter: $exporterIP Lat: $lat Long: $lon\n";

 

# select the IP address from crosscheck where it is the same as the IP address in the csv file.

my $query = qq{select object_id as ip from plixer.xcheck_hosts where inet_b2a(device_id) = '$exporterIP';};

# prepare the query using the above, execute it and pull out ‘fetch’ the result

# which should be the object_id found in crosscheck

my $sth = $dbh->prepare("$query");

$sth->execute();

my $object_id = $sth->fetchrow();

$sth->finish();

 

# Do some error checking.  If the Object ID exists, update it.

# This allows you to have IP addresses in the map that are not exporting flows

if ($object_id) {&update($object_id,$lat,$lon)};

 

}

# time for some serious magic that if you really want to understand you should call our support

sub update{

my $updateQuery = qq{update plixer.membership set lng ='@_[2]', lat='@_[1]' where object_id = '@_[0]';};

my $sth = $dbh->prepare("$updateQuery");

$sth->execute();

$sth->finish();

 

}

$dbh->disconnect();!perl

__END__

 

###############

## END SCRIPT ##

###############

Here’s another idea: IXIA exports the GPS coordinates in their IPFIX export.  We can modify the script to work with the Scrutinizer API to extract the values from the flows and plot the devices on the google map.

Hopefully the above is enough to get you started but, if you need help, just pick up the phone, call us and our technical support folks will give you a hand.

Thomas

Thomas

Thomas Pore is the Director of IT and Field Engineering at Plixer. He developed and leads, the Malware Incident Response and Advanced NetFlow Training programs which are being offered in cities across the USA. He is also an adjunct professor at the local community college and teaches ethical hacking. Thomas travels the globe meeting with customers and trying improve the Scrutinizer network incident response system. He helps clients optimize threat detection strategies and aids in the configuration of custom incident response solutions. He has a Bachelor of Science in Computer Science from Dickinson College.

Related