Detailed How To - Connect SolarWinds to PowerBI to Create Dashboards For Reporting

powerBI

Hi Everyone,
I have spent the last decade working for an organization that heavily utilized SolarWinds, and one thing I was tasked with was seeing about the possibility to connect SolarWinds to PowerBI. I spent a lot of time researching and piecing things together from varying posts, but I never saw a comprehensive how to guide on connecting Solarwinds to PowerBI. Since I just accepted another position that does not use SolarWinds and no one at my old organization has any interest in maintaining this, I'd like to share the knowledge here in hopes that it helps someone else out.

The general workflow is as follows:

1. Yourself or your SolarWinds admin creates read-only credentials that you can use specific to the DATABASE (note that this is not the same as "manage accounts" via the web UI.
2. You need to have some familiarity with SWQL. You certainly don't need to be an expert and AI can help you 90% of the way. SWQL Studio is free to download, and you can use it test queries to your SolarWinds database. Adding "with querystats" to the end of any query will provide the SQL query which we will need below.
3. If your intention is to share this PowerBI report with other users, you will need what Microsoft calls a "PowerBI Premium Content" dashboard (it shows a jewel next to the dashboard). Otherwise, no one will be able to view your dashboard.
4. You need to utilize a server (pollers work great) which will "sync" to PowerBI using a free app called "On-Premises data gateway". This software serves as a way to sync the data PowerBI data from your machine into the PowerBI cloud so that it's accessible via app.powerbi.com.
5. Lastly, you can set up scheduled refreshes per dashboard/report via app.powerbi.com.

Detailed Steps to Follow:

1. On the server where you will be using PowerBI, create a new report.
2. Click "Get data"
3. Find "SQL Server database" → Connect
   1. Server = the IP/hostname of your SolarWinds DATABASE server followed by ",1450". Example: MySolarWindsDB,1450
   2. Database = NetPerfMon
   3. Data Connectivity Mode = Import
   4. Click the drop down for Advanced options
   5. in SQL statement, you will add the SQL code. NOTE: this is not the same as SWQL that you use in SWQL Studio. Add in "with querystats" to the end of your SWQL query, run it, and then click "Query Stats" tab at the bottom. Expand out the Query column, and the entry with the most data will be your SQL query that you post in here.
   6. Click OK
4. Here you will verify that your read only DATABASE credentials works.
   1. Click Database -> use local read-only account that was created earlier
   2. Click Connect
   3. You may get a pop up for Encryption Support. If you are OK with this data being unencrypted, click OK
   4. If it worked, you will now see a table containing all your data. It should look identical to the data returned when you run the SWQL query.
   5. From here, you can either: rename the columns using transform data and right clicking each column to change from "C1, C2 ..." to something more appropriate, or do it afterward. Save yourself the trouble and do it beforehand using transform data.
   6. Click Load
   7. You will now see "Query1" under the Data column on the right

Designing the Report

There's not much I can really offer you here. PowerBI is incredibly user friendly to use if you've never touched it before. Click on one of the Visualizations, and then drag some of your data into the Legend/Values/Details. Mess around and see what you like. The possibilities are endless, and you can add as many different queries as you'd like into a single report.

Saving/Publishing the Report

Save the report, and then click Publish. Make sure you have a dashboard already created in app.powerbi.com with the "PowerBI Premium Content" permissions. Select it, and it will save.

Syncing With On-premises data gateway

This is incredibly straightforward as well. Download the free software from Microsoft, and then Sign in.

Example SWQL Query

select i.Nodename, i.ip as [Device IP], i.MachineType as [Model], i.StatusDescription as [Current Status], i.Contact as [SNMP Contact]
from orion.nodes i
where i.UnManaged='False' 
--and i.nodename like '%uncomment and specify here%'
order by i.nodename
with QUERYSTATS

Hope this helps. Trying to add screenshots but getting "Permission Problem". Will try later.

tobyw_loop1

This is great, thanks for sharing!