DB Schema for NMAP in MySQL

I now have two scripts one that connects to the remotely hosted MySQL DB and the other that starts NMAP and runs a scan with XML output.

The next step is to create some code that will read the XML output file and import it into the DB. Once the scans are in the DB all kinds of queries can be executed on the data.

DB-SCHEMA

The NMAP manual has this to say about importing XML:
For projects large enough to require a database, I recommend deciding on an optimal DB schema first, then writing a simple program or script to import Nmap XML data appropriately. Such scripts often take only minutes, thanks to the wide availability of XML parsers and database access modules. Perl often makes a good choice, as it offers a powerful database abstraction layer and also custom Nmap XML support. the section called “Manipulating XML Output with Perl” shows how easily Perl scripts can make use of Nmap XML data.

The links in the NMAP documentation where all out of date and the newer versions of NMAP have introduced new functionality which means the DB Schema needs to be extended. I found a good article on the BLOG at Redspin a penetration testing company. I will use their schema with some refinements to the DB structure to use less space as they have been very generous in there field allocations.

TABLE nmap (

    sid INTEGER PRIMARY KEY AUTOINCREMENT,

    version TINYTEXT,

    xmlversion TINYTEXT,

    args TEXT,

    types TEXT,

    starttime DATETIME,

    startstr TEXT,

    endtime DATETIME,

    endstr TEXT,

    numservices INTEGER) 

TABLE hosts (

    sid INTEGER,

    hid INTEGER PRIMARY KEY AUTOINCREMENT,

    ip4 VARCHAR(15),

    ip4num INTEGER,

    hostname TEXT,

    status TEXT,

    tcpcount INTEGER,

    udpcount INTEGER,

    mac CHAR(12),

    vendor TEXT,

    ip6 TEXT,

    distance INTEGER,

    uptime TEXT,

    upstr TEXT) 

TABLE sequencing (

    hid INTEGER,

    tcpclass TEXT,

    tcpindex TEXT,

    tcpvalues TEXT,

    ipclass TEXT,

    ipvalues TEXT,

    tcptclass TEXT,

    tcptvalues TEXT) 

TABLE ports (

    hid INTEGER,

    port INTEGER,

    type TEXT,

    state TEXT,

    name TEXT,

    tunnel TEXT,

    product TEXT,

    version TEXT,

    extra TEXT,

    confidence INTEGER,

    method TEXT,

    proto TEXT,

    owner TEXT,

    rpcnum TEXT,

    fingerprint TEXT) 

TABLE os (

    hid INTEGER,

    name TEXT,

    family TEXT,

    generation TEXT,

    type TEXT,

    vendor TEXT,

    accuracy INTEGER)

Next stage will be to write the scripting to create an instance of the DB and the parsing engine to read the NMAP XML output so that the information can be read into the correct fields.