You are here: Home » Howtos » kismet-sql-google-maps
Print This Page

 

Sorry the other load of code i had done has been lost somewhere. The other code was a better example of how to plot on google maps too, sorry, if i find it it will reappear some where on the site. 

So you've been wardriving and collected lots of data with kismet and a gps reciever, so now what? Well here is a way you can get the data onto google maps and possibly also google earth. Google maps requires a working webserver that is internet reachable.

Sql database

I have decided to store all my points in an sql database so that after a war drive points can just be uploaded. This example requires mysql but could easily be atapted for others.

Firstly create a database to hold your data, i have decided to use the same fields as kismet. I will assume some basic knowladge of mysql. Firstly spark up the mysql command you may need to specify user with -u or to request a password with -p or to specifcy the host use -h. So to connect to the sql server on 192.168.0.3 using user root and to request a password use :- 

mysql -u root -h 192.168.0.3 -p

Once connected issue the following commands 

mysql> create database wireless;

Then to set up the table structure place the following in a file table.sql 

CREATE TABLE wireless (
id int(10) unsigned NOT NULL auto_increment,
NetType text default NULL,
ESSID text default NULL,
BSSID text default NULL,
Info text default NULL,
Channel int(2) unsigned NULL,
Cloaked text default NULL,
Encryption text default NULL,
MaxRate int(5) unsigned NULL,
Carrier text default NULL,
BestSignal int(5) unsigned NULL,
BestQuality int(5) unsigned NULL,
GPSMinLat float NULL,
GPSMaxLat float NULL,
GPSMinLon float NULL,
GPSMaxLon float NULL,
GPSBestLat float NULL,
GPSBestLon float NULL,
PRIMARY KEY (id)
);

Then to insert this to the database use the following command 

mysql -u root -h 192.168.0.3 -p < table.sql

All that remains is to set up database permissions so that a normal user can read and insert data to the wireless database

Upload script

To get data into the database I am going to use a simple cgi perl script, the page will present a simple file upload box and this will be used to upload a kismet .cvs log to the server, this fill will be parsed and injected into the database. The simple html page could contain the following :- 

<form action=/cgi-bin/kismet.pl method=post enctype=multipart/form-data>
<input type="file" name="filename" width="90"><br>
<INPUT type="submit" value="Send">
</form>

Now for the cgi script, the script is called kismet.pl and should be loaded in a cgi-bin folder. the script takes the file uploaded by the previous html page and parses it directly, no storage on the server is required. The data is then inserted into the SQL database 

