Leverage Windows Analytics for Modern Ops Pt 2
– Business Intelligence –
Make Data Attractive and Meaningful to the business

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.
Power BI Template Tab | DB Table | Visual Name | Output & Power BI Visualization Fields | Query Name | Interval | Power BI Filtering | Query |
---|---|---|---|---|---|---|---|
Crash Summary | DHAppReliability | Daily Outlook Crash | Outlook Crash| Multiple Crashes | Outlook Crashes | 7 Days | · Removed Computer ID · Removed Computer Column · Remove Duplicates | DHAppReliability | where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True""" |
Crash Summary | DHOSReliability | Overall Crashes (Machines) | Machines | No Duplicates | Machine Count | Overall Crashes Just Machines | 7 days | · Removed Abnormal Shutdowns · Filtered Rows KernelModeCrashCount does not equal 0 | DHOSReliability |
Crash Summary | DHOSReliability | Kernel by Time | >1 Crash Count | Overall Crashes | 7 days | DHOSReliability | |
Crash Summary | DHDriverReliability | Wifi Driver Kernel | Netwtw06.sys > 1 crash count | Network Driver Crashes | 7 days | DHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1" | |
Critical Apps 7 days | DHAppReliability | Appfile Displayname | Apps > 50 Machine Count | Crashes Daily = Yes | App Overall Crashes Daily Count | 7 days | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion | |
Critical Apps 7 days | DHAppReliability | Sum of App Crash 7 Days | Apps > 50 Machine Count | Crashes Daily = Yes | App Overall Crashes Daily Count | 7 days | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion | |
Critical Apps 7 Days | DHAppReliability | Sum Last 7 days | Apps > 50 Machine Count | Crashes Daily = Yes | SUM | App Overall Crashes Daily Count | 7 days | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion | |
Outlook Details | DHAppReliability | App File Display | Outlook has Crashes Daily | App Version | Os Version | Machine Name | Outlook Crashes | 7 days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | DHAppReliability | where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True"" |
Outlook Details | DHAppReliability | App File Version | Filter by App Version | Outlook Crashes | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | DHAppReliability | where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True"" |
Outlook Details | DHAppReliability | Count by Time Generated | Outlook crash Total Machine count by Time Generated | Crash Daily = Yes | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates · Grouped Rows by Time Generated and New Column Count by Count Rows · Sort Rows by Date | DHAppReliability | where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True"" | |
Chrome Details | DHAppReliability | App File Name | Chrome has Crashes Daily | App Version | Os Version | Machine name | Time | Google Chrome Crash Data | 7 days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion |
Chrome Details | DHAppReliability | App File Version | Filter by app version | Google Chrome Crash Data | 7 days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion |
Excel Details | DHAppReliability | App File Name | Excel has Crashes Daily | App Version | Os Version | Machine name | Time | Microsoft Excel Crash Data | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion |
Excel Details | DHAppReliability | App File Version | Filter by app version | Microsoft Excel Crash Data | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion |
Excel Details | DHAppReliability | Count by Time Generated | Excel crash Total Machine count by Time Generated | Crash Daily = Yes | Microsoft Excel Crash 7 days | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates · Grouped Rows by Time Generated and New Column Count by Count Rows · Sort Rows by Date | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Skype | DHAppReliability | App File Name | Skype has Crashes Daily | App Version | Os Version | Machine name | Time | Microsoft Skype for Business Crash Data | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Skype | DHAppReliability | App File Version | Filter by App Version | Microsoft Skype for Business Crash Data | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Skype | DHAppReliability | Count by Time Generated | Skype crash Total Machine count by Time Generated | Crash Daily = Yes | Microsoft Skype Crash 7 days | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates · Grouped Rows by Time Generated and New Column Count by Count Rows · Sort Rows by Date | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Photo | DHAppReliability | App File Name | Photo has Crashes Daily | App Version | Os Version | Machine name | Time | Microsoft Photo Crash Data | 7 Days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Photo | DHAppReliability | App File Version | Filter by App Version | Microsoft Photo Crash Data | 7 days | · Removed Computer ID · Filtered Row Has Crashes Daily = True · Removed Duplicates | let top3AppNames = DHAppReliability | summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion | top 3 by dcountif_ComputerID | project AppFileDisplayName, AppVersion; |
Crash Details 7 days | DHOSReliability | Computer | Computer Name| Crash Count | Model | OS Version | Time Generated | Overall Crashes | 7 Days | · Crash does not Equal 0 | DHOSReliability |
Crash 7 Days (Avg) | DHOSReliability | Computer | Over all Crashes 7 days, no duplicate Machines | Overall Crashes (No Duplicates) | 7 days | · Crash does not Equal 0 · Removed Duplicates · Removed Errors | DHOSReliability |
WiFi Crashes | DHDriverReliability | Crash Events by Version | Netwt06.sys Crash Event by Version | Network driver Crashes | 7 days | DHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1" | |
WiFi Crashes | DHDriverReliability | Crash Events by Day | Netwt06.sys Crash Count by Time Generated | Network driver Crashes | 7 days | DHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1" | |
WiFi Crashes | DHDriverReliability | Crash Devices Impacted | Machine Count Crashing by Version and Total by Machine Count | Network Driver Device Impact | 7 Days | DHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1 |summarize InstallCount = dcount(ComputerID) by DriverVersion" | |
WiFi Crashes | DHDriverReliability | Total Installed Devices | Install Count by Version | Network Driver Installed by Version | 7 Days | DHDriverReliability | where DriverName == ""netwtw06.sys"" |summarize InstallCount = dcount(ComputerID) by DriverVersion" | |
WiFi Crashes | DHDriverReliability | Driver Version | Filter by Driver Version | Sum by version | Network Driver Installed by Version | 7 days | DHDriverReliability | where DriverName == ""netwtw06.sys"" |summarize InstallCount = dcount(ComputerID) by DriverVersion | |
WiFi Details | DHDriverReliability | Computer | Computer Name| Crash Count | Model | OS Version | Time Generated | Network Driver Crashes | 7 Days | DHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1 | |
Intel Video Crashes | DHDriverReliability | Crashed Events by Version | igdkmd64.sys Crash Event by Version | Intel video driver crashes | 7 Days | DHDriverReliability | where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1" | |
Intel Video Crashes | DHDriverReliability | Crash Events by Day | igdkmd64.sys Crash Count by Time Generated | Intel video driver crashes | 7 Days | DHDriverReliability | where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1" | |
Intel Video Crashes | DHDriverReliability | Crash Devices Impacted | Machine Count Crashing by Version and Total by Machine Count | Intel Video Driver Device Impact | 7 days | DHDriverReliability | where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1 |summarize InstallCount = dcount(ComputerID) by DriverVersion" | |
Intel Video Crashes | DHDriverReliability | Total Installed Devices | Install Count by Version | Intel Video Driver by Version | 7 days | DHDriverReliability | where DriverName == ""igdkmd64.sys"" |summarize InstallCount = dcount(ComputerID) by DriverVersion" | |
Intel Video Crashes | DHDriverReliability | Driver Version | Filter by Driver Version | Sum by version | Intel Video driver By Version | 7 day | DHDriverReliability | where DriverName == ""igdkmd64.sys"" |summarize InstallCount = dcount(ComputerID) by DriverVersion" | |
Intel Video Details | DHDriverReliability | Computer | Computer Name| Crash Count | Model | OS Version | Time Generated | Network Driver Crashes | 7 Days | DHDriverReliability | where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1" |
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
- 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.