Pulling Raw Data from Alienvault's OSSIM MySQL database

I've been playing with OSSIM from alienvault.com (great product), and found myself needing to get automated access to some of the raw asset data that OSSIM was collecting for import into another tool.  What I found was that it was stored in a MySQL database backend, which allowed me to query the data and export it.

To access the MySQL database, you have to first login via SSH to the OSSIM host, and "jailbreak" to the shell.  Once there, you can get the MySQL root password by examining the contents of

/etc/ossim/server/config.xml

Then connect to the database:

mysql -p alienvault

Once there, I worked out that the IP was stored in hexadecimal, and the data that I needed was spread across a couple of tables.  A little SQL magic, and I could get all the data into the string format I wanted.  Here's an example of getting the IP and MAC address:

mysql> select inet_ntoa(conv(HEX(ip),16,10)) as ip,
    -> CONCAT_WS(':',SUBSTR(HEX(mac),1,2),
    -> SUBSTR(HEX(mac),3,2), SUBSTR(HEX(mac),5,2),
    -> SUBSTR(HEX(mac),7,2), SUBSTR(HEX(mac),9,2),
    -> SUBSTR(HEX(mac),11,2)) as mac
    -> from host_ip;
+----------------+-------------------+
| ip             | mac               |
+----------------+-------------------+
| 10.1.1.1       |                   |
| 192.168.56.102 | 08:00:27:D9:81:0E |
| 192.168.56.1   | 0A:00:27:00:00:00 |
+----------------+-------------------+
3 rows in set (0.00 sec)

May not be the most efficient way, but it worked. There is plenty of other data you could grab within these tables.