#!/usr/bin/perl
print "Content-type: text/html\r\n\r\n";
# This peice of crap saves a AP location to the sql database if it has valid GPS data, if the data is duplicated it ignores the point
# To do see if signal qulaity is better for new point and update location 
####################Configuration ###########
$host 		= 'host';
$user 		= 'username';
$password 	='password';
$dbase 		= 'wireless';
$uploaddir = "/home/robin/www//httpdocs/upload"; 
use DBI;
use CGI;
print"<pre>";
$query = new CGI;
my  $filename = $query->param("filename"); 
$filename =~ s/.*[\/\\](.*)/$1/;
$upload_filehandle = $query->upload("filename");
$dbh = DBI->connect("DBI:mysql:database=$dbase;host=$host","$user","$password", {'RaiseError'=>1});
my $kml;
@data = <$upload_filehandle>;
#print @data;
my $counter=0;
foreach $line (@data) {
if  ($counter==0) {
$counter=$counter+1;
next;
}
@WD=split(';',$line);
@mykeys=("Network","NetType","ESSID","BSSID","Info","Channel","Cloaked","Encryption","Decrypted","MaxRate","MaxSeenRate","Beacon","LLC","Data","Crypt","Weak","Total","Carrier","Encoding","FirstTime","LastTime","BestQuality","BestSignal","BestNoise","GPSMinLat","GPSMinLon","GPSMinAlt","GPSMinSpd","GPSMaxLat","GPSMaxLon","GPSMaxAlt","GPSMaxSpd","GPSBestLat","GPSBestLon","GPSBestAlt","DataSize","IPType","IP");
my %HoH = ();
my $index=0;
foreach  $thekey(@mykeys) {
$HoH{$thekey}=$WD[$index];
$index++;
}
$HoH{'ESSID'} =~ s/\</_/g;
$HoH{'ESSID'} =~ s/\>/_/g;
# ok see if the BSSID is known
$sql = qq/SELECT BSSID FROM wireless WHERE BSSID="$HoH{'BSSID'}"; /;
print("Checking BSSID  $HoH{'BSSID'} ....");
if($HoH{'NetType'}ne  "infrastructure" ){	
print(" Not an AP\n");
next; 
}
if($HoH{'GPSBestLat'}==0 or $HoH{'GPSBestLat'}==0){
print(" No GPS Lock\n");
next;
}
$sth = $dbh->prepare("$sql");
$sth->execute();
my $rows=0;
while($row = $sth->fetchrow_arrayref) {
$rows=$rows+1;
}
if($rows==0)
{
print (" New entry adding to database \n");
$sql =qq/INSERT into wireless SET 
BSSID='$HoH{'BSSID'}', 
ESSID='$HoH{'ESSID'}', 
Info='$HoH{'Info'}',
Channel='$HoH{'Channel'}',
Cloaked='$HoH{'Cloaked'}',
Encryption='$HoH{'Encryption'}',
MaxRate='$HoH{'MaxRate'}',
Carrier='$HoH{'Carrier'}',
BestSignal='$HoH{'BestSignal'}',
BestQuality='$HoH{'BestQuality'}',
GPSMaxLat='$HoH{'GPSMaxLat'}',
GPSMinLat='$HoH{'GPSMinLat'}',
GPSMaxLon='$HoH{'GPSMaxLon'}',
GPSMinLon='$HoH{'GPSMinLon'}',
GPSBestLat='$HoH{'GPSBestLat'}', 
GPSBestLon='$HoH{'GPSBestLon'}';/;
$sth = $dbh->prepare("$sql");
$sth->execute();
}
if($rows==1)
{
# update data
print(" Already listed\n");
print("Encryption = $HoH{'Encryption'} \n");
print("Updating...\n");
$sql =qq/UPDATE wireless SET 
ESSID='$HoH{'ESSID'}', 
Info='$HoH{'Info'}',
Channel='$HoH{'Channel'}',
Cloaked='$HoH{'Cloaked'}',
Encryption='$HoH{'Encryption'}',
MaxRate='$HoH{'MaxRate'}',
Carrier='$HoH{'Carrier'}',
BestSignal='$HoH{'BestSignal'}',
BestQuality='$HoH{'BestQuality'}',
GPSMaxLat='$HoH{'GPSMaxLat'}',
GPSMinLat='$HoH{'GPSMinLat'}',
GPSMaxLon='$HoH{'GPSMaxLon'}',
GPSMinLon='$HoH{'GPSMinLon'}',
GPSBestLat='$HoH{'GPSBestLat'}', 
GPSBestLon='$HoH{'GPSBestLon'}'
WHERE BSSID='$HoH{'BSSID'}';/;
$sth = $dbh->prepare("$sql");
$sth->execute();
}
if($rows>1)
{
print("Multiple entries detected\n");
}
$counter=$counter+1;
}
print"<\pre>";

Display script

The display PHP script reads the sql and ouputs a lot of javascript to generate the map points. The important bits are shown below. Full details can be seen by download the code 

<?
$counter=0;
$dbname="wireless";
$DBID=mysql_connect('host:port',username,'password')
or die("Connect error");
mysql_select_db("wireless") or die("could not select database");
$query="SELECT BSSID, ESSID, GPSBestLon, GPSBestLat, Encryption, Channel FROM wireless WHERE Encryption=\"None\";";
$result=mysql_query($query);
$count["None"] =mysql_num_rows($result);
$query="SELECT BSSID, ESSID, GPSBestLon, GPSBestLat, Encryption, Channel FROM wireless WHERE Encryption=\"WEP\";";
$result=mysql_query($query);
$count["WEP"] =mysql_num_rows($result);
$query="SELECT BSSID, ESSID, GPSBestLon, GPSBestLat, Encryption, Channel FROM wireless WHERE Encryption LIKE\"%WPA%\";";
$result=mysql_query($query);
$count["WPA"] =mysql_num_rows($result);
$query="SELECT BSSID, ESSID, GPSBestLon, GPSBestLat, Encryption, Channel FROM wireless;";
$result=mysql_query($query);
$count["Total"] =mysql_num_rows($result);
$count[WPApct]=round($count["WPA"] /$count["Total"] *100,2);
$count[WEPpct]=round($count["WEP"] /$count["Total"] *100,2);
$count[Nonepct]=round($count["None"] /$count["Total"] *100,2);
for($i=0 ; $i<mysql_num_rows($result) ;$i++) {
if(!mysql_data_seek($result,$i)) {
continue;
}
if(!$row=mysql_fetch_object($result)){
continue;
}
$info="<b>ESSID</b> $row->ESSID<br>";
$info=$info."<b>BSSID</b>  $row->BSSID<br>";
$info=$info."<b>Channel</b> $row->Channel<br>";
$info=$info."<b>Encryption</b> $row->Encryption<br>";
?>
var point = new GLatLng(<? echo "$row->GPSBestLat, $row->GPSBestLon"?>);
themap.addOverlay(createMarker(point, "<?echo "$info"?>","<?echo"$row->Encryption"?>"));
mappoints[<?echo "$counter"; ?>]=point;
<?	
$counter=$counter+1;
}
?>

