I am designing a database to store nmap data. I want to store two basic types of data:
- Information about the nmap scans themselves (e.g. start and end times, nmap version used, command line arguments used)
- Information about the hosts on the network (e.g. host names, IP and MAC addresses, open ports, services running)
The primary purpose of my application is to generate a "map" of the network; essentially, a listing of which hosts are up, which hosts are down, which ports are open on which hosts, etc. For this part of the application, all I care about is the current state; in other words, the results of the latest nmap scan. However, my application also needs to do some historical reporting, like generate a graph of host OS over time.
I read this StackOverflow answer about storing historical data and it seems logical: only track the current state in your main tables and use audit tables for historical reporting. So I would have, for example, a
hosts table with the following columns:
host_id name ip_address mac_address status
ports table with the following columns:
port_id host_id number status
Every time I run nmap, I would truncate the
ports tables and insert the new results.1 The corresponding audit tables would track all of the changes over time so I could do historical reporting. Easy enough.
What I can't figure out is the best way to store the information about the nmap scans themselves. If I followed the above structure, I might have a
scans table with these columns:
scan_id start end version arguments
and a corresponding audit table. But the
scans table would only ever contain one row (the most recent scan). This seems utterly pointless. There would also be no need for a
Okay, drop the audit table just for
scans. Every time I run nmap, create a new row in the
scans table and increment the
scan_id. Problem solved.
However, now I want to generate a graph of number of hosts on the network over time. This would require me to join the
scans table with the
hosts_audit table. But now I need to add
scan_id as a column to the
ports tables in order to do the join. If I do this, however, every row in the
ports tables would have the same
scan_id, since those tables are truncated every time I run nmap. Hmmmm.
This is my first time designing a database from the ground up, and something doesn't smell right. I want the
hosts table to reflect the present state of the network, but truncating the table every time I run nmap seems...unorthodox. I want to be able to connect data from the
scans table with data from the
hosts table, but can't figure out a way that makes sense. What exactly is wrong with the design I've outlined above and how can I improve it?
- I don't think simply updating rows would work; for example, if a particular host is decommissioned, it would no longer appear in the nmap results but without truncating, would stay in the hosts table indefinitely. However, it feels a little odd to be truncating all my tables every few minutes...