Contents
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
