The Surly Admin

Spiceworks Reporting and Powershell

The Spiceworks reporting engine is pretty good, and the web interface they developed for it will meet a lot of your needs.  And with the ability to share  your reports with others there are literally thousands of reports out there for you to choose from.  Then they introduced SQL based reporting and really opened up what you could do.  But wouldn’t it be nice to bring that data into Powershell?  Read on to see how you can do this.

What’s Out There

There’s actually a lot of information already out there for reporting in Spiceworks and you can even script your reports and then send them as emails.  I won’t go into any real detail on how to do that, so before you start pulling Spiceworks data into your Powershell scripts consider these alternatives first as it might get what you want done much quicker and easier.  That said, once you can get the data into a Powershell object your reporting options expand.

What You Need

The first thing we’re going to need to pull data from Spiceworks into Powershell is the SQLite3.exe program.  This is provided by the SQLite project and can be found here.  You’ll need to scroll down to the “Precompiled Binaries for Windows” and it’s the first download there:  sqlite-shell-win32-x86-.  I like to extract the SQLite3.exe program and save it in my Spiceworks database path (your path may differ depending on your environment):

C:\Program Files\Spiceworks\db

Next thing to consider is the Spiceworks database itself.  The name of the database is Spiceworks_Prod.db, and while I’ve run many a query against it without issue Spiceworks themselves recommend to not use it for external reporting purposes.  You can read about it at this thread.  So if we can’t use the Spiceworks_Prod.db what do we do?  Well, luckily there’s a pretty easy answer.  When you run the Spiceworks automated backup (and you do, right?) it will create a new file called Spiceworks_db.back.  Well, it turns out we can run our reports just fine off of this file.  I set my backups for nightly so I always have a fresh backup file.  Your reports might not be “real-time” but you’ll be within a day of fresh data.

SQL Queries

Now that we have SQLite3.exe and our database to work off of we’re going to need a report.  We won’t be able to use the normal canned reports in Spiceworks, or even most of the custom ones because that’s using a format that SQLite3.exe can’t use.  For our Powershell reports we need SQL based reports.  There are actually quite a few SQL reports on Spiceworks that users have shared so you can use those, but let’s start with a simple one for now.

Select
  id as Ticket,
  summary as Summary,
  created_at as "Created On"
From tickets
Where status="open"

Pretty simple query to look at the “tickets” table and show us some information on all open tickets.  If you go into the Reports section in Spiceworks, click on “New Report”, give it a name and set it to a SQL report then copy and paste the above in the editor you should end up with something that looks like:

The interesting thing is you can run SQL queries using SQLite3.exe and it will produce a delimited text file with the output from the query.  The output is essential a CSV (comma separated values) but instead of using a comma for the delimiter it is using the pipe symbol.  To get this data into Powershell we take advantage of this output and the fact that Powershell isn’t picky about where it’s cmdlet’s get their information.

The first challenge is to get SQLite3.exe to run from within Powershell.  We’ll need to define where the database is and what the query we want to run is.  Here’s how:

$PathToSWdb = "\\server\c$\program files\spiceworks\db"
$Query = @"
  Select
    id as Ticket,
    summary as Summary,
    created_at as 'Created On'
  From tickets
  Where status='open'
"@

& $PathToSWdb\sqlite3.exe $PathToSWdb\spiceworks_db.back $Query

For the ultra-observant crowd you’ll notice I changed the double quotes in the query to single quotes.  I can’t stress how important this is!!  Double quotes will cause SQLite3.exe to have a problem as it will think that is the end of the query, which will malform the query and cause an error.  You should have gotten data back like this:

Since this is essentially a CSV, we can now use the ConvertFrom-CSV cmdlet to pull this data into a PSCustomObject, but there are a couple of caveats we need to deal with first.  The first is SQLite3.exe does not export any header information so we’ll have to provide that ourselves with the -Header parameter.  Second ConvertFrom-CSV will be expecting to use a comma for the delimiter so we’ll need to tell it to use the pipe symbol instead using the -Delimiter parameter.

$myData = ConvertFrom-CSV ( & $PathToSWdb\sqlite3.exe $PathToSWdb\spiceworks_db.back $Query ) -Delimiter "|" -Header TicketNum,Summary,Opened

Let’s take a quick look at our new array of objects:

You can see our three properties, and their variable type (System.String).  You can now manipulate this object like you would any other!  You can pipe it into a Where clause to further filter it down, send it to Out-Gridview to see the data in a nice interactive window.

Here’s the data piped into a Format-Table:

One thing to keep in mind, all of your data is a string type, so if you need to look at it differently (such as changing the Opened property to a DateTime type) you will have to deal with that.  One method would be to set up a simple loop and convert the data, or you can convert it whenever you have to deal with it.  Here’s a quick example of the loop method:

ForEach ($Line in $myData)
{  $Line.Opened = [datetime]$Line.Opened
}

Nothing too fancy, but gets the job done.

Checklist

1. Download SQLite3.exe and place in Spiceworks db path
2. Backup Spiceworks daily and use the Spiceworks_db.back file for reporting
3. Put your query in a here-string
4. Replace all double quotes with single quotes
5. Convert properties as needed

Combine this technique with the HTML reporting techniques, including the Set-AlternatingRows function, and you can get some pretty nice customized Spiceworks reporting.

There’s also a SpiceWorks How-To you can look at too:  Pull Spiceworks Data into PowerShell

February 4, 2013 - Posted by Martin9700 | PowerShell | html report, Reporting, spiceworks, sqlite3.exe