Leverage Windows Analytics for Modern Ops Pt 2

– Business Intelligence –

Make Data Attractive and Meaningful to the business

Windows Analytics

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:

  • My Modern Ops Template Design Framework
  • How to export query from azure/ How to add in power bi
  • Tricks/ Summarize counts
  • Visualize
  • How to use my modern ops Template
  • Miscellaneous Queries

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 TabDB TableVisual NameOutput & Power BI Visualization FieldsQuery NameIntervalPower BI FilteringQuery
Crash SummaryDHAppReliabilityDaily Outlook CrashOutlook Crash| Multiple CrashesOutlook Crashes 7 Days·  Removed Computer ID
· Removed Computer Column
·  Remove Duplicates
DHAppReliability | where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True"""
Crash SummaryDHOSReliabilityOverall Crashes (Machines)Machines | No Duplicates | Machine CountOverall Crashes Just Machines 7 days·  Removed Abnormal Shutdowns
·  Filtered Rows KernelModeCrashCount does not equal 0
DHOSReliability
Crash SummaryDHOSReliabilityKernel by Time>1 Crash CountOverall Crashes 7 daysDHOSReliability
Crash SummaryDHDriverReliabilityWifi Driver KernelNetwtw06.sys > 1 crash countNetwork Driver Crashes 7 daysDHDriverReliability | where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1"
Critical Apps 7 daysDHAppReliabilityAppfile DisplaynameApps > 50 Machine Count | Crashes Daily = YesApp Overall Crashes Daily Count 7 dayslet top3AppNames = DHAppReliability
| summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion
| top 3 by dcountif_ComputerID
| project AppFileDisplayName, AppVersion
Critical Apps 7 daysDHAppReliabilitySum of App Crash 7 DaysApps > 50 Machine Count | Crashes Daily = YesApp Overall Crashes Daily Count 7 dayslet top3AppNames = DHAppReliability
| summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion
| top 3 by dcountif_ComputerID
| project AppFileDisplayName, AppVersion
Critical Apps 7 DaysDHAppReliabilitySum Last 7 daysApps > 50 Machine Count | Crashes Daily = Yes | SUMApp Overall Crashes Daily Count 7 dayslet top3AppNames = DHAppReliability
| summarize dcountif(ComputerID, HasCrashesDaily) by AppFileDisplayName, AppVersion
| top 3 by dcountif_ComputerID
| project AppFileDisplayName, AppVersion
Outlook DetailsDHAppReliabilityApp File DisplayOutlook has Crashes Daily | App Version | Os Version | Machine NameOutlook Crashes 7 days·  Removed Computer ID
·  Filtered Row Has Crashes Daily = True
·  Removed Duplicates
DHAppReliability
| where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True""
Outlook DetailsDHAppReliabilityApp File VersionFilter by App VersionOutlook Crashes 7 Days·  Removed Computer ID
·  Filtered Row Has Crashes Daily = True
·  Removed Duplicates
DHAppReliability
| where AppFileDisplayName == ""microsoft outlook"" and HasCrashesDaily == ""True""
Outlook DetailsDHAppReliabilityCount by Time GeneratedOutlook 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 DetailsDHAppReliabilityApp File NameChrome has Crashes Daily | App Version | Os Version | Machine name | TimeGoogle 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 DetailsDHAppReliabilityApp File VersionFilter by app versionGoogle 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 DetailsDHAppReliabilityApp File NameExcel has Crashes Daily | App Version | Os Version | Machine name | TimeMicrosoft 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 DetailsDHAppReliabilityApp File VersionFilter by app versionMicrosoft 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 DetailsDHAppReliabilityCount by Time GeneratedExcel crash Total Machine count by Time Generated | Crash Daily = YesMicrosoft 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;
SkypeDHAppReliabilityApp File NameSkype has Crashes Daily | App Version | Os Version | Machine name | TimeMicrosoft 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;
SkypeDHAppReliabilityApp File VersionFilter by App VersionMicrosoft 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;
SkypeDHAppReliabilityCount by Time GeneratedSkype crash Total Machine count by Time Generated | Crash Daily = YesMicrosoft 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;
PhotoDHAppReliabilityApp File NamePhoto has Crashes Daily | App Version | Os Version | Machine name | TimeMicrosoft 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;
PhotoDHAppReliabilityApp File VersionFilter by App VersionMicrosoft 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 daysDHOSReliabilityComputerComputer Name| Crash Count | Model | OS Version | Time GeneratedOverall Crashes 7 Days·  Crash does not Equal 0DHOSReliability
Crash 7 Days (Avg)DHOSReliabilityComputerOver all Crashes 7 days, no duplicate MachinesOverall Crashes (No Duplicates) 7 days·  Crash does not Equal 0
·  Removed Duplicates
·  Removed Errors
DHOSReliability
WiFi CrashesDHDriverReliabilityCrash Events by VersionNetwt06.sys Crash Event by VersionNetwork driver Crashes 7 daysDHDriverReliability
| where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1"
WiFi CrashesDHDriverReliabilityCrash Events by DayNetwt06.sys Crash Count by Time GeneratedNetwork driver Crashes 7 daysDHDriverReliability
| where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1"
WiFi CrashesDHDriverReliabilityCrash Devices ImpactedMachine Count Crashing by Version and Total by Machine CountNetwork Driver Device Impact 7 DaysDHDriverReliability
| where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1
|summarize InstallCount = dcount(ComputerID) by DriverVersion"
WiFi CrashesDHDriverReliabilityTotal Installed DevicesInstall Count by VersionNetwork Driver Installed by Version 7 DaysDHDriverReliability
| where DriverName == ""netwtw06.sys""
|summarize InstallCount = dcount(ComputerID) by DriverVersion"
WiFi CrashesDHDriverReliabilityDriver VersionFilter by Driver Version | Sum by versionNetwork Driver Installed by Version 7 daysDHDriverReliability
| where DriverName == ""netwtw06.sys""
|summarize InstallCount = dcount(ComputerID) by DriverVersion
WiFi DetailsDHDriverReliabilityComputerComputer Name| Crash Count | Model | OS Version | Time GeneratedNetwork Driver Crashes 7 DaysDHDriverReliability
| where DriverName == ""netwtw06.sys"" and DriverKernelModeCrashCount >= 1
Intel Video CrashesDHDriverReliabilityCrashed Events by Versionigdkmd64.sys Crash Event by VersionIntel video driver crashes 7 DaysDHDriverReliability
| where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1"
Intel Video CrashesDHDriverReliabilityCrash Events by Dayigdkmd64.sys Crash Count by Time GeneratedIntel video driver crashes 7 DaysDHDriverReliability
| where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1"
Intel Video CrashesDHDriverReliabilityCrash Devices ImpactedMachine Count Crashing by Version and Total by Machine CountIntel Video Driver Device Impact 7 daysDHDriverReliability
| where DriverName == ""igdkmd64.sys"" and DriverKernelModeCrashCount >= 1
|summarize InstallCount = dcount(ComputerID) by DriverVersion"
Intel Video CrashesDHDriverReliabilityTotal Installed DevicesInstall Count by VersionIntel Video Driver by Version 7 daysDHDriverReliability
| where DriverName == ""igdkmd64.sys""
|summarize InstallCount = dcount(ComputerID) by DriverVersion"
Intel Video CrashesDHDriverReliabilityDriver VersionFilter by Driver Version | Sum by versionIntel Video driver By Version 7 dayDHDriverReliability
| where DriverName == ""igdkmd64.sys""
|summarize InstallCount = dcount(ComputerID) by DriverVersion"
Intel Video DetailsDHDriverReliabilityComputerComputer Name| Crash Count | Model | OS Version | Time GeneratedNetwork Driver Crashes 7 DaysDHDriverReliability
| 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

  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