I thought I'd share it...
It might help someone out.
- Code: Select all
#!/usr/bin/perl
#
# Create mysql insert statements from a netstumbler summary file
#
# - Will add in vendor from oui list if oui.txt exists in current directory
# http://standards.ieee.org/regauth/oui/oui.txt
# - Will decode flags and populate individual variables
# - Updates the date as each row only stores time
#
# usage : convert_sum.pl < summary.sum > filename.sql
use integer; # for bit stuff with flags
%ouilist = ();
initoui();
while(<>) {
#strip dos ^M and any newlines
s/\015$//;
chomp;
# backquote quote all ' for db insert
s/\'/\\'/g;
if (/^#/) {
# pull date from header, exists multiple times when merging files
$date = $1 if (/^#.*DateGMT:\s*(\d{4}-\d{2}-\d{2})/);
} else {
# get all fields, file is tab delimited
($lat, $long, $ssid, $type, $bssid, $time, $snr, $name, $flags, $chanbits, $bcn, $rate, $lastchannel) = split /\t/;
# clean up fields
$ssid =~ s/\( (.*) \)/\1/; # remove leading and trailing brackets
$bssid =~ s/\( (.*) \)/\1/; # remove leading and trailing brackets
$name =~ s/# \( (.*) \)/\1/; # remove leading and trailing brackets and leading #
$time =~ s/ \(.*//; # remove GMT
$time = "$date $time"; # build date/time field.
($snr, $sig, $noise) = ($snr =~ /(\d+) (\d+) (\d+)/); # split field into 3
# empty flags, split out flags into variables
($flg_ess, $flg_ibss, $flg_cfpoll, $flg_cfpreq, $flg_wep, $flg_shpre, $flg_pbcc) = ();
$flg_ess = 1 if hex ($flags) & 0x01;
$flg_ibss = 1 if hex ($flags) & 0x02;
$flg_cfpoll = 1 if hex ($flags) & 0x04;
$flg_cfpreq = 1 if hex ($flags) & 0x08;
$flg_wep = 1 if hex ($flags) & 0x10;
$flg_shpre = 1 if hex ($flags) & 0x20;
$flg_pbcc = 1 if hex ($flags) & 0x40;
# get the vendor name from the oui list
$vendor = getvendor($bssid);
#create sql
print "INSERT INTO wireless_data VALUES (NULL, \'$lat\', \'$long\', \'$ssid\', \'$type\', \'$bssid\', \'$vendor\', \'$time\', \'$snr\', \'$sig\', \'$noise\', \'$name\', \'$flags\', \'$chanbits\', \'$bcn\', \'$rate\', \'$lastchannel\', \'$flg_ess\', \'$flg_ibss\', \'$flg_cfpoll\', \'$flg_cfpreq\', \'$flg_wep\', \'$flg_shpre\', \'$flg_pbcc\');\n";
}
}
sub getvendor($)
{
$chkbssid = shift;
$chkbssid =~ s/^(\w\w:\w\w:\w\w).*/\1/;
return $ouilist{uc($chkbssid)};
}
sub initoui()
{
open(local *ouifile, "oui.txt");
while (<ouifile>) {
$ouilist{uc("$1:$2:$3")} = $4 if (/^(\w\w)-(\w\w)-(\w\w)\s+\(hex\)\s+(.*)\s+$/);
}
close (ouifile);
}
and the mysql database creation script
- Code: Select all
create table wireless_data (
id int(11) not null auto_increment,
Latitude varchar(13),
Longitude varchar(13),
SSID varchar(255),
Type varchar(10),
BSSID varchar(17),
Vendor varchar(255),
FoundTime datetime,
SNR int(3),
Sig int(3),
Noise int(3),
Name varchar(255),
Flags varchar(4),
Channelbits varchar(8),
BcnIntvl varchar(4),
DataRate varchar(5),
LastChannel int (2),
f_ess int (1),
f_ibss int (1),
f_cfpoll int (1),
f_cfpollreq int (1),
f_wep int (1),
f_shortpre int (1),
f_pbcc int (1),
PRIMARY KEY (id),
KEY uid (id)
);