So this next post covers how to automagically pull data from RVTools and push the results into a PowerBI Dataset.
After spotting @Kim_Bottu post over at vmusketeers, I realised that maybe there would be some interest from the community in my script to push datasets on a schedule with Powershell.
Grab the script here, checkout the pre-requisites / how to below.
In Azure
- Create service principle in Azure App Registrations
The Application (Client) ID will need to be assigned to the variable below
[String]$PowerBIClientID = "099ccc111-2z2z-111z-aaa6-9bbb1bbb114a"
The script uses service principle with a cert, so make sure to assign a cert to the app and import it to the machines certificate store where you plan to run the script. (The certificate must contain the private key when exported from Azure)
Update the certificates thumbprint in the script.
[String]$PowerBICertThumb = "1111aaaa111111a1111111a11a1a1a111a111aa1a"
Make sure to set the API permissions for the App.
2). Head over to PowerBI, load your Workspace, click on Workspace Access and add the PowerBi-DatasetRefresher user as member.
Update the script with your TenantID into $PowerBITenant and WorkspaceID to $PowerBIGroupID
[String]$PowerBITenant = "2a222222-a2a2-2a22-aaa2-11aaaaaaaa11"
[String]$PowerBIGroupID = "1y123x45-z67b-0000-0000-zzz111z222222"
My script only extracts vInfo from vCenter with variable RVExtract, but if you want to adapt the script to pull more data and tables make sure you check out the RVTools documentation and make the required changes to the script.
[String]$RVExtract = "ExportvInfo2xlsx"
Make sure you fill in / update the details in the script to for your setup.
[String]$RVUser = 'rv_tools@vman.sso'
[String]$RVEncryptedPassword = '_RVToolsPWDZZZZZZZZZZYYYYYYYYYYYYYYYYYYYYYYYPPPPPPPPPOOOO='
[String]$RVToolsPath = "C:\Program Files (x86)\Robware\RVTools"
[String]$RVOutputFolder = "C:\temp"
The first time that you set the script to create the dataset & table specified in $PowerBIDatasetName and $PowerBIDatasetTable so run it manually 1 time with the option below.
[Bool]$CreateDataset = $true
That’s it, if you head over to Datasets in PowerBi you should see the RVTools dataset listed!!
Setup the script to run on a schedule and create the desired dashboards / reports in PowerBI from this new dataset.
I hope this was helpful.
vMan
Recent Comments