Hi there, I have been wanting to share my fun times had with the Capacity and Performance solution in OMS for a while now, but have struggled to find the time.. Luckily for you I found a few moments today to have another tinker and got a little over excited and extended past OMS, but onto PowerBI and an Android device..
So a quick run down on what we’ll cover (at varying levels) in this post:
- Connect SCOM to OMS
- Connect OMS to PowerBI
- Configured a PowerBI Dataset via Log Analytics
- Created a report and dashboard in PowerBI
- Go to the beach and monitor data from an Android device
Ok, let’s get to it..
SCOM to OMS
I am using an on-premises SCOM management group that is connected to OMS. I have added the monitored blades from one of our fault domains (aka enclosures) to send data to OMS for a bit of dashboard fun.
If you have not yet connected your SCOM to OMS and need to work out how, check this guide out on TechNet: https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-om-agents
I won’t go in detail on how to send data up via SCOM as that’s not the real purpose of this post, but essentially the connection process is:
1) Create OMS workspace in Azure or use an existing
2) Connect SCOM to OMS
3) Add a computer/group from SCOM
4) Turn on the Capacity and Performance (Preview) Solution from the Solution Gallery
note: For my demo I am using the above mentioned Solution but you can use any type of data source that you have feeding into Log Analytics
More information on OMS Solutions can be found on this here: https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-capacity
5) Grab a coffee and wait for the data pour in…
Ok, so we’re all up to the same stage.. great!
Firstly go to your Workspace Overview
Find the Capacity and Performance (Preview) tile and select it
Almost instantly you will be greeted by a set of pre defined tiles showing the performance of the connected environment
Side note: OMS on Android
I installed the Microsoft OMS app on an Android device and connected to my Workspace.
Below is a screenshot from my android showing the Capacity and Performance solution tiles
From search to reviewing dashboards it was less than 5 minutes.. Obviously after a successful MFA challenge
Ok, that was a bit of fun.. back to the case at hand..
Log Analytics to PowerBI
So OMS dashboards are great, but what if you want to add other custom data to a dashboard that lives in PowerBI, how do we do this?
Well, really easily actually… TechNet has an article for this which can be found here: https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-powerbi
A quick rundown on the process:
1) Sign up to PowerBI as your desired user. In my case it was my Work AAD account
2) Enable the PowerBI feature in OMS portal
Settings > Preview Features > PowerBI Connector > Enable
Acknowledge the disclaimer
3) Connect your PowerBI account
(refresh if the button is not there yet)
Side note: I also have a Microsoft account associate to my email address. PowerBI is connected to my Work ID and the OMS Workspace is connected to my Microsoft ID. So just confirming that you can connect OMS and PowerBI across account types
OMS query as a PowerBI Dataset
Ok, we’re now in a position where SCOM data is connected to OMS which is connected to PowerBI.. Cool hey? Ok, now let’s create some quick datasets
Go to Log Search and start creating your query….
Quick Tip: You can essentially create any query in Log Analytics and forward that as a dataset to PowerBI, but why reinvent the wheel? For the demo I’ll use the Capacity and Performance solution to do the heavy lifting for us. But this can be any log search from any OMS connected data source.
Go to back to your solution tile that we enabled earlier. In this example I am going to use the default ‘Host CPU Utilization’ query.
Selecting this takes us back to the Log Search and pre-populates with the query used to display the data in the solution. Perfect!
Ok, that’s cool, but the real fun starts here because now we have a new option available to us, PowerBI
Give your query a name, adjust the schedule to your liking and finally give the Dataset a name.. This is how it will appear in PowerBI
note: I set to 15 minutes for the purpose of this demo but for production use I would set this particular query to 1 hour.
During this demo I created 3 datasets. CPU and Memory performance plus a missing updates query from another solution. But that’s just me playing….
So pop on over to your PowerBI portal and log in.
All things being equal, your newly created Datasets should be there waiting for you.. If not, it is only a matter of a minute or two.
Alright, let the PowerBI fun begin!
Select your newly created Dataset from the left menu and then choose your visualization type.
For the purpose of this demo, I am going to recreate a similar dashboard to what we see in OMS. So I went with the Line Chart
Drag the below data results to the chart fields:
Axis – TimeGenerated
Legend – Computer
Values – AggregatedValue
click the down arrow next to the AggregatedValue property and select ‘Average’
You should now see a chart similar to the below
Next click on the white space outside of the chart then select a new visualization. I went with the Gauge
Drag the AggregatedValue to the Value field and select Average again..
Now let’s clean this up a little bit:
Click the format tool (paint roller) and give your chart a more friendly name. I went with CPU Speedometer.
Select the line graph and edit to your liking. For this demo I changed the position of the legend and named the chart CPU Performance.
Note: If you want to filter the data you can filter by report, page or visualization (chart). For the purpose of the demo, I have filtered out a couple of hosts.
Click save in the top right and name your new report/s
I created a few reports as demonstrated below
On the report item you want to pin to a dashboard, hit the pin in the top right
Select New Dashboard and name it accordingly.
Repeat this process for any other reports you want on your dashboard.
Now select your newly created dashboard and your custom reports are automagically there.
After a little bit of a play this is what my dashboard looked like on the PowerBI Android App
Ok, now with near real-time performance metrics via my new dynamic dashboard wherever I am, I’m off to the beach..