Parse nmap XML data into Mysql database

Hi,
I know there must be a way since I have seen parsers for XML data. What I would like to do is to parse XML data I capture from nmap (network scanner) and put that data into a database and normalize the data. I have thousands of workstations to scan and would like to filter the database so I only will see the data that is out of the ordinary. My problem is how to get the data from XML to Mysql.

An example of the data that comes out of nmap:

<?xml version="1.0" ?>
<?xml-stylesheet href="nmap.xsl" type="text/xsl"?>
<!-- Nmap 4.00 scan initiated Tue Feb 14 13:26:26 2006 as: nmap -sS -v -oX c:\1.xml server99 -->
<nmaprun scanner="nmap" args="nmap -sS -v -oX c:\1.xml server99" start="1139945186" startstr="Tue Feb 14 13:26:26 2006" version="4.00" xmloutputversion="1.01">
<scaninfo type="syn" protocol="tcp" numservices="1672" services="1-1027,1029-1084,1103,1109-61441,65301" />
<verbose level="1" />
<debugging level="0" />
<host><status state="up" />
<address addr="192.168.0.1" addrtype="ipv4" />
<hostnames />
<ports><extraports state="closed" count="1665" />
<port protocol="tcp" portid="135"><state state="open" /><service name="msrpc" method="table" conf="3" /></port>
<port protocol="tcp" portid="139"><state state="open" /><service name="netbios-ssn" method="table" conf="3" /></port>
<port protocol="tcp" portid="445"><state state="open" /><service name="microsoft-ds" method="table" conf="3" /></port>
<port protocol="tcp" portid="1030"><state state="open" /><service name="iad1" method="table" conf="3" /></port>
<port protocol="tcp" portid="2001"><state state="open" /><service name="dc" method="table" conf="3" /></port>
<port protocol="tcp" portid="2002"><state state="open" /><service name="globe" method="table" conf="3" /></port>
<port protocol="tcp" portid="5900"><state state="open" /><service name="vnc" method="table" conf="3" /></port>
</ports>
</host>
<runstats><finished time="1139945187" timestr="Tue Feb 14 13:26:27 2006"/><hosts up="1" down="0" total="1" />
<!-- Nmap run completed at Tue Feb 14 13:26:27 2006; 1 IP address (1 host up) scanned in 0.882 seconds -->
</runstats></nmaprun>

thanks for your help!
--dutch
LVL 1
dutch7773Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
You have to do this at the application level.  You could do it in Java, PHP, even write an XSL transformation that outputs delimited text files or SQL statements.
todd_farmerCommented:
Note that MySQL 5.1 (in alpha) supports XPath:

http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html

THat doesn't really help you load XML data to a normalized data structure, though.  It allows you to load native XML and query against nodes in the XML document, which you could then use to populate a normalized data structure, though.
dutch7773Author Commented:
Now you mention XSL, I found that on http://www.benjamin-erb.de/software/nmap_xsl/
Can I use a file like that to create the output for Mysql?
This XLS will make a real nice html page, but I want my output in a database...

I also found a parser at http://search.cpan.org/~apersaud/Nmap-Parser/Parser.pm#NAME but this is very intimidating since I'm not a Perl programmer.

I was kinda hoping for some GUI that I can drag fields to map to DB fields or something :)

thanks
--dutch
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

todd_farmerCommented:
Yes, you could use a file like that to output something like comma-delimited text that can be imported into MySQL easily.  You could even go whole-hog and create output that is a SQL statement that MySQL can execute directly.

I'm not familiar with any GUI products that can do that (but there probably are a few).  You might check in the XML topic area to see if anybody has any ideas.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ahoffmannCommented:
if you have a XSL transformation to HTML, it should be easy to change that to write SQL or simple CSV
dutch7773Author Commented:
thanks folks, it seems to me that I need to rewrite the XSL.
Do you have any pointers or documentation on this? I have no knowledge about the XSL, do have some programming experience so I'm sure I'll be able to figure it out. But if I can get some hints from you that would just give me that boost I need to get this going.
thanks
--dutch
todd_farmerCommented:
The XML topic area here would be a good starting point.  What I know about XSLT is from the Manning publication, XSLT in Action and a handful of websites I ran across through Google (nothing so great that I bookmarked them or anything).
dutch7773Author Commented:
thanks Todd! I will go to the XML area
todd_farmerCommented:
Thanks for the points!  I'm sure they can help you out with the XSLT questions much better than I (I contemplated trying, but they will do much, much better).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.