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 built-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 😉
Creating a source table in vScope
To start, we need to create and save a “source” table in vScope. Once saved, this table will function as the source of what data should show up in Power BI. When integrated with 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.
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.
The last step is to add filters. I’ll keep it simple in this step too. Only list machines with Running = true.
Now, the important part is not what columns and filters you choose to add. What you are going for is a TableID that will be used to target what table (and data) should be extracted from vScope. To get a TableID you will need to save the table. So, Hit “Save Table” and name your table e.g. Source Table For Power BI.
Well done! The TableID can now be found in the Permalink stored under the “Collaborators” settings:
(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 include 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
Select the Advanced tab and fill in your endpoint’s URL. 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 has 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:
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.
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!
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:
save the table..
Refresh in Power BI (in the top menu) and the new column will appear. Magic!
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.
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 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 to anyone who needs it the most.