Import NMAP XML Into MySQL (and Print Report)

These are the notes for importing data from XML document into MySQL, and then to print a simple report.

parse.php

<?php

$file = file('nmapTest.xml');

$servername = "localhost";
$username = "nmap_user";
$password = "123456";
$db = "nmap";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
	die("Connection failed: ". $conn->connect_error);
}

$ip;
$mac;
$vendor;
$hostname;
$port;
$portArray = array();
$portList;
$timestamp;

foreach($file as $line){
	
	//Get IP Address
	if (strpos($line, 'addrtype="ipv4"') == TRUE){
	preg_match('/addr=".* addrtype/',$line,$results);
	$ip = implode(" ",$results);
	$ip = ltrim($ip, 'addr="');
	$ip = rtrim($ip, '" addrtype');
	print "<br><strong><u>Device</u></strong><br>";
	print "IP Address:  $ip<br>";
	}

	//Get Vendor
	if (strpos($line, 'addrtype="mac"') == TRUE){
	preg_match('/vendor=".*"/',$line,$results);
	$vendor = implode(" ",$results);
	$vendor = ltrim($vendor,'vendor="');
	$vendor = rtrim($vendor, '"');
	print "Vendor: $vendor<br>";
	}
	
	//Get MAC Address
	if (strpos($line, 'addrtype="mac"') == TRUE){
	preg_match('/addr=".*" addrtype/',$line,$results);
	$mac = implode(" ",$results);
	$mac = ltrim($mac,'addr="');
	$mac = rtrim($mac, '" addrtype');
	print "MAC Address: $mac<br>";
	}
	
	//Get Hostname
	if (strpos($line, 'type="PTR"') == TRUE){
	preg_match('/name=".*" type/',$line,$results);
	$hostname = implode(" ",$results);
	$hostname = ltrim($hostname,'name="');
	$hostname = rtrim($hostname, ' type');
	$hostname = rtrim($hostname, '"');
	print "Hostname:  $hostname<br>";
	}
	
	//Get Ports
	if (strpos($line, 'portid="') == TRUE){
	preg_match('/portid=".*><state/',$line,$results);
	$port = implode(" ",$results);
	$port = ltrim($port,'portid="');
	$port = rtrim($port, '"><state');
	print "Port: $port<br>";
	array_push($portArray, $port);
	}
	
	//Add Values to Database
	if (strpos($line, '/host>') == TRUE){
	$timestamp = time();	
	$portList = implode(", ",$portArray);
	$sql = "insert into log(ip,mac,vendor,hostname,ports,timestamp) values ('$ip','$mac','$vendor','$hostname','$portList','$timestamp')";

	if ($conn->query($sql) === TRUE) {
		echo "Data Added: $ip  - $mac - $vendor - $hostname - $portList - $timestamp <br>";
	} else {
		echo "Error: ".$sql."<br>".$conn->error;
	}
	$ip = " ";
	$mac = " ";
	$vendor = " ";
	$hostname = " ";
	unset($portArray);
	$portArray = array();
	$portList = " ";
	}

}

$conn->close();

?>

nmapReport.php

<?php

$servername = "localhost";
$username = "nmap_user";
$password = "123456";
$db = "nmap";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
	die("Connection failed: ". $conn->connect_error);
}

$sql = "select * from log";
$result = $conn->query($sql);

$splitTrigger;

echo "<h1>NMAP Scan Report</h1>";

if ($result->num_rows > 0){
    echo "<table>";
while($row = $result->fetch_assoc() ){
    if ($row["timestamp"] != $splitTrigger) {
        echo "<tr><td>Scan At: ".date("h:i:sa d-m-Y",$row['timestamp'])."</td></tr>";
        $splitTrigger = $row["timestamp"];
    }
	echo "<tr><td>".$row["ip"] ."</td><td>" .$row["mac"]."</td><td>" .$row["vendor"]."</td><td>" .$row["ports"]."</td><td>" .$row["timestamp"]."</td></tr>";
}
echo "</table>";
} else {
	echo "0 records";
}
$conn->close();

?>

MySQL Setup

create database nmap;

use nmap;

create table log(
    log_id int auto_increment primary key,
    ip text,
    mac text,
    vendor text,
    hostname text,
    ports text,
    timestamp text
);

create user nmap_user identified by '123456';

grant all privileges on nmap.log to namp_user;

