Please write a KQL query that can detect which devices have a specific KB, e.g.…
- User
Please write a KQL query that can detect which devices have a specific KB, e.g. KB4557854, installed. Ensure that the table you query includes data about KBs.
- Assistant
// Query to detect devices with a specific KB installed (e.g., KB4557854) // Replace 'KB4557854' with the KB number you want to search for. let kbToFind = "KB4557854"; // Use the Update table. This table is commonly used for KB information in Microsoft Defender for Endpoint/Microsoft Sentinel. // Adjust the table name if your environment uses a different table for update information. Update | where HotfixID == kbToFind // Filter for the specific KB | distinct DeviceEntityId, HotfixID, InstalledDateTime // Get unique devices and relevant info | project DeviceEntityId, HotfixID, InstalledDateTime, Title // Project the desired columns | summarize count() by DeviceEntityId, HotfixID, InstalledDateTime, Title // Count occurrences (optional, but useful) | order by InstalledDateTime desc // Order by installation date (optional)Explanation:
-
let kbToFind = "KB4557854";: This line defines a variablekbToFindto store the KB number you're looking for. This makes the query easier to modify. Important: Change"KB4557854"to the actual KB number you want to find. -
Update: This specifies the table to query. TheUpdatetable in Microsoft Defender for Endpoint/Microsoft Sentinel (and similar platforms) typically contains information about installed updates, including KBs. Important: If your environment uses a different table for update information (e.g.,WindowsUpdate), replaceUpdatewith the correct table name. You'll need to explore your data source to find the appropriate table. -
| where HotfixID == kbToFind: This is the core filtering step. It filters theUpdatetable to include only rows where theHotfixIDcolumn (which usually contains the KB number) matches the value stored in thekbToFindvariable. -
| distinct DeviceEntityId, HotfixID, InstalledDateTime: This ensures that you only get unique combinations of device ID, KB number, and installation date. This prevents a device from being listed multiple times if the KB was installed multiple times (which can sometimes happen).DeviceEntityIdis a common column name for the device identifier. -
| project DeviceEntityId, HotfixID, InstalledDateTime, Title: This selects the columns you want to see in the results.DeviceEntityIdis the device identifier,HotfixIDis the KB number,InstalledDateTimeis when the KB was installed, andTitleprovides a description of the KB. Adjust the columns as needed. -
| summarize count() by DeviceEntityId, HotfixID, InstalledDateTime, Title: This is optional, but it's often useful. It groups the results by device ID, KB number, installation date, and title, and then counts the number of occurrences for each group. This can help you identify if a KB was installed multiple times on the same device. -
| order by InstalledDateTime desc: This is also optional. It sorts the results in descending order of installation date, so you see the most recently installed KBs first.
Important Considerations:
- Table Name: The most crucial part is to use the correct table name for update information in your environment. Check your data source documentation or explore the available tables in your KQL environment.
- Column Names: The column names (
HotfixID,DeviceEntityId,InstalledDateTime,Title) might be different in your environment. Adjust the query accordingly. Use the schema explorer in your KQL environment to find the correct column names. - Data Availability: Ensure that the
Updatetable (or the equivalent table in your environment) actually contains the data you need. Data retention policies might affect the availability of older KB installation records. - KB Format: Some systems might store KB numbers with or without the "KB" prefix. If your data uses a different format, adjust the
whereclause accordingly (e.g.,where HotfixID contains kbToFind). - Performance: For very large tables, consider adding more specific filters to improve query performance. For example, you could filter by a specific date range.
How to find the correct table and column names:
- Explore your KQL environment: Most KQL environments (like Azure Data Explorer, Microsoft Sentinel, or Microsoft Defender for Endpoint) have a schema explorer or a way to browse the available tables and their columns.
- Look for tables related to updates or Windows events: Common table names include
Update,WindowsUpdate,SecurityEvent, or similar. - Examine the columns: Look for columns that contain information about KB numbers, device identifiers, and installation dates. Common column names include
HotfixID,KBID,DeviceEntityId,DeviceId,InstalledDateTime,EventTime, etc. - Run a sample query: Try running a simple query like
Update | take 10to see the structure of the table and the available columns. This will help you identify the correct table and column names to use in your query.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.