Import Spiceworks users into FreshDesk

I found that the simplest way to migrate Spiceworks users into FreshDesk as contacts is as follows:

  1. In FreshDesk, set up companies (if required). This will ensure that contacts are assigned to the relevant company upon creation.
  2. Export the Spiceworks users to CSV. I used SQLite Studio.
    1. Run the query (see the bottom of this article)
    2. Copy the results from SQLite Studio, and paste into Excel
    3. Add a header row at the top of the Excel worksheet with the following headings in this order:
      1. email
      2. name
      3. title
      4. mobile
      5. phone
      6. time_zone
      7. language
    4. Save the CSV
  3. Import the CSV into FreshDesk. It will ask you to map the fields, but we’ve named the fields identically anyway so this will be simple.

Note: If you want to see which fields are valid to use for contact import, access the following URL http://{your_helpdesk_url}/admin/contact_fields.json

I use the JSON Formatter extension for Chrome to make JSON readable.

Here’s the query for SQLite. Note that this will only get users who have created tickets in the past two years:

select distinct as email,
u.first_name || ' ' || u.last_name as name,
u.title as job_title,
u.cell_phone as mobile,
u.office_phone as phone,
u.location as time_zone,
'en' as language
from users as u
inner join tickets as t on = t.created_by
where (u.disabled is null) and ((u.first_name is not null) and (u.last_name is not null))
and (t.created_at > date('now','-2 years'))
order by u.last_name

Dashboarding Spiceworks with PowerShell


So, we needed a better way to keep an eye on our Spiceworks helpdesk tickets. I’d seen Geckoboard around, and decided to have a look into it.

What sold it for me was the fact that Geckoboard have a Push API that would allow me to push data to the dashboard widgets, thus avoiding the need to create some sort of webservice that I have to open ports on the firewall to. The other thing with a webservice is that it’s usually run with compiled code, which isn’t as flexible to maintain or for other team members to pick up.

The Geckoboard Push API uses JSON, which is easy to work with in PowerShell. I’d already worked on getting data out of Spiceworks using PowerShell for my email reports, so I had that part of it covered also. The rest was just a matter of wiring together the right queries to extract the data that I wanted to monitor.

The prerequisites for this are the same as in the previous blog post about Spiceworks and data extraction; .NET 4.5 & WMF 4.0, and the SQLite PowerShell Provider.

The basic gist of the process is this:

  1. Create a dashboard on Geckoboard
  2. Add a custom widget of your choosing. The documentation for each widget describes how the JSON data should look. Make sure you set its method to Push, and grab the Push URL and Widget key whilst you’re at it. These are used in the script in step 4.
  3. Work out the SQL query that you need to extract the data. See this blog post for how I normally do it.
  4. Write a PowerShell script that uses the query from step 3, and then posts the data to the widget
  5. Run the script in a scheduled task to push the data to Geckoboard.

Since there is one script per widget, I’ve currently got 14 scripts. What I’ve done is created a wrapper script that contains common code. This script then goes and runs all of the widget-specific scripts. The wrapper script is the one that I run in a scheduled task. Ours runs every minute.

The wrapper script:

Here’s the code for a simple widget, “Tickets Open”:

Here’s an example of a more complex widget update script that populates a list widget:

I can provide code for the other widgets if anyone’s interested in implementing a similar solution.

We’ve been running this for over 6 months now. It’s a great way of keeping on top of the helpdesk workload, and making sure that everyone’s delivering the service that they should.

Inspecting the Spiceworks Database

Recently I’ve had to write some custom queries to get data out of the Spiceworks database. I’ve found that the easiest way to do it is as follows:

  1. Open SQLite Studio (download it here)
  2. Grab a copy of the Spiceworks database, spiceworks_prod.db. It usually lives in C:Program Files (x86)Spiceworksdb on the machine running Spiceworks. The good thing is that this can be done without stopping the Spiceworks service.
  3. Add the database in SQLite, leaving all other options at their defaults, then click OK
    Screen capture of the add database screen in SQLite showing the Spiceworks DB connectioni details
  4. Right-click on the database, and click Connect

You’ll then be able to view the tables and browse their data. If I’m scripting something, I always compose and test the SQL queries here first before moving them across into my scripts.

Screen capture of tickets table contents from the spiceworks database

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.

Continue reading