Veeam Backup for Office 365 as DataSet for PowerBI REST API

Using Veeam Backup for Office 365 as DataSet for PowerBI using PowerBi Query.

 I did a previous post on a self-service Dashboard which is built upon Veeam’s VBO APIs here: http://www.mritsurgeon.co.za/2020/07/single-install-script-for-veeam-backup.html

I wanted to see if there was a way to use GET API method to share data with PowerBi as a data source.

I previously did a post on Veeam Backup & Replication using its config SQL DB as Data Source for PowerBI here:

http://www.mritsurgeon.co.za/2020/04/using-veeam-as-dataset-for-powerbi.html

I wanted to do something similar with Veeam backup for Office 365 but using API to populate the data source for Power BI.

So, let’s start:

Configuration:

After you have installed Veeam backup for office 365 if not already installed Here is the link:

https://www.veeam.com/backup-microsoft-office-365.html

You need to enable REST API in the options:

Top left corner Click the Hamburger menu

Select Options > REST API ( TAB ) > Enable REST Service

Then install a self-signed certificate , See the below illustration ( Screenshot )

** Notice I increased the token Life span , I did this so I don’t need to update Token while I’m creating all  my PowerBi Queries

Configuration With PowerShell

If you wanted to set the above Rest API options using PowerShell rather than the GUI , you can refer to this Video that My colleague Michael Cade, Senior Technologist, Veeam Product Strategy  for a simple Automated Install & Configuration of Veeam Backup for Office 365.

In this post i included the Option to Set Rest API part of the automation script , written by another colleague Timothy Dewin , Enterprise Systems Engineer.

Timothys Gists for Automation here : https://gist.github.com/tdewin/c2b48df494a219831cd25c0087893df6

To see the full script in action here is a recording made on Michael Cade's YouTube Channel :

SCRIPT

$hostname = "localhost"

$cert = New-SelfSignedCertificate -subject $hostname -NotAfter (Get-Date).AddYears(10) -KeyDescription "Veeam Backup for Microsoft Office 365 auto install" -KeyFriendlyName "Veeam Backup for Microsoft Office 365 auto install"

    $certfile = (join-path $path "cert.pfx")

    $securepassword = ConvertTo-SecureString "YOURPASSWORD" -AsPlainText -Force

    Export-PfxCertificate -Cert $cert -FilePath $certfile -Password $securepassword

    #log("[VBO365 Install] Enabling RESTful API service")

    Set-VBORestAPISettings -EnableService -CertificateFilePath $certfile -CertificatePassword $securepassword

REST API

Now that the REST API service is running, we can use Swagger to issue a Bearer Token that we will use for Authorization on API calls

To get the Token we must visit Swagger UI to request token, you can do this through postman or alternatives , but since Swagger is included into Veeam backup for office 365 it requires no additional installations.

To access swagger you can either use the browser to access:

https://LocalHost:4443/swagger/ui/index

Or simply through Veeam backup for office 365 Console, top left corner Click the Hamburger menu once again > Console > Swagger

This will open the same URL , illustration ( Screenshot Below )

In the API docs / Index page, navigate & expand /V4/TOKEN

In the Parameters Section fill in :

Username :

Password :

Use the same credentials as you would to login to Veeam backup for office 365 Console.

Once filled, click “ TRY IT OUT “

**You can Alternatively Run this with CURL which is shared in the same screen.

You should receive a Response code 200 & see the response body populated with data.

Copy the “Access_token”: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

We now have enough to authorize the API request *remember this Key will expire and is based upon the setting mentioned earlier in the Veeam Backup for O365 REST API options.

You can return to this page and re-generate the key if Token expires.

POWER BI Data Source Setup

Now head over to PowerBI Desktop if not installed, install from here:

https://powerbi.microsoft.com/en-us/downloads/

On Opening PowerBi Desktop app , it will ask to connect to Data Source ,

Under Get Data > select Other > Select Web > Connect

Illustration (Screenshot Below)

In the next window change to Advance for the connection.

You are then presented with more options and I will explain how to use each.

First we Input the URL that we will be requesting API commands from in URL Parts.

This will be:

https://YOURHOSTNAME:4443

When I used Localhost, I ran into TLS / Untrusted certificate errors, so I changed to hostname to avoid the errors.

Add a second URL Part with the API Object we want to call, I used “JOBS”

/v4/Jobs

If you look at the URL Preview you will see the Complete URL for the API call.

To find different URL Parts ( API Objects ) return to the Swagger URL page opened to see the available Objects to call.

See Illustration below (Screen Shot)

Once you have the required API object to call and you have added it as second URL Part , you need to set a HTTP Request Header parameter , we are going to use this to authenticate our API call.

Add header:

“Authorization”

In the Value field add Bearer And then your Token that you copied in the first Swagger section.

Authorization: Bearer <token>

Then Click ok ..

This will Open PowerBi Queries , In my example it initially showed a list of 3 sources , the 3 sources represented each job , if you click on the source it will display details about that Job.

I then copied the query twice and changed the Fx too:

=Source{0}  “ This represented Job 1 “

=Source{1}  “ This represented Job 2 “

=Source{1}  “ This represented Job 3 “

I then opened NEW source from the tool bar and repeated previous steps to connect to WEB source using different URL Parts:

/v4/Jobs

/v4/RestoreSessions

/v4/JobSessions

/v4/Organizations

/v4/BackupRepositories

This created Additional Queries

By Opening “Advanced Editor” either by right clicking on a Query or  from the Tool bar

You can Review the Edit the Query as well as Update the Token if Expired.

The Basic Query:

let

    Source = Json.Document(Web.Contents("https://YourhostName:4443/v4" & "/RestoreSessions ", [Headers=[Authorization="Bearer <Your Token>"]])),

    results = Source[results]

in

    results

Once Happy with your PowerBi Queries, you can select “Close and apply” from the Tool bar

(Make sure that your Token is still Valid)

You will Now see Data In your Table View & Relationships.

See The below Illustration (Screen Shot) 

This was just a simple approach to extract Data from Veeam Backup for office 365 into PowerBi

After All the Data is Added you can begin to manipulate and build a Report

This was just a Quick Easy example 

Illustration below (Screenshot)

Potential Setbacks:

If the tokens expire you can’t refresh your data collection until you update each PowerBi query with latest Token.

For more refined Data you will need to Edit the PowerBi Queries a bit more to filter results.

Custom ODBC drivers:

Ive seen a few articles where you can create custom ODBC Drivers for REST to visualize REST data in Power BI, this should allow to use the Refresher Token to keep API connection live for your Data source refreshes in PowerBi.

Thank you for taking the time to Read , Please share & Comment.