{"id":80,"date":"2018-09-29T06:44:52","date_gmt":"2018-09-29T06:44:52","guid":{"rendered":"http:\/\/www.spktechfit.com\/?p=80"},"modified":"2018-09-29T06:44:52","modified_gmt":"2018-09-29T06:44:52","slug":"how-to-export-spiceworks-reports-to-html-using-powershell","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=80","title":{"rendered":"How To Export Spiceworks Reports to HTML Using Powershell"},"content":{"rendered":"
\n
\n

Introduction<\/h1>\n

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.<\/p>\n<\/div>\n<\/div>\n

\n
\n

Step 1: Create your SQL Query<\/h2>\n<\/div>\n
\n

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\u00a0http:\/\/sqlitebrowser.org\/<\/a>\u00a0. This will show the database schema and allow you to build and test your queries.<\/p>\n

A sample query to show all current open tickets:
\nselect
\n(select (first_name || ‘ ‘ || last_name) as ‘Created By’ from users where tickets.created_by=users.id) AS ‘Created By’
\n, SUBSTR(summary,1,255) AS Summary
\n,tickets.created_at as ‘Created’
\nfrom
\ntickets
\nwhere status = ‘open’<\/p>\n<\/div>\n

\n

Step 2: Install the PSSQLite Module<\/h2>\n<\/div>\n
\n

if you have PowerShell 5, or the PowerShellGet module, do the following:
\n– Open Powershell as an Administrator
\n– Run the following command:\u00a0Install-Module PSSQLite<\/p>\n

– Then run the following to import the module: Import-Module PSSQLite<\/p>\n

\"\"<\/p>\n

Alternatively, you can download the module, extract it, then import it. See the following for more instructions on that:\u00a0\u00a0https:\/\/github.com\/RamblingCookieMonster\/PSSQLite#instructions<\/a><\/p>\n

\n
\n
\n

Step 3: Set the datasource as your Spiceworks database copy<\/h2>\n<\/div>\n
\n

Create a variable and assign it the path to your copy of the Spiceworks database.
\nEG:
\n$DataSource = “C:\\Backup\\Spiceworks_Prod.db”<\/em>\"\"<\/p>\n<\/div>\n<\/div>\n

\n
\n

Step 4: Setup your Query in Powershell<\/h2>\n<\/div>\n
\n

Create a variable and assign it the string of your SQL query.
\nEG:
\n$Query = “select\u00a0<\/em>
\n(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\u00a0<\/em>
\n, SUBSTR(summary,1,255) AS Summary\u00a0<\/em>
\n,tickets.created_at as ‘Created’\u00a0<\/em>
\nfrom\u00a0<\/em>
\ntickets\u00a0<\/em>
\nwhere status = ‘open’\u00a0<\/em>
\n“<\/em>\"\"<\/p>\n<\/div>\n<\/div>\n

\n
\n

Step 5: Run the query and export to HTML<\/h2>\n<\/div>\n
\n

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.<\/p>\n

Invoke-SqliteQuery -DataSource $DataSource -Query $Query | ConvertTo-html -Title “Daily Report” -Body (get-date) > C:\\SpiceworksReport.htm<\/em>\"\"<\/p>\n<\/div>\n<\/div>\n<\/div>\n

\n

References<\/h2>\n