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.