Best Way to Import Data Into PowerBI from SolarWinds/SWQL?

Hi,

I've spent some time looking at previous posts, but none provide a step by step instructional for how to import data into PowerBI.

The two options that I've seen discussed are:

1. Import data from SQL Server

2. Import data from web

I am not able to make any progress with the SQL server connection. I've looked at documentation, tried different configuration options, but I always end up with the "Could not open a connection to SQL Server" error. With the web option, I input the URL link that I am able to copy from SWQL Studio using "Copy query as", but I consistently receive "Could not establish trust relationship for the SSL/TLS secure channel".

In an ideal world, I would love to be able to write a query and test using SWQL Studio, and then import that query into PowerBI and build dashboards using this.

Has anyone had any luck with either above option, or possibly an entirely different method? I'd really appreciate any advice.

Thanks!

Find more posts tagged with

powerbi

SWQL

Comments

KMSigma.SWI

PowerBI requires a valid certificate for endpoints. Since the API uses a self-signed certificate, PowerBI (to the best of my knowledge) will never allow it as an authorized endpoint. The best option would be to change over the certificate to one that's trusted. The instructions for making that change are in Configure the SolarWinds Platform Web Console to use HTTPS.

bobmarley

I do not have a full answer for you but the way I get data into some of our third party tools is first define a SWQL query that will retrieve the data you wish to import. Next run the query from within a powershell script and save it as a .csv file to a network location. Create a scheduled task to run the powershell script on whatever interval you need. On the BIQuery side:

In Power BI, click Get Data in the lower left screen.
Under Import or Connect to Data > Files, click Get.
Click Local File.
Choose which file to upload and click Open.
Click Upload under Upload your Excel file to Power BI.
The message “Your file has been uploaded” should appear.

Try this URL for getting the data out. It has a good example of how it is done. Export Custom Properties - Scripts - The Orion Platform - THWACK (solarwinds.com)

chad.every

Starting in version 2023.1 a feature was added to update the SWIS/API certificate with your own.

Details on updating that certificate are found here.

I have not tried this personally, but if you do update the SWIS cert with a signed cert, i'd be curious to know if it allows you to connect to Power BI.