Leverage Windows Analytics for Modern Ops Pt 2
– Business Intelligence –
Make Data Attractive and Meaningful to the business
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
- 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
Add comment