Creating KQL Query to Get Azure VMs Not Patched Since 30 Days: A Step-by-Step Guide

Are you struggling to keep track of your Azure Virtual Machines (VMs) that haven’t received security patches in over 30 days? Worry not, friend! In this article, we’ll dive into the world of Kusto Query Language (KQL) and create a query that will help you identify those VMs that need some TLC.

What is KQL?

Kusto Query Language (KQL) is a powerful query language used to explore and analyze data in Azure Data Explorer, Azure Log Analytics, and Azure Monitor. It’s similar to SQL, but with a more concise and expressive syntax. KQL is designed to work with structured and semi-structured data, making it perfect for querying Azure resources.

Why Do I Need to Monitor Patched VMs?

Unpatched VMs are a security risk waiting to happen. Cyber threats and vulnerabilities can exploit these gaps, leading to data breaches, system compromise, and even ransomware attacks. By monitoring patched VMs, you can:

• Ensure compliance with security policies and regulations
• Reduce the attack surface of your Azure infrastructure
• Improve the overall security posture of your organization

Creating the KQL Query

Now, let’s get to the good stuff! To create the KQL query, we’ll need to follow these steps:

1. Connect to your Azure Log Analytics workspace
2. Use the Update table to query update history
3. Filter results to show only Azure VMs
4. Apply the 30-day threshold for patching
5. Format the output for easy reading

let VMsNotPatched = 
  Update 
  | where classification == 'SecurityUpdate' 
  | where todatetime(lastupdatedatetime) < now(-30d) 
  | where computer in ((Resources 
      | where type == 'microsoft.compute/virtualmachines' 
      | project Computer = name))
  | summarize 
    make_list(Computer), 
    make_list(name)
  | project-away _ 
  | sort by Computer asc;
VMsNotPatched

Breaking Down the Query

Let's dissect the query to understand what each part does:

• Update: We're using the Update table, which contains update history for all resources.
• classification == 'SecurityUpdate': We filter the results to show only security updates.
• todatetime(lastupdatedatetime) < now(-30d): We convert the lastupdatedatetime column to a datetime format and compare it to the current time minus 30 days. This gives us the VMs that haven't received updates in over 30 days.
• computer in ((Resources ...)): We use a subquery to get a list of VMs from the Resources table. We filter the results to show only Azure VMs (type == 'microsoft.compute/virtualmachines') and project the name column as Computer.
• summarize ...: We use the summarize operator to group the results by Computer and create a list of affected VMs.
• project-away _: We remove the unnecessary columns from the output.
• sort by Computer asc: Finally, we sort the output in ascending order by Computer name.

Running the Query

Now that we have our KQL query, let's run it in Azure Log Analytics:

1. Log in to your Azure Log Analytics workspace.

2. Click on the "Queries" button in the top left corner.

3. Click on the "New query" button.

4. Paste the KQL query into the query editor.

5. Click the "Run" button.

Understanding the Output

The query output will show you a list of Azure VMs that haven't received security patches in over 30 days, along with the number of updates required:

Computer	Updates Required
vm-001	5
vm-002	3
vm-003	2

Next Steps

Now that you have a list of VMs that need patching, it's time to take action:

• Review the patching history for each VM to identify the missing updates.
• Apply the necessary patches using Azure Update Management or your preferred patching tool.
• Re-run the query to verify that the VMs have been updated successfully.

By following these steps, you'll be able to identify and address potential security risks in your Azure environment, ensuring the security and compliance of your infrastructure.

Conclusion

Creating a KQL query to get Azure VMs not patched since 30 days is a straightforward process that can help you maintain a secure and compliant Azure environment. By following this guide, you'll be able to identify and address potential security risks, reducing the attack surface of your infrastructure.

Remember to regularly review and update your query to ensure it remains effective in identifying unpatched VMs. Happy querying!

Frequently Asked Question

Getting stuck while creating a KQL query to get Azure VMs not patched since 30 days? Worry not, we've got you covered!

Now, go ahead and craft your KQL query to get Azure VMs not patched since 30 days with confidence!