nmapTest.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE nmaprun>
<?xml-stylesheet href="file:///usr/bin/../share/nmap/nmap.xsl" type="text/xsl"?>
<!-- Nmap 7.80 scan initiated Fri Jul  3 14:55:38 2020 as: nmap -oX /var/www/html/nmapTest.xml 192.168.1.0/24 -->
<nmaprun scanner="nmap" args="nmap -oX /var/www/html/nmapTest.xml 192.168.1.0/24" start="1593788138" startstr="Fri Jul  3 14:55:38 2020" version="7.80" xmloutputversion="1.04">
<scaninfo type="syn" protocol="tcp" numservices="1000" services="1,3-4,6-7,9,13,17,19-26,30,32-33,37,42-43,49,53,70,79-85,88-90,99-100,106,109-111,113,119,125,135,139,143-144,146,161,163,179,199,211-212,222,254-256,259,264,280,301,306,311,340,366,389,406-407,416-417,425,427,443-445,458,464-465,481,497,500,512-515,524,541,543-545,548,554-555,563,587,593,616-617,625,631,636,646,648,666-668,683,687,691,700,705,711,714,720,722,726,749,765,777,783,787,800-801,808,843,873,880,888,898,900-903,911-912,981,987,990,992-993,995,999-1002,1007,1009-1011,1021-1100,1102,1104-1108,1110-1114,1117,1119,1121-1124,1126,1130-1132,1137-1138,1141,1145,1147-1149,1151-1152,1154,1163-1166,1169,1174-1175,1183,1185-1187,1192,1198-1199,1201,1213,1216-1218,1233-1234,1236,1244,1247-1248,1259,1271-1272,1277,1287,1296,1300-1301,1309-1311,1322,1328,1334,1352,1417,1433-1434,1443,1455,1461,1494,1500-1501,1503,1521,1524,1533,1556,1580,1583,1594,1600,1641,1658,1666,1687-1688,1700,1717-1721,1723,1755,1761,1782-1783,1801,1805,1812,1839-1840,1862-1864,1875,1900,1914,1935,1947,1971-1972,1974,1984,1998-2010,2013,2020-2022,2030,2033-2035,2038,2040-2043,2045-2049,2065,2068,2099-2100,2103,2105-2107,2111,2119,2121,2126,2135,2144,2160-2161,2170,2179,2190-2191,2196,2200,2222,2251,2260,2288,2301,2323,2366,2381-2383,2393-2394,2399,2401,2492,2500,2522,2525,2557,2601-2602,2604-2605,2607-2608,2638,2701-2702,2710,2717-2718,2725,2800,2809,2811,2869,2875,2909-2910,2920,2967-2968,2998,3000-3001,3003,3005-3007,3011,3013,3017,3030-3031,3052,3071,3077,3128,3168,3211,3221,3260-3261,3268-3269,3283,3300-3301,3306,3322-3325,3333,3351,3367,3369-3372,3389-3390,3404,3476,3493,3517,3527,3546,3551,3580,3659,3689-3690,3703,3737,3766,3784,3800-3801,3809,3814,3826-3828,3851,3869,3871,3878,3880,3889,3905,3914,3918,3920,3945,3971,3986,3995,3998,4000-4006,4045,4111,4125-4126,4129,4224,4242,4279,4321,4343,4443-4446,4449,4550,4567,4662,4848,4899-4900,4998,5000-5004,5009,5030,5033,5050-5051,5054,5060-5061,5080,5087,5100-5102,5120,5190,5200,5214,5221-5222,5225-5226,5269,5280,5298,5357,5405,5414,5431-5432,5440,5500,5510,5544,5550,5555,5560,5566,5631,5633,5666,5678-5679,5718,5730,5800-5802,5810-5811,5815,5822,5825,5850,5859,5862,5877,5900-5904,5906-5907,5910-5911,5915,5922,5925,5950,5952,5959-5963,5987-5989,5998-6007,6009,6025,6059,6100-6101,6106,6112,6123,6129,6156,6346,6389,6502,6510,6543,6547,6565-6567,6580,6646,6666-6669,6689,6692,6699,6779,6788-6789,6792,6839,6881,6901,6969,7000-7002,7004,7007,7019,7025,7070,7100,7103,7106,7200-7201,7402,7435,7443,7496,7512,7625,7627,7676,7741,7777-7778,7800,7911,7920-7921,7937-7938,7999-8002,8007-8011,8021-8022,8031,8042,8045,8080-8090,8093,8099-8100,8180-8181,8192-8194,8200,8222,8254,8290-8292,8300,8333,8383,8400,8402,8443,8500,8600,8649,8651-8652,8654,8701,8800,8873,8888,8899,8994,9000-9003,9009-9011,9040,9050,9071,9080-9081,9090-9091,9099-9103,9110-9111,9200,9207,9220,9290,9415,9418,9485,9500,9502-9503,9535,9575,9593-9595,9618,9666,9876-9878,9898,9900,9917,9929,9943-9944,9968,9998-10004,10009-10010,10012,10024-10025,10082,10180,10215,10243,10566,10616-10617,10621,10626,10628-10629,10778,11110-11111,11967,12000,12174,12265,12345,13456,13722,13782-13783,14000,14238,14441-14442,15000,15002-15004,15660,15742,16000-16001,16012,16016,16018,16080,16113,16992-16993,17877,17988,18040,18101,18988,19101,19283,19315,19350,19780,19801,19842,20000,20005,20031,20221-20222,20828,21571,22939,23502,24444,24800,25734-25735,26214,27000,27352-27353,27355-27356,27715,28201,30000,30718,30951,31038,31337,32768-32785,33354,33899,34571-34573,35500,38292,40193,40911,41511,42510,44176,44442-44443,44501,45100,48080,49152-49161,49163,49165,49167,49175-49176,49400,49999-50003,50006,50300,50389,50500,50636,50800,51103,51493,52673,52822,52848,52869,54045,54328,55055-55056,55555,55600,56737-56738,57294,57797,58080,60020,60443,61532,61900,62078,63331,64623,64680,65000,65129,65389"/>
<verbose level="0"/>
<debugging level="0"/>
<host starttime="1593788138" endtime="1593788180"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.1" addrtype="ipv4"/>
<address addr="10:0C:6B:0D:8C:ED" addrtype="mac" vendor="Netgear"/>
<hostnames>
<hostname name="_gateway" type="PTR"/>
</hostnames>
<ports><extraports state="closed" count="997">
<extrareasons reason="resets" count="997"/>
</extraports>
<port protocol="tcp" portid="53"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="domain" method="table" conf="3"/></port>
<port protocol="tcp" portid="80"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http" method="table" conf="3"/></port>
<port protocol="tcp" portid="5000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="upnp" method="table" conf="3"/></port>
</ports>
<times srtt="400" rttvar="43" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788165"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.2" addrtype="ipv4"/>
<address addr="80:EA:96:E8:72:1B" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="998">
<extrareasons reason="resets" count="998"/>
</extraports>
<port protocol="tcp" portid="5009"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="airport-admin" method="table" conf="3"/></port>
<port protocol="tcp" portid="10000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="snet-sensor-mgmt" method="table" conf="3"/></port>
</ports>
<times srtt="877" rttvar="253" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788186"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.3" addrtype="ipv4"/>
<address addr="8C:86:1E:B1:34:6F" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="999">
<extrareasons reason="resets" count="999"/>
</extraports>
<port protocol="tcp" portid="62078"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="iphone-sync" method="table" conf="3"/></port>
</ports>
<times srtt="6530" rttvar="1735" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788187"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.8" addrtype="ipv4"/>
<address addr="D0:03:4B:03:BA:07" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="993">
<extrareasons reason="resets" count="993"/>
</extraports>
<port protocol="tcp" portid="5000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="upnp" method="table" conf="3"/></port>
<port protocol="tcp" portid="7000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="afs3-fileserver" method="table" conf="3"/></port>
<port protocol="tcp" portid="7100"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="font-service" method="table" conf="3"/></port>
<port protocol="tcp" portid="49152"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="49153"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="49154"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="62078"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="iphone-sync" method="table" conf="3"/></port>
</ports>
<times srtt="4166" rttvar="1223" to="100000"/>
</host>
<host starttime="1593788140" endtime="1593788162"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.11" addrtype="ipv4"/>
<address addr="90:72:40:05:C9:15" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="filtered" count="1000">
<extrareasons reason="admin-prohibiteds" count="999"/>
<extrareasons reason="no-response" count="1"/>
</extraports>
</ports>
<times srtt="903" rttvar="406" to="100000"/>
</host>
<host starttime="1593788140" endtime="1593788180"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.12" addrtype="ipv4"/>
<address addr="28:80:88:47:C1:08" addrtype="mac" vendor="Netgear"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="998">
<extrareasons reason="resets" count="998"/>
</extraports>
<port protocol="tcp" portid="53"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="domain" method="table" conf="3"/></port>
<port protocol="tcp" portid="80"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http" method="table" conf="3"/></port>
</ports>
<times srtt="3295" rttvar="717" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788169"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.13" addrtype="ipv4"/>
<address addr="34:64:A9:44:C8:62" addrtype="mac" vendor="Hewlett Packard"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="992">
<extrareasons reason="resets" count="992"/>
</extraports>
<port protocol="tcp" portid="80"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http" method="table" conf="3"/></port>
<port protocol="tcp" portid="139"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="netbios-ssn" method="table" conf="3"/></port>
<port protocol="tcp" portid="443"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="https" method="table" conf="3"/></port>
<port protocol="tcp" portid="445"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="microsoft-ds" method="table" conf="3"/></port>
<port protocol="tcp" portid="631"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="ipp" method="table" conf="3"/></port>
<port protocol="tcp" portid="8080"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http-proxy" method="table" conf="3"/></port>
<port protocol="tcp" portid="9100"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="jetdirect" method="table" conf="3"/></port>
<port protocol="tcp" portid="9220"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
</ports>
<times srtt="7959" rttvar="1583" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788180"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.14" addrtype="ipv4"/>
<address addr="28:80:88:47:C0:18" addrtype="mac" vendor="Netgear"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="998">
<extrareasons reason="resets" count="998"/>
</extraports>
<port protocol="tcp" portid="53"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="domain" method="table" conf="3"/></port>
<port protocol="tcp" portid="80"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http" method="table" conf="3"/></port>
</ports>
<times srtt="4306" rttvar="658" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788161"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.15" addrtype="ipv4"/>
<address addr="94:F6:D6:F3:DC:A6" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="999">
<extrareasons reason="resets" count="999"/>
</extraports>
<port protocol="tcp" portid="49175"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
</ports>
<times srtt="394" rttvar="190" to="100000"/>
</host>
<host starttime="1593788140" endtime="1593788162"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.17" addrtype="ipv4"/>
<address addr="F0:18:98:24:D0:BC" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="999">
<extrareasons reason="resets" count="999"/>
</extraports>
<port protocol="tcp" portid="49152"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
</ports>
<times srtt="7472" rttvar="2105" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788184"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.19" addrtype="ipv4"/>
<address addr="D4:90:9C:D4:C5:68" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="994">
<extrareasons reason="resets" count="994"/>
</extraports>
<port protocol="tcp" portid="5000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="upnp" method="table" conf="3"/></port>
<port protocol="tcp" portid="7000"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="afs3-fileserver" method="table" conf="3"/></port>
<port protocol="tcp" portid="7100"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="font-service" method="table" conf="3"/></port>
<port protocol="tcp" portid="49152"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="49153"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="62078"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="iphone-sync" method="table" conf="3"/></port>
</ports>
<times srtt="6788" rttvar="1814" to="100000"/>
</host>
<host starttime="1593788138" endtime="1593788162"><status state="up" reason="arp-response" reason_ttl="0"/>
<address addr="192.168.1.21" addrtype="ipv4"/>
<address addr="68:5B:35:AA:61:2B" addrtype="mac" vendor="Apple"/>
<hostnames>
</hostnames>
<ports><extraports state="closed" count="996">
<extrareasons reason="resets" count="996"/>
</extraports>
<port protocol="tcp" portid="445"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="microsoft-ds" method="table" conf="3"/></port>
<port protocol="tcp" portid="548"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="afp" method="table" conf="3"/></port>
<port protocol="tcp" portid="49152"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
<port protocol="tcp" portid="49153"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="unknown" method="table" conf="3"/></port>
</ports>
<times srtt="375" rttvar="80" to="100000"/>
</host>
<host starttime="1593788187" endtime="1593788187"><status state="up" reason="localhost-response" reason_ttl="0"/>
<address addr="192.168.1.23" addrtype="ipv4"/>
<hostnames>
<hostname name="ubuntu" type="PTR"/>
</hostnames>
<ports><extraports state="closed" count="998">
<extrareasons reason="resets" count="998"/>
</extraports>
<port protocol="tcp" portid="22"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="ssh" method="table" conf="3"/></port>
<port protocol="tcp" portid="80"><state state="open" reason="syn-ack" reason_ttl="64"/><service name="http" method="table" conf="3"/></port>
</ports>
<times srtt="29" rttvar="0" to="100000"/>
</host>
<runstats><finished time="1593788187" timestr="Fri Jul  3 14:56:27 2020" elapsed="49.48" summary="Nmap done at Fri Jul  3 14:56:27 2020; 256 IP addresses (13 hosts up) scanned in 49.48 seconds" exit="success"/><hosts up="13" down="243" total="256"/>
</runstats>
</nmaprun>

Be the first to comment

Leave a Reply