Step-by-step: replacing a Log Analytics mobile solution with Power BI

Recently Microsoft announced that they will be fully moving the OMS portal into the Azure portal and that the current OMS mobile will be deprecated as well. (If you are interested there is an open area for feedback on this topic available here). One of the side-benefits of the OMS portal was that once you created a solution in the OMS portal it was also immediately available in the OMS mobile application.

One of the options mentioned as a potential replacement was Power BI and the mobile application which is available for Power BI. To test the functionality available in Power BI as a potential replacement for existing OMS mobile I am going to blog on the process to migrate an existing solution in Log Analytics to have a mobile application running in Power BI. The existing solution we will migrate over is the Server Performance Solution for Microsoft Log Analytics.

The current Server Performance Solution provides up/down information, and information on key performance indicators for the systems (processor, disk, memory, network).

As a result of being an existing solution, we can access the data via a mobile device with no custom development beyond the initial creation of the solution in Log Analytics. Below are screenshots from the current OMS mobile displaying the solution shown above.

Below are sample outputs from the mobile app showing availability, processor, memory, disk, and network information.


For a mobile version of this in Power BI we would be looking for similar information – availability, processor, memory, disk, and network information.

In this blog post we will cover the following topics:

  • Identifying the queries to use from Log Analytics
  • Building the queries to use to get data into Power BI
  • Getting the data that we want from Log Analytics into Power BI
  • Creating visualizations in Power BI
  • Customizing visualizations in Power BI for a mobile view (optional for the report)
  • Publishing Power BI
  • Creating a dashboard in Power BI
  • Scheduling updates
  • Results on the new mobile experience

 

Identifying the queries to use from Log Analytics

The first step is to gather the queries from the current solution. This can be done by opening the solution in the view designer and making copies of the queries which are used in the solution:

The list of queries from this solution are below (with duplicates removed and any queries which were the same except for sort orders removed):

  • Heartbeat | summarize LastCall = max(TimeGenerated) by Computer | where LastCall > ago(5m) | count
  • Heartbeat | summarize LastCall = max(TimeGenerated) by Computer | where LastCall < ago(5m) | count
  • search * | summarize AggregatedValue = count() by Computer
  • Perf | where CounterName == “% Processor Time” | summarize AggregatedValue = avg(CounterValue) by Computer
  • Perf | where CounterName == “% Processor Time” | summarize AggregatedValue = percentile(CounterValue, 90) by Computer
  • Perf | where CounterName == “% Free Space” | summarize AggregatedValue = avg(CounterValue) by Computer
  • Perf | where (CounterName == “% Free Space” and InstanceName != “_Total”) | summarize AvgFreeSpace = percentile(CounterValue, 90) by Computer, InstanceName | sort by AvgFreeSpace asc
  • Perf | where CounterName == “Free Megabytes” and InstanceName !contains “HardDisk” and InstanceName !contains “Program Files” | summarize AggregatedValue = avg(CounterValue) by Computer
  • Perf | where (CounterName == “Free Megabytes” and InstanceName != “_Total”) and InstanceName !contains “HardDisk” and InstanceName !contains “Program Files” | summarize AvgFreeSpace = percentile(CounterValue, 90) by Computer, InstanceName | sort by AvgFreeSpace asc
  • Perf | where (CounterName == “% Committed Bytes In Use” or CounterName == “% Used Memory”) | summarize AggregatedValue = percentile(CounterValue, 90) by Computer
  • Perf | where CounterName == “Available MBytes” or CounterName == “Available MBytes Memory” | summarize AvgFreeMemory = percentile(CounterValue, 90) by Computer | sort by AvgFreeMemory asc
  • Perf | where ObjectName == “Network Interface” and CounterName == “Bytes Total/sec” | summarize BytesTotal = avg(CounterValue) by Computer | sort by BytesTotal desc
  • Perf | where ObjectName == “Network Adapter” and CounterName == “Bytes Received/sec” | summarize PacketsReceived = avg(CounterValue) by Computer
  • Perf | where ObjectName == “Network Adapter” and CounterName == “Bytes Sent/sec” | summarize PacketsSent = avg(CounterValue) by Computer
  • Perf | where ObjectName == “Network Interface” and CounterName == “Bytes Total/sec” | summarize BytesTotal = avg(CounterValue) by Computer
  • Perf | where (CounterName == “Total Bytes Transmitted” or CounterName == “Total Bytes Received”) | summarize BytesTotal = sum(CounterValue) by Computer

 

