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:
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:
- Ensure that .NET 4.5 and WMF 4.0 are installed.
- Install the SQLite PowerShell Provider into %systemroot%System32WindowsPowerShellv1.0Modules
- Verify that the provider works:
import-module sqlite get-command -Module sqlite
- Place the script in a location on the server. I normally create a folder like c:scripts
- Modify the script to suit your environment. At the very least, this will be:
- Create a scheduled task to run the script. I created one that will run it at 8AM every work day.
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: