Linking Power BI to the Quest K1000 SMA

We use the Quest K1000 Systems Management Appliance at Dickinson to manage many tasks, including Helpdesk ticketing, deploying patches, scripting and tracking inventory. Because of how much we use the appliance in our day to day management of devices there is a lot of data available in its database. One of the great things about the KACE appliances is the ability to connect third party reporting tools directly to the MariaDB backend with a read-only reporting user. Over the past year or so I have been creating Power BI reports using that connection and this post will describe how I setup the connection and some guidance on creating your own dashboards and reports. This blog post will focus on working locally with an ODBC connection to the appliance. In future posts I will go into how you can work collaboratively using the Power BI service and give more detail on how you can use data in the KACE SMA database more effectively using relationships and custom queries.

I'm making a few assumptions here:
You are working with ORG1 (the appliance can have multiple organizations)
There isn't a firewall blocking your machine from accessing port 3306 on your appliance
You have Dell computers in your KACE inventory

First things first - Setup the appliance

In order to connect to the database you need to enable the reporting user. In the K1 admin interface browse to Settings, Security Settings. Check the box for Enable database access. Save your changes.
Note that if you have more than one organization you will need to enable to user for each one.

Next - Setup the ODBC connection

Although the KACE database is running MariaDB the MySQL connector will be close enough. You'll need to download the MySQL ODBC Connector and configure a connection to the KACE database. See the KACE administrator guide for more details.

Fire up Power BI

You will need the desktop version of Power BI from Microsoft. Download and install the client and launch it. It should open to a blank workbook. Click the Get Data button and then select Database and MySQL database. If your ODBC connection is setup correctly you should soon be seeing a list of the tables in the KACE SMA database.

Select some tables and make a report

If you aren't already familiar with the database structure then the list you are viewing will be daunting. Let's start with something rather simple that won't require a lot of database knowledge. In the list of tables select the following tables:
ORG1.DELL_ASSET
ORG1.DELL_WARRANTY
ORG1.MACHINE

Power BI will normally attempt to detect relationships between the tables for you. Click the Manage Relationships button to see how it did. Regardless of what Power BI made you will need the following, so add them if they aren't there (you may need to delete or de-activate any relationships that are already present).
ORG1 MACHINE CSP_ID_NUMBER to ORG1 DELL_ASSET SERVICE_TAG
ORG1 DELL_ASSET SERVICE_TAG to ORG1 DELL_WARRANTY SERVICE_TAG

Power BI now has a basic understanding of how rows in the MACHINE table relate to the other tables. Let's make a simple report that shows the ship date of your Dell computers.
On the right side of the window in the Fields pane there should be a list of tables, expand the ORG1 MACHINE table and check the box next to NAME. You should get a table visualization that list the computer names in your database. Next, expand the ORG1 DELL ASSET table and check the box next to SHIP_DATE and four additional columns should appear showing Year, Quarter, Month and Day.

That's pretty exciting! Of course, you might not like the format of the date because Power BI defaults to showing dates using a date hierarchy, this is useful for other kinds of visualizations, but not so much for tables. In the Visualizations pane there should be a Values section that shows NAME and SHIP_DATE. SHIP_DATE should be expanded to show the parts, click on the arrow next to SHIP_DATE and change the option from Date Hierarchy to SHIP_DATE. Your table should update to show the dates in the standard form.

The table is still showing all computers, so to make the output look nicer you can use the filters to only show items where the SHIP_DATE is not blank. This should be an option under the Advanced filtering options.

Let's make a pretty picture using this same data. In the visualizations pane click on the Pie chart visual. Drag the SHIP_DATE column from the Fields table to the Legend section. Next, drag the ID column from the MACHINE table to the Values section. BAM! Now we have a pretty pie chart showing the number of computers shipped per year. For something even cooler, click on one of the pie sections and watch the table you created earlier only show the computers shipped in that year. #MindBlown

This is but a sampling of what you can do with Power BI and your KACE SMA data. I encourage you to try making other types of visualizations using just these three table to start. As you add fields from the different tables you will eventually find that something breaks the visuals, this is normally caused by Power BI not understanding the relationship between what is already in the table and what you just added to it. In a future post I will talk about how to get around those limitations.