And thats it!, There is room for improvement but it basicly works. The update script should be improved to better update existing records and the display system will not scale to huge numbers very well but you could change the php to restrict the data set returned to help with this.

Generating my scatter plot

The code used to read and generate the scatter plot shown on wireless.shtml is shown below 

#!/usr/bin/perl
use DBI;
####################Configuration ###########
$host 		= 'host;
$user 		= 'username';
$password 	='password';
$dbase 		= 'wireless';
my $counter=0;
$dbh = DBI->connect("DBI:mysql:database=$dbase;host=$host","$user","$password", {'RaiseError'=>1});
$sql = qq/SELECT BSSID, ESSID, Encryption, GPSBestLat, GPSBestLon FROM wireless ; /;
$sth = $dbh->prepare("$sql");
$sth->execute();
print("set term png small\n");
print("set title \"Wireless AP's\"\n");
print("set ylabel \"Latitude\"\n");
print("set xlabel \"Longitude\"\n");
print("plot \"-\" with dots\n");
#foreach $line (@data) {
while($ref = $sth->fetchrow_hashref())
{
print("$ref->{'GPSBestLon'} $ref->{'GPSBestLat'}\n");
}

This script is called by a cron job once a week via the following command and the image file saved to the webserver 

dumpplot.pl | gnuplot > /home/robin/www/httpdocs/wireless.png

Direct .cvs to klm conversion

If you don't want to involve a webserver for google earth etc then use the following script :- 
#!/usr/bin/perl
my $filename = shift;
my $kml;
open (FILE,$filename);
@data = <FILE>
close(FILE); 
my $counter=0;
$kml="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$kml=$kml."<kml xmlns=\"http://earth.google.com/kml/2.0\">\n";
$kml=$kml."<Document>\n";
$kml=$kml."  <name> Wireless AP location</name>\n";
$kml=$kml."  <description> APs detected</description>\n";
foreach $line (@data) {
if  ($counter==0) {
$counter=$counter+1;
next;
}
@WD=split(';',$line);
@mykeys=("Network","NetType","ESSID","BSSID","Info","Channel","Cloaked","Encryption","Decrypted","MaxRate","MaxSeenRate","Beacon","LLC","Data","Crypt","Weak","Total","Carrier","Encoding","FirstTime","LastTime","BestQuality","BestSignal","BestNoise","GPSMinLat","GPSMinLon","GPSMinAlt","GPSMinSpd","GPSMaxLat","GPSMaxLon","GPSMaxAlt","GPSMaxSpd","GPSBestLat","GPSBestLon","GPSBestAlt","DataSize","IPType","IP");
my %HoH = ();
my $index=0;
foreach  $thekey(@mykeys) {
$HoH{$thekey}=$WD[$index];
$index++;
}
if($HoH{'NetType'}ne  "infrastructure" )
{	next; }
if($HoH{'GPSBestLat'}==0 or $HoH{'GPSBestLat'}==0)
{next;}
$kml=$kml."\n <Placemark>\n";
$HoH{'ESSID'} =~ s/\</_/g;
$HoH{'ESSID'} =~ s/\>/_/g;
$kml=$kml."    <name>$HoH{'ESSID'}</name>\n";
$kml=$kml."    <description>BSSID $HoH{'BSSID'} </description>\n";
$kml=$kml."    <Point>\n";
$kml=$kml."      <coordinates>$HoH{'GPSBestLon'},$HoH{'GPSBestLat'},0</coordinates>\n";
$kml=$kml."    </Point>\n";
$kml=$kml."  </Placemark>\n";
$counter=$counter+1;
}
$kml=$kml."\n</Document>\n";
$kml=$kml."</kml>\n";
print $kml;

To use the above script save it as kismetparse.pl and then run kismetparse.pl Kismet-Log.csv > output.kml

Any comments welcome!


^ Top

Previous page: Howtos
Next page: WinTV-Debian-HowTO