Summary File to Database

Take a script, leave a script - Like the penny tray only different

Summary File to Database

Postby cloak » Thu Jun 09, 2005 3:56 pm

I whipped up a quick script yesterday for creating a set of insert statements from a netstumbler summary file for putting all your points into a mysql database. It also reads in an oui file and determines the vendor.

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)
);
cloak
Mini Stumbler
 
Posts: 2
Joined: Wed Jul 16, 2003 1:13 pm

Postby nashr » Fri Jun 10, 2005 3:09 am

Thank you. I'm certainly going to play with this. There is another recent script (similar?). Do a search on perl and mysql and see if you can dig it up. I'm a bit pressed for time this morning ;)


Thanks for sharing!
Help! I've been Simpsonized!
User avatar
nashr
 
Posts: 1585
Joined: Fri Aug 09, 2002 6:12 am
Location: Virginia

Postby p1mp » Fri Jun 17, 2005 1:56 pm

cloak wrote:I whipped up a quick script yesterday for creating a set of insert statements from a netstumbler summary file for putting all your points into a mysql database. It also reads in an oui file and determines the vendor.

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]

and the mysql database creation script

[CODE]
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)
);



how do u take this info and publish in a php or html page?
p1mp
Mini Stumbler
 
Posts: 24
Joined: Tue Mar 11, 2003 1:36 am
Location: erie,pa

Postby wrzwaldo » Fri Jun 17, 2005 2:04 pm

p1mp wrote:how do u take this info and publish in a php or html page?



Do you enjoy seeing your posts in the bin? Personally I wish they would just ban you and be done with it.
wrzwaldo
 
Posts: 8995
Joined: Sun Dec 14, 2003 12:43 pm

Postby Thorn » Fri Jun 17, 2005 2:06 pm

That's WAY too complicated for a post, or even a single thread. I would suggest O'Reilly's Learning PERL and Managing & Using MySQL.
Thorn
Stop the TSA now! Boycott the airlines.
Thorn
 
Posts: 10340
Joined: Sat Apr 13, 2002 3:00 am
Location: Villa Straylight

Postby greenghost » Fri Sep 09, 2005 3:51 am

Uhmmm,

i'm trying to code an utility for importing my netstumbler summaries into ms access 2003 and i'd like to decode the flags.

Could anybody tell me wehich way the flags are coded?
greenghost
Mini Stumbler
 
Posts: 9
Joined: Mon Feb 02, 2004 9:01 pm

Postby Thorn » Fri Sep 09, 2005 4:14 am

Search on "FLAGS". This has been discussed in detail multiple times.
Thorn
Stop the TSA now! Boycott the airlines.
Thorn
 
Posts: 10340
Joined: Sat Apr 13, 2002 3:00 am
Location: Villa Straylight


Return to Scripts

Who is online

Users browsing this forum: No registered users and 3 guests