PowerShell, Spiceworks, Technology

How To Export Spiceworks Reports to HTML Using Powershell

Introduction

This allows you to query the Spiceworks database and save the results as a HTML report. It uses the PSSQLite Powerhsell module and then the ‘ConvertTo-HTML’ command to output it to HTML.

Step 1: Create your SQL Query

It is best to run your test query’s against a copy of your Spiceworks database, not your live database. There are a number of tools that allow you to query and browse the database. I like to use http://sqlitebrowser.org/ . This will show the database schema and allow you to build and test your queries.

A sample query to show all current open tickets:
select
(select (first_name || ‘ ‘ || last_name) as ‘Created By’ from users where tickets.created_by=users.id) AS ‘Created By’
, SUBSTR(summary,1,255) AS Summary
,tickets.created_at as ‘Created’
from
tickets
where status = ‘open’

Step 2: Install the PSSQLite Module

if you have PowerShell 5, or the PowerShellGet module, do the following:
– Open Powershell as an Administrator
– Run the following command: Install-Module PSSQLite

– Then run the following to import the module: Import-Module PSSQLite

Alternatively, you can download the module, extract it, then import it. See the following for more instructions on that:  https://github.com/RamblingCookieMonster/PSSQLite#instructions

Step 3: Set the datasource as your Spiceworks database copy

Create a variable and assign it the path to your copy of the Spiceworks database.
EG:
$DataSource = “C:\Backup\Spiceworks_Prod.db”

Step 4: Setup your Query in Powershell

Create a variable and assign it the string of your SQL query.
EG:
$Query = “select 
(select (first_name || ‘ ‘ || last_name) as ‘Created By’ from users where tickets.created_by=users.id) AS ‘Created By’ — gets users first name and last name on the id number 
, SUBSTR(summary,1,255) AS Summary 
,tickets.created_at as ‘Created’ 
from 
tickets 
where status = ‘open’ 

Step 5: Run the query and export to HTML

At this stage we invoke the PSSQLite command using the datasource and query we created, then export it to html using the ConvertTo-html command.

Invoke-SqliteQuery -DataSource $DataSource -Query $Query | ConvertTo-html -Title “Daily Report” -Body (get-date) > C:\SpiceworksReport.htm

Leave a Reply

Your email address will not be published. Required fields are marked *