Accessing the Spiceworks Database – Part 1

Posted by beakersoft | Posted in spiceworks | Posted on 11-03-2008

4


Header

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:

No Tables

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:

Excel 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

Table Options

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!

Database Tables

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.

Comments (4)

[…] I have found out since witting this that you can indeed query the underlying database see this post for […]

Can you post some details on using SQL Reporting Services with Spiceworks? Do you setup a System DSN? What does your Report Services “data set” config look like? Especially the “connection string” for the “data source”.

Thanks!
Mike

Hi Mike,

I’ve not done much with it in Reporting services (its on my to do list) but just create a system odbc to the sqlite database and point your connection string at that. Reporting services seemed to struggle reading the tables so thats when i kind of stopped looking

I am looking for the same information with Reporting Services, has anyone found this out?

Write a comment

Comments links could be nofollow free.