Power BI, Spiceworks, Technology

Spiceworks Reports with Power BI(with Video tutorial)

Power BI is a fantastic, and relatively easy, way of getting some great reports and analytics out of your Spiceworks database.

UPDATE: I have added this video tutorial to hopefully make this clearer and easier to follow.

Contents

Creating your queries

First you will need to know how to extract the data you are looking for. The easiest way to do this is to use something like http://sqlitebrowser.org/ to browse the database. This will show the database schema and allow you to build and test your queries. NOTE: remember to run this on a copy of your database.

 

Create the ODBC Connection to the Spiceworks database

You need to create an ODBC connector for your spiceworks database to input the data to Power BI.

Note: Its best to use a copy of your database instead of trying to connect to your production database

  1. Go to http://www.ch-werner.de/sqliteodbc/ and download the correct version for system
  2. Install using default options
  3. Go to ‘Administrative Tools -> ODBC Data Sources’
  4. Go to the ‘System DSN’ tab
  5. Click on ‘Add’
  6. Click on ‘SQLite 3 ODBC Driver’ and click Finish
  7. Give the Data Source a name and browse to the location of the database
  8. Click OK

 

Connecting to the Data Source in Power BI

  1. In Power BI click on ‘Get Data’
  2. Select ODBC as the connection type

3. Select the Data Source name from the dropdown list
4. At this stage, I find it best to enter an SQL statement, anytime I have tried to pull in the whole database I ran into a lot of errors. To enter an SQL statement click on ‘Advanced Options’
6. Paste in your statement (See sample below) and click OK.

Your data is now loaded into Power BI and you can have loads of fun creating reports and visualizations

 

——————–

Sample query

This will get the following from open tickets: ticket number as a link, creators full name, creators AD username, Summary, category, status, priority, Assigned to, date created, days open
Note: make sure to change it to your server name in the ticket link:

————————————————————–

select

tickets.id as “Ticket #”

, (select first_name as “Created By” from users where tickets.created_by=users.id) AS “Created By”

, SUBSTR(summary,1,5) AS Summary

, category

, status

, priority

,  (select first_name from users where tickets.assigned_to=users.id) AS “Assigned to”

,tickets.created_at

,tickets.closed_at

, round(julianday(tickets.closed_at) – julianday(tickets.created_at),2) AS “Days Open”

from tickets

————————————————————-

 

31 thoughts on “Spiceworks Reports with Power BI(with Video tutorial)

  1. I don’t suppose you have the queries for your example dashboard above do you? I’ve been playing with your sample script and can get some info, but not close to the great one you’ve got and it’s really quite impressive.

    1. Hi Sam,
      Yeah sure, I can give you some queries if you want. What kind of info are you looking for? What are you trying to get out of the database?

      1. Agreed. That dashboard in the screenshot is very nice and would be perfect for my organization. Could you supply the queries for those? I’ll leave my email behind.

        1. Hey Joe,
          Sorry for the reply in getting back to you. Your comment got lost in the sea of spam messages!
          Are you still lookingfor help with this?

          Cheers,
          Sean

  2. Hey Sean, whats up bro.

    Thanks for the explanation, can you send me the query that you using to build the dashboards for image on top of this tutorial?

    I really appreciate your tutorial. Thx!

  3. Hi Sean,

    I’d really appreciate it if you could share the query with me too. Thanks for the great work putting this up. Found it really useful – my first time with PowerBI.

    All the best
    Jon

    1. Hi John,
      No problem, glad to be of help. Ill send you an email with the query.

      If you need any more help with it let me know.

      Cheers,
      Sean

  4. Hi Sean

    I would appreciate if you could also share those queries with me as well.
    I’ve found this guide really useful, so thank you.

    Troy

    1. Hi Sean,

      I apologize for being a leach but could you send me the same queries the others we’re requesting?… First time using BI as well

      Thanks

      Mark

  5. Also, do you have any queries that you could share regarding live inventory of Desktops, Laptops, Servers, OS’s etc?

    Thanks

    Mark

    1. Hi Mark,
      I will send you an email with the original query.

      In terms of the inventory, I dont currently have anything but it would be easy to setup. If you can give me an idea of what you are looking for I can give it a go.

      Cheers,
      Sean

  6. Sean, If you’re still willing to share your dashboard Query’s I’d be most appreciative. I’m working on showing my boss what PowerBI can do to hopefully get some much needed attention there.

  7. Sean,

    As the others, if you are still willing, I’d also appreciate the query you used.

    Thank you!

  8. Hi Sean,

    I appreciate your insight.
    I do need your help. In following the sample query it fails for me.
    We are running the spiceworks ver7.5 with the last update was done.
    ——————-
    Error:
    Error Code : 17
    Message : database schema has changed
    near “#””: syntax error
    Result : Schema

  9. another error is :
    Execution finished with errors.
    Result: near “#””: syntax error
    At line 1:
    select

  10. Hi Sean,
    I tried to use your query and found the video very interesting.
    I don’t know much about Databases and BI, but studying SpiceWorks DB, I don’t find these columns as “Ticket #” or even “Created By” I always get errors …. Although when exporting the reports directly in spiceworks, using XLS or CSV both have these columns.
    Could you give me an idea of what to do?
    BR, from Brazil.

    1. Hi Bruno,
      Thanks for checking out my blog.

      Where it uses “as” in the query, they are variable names for columns. So you won’t see those columns in the database.

      Where it says ‘tickets.id as “Ticket #’, the column name is ‘id’ in the ‘tickets’ table.

      Hope that makes sense. Let me know if you have any other issues. I’ll send you an email which you can reply to if you want to send me a screenshots of your issues.

      Cheers,
      Sean

  11. Hello Sean! Just another guy who would like that query for the image for the top. Do you think you could hook me up?

Leave a Reply to sean Cancel reply

Your email address will not be published. Required fields are marked *