Dashboarding Spiceworks with PowerShell

Geckoboard-Spiceworks

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:

https://gist.github.com/dstreefkerk/0aecf241bc86ed17bb68.js

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

https://gist.github.com/dstreefkerk/0678538586a8682b3252.js

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

https://gist.github.com/dstreefkerk/d8ebafbabf735bc9002f.js

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.

7 thoughts on “Dashboarding Spiceworks with PowerShell

  1. I’d be interested in seeing your other scripts. You did a great job on the dash board btw. Are you availible for hire to help create some custom reports via email or dashboards? If so whats your hourly rate.

    Thanks for the helpful blog!

    Like

      • Definitely. I’ll put them up online as soon as I get the chance to review them. I’m quite busy at the moment. Hopefully in the next week or so.

        Like

  2. Would also love to have the other PS scripts! That dashboard is exactly the data i’d love to “hang” on our door/TV.

    Like

    • You can grab the Zip file from here: https://dl.dropboxusercontent.com/u/5710807/Uploads/Blog/Spiceworks-Geckoboard.zip

      1. Extract the Zip file
      2. Edit Update-GeckoBoard_Spiceworks_Dashboard.ps1
      2.1 Specify the path to the Scripts folder from the Zip file in the variable $scriptLocation
      2.2 Enter your API key in the $apikey variable
      3. Edit each of the files within the scripts folder, and enter the correct widget key in the $widgetId variable
      4. Open Update-GeckoBoard_Spiceworks_Dashboard.ps1 in the PowerShell ISE and run it. Any errors from the “sub scripts” will appear in the output pane.
      5. Create a scheduled task to run Update-GeckoBoard_Spiceworks_Dashboard.ps1 periodically
      6. That’s it

      I’ve included the custom widget type details in each of the sub scripts, just so you know what to set up within your own Geckoboard instances.

      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