Scoping OMS Performance Data in Power BI

Written by Tao Yang

when working on a dashboard or a portal, sometimes it is good that the portal is more interactive. I often found it’s more useful then just a static widget. Since I come from the monitoring back ground, I’ll use performance data as an example.

In the good old SCOM, we have this awesome 3rd party web portal called Squared Up, which allows you to choose the time frame for the perf graph:

image

and you can also select the time frame by highlighting a section from the graph itself:

image

In OMS, when we are playing with the Near Real-Time (NRT) Performance data (Type=Perf), we also have the options to specify the time frame of our choice:

image

Additionally, if we have chosen a time scope that is 6 hours or less, we are able to see the raw NRT perf data coming in every few seconds (in light blue colour):

image

Both Squared Up (for SCOM) and OMS portal provides very interactive ways to consume the perf data.

As we all know, OMS has the ability to send collected data to Power BI, therefore we are also able to create Power BI reports that contains performance data injected by OMS. i.e.:

image

As you can see, with the Power BI Line Chart visual, we can even add a trend line (the black dotted line), which is very nice in my opinion. However, by using native visuals, there are few limitations with displaying performance data in Power BI:

  • The time frame cannot be easily scoped
  • The computer and performance counters cannot be easily scoped

What I mean is, you can absolutely create a filters on either visual level, or page level or even the report level to create desired scopes – just like what I did in the example above:

image

But these filters are rather static. You won’t be able to alter them once you’ve saved the report. Obviously, as the report creator, you don’t really want to multiple almost identical visuals for different counters for different computers. In my opinion, reports like these become less interactive and user friendly because they are too static.

So, how do we make these Power BI reports more interactive? there are few options:

1. Use a Slicer to filter the computers OR the counters

In Power BI, you can add a slicer to your page. Slicers makes the report more interactive. users can choose one or more items from the slicer and other visuals on the page will be updated automatically based on users selection.

image

In the above example, I’ve used page level filter to only display the ‘Availability MBytes’ counter, and users can use the slicer to choose the computers they are interested in.

This solution is easy to implement, it may satisfy the requirements if you are only interested in a specific counter from a long term trend point of view – since we are not filtering the time windows, it will display the entire period that is available in Power BI.

2. Use the Custom Visual ‘Hierarchy Slicer’ to filter the computers AND the counters

For Power BI, you can download custom visuals from https://app.powerbi.com/visuals/?WT.mc_id=Blog_CustomVisuals and then import into your reports.

One of the custom visual you can download is called Hierarchy Slicer:

image

As the name suggests, comparing to the original built-in slicer, this visual allows you to build a hierarchy for your slicers:

image

As you can see, I’ve added Computer name as the top level filter in the hierarchy slicer, followed by the counter name as the second level in the slicer. As the result, I don’t have to use the filters for this page. Users can simply click on a counter (2nd level)  to view the graph for the counter on that specific computer, or select a computer (1st level) to see all the perf data for that particular computer. Obviously, you can make the counter name as the top of the hierarchy and place the computer name as the second level if that suits your needs better.

Note: As per introduction video for this visual, you can enable multi-select by configuring the visual and turn off the ‘Single Select’ option:

image

However, based on my experience, this option is only available when you are using Power BI Desktop. It is not available in Power BI Online.

image

Therefore we won’t be able to use multi-select for the OMS injected data because we cannot use Power BI Desktop with OMS data.

3. Use the Brush Chart custom visual to scope the time frame

Another cool custom visual is called Brush Chart, it is also called ‘Advanced Time Slicer’ on the download page:

image

I am using this together with the hierarchy slicer, so I can scope both computers and counters, as well as the perf data time window.

image

As you can see, there are 2 graphs on this visual. I can use mouse (or other pointing devices) to select a time window from the bottom graph, and the top graph will be automatically zoomed into the selected time period.

4. Use the Time Brush Custom Visual to scope the time frame

The Time Brush custom visual is very similar to the Brush Chart (aka Advanced Time Slicer).

image

It cannot be used by itself, it acts as the control for other visuals. in the example below, I’m using it together with the Line Chart visual, as well as the hierarchy slicer:

image

