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.

Log analytics mobile solution

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

Log analytics mobile solution

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.

Log analytics mobile solution

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

Log analytics mobile solution
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:

Log analytics mobile 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).

Log analytics mobile solution

 

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.

Log analytics mobile solution

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

Log analytics mobile solution

Creating visualizations in Power BI

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

Log analytics mobile solution

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)

 Log analytics mobile solution

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.

Log analytics mobile solution

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

Log analytics mobile solution

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.

Log analytics mobile solution

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

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

Log analytics mobile solution

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.

Log analytics mobile solution

Then you can create your own dashboard.

Log analytics mobile solution

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

Log analytics mobile solution

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

Log analytics mobile solution

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

Log analytics mobile solution

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 “server performance” dataset in this example.

Log analytics mobile solution

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

Log analytics mobile solution

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!

Log analytics mobile solution

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 created is available for download here (I expect that you will need to update the queries with your own particular workspace information). Get in touch with us for more info.

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.