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
and a ports
table with the following columns:
port_id
host_id
number
status
Every time I run nmap, I would truncate the hosts
and 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.
The problem(s)
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 scan_id
column.
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 hosts
and ports
tables in order to do the join. If I do this, however, every row in the hosts
and 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...