As you can see, when I select a period from the Time Brush visual, the line chart got updated automatically.

5. use other custom visuals

There are a lot of other custom visuals that you can download. for example, there’s another time slicer called TimeLine that allows you specify a precise,  specific time frame.

image

Conclusion

By using the combination of various slicers, we can produce more interactive and user friendly reports in Power BI. In the examples listed above, I can quickly produce a single report for ALL the OMS performance data, and users can simply choose the computer, counter and the time frame from the report itself. There is no need to create separate reports for different counters or computers.

I hope you find these tips useful, and have fun with OMS and Power BI!

Calculating SQL Database DTU for Azure SQL DB Using PowerShell

Written by Tao Yang

over the last few weeks, I have been working on a project related to Azure SQL Database. One of the requirements was to be able to programmatically calculate the SQL Database DTU (Database Throughput Unit).

Since the DTU concept is Microsoft’s proprietary IP, the actual formula for the DTU calculation has not been released to the public. Luckily, Microsoft’s Justin Henriksen has developed an online Azure SQL DB DTU Calculator, you can also Justin’s blog here. I was able to use the web service Justin has developed for the online DTU Calculator, and I developed 2 PowerShell functions to perform the calculation by invoking the web service. The first function is called Get-AzureSQLDBDTU, which can be used to calculate DTU for individual databases, the second function is called Get-AzureSQLDBElasticPoolDTU, which can be used to calculate DTU for Azure SQL Elastic Pools.

Obviously, since we are invoking a web service, the computer where you are running the script from requires Internet connection. Here’s a sample script to invoke the Get-AzureSQLDBDTU function:

Note: you will need to change the variables in the ‘variables’ region, the $LogicalDriveLetter is the drive letter for the SQL DB data file drive.

The recommended Azure SQL DB service tier and coverage % can be retrieved in the ‘Recommendations’ property of the result:

image

the raw reading for each perf sample can be retrieved in the ‘SelectedServiceTiers’ property of the result:

image

Lastly, thanks Justin for developing the DTU calculator and the web service, and pointing me to the right direction.

SharePointSDK PowerShell Module Updated to Version 2.1.0

Written by Tao Yang

OK, this blog has been very quiet recently. Due to some work related requirements, I had to pass few Microsoft exams. so I have spent most of my time over the last couple of months on study. Firstly, I passed the MCSE Private Cloud Re-Certification exam, then I passed the 2 Azure exams: 70-532 Developing Microsoft Azure Solutions and 70-533 Implementing Microsoft Azure Infrastructure Solutions. Other than studying and taking exams, I have also been working on a new version of the SharePointSDK PowerShell module during my spare time. I have finished everything on my to-do list for this release last night, and I’ve just published version 2.1.0 on PowerShell Gallery and GitHub:

This new release includes the following updates:

01. Fixed the “format-default : The collection has not been initialized.” error when retrieving various SharePoint objects.

i.e. When retrieving the SharePoint list in previous versions using Get-SPList function, you will get this error:

image

This error is fixed in version 2.1.0. now you will get a default view defined in the module:

image

02. SharePoint client SDK DLLs are now automatically loaded with the module.

I have configured the module manifest to load the SharePoint Client SDK DLLs that are included in the module folder. As the result of this change, the Import-SPClientSDK function is no longer required and has been removed from the module completely.

In the past, the Import-SPClientSDK function will firstly try to load required DLLs from the Global Assembly Cache (GAC) and will only fall back to the DLLs located in the module folder if they don’t exist in GAC. Since the Import-SPClientSDK function has been removed, this behaviour is changed in this release. Starting from this release, the module will not try to load the DLLs from GAC, but ALWAYS use the copies in the module folder.

03. New-SPListLookupField function now supports adding additional lookup columns.

When adding a lookup field in a SharePoint list, you can specify including one or more additional columns. i.e.:

image

The previous versions of this module did not support adding additional columns when creating a lookup field. In this version, you are able to add additional columns using the “-AdditionalSourceFields” parameter to achieve this goal.

04. Various minor bug fixes

Other than above mentioned updates, this version also included various minor bug fixes.

Special Thanks

