Reporting on Spiceworks tickets via SQLite and PowerShell

We’ve been using Spiceworks as our helpdesk ticketing solution for years, as it was way better than any commercial options back then, and it’s still doing the job well.

As we’re currently attempting to put more emphasis on support through the helpdesk, I thought I’d have a look at the built-in reports. While they’re great, there was nothing that suited my requirement of emailing each helpdesk operator their open tickets on a schedule. I also have several other requirements to do with SLAs, but I’m yet to implement solutions to those.

I toyed with the idea of creating a standalone application that could be customised to run certain reports, but then I came across the SQLite PowerShell Provider on CodePlex. This works out perfectly as I can access Spiceworks’ SQLite database using PowerShell.

I believe that PowerShell is a great option for this sort of functionality as it’s easy to make changes to the functionality of the script, compared to a compiled executable.

After some strange issues, I managed to get it all working using hand-built SQL queries – for which I used this great little piece of software: SQLiteStudio.

The script gets the list of admin users in Spiceworks, and then emails them each a HTML-formatted email containing details about their open tickets. The email that the helpdesk operator receives looks like this:
Spiceworks-EmailTicketReport

Note (27 Feb 2014): I’ve updated the script to include some basic SLA support, and to split the email into multiple sections covering low, medium, and high priority tickets.

To get it working, you need to do the following on the machine that’s running Spiceworks:

  1. Ensure that .NET 4.5 and WMF 4.0 are installed.
  2. Install the SQLite PowerShell Provider into %systemroot%System32WindowsPowerShellv1.0Modules
  3. Verify that the provider works:
    import-module sqlite
    get-command -Module sqlite
  4. Place the script in a location on the server. I normally create a folder like c:scripts
  5. Modify the script to suit your environment. At the very least, this will be:
    1. $spiceworksServerName
    2. $spiceworksServerPort
    3. $spiceworksDatabaseFilePath
    4. $emailSender
    5. $emailServer
  6. Create a scheduled task to run the script. I created one that will run it at 8AM every work day.
    2014-02-12 22_58_58-mRemoteNG - FH mRemote Connection List.xml
    2014-02-12 23_00_00-mRemoteNG - FH mRemote Connection List.xml2014-02-12 23_00_28-mRemoteNG - FH mRemote Connection List.xml

A few notes:

  • Test it with dummy addresses or similar before putting it into production. Eg. Change the -To parameter of Send-MailMessage to your own email address.
  • I haven’t yet added support for SMTP auth

Grab the script here:
https://gist.github.com/dstreefkerk/8954603.js

2 thoughts on “Reporting on Spiceworks tickets via SQLite and PowerShell

  1. This works wonderfully. Thanks for posting this.
    One issue I’m having is sending emails with tickets to all users including helpdesk_tech. I do see it’s pulling those users here:
    $adminUsers = Invoke-Item spiceworks: -sql ‘select * from users where role=”admin” or role=”helpdesk_tech” or role=”helpdesk_admin”‘
    but i’m not getting those sent. Please advise

    Thank you,
    Manny Mendoza

    Like

    • Hi Manny,

      My apologies for the late reply. My blog wasn’t emailing me when it received comments, but I’ve resolved that now.

      There was a major bug in my script, so it was breaking out of the for loop if it encountered a user with no tickets, instead of continuing to the next user. Maybe that was what caused your problem?

      I’ve tested it locally, and it works fine for ‘helpdesk_tech’ users.

      If it still doesn’t work, you could always copy the SQL query you quoted in your comment into SQLiteStudio and run it against a copy of your Spiceworks DB. That would at least give you the list of users that the script is seeing. Either that, or open the script in the PowerShell ISE, insert a breakpoint at line #251, and type ‘$adminUsers | ft first_name,last_name’ in the console and hit Enter. That will give you a table of all of the users that are being pulled from SQLite.

      Regards,
      Daniel

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s