OMS PowerBI SCOM

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

image

3) Add a computer/group from SCOM

image

4) Turn on the Capacity and Performance (Preview) Solution from the Solution Gallery

image

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

image

Find the Capacity and Performance (Preview) tile and select it

image

Almost instantly you will be greeted by a set of pre defined tiles showing the performance of the connected environment

image

 

 

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

image

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

image

3) Connect your PowerBI account

(refresh if the button is not there yet)

image

 

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

image

 

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

image

 

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.

image

 

Selecting this takes us back to the Log Search and pre-populates with the query used to display the data in the solution. Perfect!

image

 

Ok, that’s cool, but the real fun starts here because now we have a new option available to us, PowerBI

image

 

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.

image

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.

image

 

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

image

 

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’

image

 

You should now see a chart similar to the below

image

 

Next click on the white space outside of the chart then select a new visualization. I went with the Gauge

image

 

Drag the AggregatedValue to the Value field and select Average again..

image

 

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 100image

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.

image

 

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.

image

Click save in the top right and name your new report/s

I created a few reports as demonstrated below

image

 

On the report item you want to pin to a dashboard, hit the pin in the top right

image

Select New Dashboard and name it accordingly.

image

 

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

 

image

 

image

 

Ok, now with near real-time performance metrics via my new dynamic dashboard wherever I am, I’m off to the beach..

Enjoy!
Dan

Leave a Reply

Your email address will not be published. Required fields are marked *