Query Details
System age And Update Status analysis
Query
// This query identifies devices with an OS installation older than 3 years and calculates the number of days since the last patch.
OsVersion
| join (
WindowsQfe
| summarize LastPatchDate = max(InstalledDate) by Device
) on Device
| where InstallDateTime < ago(1095d)
| project Device, OsName, InstallDateTime, LastPatchDate,
DaysSinceLastPatch = datetime_diff('day', now(), LastPatchDate)Explanation
This query is designed to find devices that have an operating system (OS) installed for more than three years and determine how many days have passed since they were last updated with a patch. Here's a breakdown of what it does:
-
Identify Devices with Old OS Installations: It looks for devices where the OS was installed more than 1,095 days ago (which is equivalent to three years).
-
Find the Last Patch Date: It retrieves the most recent patch installation date for each device.
-
Calculate Days Since Last Patch: For each device, it calculates the number of days that have passed since the last patch was installed.
-
Output: The query outputs a list of devices, including their OS name, installation date, the date of the last patch, and the number of days since the last patch was applied.
Details
Ugur Koc
Released: February 28, 2025
Tables
Keywords
Operators