Introduction
In the interest of getting OTbase users up to speed with creating Power BI dashboards, we have created a template with three dashboards that you can use as building blocks for your own Power BI reporting. Those dashboards are:
Software dashboard
Firmware dashboard
Vulnerabilities dashboard
All dashboards are contained in one PBIX file that you can download from the customer download portal if you have licensed the OTbase Power BI connector. The dashboards assume that you have the connector already installed.
Getting started
Open the template in Power BI. It should look like this:
You'll see all these error messages because the dashboards aren't connected to any data yet. We'll fix this by adding the OTbase Power BI Connector with "Get Data".
To get the most out of this dashboard, we recommend to pull all tables when given the option after entering source data parameters.
After your data has loaded, you will see that some widgets still show error messages. This is what we're going to fix now.
Adding relationships
Power BI needs a little help understanding how the various tables are linked among each other. This is what we're going to fix by adding relationships. To do so, click on "Model view", which brings up the relationship workspace.
As you can see, Power BI did already create several relationships automatically. But it needs some help to finish the job.
First, add the relationship between the devices table and the hardware table, using Device ID as the key. Simply click and Drag Device ID from Devices to Hardware. Change “Cross-Filter Direction” to “Both”. Then click “Save”.
As a result, you should now see the new relationship in the workspace. The bidirectional arrow indicates the proper cross-filter direction.
Now we need to update all of the other relationships filter directions. Double-Click on each of the connection symbols, change cross-filter direction to “both”, and save.
As a result, your model workspace should look like this:
Done! This operation will ensure all tables are properly related and will function correctly in the context of slicers and filters -- and you only have to do it once.
Fixing the criticality widget
On the software and firmware dashboards you will see that a widget that is supposed to show device criticality is still broken. This is because the widget doesn't visualize raw data but grouped data, and groups cannot be included in a template without a data source. By adding the respective group manually, we'll fix the problem.
In order to remedy this, we need to recreate the group. Select the broken widget and click the three dots next to criticality under “devices” in your data pane.
Then click “new group”.
In the dialog box that pops up, select all values and click on "Group".
Note that the actual values you'll see depend on the data that your users have input. In OTbase, users can use whatever they want to express device criticality. The whole purpose of this exercise here is to allow you to separate devices which are in any way critical from those which are not. (If your users haven't assigned criticality to any device, you can simply delete the criticality widget altogether.)
In the dialog, rename the group to "Critical".
As a final step we label non-critical devices as non-critical. Click “Include Other group” and rename the group “Non-Critical”.
After clicking Ok, your group values should now be displayed properly:
Note that just like magic, the grouping and labeling automatically shows up in the Criticality widget on the Firmware dashboard -- because it's associated with the data, not with the widgets.
Fixing the days since last software execution widget
The final fix that we need to apply is, again, on the software dashboard where we expose a histogram for the days since last software execution. Here we don't want to expose raw data in the interest of showing patterns rather than being too granular.
The first part of the solution is similar to above. We’re going to click the three dots next to “Days Since Last Run” in the Software table of the Data pane. Click “New Group”.
Now set the bin size to a value of 30 (days), or any other value that you want to use, and click Ok.
We're not quite done yet. Click the three dots next to the software table and select "New Measure".
This brings up the DAX editor dropdown at the top of the window:
Now copy and paste the following DAX code in the editor and save by clicking the green checkmark:
ShowDaysSinceLastRun =
IF(
NOT(ISFILTERED(Devices[Reference Location]))
&& NOT(ISFILTERED(Software[Name]))
&& NOT(ISFILTERED(Devices[Criticality (groups)])
&& NOT(ISFILTERED(Software[Version]))
),
BLANK(),
COUNT(Software[Name])
)
Afterwards, the error message in the widget should disappear. Note that the widget will remain empty until you select one or more software products from the table in the lower left corner. The spread of days since last execution will then be displayed as a histogram.
A large spread tells you right away that the selected application(s) show a large variety in respect to when they have last been used. Software applications that haven't been used for a long time make good candidates for system hardening.
Comments
0 comments
Please sign in to leave a comment.