In this blog post, I will show how to create and schedule a custom report for monitoring Windows Update using a combination of KQL and Azure Automation. The Windows Update Compliance is offered through the Azure portal and helps you monitor security, quality, and feature updates for Windows 10 or 11. It also gives you a report of the device and updates issues related to compliance that need attention.
The Update Compliance dashboard has lots of data; in the workspace, you will find numerous tables holding different data types. You can use KQL queries to get the data you are interested in! Let’s get started then with creating some KQL queries and automating them as email or even getting posted in the Teams channel. We will cover the following:
- Creating a blank logic app and workflow.
- Adding a Recurrence trigger that specifies the schedule to run the workflow.
- Add a custom query to the workflow.
- Add an action that sends an email
- Add a webhook for Teams
Azure Logic Apps, from Microsoft Azure, is a cloud-based Platform-as-a-Service (PaaS) used to automate tasks, workflows, etc. It helps create and design automated workflows that integrate services, systems, and applications.
Below are the steps to create a logic app:
- Sign in to the Azure portal with your Azure account.
- In the Azure search box, enter
logic apps, and select Logic apps.
- On the Logic apps page, select Add.
- On the Create Logic App pane, on the Basics tab, provide the following basic information about your logic app:
- When you’re ready, select Review + Create. On the next page, validate and click Create.
Design The Workflow
As the next step, we will add the Recurrence trigger, which runs the workflow based on a specified schedule. Every workflow must start with a trigger, which fires when a specific event happens or when new data meets a specific condition.
- On the workflow designer, under the search box, select Built-in.
- In the search box, enter recurrence, and select the Recurrence trigger.
- On the Recurrence shape, select the ellipses (…) button, and then select Rename. Rename the trigger with this description:
- Inside the trigger, change these properties as required
- In the workflow designer, under the Recurrence trigger, click SAdd an Action
- In the search box, enter Run query and visualize results.
- Sign in with user credentials or select Service Principal and fill in the Parameters for creating a connection.
- In the Query field, enter the Log Analytics query you want to report on. For example, I want to schedule a report on the compliance of the latest 2 security patches, so I have used the below query:
- Keep HTML Table as chart type, as this will give you the same output as shown in Log Analytics table
- Add an action that sends you an email when the query is executed.
You can customize the mail body, CC, To & From attributes to fit your requirement.
- If you also want this report sent to your Teams channel, add the WebHook URL for your Teams.
As the last step, save your Logic App and click Run to test it.
Verify The Report
Open your email client app, and you should see this email with the report attached in your inbox.
Pretty neat & simple 😊. With this article, we saw how easily the queries in Windows Compliance could be automated/scheduled. Rest, it’s all your imagination and scripting power to extend it as per your business requirement.
Signing off for the day. Keep learning & keep sharing.