Random thoughts, views, and rantings

I was never really happy with the old way that I had parsed the IEEE oui.txt file to determine vendor names based on MAC addresses.  It was slow, required manual manipulation of the oui file before processing, and was tailored only to gpsdrive’s geoinfo MYSQL schema.  Along with the ongoing Kismet/AP work I’ve been (slowly) doing, I wanted a better way to determine AP vendors from MAC addresses that was easily run, updatable, and fast.

The best way I found was to store parsed MAC to vendor information in a database table which was easy to do since I was already using SQLite for the WIP Kismet parsing code.  I used this schema:

CREATE TABLE manuf (
mac TEXT PRIMARY KEY NOT NULL,
manuf TEXT NOT NULL);

and the following parsing code:

<?php
try
{
  $dbh = new PDO("sqlite:/path/to/database.sqlite");
}
catch(PDOException $e)
{
  echo $e->getMessage();
}

$ouifile = file("http://standards.ieee.org/regauth/oui/oui.txt");

$dbh->exec("BEGIN;");
$delete = $dbh->prepare("DELETE FROM manuf;");

$insert = $dbh->prepare("INSERT INTO manuf (mac, manuf) VALUES (?, ?);");
$insert->bindParam(1, $mac);
$insert->bindParam(2, $manuf);

$vacuum = $dbh->prepare("VACUUM;");

$delete->execute();
$vacuum->execute();

foreach ($ouifile as $line)
{
  if (!substr_count($line, "(hex)"))
  {
    continue;
  }
  $mac = str_replace("-", ":", substr($line, 0, 8));
  $manuf = trim(substr($line, strpos($line, "(hex)") + 5));
  $manuf = preg_replace('/[\',\.\(\)]/', '', $manuf);
  $manuf = preg_replace(
    '/(the|inc|incorporated|plc|s\/a|a\/s|ab|ag|kg|gmbh|limited|ltd|spa|llc)/i',
    '', $manuf);
  $manuf = ucwords(strtolower($manuf));
  $manuf = preg_replace('/\s\s+/', ' ', $manuf);
  $manuf = trim($manuf);
  $insert->execute();
}

$dbh->exec("END;");
$dbh = null;
echo "Done inserting";
?>

Basically, it pulls the oui.txt from the IEEE, parses it and removes some unneeded characters/info, and puts it in the database.  This is then easy to query against using standard SQL and it is now easily run and updated and takes milliseconds instead of seconds to run.

I’ve created a preliminary database schema for kismet log info that I think holds all useful information from the netxml files.

CREATE TABLE networks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
bssid TEXT DEFAULT NULL,
essid TEXT DEFAULT NULL,
cloaked BOOLEAN DEFAULT NULL,
channel INTEGER DEFAULT NULL,
encryption TEXT DEFAULT NULL,
manuf TEXT DEFAULT NULL,
ipaddress TEXT DEFAULT NULL,
iptype TEXT DEFAULT NULL,
maxrate INTEGER DEFAULT NULL,
maxseenrate INTEGER DEFAULT NULL,
beaconrate INTEGER DEFAULT NULL,
llcpackets INTEGER DEFAULT NULL,
datapackets INTEGER DEFAULT NULL,
cryptpackets INTEGER DEFAULT NULL,
totalpackets INTEGER DEFAULT NULL,
datasize INTEGER DEFAULT NULL,
firsttime TEXT DEFAULT NULL,
lasttime TEXT DEFAULT NULL,
gpsminlat NUMERIC DEFAULT NULL,
gpsminlon NUMERIC DEFAULT NULL,
gpsminalt NUMERIC DEFAULT NULL,
gpsmaxlat NUMERIC DEFAULT NULL,
gpsmaxlon NUMERIC DEFAULT NULL,
gpsmaxalt NUMERIC DEFAULT NULL,
gpspeaklat NUMERIC DEFAULT NULL,
gpspeaklon NUMERIC DEFAULT NULL,
gpspeakalt NUMERIC DEFAULT NULL)

CREATE TABLE clients (
id INTEGER PRIMARY KEY AUTOINCREMENT,
netid INTEGER DEFAULT NULL,
macaddress TEXT DEFAULT NULL,
channel INTEGER DEFAULT NULL,
manuf TEXT DEFAULT NULL,
ipaddress TEXT DEFAULT NULL,
iptype TEXT DEFAULT NULL,
maxseenrate INTEGER DEFAULT NULL,
llcpackets INTEGER DEFAULT NULL,
datapackets INTEGER DEFAULT NULL,
cryptpackets INTEGER DEFAULT NULL,
totalpackets INTEGER DEFAULT NULL,
datasize INTEGER DEFAULT NULL)

Its a two table design–the networks table holds the info about the APs while the clients table holds info about seen clients. If you don’t recognize the SQL variant, its SQLite, a choice I’m thinking of making so that the database can easily be replicated/edited/utilized without running a server.

And yes, the netid in the clients table should be a foreign key to id in the networks table, but as SQLite just added support for foreign keys, I’m not sure I want to include that yet.

For my ongoing AP code work that I am doing, I wanted to supply AP vendors based on the MAC address of the AP.  Now, for any cloned MAC addresses this will be inaccurate or give an Unknown entry, but for any genuine MAC address the vendor can be determined!

I first started with the OUI file published by the IEEE: http://standards.ieee.org/regauth/oui/oui.txt  This file gives the vendor information for the leading 3 bytes of the 6 byte MAC address.  Each leading 3 bytes is uniquely assigned to one vendor, which can hold 2^24 or 16.8 million unique MAC addresses.

To begin, I ran

grep "(hex)" oui.txt>ouitab.txt

to get just the lines with the leading half of the mac in the form XX-XX-XX and the vendor’s name.  I then replaced the spaces, (hex), and one tab to get all lines in the form XX-XX-XX(tab)Vendor and saved this as oui.txt.

Instead of creating a new field in the geoinfo database from GPSDrive, I instead chose to use the unused comment field to hold the vendor information.  The rest was simple: write a php script to search the revised oui.txt file for the leading 3 bytes of the MAC for each AP in the database and then update the database by writing the Vendor to the comment field.  I came up with this:

<?php
$oui = file('oui.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$dbconnection = mysql_connect('localhost', 'root', 'Insert Password Here');
$dbselected = mysql_select_db('geoinfo', $dbconnection);
$result = mysql_query("SELECT * FROM waypoints");
while ($row = mysql_fetch_assoc($result)) {
  $mac = $row['macaddr'];
  $macorig = $row['macaddr'];
  $mac = substr($mac, 0, 8);
  $mac = str_replace(":", "-", $mac);
  $mac = strtoupper($mac);
  $search = preg_grep("/{$mac}/i", $oui);
  $search = array_values($search);
  $exploded = explode("\t", $search[0]);
  if ($exploded[1] == "")
    $update = "UPDATE waypoints SET comment = 'Unknown' WHERE macaddr = '$macorig'";
  else
    $update = "UPDATE waypoints SET comment = '$exploded[1]' WHERE macaddr = '$macorig'";
  $updateresult = mysql_query($update);
}
mysql_close($dbconnection);
echo "Complete";
?>

This set the comment field with the found vendor name if it could be found or put Unknown if the MAC could not be found.

Note that using preg_grep in this way is VERY slow as it linearly searches oui.txt for a match for EACH AP.  For ~3700 APs it took about 45 seconds to run, which was fine for a quick and dirty script but for more APs would of course take longer.

More code updates to come as I work more on the AP code!