Power BI, Spiceworks, Technology

Spiceworks Reports with Power BI

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

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
‘http://servername/tickets/v2#single_ticket/’||tickets.id AS “Ticket Link”
, tickets.id as “Ticket #”
, (select (first_name || ” ” || last_name) as “Created By” from users where tickets.created_by=users.id) AS “Created By”
, (select cn as “Username” from ad_users where tickets.created_by=ad_users.user_id) AS “Username”
, summary
, category
, status
, priority
, (select (first_name || ” ” || last_name) as “Tech” from users where tickets.assigned_to=users.id) AS “Assigned to”
,tickets.created_at
, round(julianday(‘now’) – julianday(tickets.created_at),2) AS “Days Open”
from tickets
where status = ‘open’

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

 

9 thoughts on “Spiceworks Reports with Power BI

  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

Leave a Reply

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