When I first started to use Spiceworks, one of my main complaints was that you couldn’t access the data outside of the Spiceworks desktop interface. Well, turns out I was wrong.

I first had my eye’s opened from this thread on the Spiceworks forums. It informed me that it uses a SQLite database to store all its information. So, I asumed getting at the data would be easy. As with most things I was mistaken.

I wanted to use SQL Reporting Services to show the data, so the first thing I needed was an ODBC Driver. I found one at http://www.ch-werner.de/sqliteodbc/, so I downloaded and installed it. I created an ODBC entry, a new project in reporting services and pointed it to this data source, added a report, went to the Query builder to try and add some tables but to my surprise there was nothing there:

So, how was I supposed to get a list of the tables? I could have tried to write an application in dotnet to try and query it, but that sounded like hard work so I thought about using Excel instead, as it has its own in built query editor. This is where I managed to get the table information from, but it was more luck than judgement.

Open up Excel, go into the Data menu, then Import External Data, New Database Query. (if you have never used this before it will install the old Excel query app). You will then get the Choose Data Source Window, find your Spiceworks ODBC in the list and hit OK. You should get this error:

Hit OK on the error and you will drop to the Query Wizard screen. From here hit Options and you will get a list of (surprisingly) table options. At this point they will probably all be ticked. Untick them all and hit OK

Once you have hit OK, go into the Options again and this time select all (tables, views, system tables, synonyms) the options again. Then OK again. This time (as if by magic), you should get a list of all the database tables in the left hand column!

As a test, select one of the tables (say devices) and drop it into the query and get it to put the data into Excel. After a second or so the sheet should update and contain the contents of the devices table!

So, now I can get a list of the tables out (long winded as it may be), I can now use SQL Reporting services to query Spiceworks. I did I quick test by creating a reporting and doing a select * from devices and it worked a treat.

In the next part of the article I will go through writing a simple report in SQL Reporting Services.