On the go Hyper-V performance with OMS Log Analytics & PowerBI…

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!

Log Analytics

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 Open-mouthed smile

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?

Great question!

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 Smile

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 Smile

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.

Save it!

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….

PowerBI

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.

We are showing a percentage so set the Gauge axis Max to 100

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.