Leverage Windows Analytics for Modern Ops – Part 2

Need help visualizing to the business which hardware works best in our environment? Which operating system (version) is crashing the most?  Which application versions run the best or simply which end users machine is experiencing tons of crashes?  These are all use cases for using Azure Log Windows Analytics to track down this data proactively rather than waiting on help desk tickets, escalations or heavy email traffic.  I will be reviewing the following in Part 2 of my first post around Modern Ops:

Azure Analytics (Windows Analytics) Requirements & setup:

• https://docs.microsoft.com/en-us/windows/deployment/update/windows-analytics-azure-portal
• https://docs.microsoft.com/en-us/windows/deployment/update/device-health-get-started

1) Dashboard Design Framework

A.Top Application Crashes > 50 or more devices impacted

i. Multiple Crashes in a 7-day period

B.Top Driver Crashes

i. Distinguish crashes by day

ii. By Version

iii. By Volume

C.Overall Crash Event Counts/Trends by day

D.Overall % of Devices Crashing (28-day period)

Dashboard Use case – Design

This is to give you an idea or the framework design for my dashboard that I built for monitoring our device & app performance

Please reference my template here instructions for how to open it are in Part 5. You might want to go ahead and get the template from HERE.

2) How to export from Azure Analytics and import into Power BI

A. Luckily for us Microsoft has made this process extremely easy. Don’t roll your eyes at me! Check out how easily this is done

i. Simply log into your Azure portal

ii.Select Log Analytics Workspaces

iii. Write a Query, and press run

iv. Select the Export Button

v. Select export to Power BI

vi. This will download a file – open the file

vii. Copy and paste everything in blue. The black box indicates your azure tenant api connection information/workspace ID information. This is important to know. (I randomized this one – so don’t use it

viii. Next Open Power BI

ix. Create a new file

x. On the home ribbon select – Edit Queries

xi. In the Query Editor, on the home ribbon select Advanced Editor

xii. Replace and paste from your export file here

3) Power BI Tricks

1. A. Now before we start doing some neat nifty visuals to manipulate our data there are a couple of ways of doing this. The nice thing about Power BI is you can be an expert in writing queries, or you can do basic queries, import, and then modify in the Power BI GUI. Both are beneficial, I recommend attempting in-depth queries until you get stuck, then import into Power BI and filter, step, formula away using the GUI. It is 2019 and there is no need to troubleshoot a machine for 1 hour and then re-image. There for there is no need to ever get stuck on a query for hours when you can pick apart data, and manipulate it in any way you want from just a single line, or even just a table. Don’t let anyone belittle you for that, work smarter not harder – ALWAYS. (But writing nifty code off the top of your head still makes you a badass and looks cool, don’t forget that)

i. Example

ii. You could write a simple query

 A. DHDriverReliability | where DriverKernelModeCrashCount >= 1

 B. Or if you’re really lazy you could just write

 i. DHDriverReliability

 ii. Import into Power BI

 iii. Filter columns as you see fit in GUI – At the end of the day it doesn’t matter how you do it, the worlds your oyster. I will show you how to do both so you can be a lazy sysadmin

iii. In Power BI in Advanced Editor – Select our query you imported, and you can see the columns as needed – Quite intuitive and easy.

iv. Need a nice line graph over time generated, you can easily achieve this by filtering and grouping by time generated if the right columns are there.

 A. Simply open the query you would like to do this

 B. Start chopping out what you do not need, to show 30 days of excel crashes by numbers I only need two things. Date generated, and Row counts per date.

 C. Hold ctrl click computers and time generated, then right click select group by.

 D. Now I only have two columns and this makes for easy use to visualize – This is now displaying the amount of machines that have a particular app crash in the day.

4) Visualize the Data

i. In Power BI, for this example I select the line graph

ii. Check the data columns that I want to include in the line graph

iii. Select the Values/Count, and the filters I want to use

iv. Referencing the Excel Tab the modern ops template you can now see I’m displaying detailed information on the machines that are having issues, and a trending graph for 7 days.

v. You can use any variety you would like for visualization that you want. Feel free to mix and match the column you want to include. This ais an example of my Wifi tab on the Modern ops template. As you can see, we made some changes that fixed wifi crashes.

vi. You can see the direct correlation, the wifi crashes go down so does the overall crashes in the environment (referenced modern ops tab “Crash summary tab”). These are great examples to show the business that the changes you are putting in have a positive impact and are improving the environment. It is very hard to argue, or debate when you have the data showing the trend.

5) How you User the Modern Ops Template

A. You are more than welcome to download modern ops template (in section #1) butyou will have to do a little tweaking to have it work with your organization.

B. First you will need to download the template

C. Open the template in Power BI, and select cancel on both windows.

D. Select Edit Queries in the Top Ribbon

E. In the queries window right click a query and select Advanced Editor

F. In the “Https://api.loganalytics.io/” copy and paste your work space ID, you can find this information when you export a query from Windows Analytics. (Refer to section 2)

G. Select Edit Credentials

H. Select organizational account, make sure you have your permissions set correctly in azure log analytics, and then log-in

Below are a few more screenshots from what you can expect to see with our Modern Operations solution.

Part 3 of this post will be taking this analytical data and using EDR (Endpoint Detection and Response) tool to self-heal devices. We will be using a tool from 1E named Tachyon and SCCM to demonstrate that!

Chad Arvay

Chris Buck