Integrate vScope with power BI

You are here:--Integrate vScope with power BI

Integrate vScope with power BI

How to integrate vScope with Power BI

Extracting data from the API is a good thing. Integrating it in other systems is great. This guide will show you how to automatically populate tables, graphs and plots in Power BI.

BY ERIC NILSSON

Power BI is a powerful business analytics tool that makes it easy to build dynamic visualizations of data. The source for the visualization can be all from Excel spreadsheets, MS SQL databases or, vScope. Since vScope has an open REST API (available to any organization with a licensed vScope running) you can automatically populate Power BI with data Discovered by vScope. Much more convenient than building and managing manual spreadsheets.

Integrating vScope data into Power BI

In this guide I will show you how to set up Power BI to pull any data from vScope! We will integrate using vScope’s build in API. This will also be done in a secure way using an API Token.

Prerequisites

In order to securely connect to vScope’s API we need an API Token. Check out this guide on how to create and manage API Tokens in vScope: How to pull data from vScope via the API using a token. And of course, you also need to have Power BI installed 😉

Download Power BI

Creating a source table in vScope

To start off, we need to create and save a ”source” table in vScope. Once saved, this table will function as the source to what data that should show up in Power BI. When integrated in Power BI, vScope will automatically populate Power BI with all cell values, changes or new columns added.

Let’s build a simple table about All Machines to start with.

Click ”New Table” and select the ”All Machines” resource from the dropdown.

power bi integration

Second step, add some columns. I will keep it very simple here just to show you the basics. So let’s add ”IP” and ”CPU Usage” to our table.

power bi integration

Last step is to add filters. I’ll keep it simple in this step too. Only list machines with Running = true.

power bi integration

Now, the important part is not what columns and filters you choose to add. What you are going for is basically a TableID that will be used to target what table (and data) that should be extracted from vScope. In order to get a TableID you will need to save the table. So, Hit ”Save Table” and name your table to e.g. Source Table For Power BI.

power bi integration

Well done! The TableID can now be found in the Permalink stored under the ”Collaborators” settings:

power bi integration

(Tip: this ID can also be found by going into your browser’s console and enter: vscope.dumpPreset())

Notice that this is a Permalink. Any customizations of the table that are saved will be reflected in the data imported to Power BI or any other system, that is new columns or filters etc.  Anyway, copy the ID and move over to the next step.

(Tip#2: All saved tables can also be found by doing a GET /rest/tables?presetType=user)

Importing the data to Power BI

Now tab to Power BI. Power BI can import data from a variety of sources like databases, Azure, files and API endpoints. The neat thing here is that vScope’s API endpoints includes e.g. exporting a table in Microsoft XLSX format. Using this endpoint will allow us to easily get the data recognized and correctly formatted by Power BI.

You find all API documentation under

http://[YOUR-VSCOPE]/apidoc/

but let me give you a shortcut here: This is the endpoint that you will use.

GET https://{YOUR-VSCOPE}/rest/v1/tables/{YOUR-TABLEID}/data/xlsx

Substitute {YOUR-TABLEID} for the ID of the table you just created. And {YOUR-VSCOPE} with your vScope address.

In my case my endpoint will look like this:

GET https://vscope/rest/v1/tables/1504087939270/data/xlsx

Under the Home tab, click Get Data  in the top bar and select Web

power bi integration

Select the advanced tab and fill in your endpoint’s URL. In order to get access to the vScope you must add the token for your vScope installation (found under Settings -> API tokens). Make sure that the token is for a user that have access to the table. Copy the token and paste it under the heading ”HTTP request header parameters (optional)”. Name the header token. It should look something like this:

power bi integration

Of course, the TableID and token will look different on your screen.

Brace yourself. Now, hit OK!

If successful you should get a preview of the data that Power BI received from vScope.

power bi integration

If everything is okay, click on the checkbox next to your table name inte the list to the left, then press Load.  Congratulations, you’ve created your first Power BI table that is populated with data from vScope!

power bi integration

Visualizing the data in PowerBI

The data can be used just as any other data in Power BI. The great thing however is that the table we just created in Power BI is linked to the (source) table in vScope. So for instance if you want to add a column to the Power BI table, just add it to the table in vScope:

power bi integration

save the table..

power bi integration

Refresh in Power BI (in the top menue) and the new column will appear. Magic!

power bi integration

power bi integration

Of course any new resources or changes that are discovered by vScope will also update the table in Power BI.

This process can be repeated as many times as you want. Just to show off, this is an example of three tables that I created charts for while playing around with Power BI.

power bi integration

Ending notes

Once learned, extracting data from vScope via the API can be done in numerous ways. Let vScope take care of collecting, structuring and relating the data and use the API to get the insights elsewhere. In this guide I’ve shown how this could be done in Power BI helping you to automate reporting and better visualizing what your IT really looks like. However with basically the same maneuvers you can connect vScope to many other BI systems, CMDBs, Excel, Helpdesks etc. So don’t silo your data. Instead, use integration to get the most out of it and make it accessible for anyone who needs it the most.

Eric Nilsson

Front-end Developer

Sign up for newsletter

Join our community and 1000+ IT professionals by signing up for blogs, news and business insight through our newsletter

2018-04-03T07:41:57+00:00