I’d like to thank my friend and fellow CDM MVP Jakob Gottlieb Svendsen (@JakobGSvendsen) for his feedback. Most of the items updated in this release were results of Jakob’s feedbacks.

Blog Site Recovered–Finally

Written by Tao Yang

If you are a regular visitor of this blog, you may have noticed that this blog has been down since last Thursday, and I’ve only been able to get it back online few hours ago (Monday afternoon my time). The downtime was caused by the server which is hosting my blog. My hoster couldn’t recover the sever, and ended up restored my site from the backup (that they took  on 24th April, which was 3 weeks ago).

Due to the hoster’s lack of ability to maintain my site, I have lost 3 weeks of data (2 most recent blog posts, comments, etc.). Only until today after I talked to their technical support people on the phone, I found out that they only backup my site once a week (and the recent backups were corrupted).

Putting my emotion aside, I have managed to find the Windows Live Writer’s drafts for the 2 blog posts that I lost, and I have just re-published them. I made sure the URLs are still the same as the original ones, but you may see them appear in your RSS feeds again (as duplicate feeds). Also, if you have left any comments on my blog over the last 3 weeks, they are probably gone now.

I apologise for any inconvenience this outage may have caused. I am looking into my own WordPress backup solutions now.

Also, my hosting plan is due for renewal in about a month time, I think I’ve got do something about it Smile.

Upcoming SCOM Bootcamp-Melbourne Australia

Written by Tao Yang

I’m teaming up with Infront Consulting, Australia and will deliver a 4-day in-person instructor-led SCOM 2012 bootcamp at Melbourne, Australia. The content of this bootcamp was developed by Infront Consulting group and it has been very popular internationally.

This bootcamp is designed for SCOM administrators and operators. If you are running SCOM (or planning to implement SCOM) in your environment, I strongly recommend you enrol to this bootcamp and spend 4 days with myself and other folks attending the bootcamp.

Here’s the detail of this training event:

SCOMBootcamp

SCOM 2012 Bootcamp – Australia

Date: 20 – 23 June 2016

Location:

Saxons Training Facilities Melbourne
Level 8
500 Collins Street
Melbourne VIC 3000

Please join us for the first Infront Consulting SCOM 2012 Bootcamp in Australia! Tao Yang is a well-known author, speaker, blogger and SCOM expert who will be guiding you in person in the SCOM 2012 R2 Bootcamp.

This four-day Bootcamp is a mix of in-depth instructor led training and hands-on labs where you will learn how to administer System Center Operations Manager 2012. This course will provide students with an understanding of the Operations Manager 2012 Architecture, features and how to administer and maintain Operations Manager 2012.

Cost: $3,600 AUD + GST per student, includes course materials and access to Hands on Labs.

Modules covered:

Session 1: Overview of System Center Operations Manager 2012

Session 2: Operations Manager 2012 Architecture

Session 3: Installing Operations Manager 2012

Session 4: Installing the Gateway Server Role

Session 5: Configuring Operations Manager Security

Session 6: Agent Deployment and Configuration

Session 7: Alert Notification and Incident Remediation

Session 8: Management Pack Tuning and Targeting Best Practices

Session 9: Tuning of the Core Microsoft MPs

Session 10: Application Performance Monitoring

Session 11: Network Monitoring in Operations Manager 2012

Session 12: Working in the Operations Manager Shell

Session 13: Building Custom Monitoring Solutions & Distributed Applications

Session 14: Reporting & Dashboards

Session 15: Third Party Extensions

Registration Link:

https://www.eventbrite.com/e/scom-2012-bootcamp-australia-tickets-25190237679

Hope to see you there!

Recordings Available for the VSAE MP Authoring Webinar with Squared Up

Written by Tao Yang

Last night, I conducted 2 webinars with Richard Benwell of Squared Up on MP Authoring. I recorded both sessions from my computer using Camtasia, and now the recordings for both sessions are now available on Squared Up’s YouTube channel:

First Session: https://www.youtube.com/watch?v=oH035DgbUSQ

Second Session: https://www.youtube.com/watch?v=Xu3yRE770QA

Lastly, the workshop guide, slide deck and the sample VSAE project is also available on GitHub:

https://github.com/tyconsulting/SquaredUp-VSAE-Workshop