Building the queries to use to get data into Power BI

By reviewing the original queries we can now break these down into what core types of data we are actually using. The above queries use these types of data:

  • Heartbeat (1 query)
  • Perf : For the following counter names: (10 queries)
    • % Processor Time
    • % Free Space
    • Free Megabytes
    • % Committed Bytes in Use
    • Available Mbytes
    • Bytes Total/sec
    • Bytes Received/sec
    • Bytes Sent/sec
    • Total Bytes Transmitted
    • Total Bytes Received
  • There is also a query which uses search * (generally not a good practice) but this can also be minimized by only gathering the count of the amount of the data. (1 query)

Based upon the above we could generate upwards of 12 queries which Power BI would then gather from log analytics. For simplicity we will break these down into the minimal number of queries which maintaining a common key between each query. For this example we have three different types of data so we need three queries (one for Heartbeat, one for total data and one for all of the performance counters). The “Computer” field will be used to span these queries in Power BI. The queries below are variations of the ones above specifically designed to gather only the fields that we want in order to minimize the data which is being gathered by Power BI.

  • Heartbeat: Heartbeat | summarize LastCall = max(TimeGenerated) by Computer
  • Data volume: search * | where Computer !contains "Contoso" and Computer !in ("","-") | summarize DataVolume = count() by Computer
  • Performance counters:

let StartDate = ago(7d);

let EndDate = (datetime(now));

Perf

| where TimeGenerated >= StartDate and TimeGenerated < EndDate and CounterName in ("% Processor Time","% Free Space","Free Megabytes","% Committed Bytes in Use","Available Mbytes","Bytes Total/sec","Bytes Received/sec","Bytes Sent/sec","Total Bytes Transmitted","Total Bytes Received")

| project Computer, ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated, CounterPath

We can now take the three queries listed above and run them one at a time with an Export to Power BI (M Query).

 

Getting the data that we want from Log Analytics into Power BI

In Power BI Desktop we use the exported M Queries to add each of our different types of data from Log Analytics (DataVolume, Heartbeat and Perf). The documentation team has covered the process of getting data out of Log Analytics very well here.  For our example we just need to add three different queries and then name them accordingly.

On the relationships tab we can see that the various data sources are connected via the field which is common to them (Computer).

Creating visualizations in Power BI

Within Power BI we build out the various visualizations which will be available on the mobile version.

For each performance counter we need to make some specific configurations such as:

  • Specifying the CounterName (% Free Space in this example)
  • Using the Average of the CounterValue
  • Setting the TimeGenerated to display the top 1 by latest time (for free disk space we really only tend to care about the most current state of the KPI)

 

Customizing visualizations in Power BI for a mobile view (optional for the report)

Once we have built the various visualizations in Power BI you can now change to the Mobile view by choosing the Phone Layout option for the report.

From the Phone Layout view we can drag and drop and size the various visualizations which have been created in Power BI.

Publishing Power BI

Once we have everything put together the way we want to for the mobile view we can now publish the report which we put together.

Choose where you want it to publish to (My Workspace as an example)

And Power BI will make this available in Power BI online.

Microsoft provides solid documentation on this process as part of the same documentation available here.

 

Creating a dashboard in Power BI

To create a dashboard from a report, we open the report in Power BI online and then we pin the various visualizations to the new dashboard using the pin icon on the individual visualizations.

Then you can create your own dashboard.

Pin each of the various visualizations to the dashboard and on the last one we now can create the phone view for the dashboard.

From the Edit phone view we can pin our various visualizations to the dashboard.

From the phone view we can pin the various tiles to the phone view now.

Once these visualizations have been pinned to the dashboard we are now ready to schedule updates to keep the data current in the visualizations.

 

Scheduling updates

Now that we have the dashboard created we need to configure when the refresh will occur. To do this we set the properties of the “ServerPerformance” dataset in this example.

Next we identify the timeframes for the refresh of the data (and the account credentials).

For details on how to schedule refresh, see Microsoft’s documentation here.

Results on the new mobile experience

The following are examples of the first draft from the new mobile experience showing data volume, processor, disk, memory and network KPI’s!

Summary: Once you get used to how to use Power BI to create a mobile experience it’s pretty straight-forward to generate your own visualizations which will run on your mobile devices using the Power BI mobile app. The sample PBIX that I’ve create is available for download here (I expect that you will need to update the queries with your own particular workspace information).

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.