Query Details
Vulnerable Inactive Devices Detection
Query
//This query identifies devices inactive for more than 10 days that have specific CVE vulnerabilities
//Helps IT engineers track devices where patches couldn't be applied due to inactivity
let CheckinAlertThreshold = 10; // Change the threshold here
DeviceTvmSoftwareVulnerabilities
| where CveId == "CVE-2024-30103"
| join DeviceInfo on DeviceName
| where Timestamp > ago(30d)
| where isnotnull(OSBuild)
| summarize LatestCheckin = max(Timestamp), ReportId = max(ReportId) by DeviceId, DeviceName, OSPlatform, OSBuild, CveId
| extend DaysSinceCheckin = datetime_diff('day', now(), LatestCheckin)
| where DaysSinceCheckin > CheckinAlertThreshold
| project DaysSinceCheckin, LatestCheckin, DeviceName, CveId, OSPlatform, OSBuild, ReportId
| sort by DaysSinceCheckin asc Explanation
This query is designed to help IT engineers identify devices that have been inactive for more than 10 days and have a specific security vulnerability, identified by the CVE ID "CVE-2024-30103". Here's a simple breakdown of what the query does:
-
Set a Threshold: It sets a threshold of 10 days for device inactivity, which can be adjusted if needed.
-
Filter Vulnerabilities: It looks for devices with the specific vulnerability "CVE-2024-30103".
-
Join Device Information: It combines this vulnerability data with additional device information based on the device name.
-
Recent Activity: It considers only the data from the last 30 days and ensures that the operating system build information is available.
-
Summarize Data: For each device, it finds the most recent check-in date and the latest report ID.
-
Calculate Inactivity: It calculates how many days have passed since the device last checked in.
-
Filter Inactive Devices: It filters out devices that have been inactive for more than the specified threshold (10 days).
-
Select and Sort Data: Finally, it selects relevant information (like days since last check-in, device name, and OS details) and sorts the results by the number of days since the last check-in, in ascending order.
This helps IT engineers track devices that might not have received necessary security patches due to being inactive.
Details
Satyanarayana Alladi
Released: November 10, 2024
Tables
Keywords
Operators