HybridWorkerToolkit PowerShell Module Updated to Version 1.0.3

Written by Tao Yang

Few days ago, I published a PowerShell Module to be used on Azure Automation Hybrid Workers called HybridWorkerToolkit. You can find my blog article HERE.

Yesterday, my good friend and fellow CDM MVP Daniele Grandini (@DanieleGrandini) gave me some feedback, so I’ve updated the module again and incorporated Daniele’s suggestions.

This is the list of updates in this release:

  • A new array parameter for New-HybridWorkerEventEntry called “-AdditionalParameters”. This parameter allows users to insert an array of additional parameters to be added in the event data:

SNAGHTMLb6e7547

  • A new Boolean parameter for New-HybridWorkerEventEntry called “-LogMinimum”. This is an optional parameter with the default value of $false. When this parameter is set to true, other than the user specified messages and additional parameters, only the Azure Automation Job Id will be logged as event data:

image

As we all know, we pay for the amount of data gets injected into our OMS workspace, this parameter allows you to minimise the size of your events (thus saves money on your OMS spending).

I have published this new release to both GitHub and PowerShell Gallery.

Gadget Show Off: My Presentation and Recording Equipment for Surface Pro 4

Written by Tao Yang

OK, it’s Friday night, I feel like writing something on this blog. But after couple of glasses of wine, and I don’t really want to write anything technical that requires too much brain power. So I picked an easy topic for tonight. I have been using a Creative Sound Blaster Bluetooth Pre-Amp and a lapel microphone to record my in-person community presentations. Based on my experience, the recording quality from that device is very average. Last weekend, after I recorded my presentation at Global Azure Bootcamp, I have decided to ditch it and look for a better solution. Over the last few days, I spent some time looking for a new Bluetooth microphone to replace the sound blaster device.

For those who know me well, I can be seen as a gadget man. I like playing with gadgets. after few days of research, I ended up with a Sony ECM-AW4 Bluetooth microphone from eBay. In order to test it, I connected all required equipment on my Surface Pro 4, and created a dummy presentation on this very same topic (My presentation and recording equipments for Surface 4), then presented using these equipments and recorded it using Camtasia. You can watch the recording on YouTube:

To summarise, I am using the following devices during the presentation:

1. Sony Bluetooth Microphone ECM-AW4 (http://www.sony.com.au/product/ecm-aw4)

Sony Mic

Although this device is designed for digital cameras and camcorders, it works with your smart phones and computers. There are some noticeable features:

  • range support up to 50 metres
  • supports external lapel microphones
  • supports headphones – for private communication between the camera man (via the receiver mic) and the person in the camera (via the headphone connected to the Bluetooth mic). this communication is not recorded / passed to the recording device.
  • comes with a wind screen to be used to cover the microphone when shooting outside in a windy condition
  • comes with an arm band which allows you to attach the mic on your arm.

 

2. Audio-Technica AT9903 lapel microphone (http://audio-technica.com.au/products/at9903/)

mic

I’m connecting this mic to the Sony Bluetooth mic only because I have already got it. I’ve also tried using the Bluetooth mic without this external lapel mic, the quality is also pretty good.

3. Creative Sound Blaster Play!2 USB Sound Card (http://au.creative.com/p/sound-blaster/sound-blaster-play-2)

sound card

I had to purchase this USB sound card for my Surface Pro 4 because the 3.5mm audio jack on my Surface does not support microphones. I also have a Lenovo Yoga Pro 3 ultrabook, unfortunately, it is the same case on the Yoga Pro laptop. So no matter which computer do I use for the presentation, I will have to get a USB sound card. Therefore I bought this one because I have been using Creative Sound Blaster external sound cards for many years (right now I have 2 on my desk for 2 NUCs that I’m using as my day-to-day PCs), and this one is very compact – just like a USB dongle.

4. Logitech R800 Presenter (http://business.logitech.com/en-us/product/professional-presenter-r800-business)

presenter

In my opinion, this is a must-have device for all your presentations. It is certainly very popular as I’ve seen many of my MVP friends using the very same device.

5. USB 3 Hub

USB hub

Because Surface Pro 4 only has one USB port and I need to connect both the presenter receiver and the Bluetooth Microphone Receiver to it, I have to use a USB hub. I’ve got this Inateck USB 3 Hub with GB Ethernet adapter few years ago for my old Surface Pro 2. It’s good that it still works without having to install any drivers on Surface Pro 4 running Windows 10.

So, I know many of my MVP friends present in many user group meetings, not sure if anyone has come up with other solutions for recording these in-person presentations. I’m pretty happy with this setup I came up with. Overall, it’s very compact, I don’t need to carry too many additional devices with me. If you are looking to achieve something similar, I hope you find this post and the YouTube video useful.

overall1

overall2

On the other hand, the recording quality is not as good as my setup on my desktop (Intel NUC) for webinars, but it can be a topic for another day:

desktop mic

Upcoming Webinar on MP Authoring Using VSAE

Written by Tao Yang

Last week, I have conducted a workshop with Richard Benwell from Squared Up to a group of Squared Up’s customers at an internal company event. In the workshop, I led the students and built a sealed OpsMgr management pack with a simple agent task.

After the workshop, our plan is to make the content available to general public, therefore, Richard and I will be conducting 2 additional webinars next week to cover different time zones. We will repeat what we did in last week’s internal event, and demonstrate how to build such a MP from scratch using VSAE and Visual Studio 2015. This is an absolute beginner’s guide to authoring management packs using VSAE. As you will see, we are writing the entire MP in Visual Studio without having to type any XML code!

If you are interested in this topic, please feel free to pick a time that’s suitable to you from the registration page below:

https://attendee.gotowebinar.com/rt/3979835542420770052

Lastly, the workshop guide, and a sample completed Visual Studio project can be found in this GitHub repo: https://github.com/tyconsulting/SquaredUp-VSAE-Workshop.

If you’d like to build the MP during the webinar with us, there are some pre-requisites that you must complete the steps outlined in section of the workshop guide before attending to the webinar.

Looking forward to seeing you next week!

New PowerShell Module HybridWorkerToolkit

Written by Tao Yang

HybridWorkerToolkit23/04/2016 Update: released version 1.0.3 to GitHub and PowerShell gallery. New additions documented in this blog post.

21/04/2016 Update: updated GitHub and PowerShell gallery and released version 1.0.2 with minor bug fix and updated help file.

Introduction

Over the last few days, I have been working on a PowerShell module for Azure Automation Hybrid Workers. I named this module HybridWorkerToolkit.

This module is designed to run within either a PowerShell runbook or a PowerShell workflow runbook on Azure Automation Hybrid Workers. It provides few functions that can be called within the runbook. These activities can assist gathering information about Hybrid Workers and the runbook runtime environment. It also provides a function to log structured events to the Hybrid Workers Windows Event Logs.

My good friend and fellow MVP Pete Zerger posted a method he developed to use Windows event logs and OMS as a centralised logging solution for Azure Automation runbooks when executed on Hybrid Workers. Pete was using the PowerShell cmdlet Write-EventLog to log runbook related activities to Windows event log and then these events will be picked up by OMS. Log Analytics. This is a very innovative way of using Windows event logs and OMS. However, the event log entries written by Write-EventLog are not structured are lacking basic information about your environment and the job runtime.  Couple of weeks ago, another friend of mine, Mr. Kevin Holman from Microsoft also published a PS script that he used to write to Windows event logs with additional parameters.

So I combined Pete’s idea with Kevin’s script, as well as some code I’ve written in the past for Hybrid Workers, and developed this module.

Why do we want to use Windows Event logs combined with OMS for logging runbook activities on Hybrid workers? As Pete explained on this post, it provides a centralised solution where you can query and retrieve these activity logs for all your runbooks from a single location. Additionally, based on my experience (and also confirmed with few other friends), is that when you use Write-Verbose or Write-Output in your runbook and enabled verbose logging, the runbook execution time can increase significantly, especially when loading a module with a lot of activities. Based on my own experience, I’ve seen a runbook that would normally takes a minute or two to run with verbose logging turned off ended up ran over half an hour after I enabled verbose logging. This is another reason I’ve developed this module so it gives you an alternative option to log verbose, error, process and output messages.

Functions

This module provides the following 3 functions:

  • Get-HybridWorkerConfiguration
  • Get-HybridWorkerJobRuntimeInfo
  • New-HybridWorkerRunbookLogEntry

Note: Although the job runtime are different between PowerShell runbooks and PowerShell Workflow runbooks, I have spent a lot of time together with Pete making sure we can use these activities exactly the same ways between PowerShell and PowerShell workflow runbooks.

Get-HybridWorkerConfiguration

This function can be used to get the Hybrid Worker and Microsoft Monitoring Agent configuration. A hash table is returned the following configuration properties retrieved from Hybrid Worker and MMA agent:

  • Hybrid Worker Group name
  • Automation Account Id
  • Machine Id
  • Computer Name
  • MMA install root
  • PowerShell version
  • Hybrid Worker version
  • System-wide Proxy server address
  • MMA version
  • MMA Proxy URL
  • MMA Proxy user name
  • MMA connected OMS workspace Id

Get-HybridWorkerJobRuntimeInfo

This function retrieves the following information about the Azure Automation runbook and the job run time. They are returned in a hashtable:

  • Runbook job ID
  • Sandbox Id
  • Process Id
  • Automation Asset End Point
  • PSModulePath environment variable
  • Current User name
  • Log Activity Trace
  • Current Working Directory
  • Runbook type
  • Runbook name
  • Azure Automation account name
  • Azure Resource Group name
  • Azure subscription Id
  • Time taken to start runbook in seconds

New-HybridWorkerRunbookLogEntry

This function can be used to log event log entries. By default, other than the event message itself, the following information is also logged as part of the event (placed under the <EventData> XML tag:

  • Azure Automation Account Name
  • Hybrid Worker Group Name
  • Azure Automation Account Resource Group Name
  • Azure Subscription Id
  • Azure Automation Job Id
  • Sandbox Id
  • Process Id
  • Current Working Directory ($PWD)
  • Runbook Type
  • Runbook Name
  • Time Taken To Start Running in Seconds

This function also has an optional Boolean parameter called ‘-LogHybridWorkerConfig’ When this parameter is set to $true, the event created by this function will also contain the following information about the Hybrid Worker and MMA:

  • Hybrid Worker Version
  • Microsoft Monitoring Agent Version
  • Microsoft Monitoring Agent Install Path
  • Microsoft Monitoring Agent Proxy URL
  • Hybrid Worker server System-wide Proxy server address
  • Microsoft OMS Workspace ID

Sample Runbooks

Sample PowerShell Runbook:

Sample PowerShell Workflow Runbook

As you can see, the way to call these functions between PowerShell and PowerShell Workflow runbooks are exactly the same.

Hybrid Worker Configuration output:

SNAGHTML40e35ad

Hybrid Worker Job Runtime Info output:

SNAGHTML40f4d28

Event generated (with basic information / without setting –LogHybridWorkerConfig to $true):

SNAGHTML4159a60[4]

Event generated (whensetting –LogHybridWorkerConfig to $true):

SNAGHTML4150515

Consuming collected events in OMS

Once you have collected these events in OMS, you can use search queries to find them, and you can also create OMS alerts to notify you using your preferred methods.

Searching Events in OMS

i.e. I can use this query to get all events logged by a particular runbook:

Type=Event “RunbookName: Test-HybridWorkerOutput-PSW”

image

or use this query to get all events for a particular job:

Type=Event “JobId: 73A3827D-73F8-4ECC-9DE1-B9340FB90744”

image

OMS Alerts

i.e. if I want to create an OMS alert for any Error events logged by New-HybridWorkerRunbookLogEntry, I can use a query like this one:

Type=Event Source=AzureAutomation?Job* EventLevelName=Error

image

image

Download / Deploy this module

I have published this module on Github as well as PowerShell Gallery:

GitHub Repository: https://github.com/tyconsulting/HybridWorkerToolkit

PowerShell Gallery:  http://www.powershellgallery.com/packages/HybridWorkerToolkit/1.0.3

Credit

I’d like to thank Pete and Kevin for the ideas in the first place, also I’d like to thank Pete, Jakob Svendsen, Daniele Grandini and Kieran Jacobsen for the testing and feedback!