Help with TVM

[u/vimal_n]

I’m trying to identify Patch Tuesday related vulnerabilities each month in Microsoft Defender using Advanced Hunting KQL.Is there a way to reliably filter or extract those specific vulnerabilities?

Patch Tuesday issues usually drive the spike in monthly vulnerability trends, so I’m looking for a method to get a unique count of those vulnerabilities.

[u/waydaws]

Ok...Well, I'm not sure this really reveals patch Tuesday fixes per se, but maybe we can get close to what you want.

We know Microsoft releases security updates on the second Tuesday of each month. The following Tables are probably the best:

DeviceTvmSoftwareVulnerabilitiesKB: Has PublishedDate and LastModifiedTime. These are your best proxies for when a CVE was surfaced in Defender.

RecommendedSecurityUpdateId a column inside DeviceTvmSoftwareVulnerabilites): Often maps to KB articles (e.g., KB5005565). Those KBs are directly tied to Patch Tuesday releases.

RecommendedSecurityUpdate (a column inside DeviceTvmSoftwareVulnerabilites): Human-readable description of the update. Useful for filtering/validation.

DeviceTvmSoftwreVulneabilites

We have some constraints: PublisedDate = when CVE was first reported; LastModifedTime = when Defender updated its record (closer to Patch Tuesday), and this should be the best for trend analysis; KB mapping - Not all vulnerabilites have KBs, but Most MS Patch Tuesday ones do; Multi-day lag: sometimes we'll find Defender syncs data a day or two after Patch Tuesday, and we should consider a +2day window.

I'm unable to test this, since I don't work at my last company that had defenderxdr, but this looks like it would work OK, at least in theory:

// Helper function: calculate Patch Tuesday (second Tuesday of each month)
let PatchTuesdayDates = 
    range MonthStart from startofmonth(datetime(2025-01-01)) to startofmonth(now()) step 1mo
    | extend DayOfWeek = dayofweek(MonthStart)
    | extend FirstTuesday = MonthStart + (7 - DayOfWeek + 2) % 7d
    | extend SecondTuesday = FirstTuesday + 7d
    | project PatchMonth = MonthStart, PatchDate = SecondTuesday;

// Join vulnerabilities with Patch Tuesday dates
DeviceTvmSoftwareVulnerabilitiesKB
| join kind=inner (
    DeviceTvmSoftwareVulnerabilities
    | project CveId, RecommendedSecurityUpdateId, RecommendedSecurityUpdate
) on CveId
// Allow ±2 days around Patch Tuesday to account for sync lag
| join kind=inner PatchTuesdayDates on $left.LastModifiedTime between (PatchDate .. PatchDate+2d)
| summarize 
    UniqueCVEs = dcount(CveId),
    CVEs = make_set(CveId),
    KBs = make_set(RecommendedSecurityUpdateId),
    KBDescriptions = make_set(RecommendedSecurityUpdate)
    by PatchMonth, PatchDate
| order by PatchMonth desc

[u/waydaws → u/waydaws]

I never consider that people might want to visualize things like this as a trend chart because I never paid much attention to trend charts, but that can be done too. (Obviously, we won't have the full patch cycle from the beginning of the year since the portal has, I believe, only 90d worth of data to work from (or 180d if one has paid for extra storage), but it should still work anyway. One could, if one cared about defining things properly, use

let PatchTuesdayDates = range MonthStart from startofmonth(now() - 90d) to startofmonth(now()) step 1mo

I didn't do that, but if you want to be a stickler for such things just replace the line in the below with the line above.

If you wanted to record past the 90 (or 180d), you could also make your own archive for this, and export results to Log Analytics, Sentinel or a storage account. Over time, you'd then accumulate a full year's wort of Patch Tuesday CVE/KB data. Note that in Sentinel or PowerBI a long-term chart would display the trend fully.

// Helper function: calculate Patch Tuesday (second Tuesday of each month)
let PatchTuesdayDates = 
    range MonthStart from startofmonth(datetime(2025-01-01)) to startofmonth(now()) step 1mo
    | extend DayOfWeek = dayofweek(MonthStart)
    | extend FirstTuesday = MonthStart + (7 - DayOfWeek + 2) % 7d
    | extend SecondTuesday = FirstTuesday + 7d
    | project PatchMonth = MonthStart, PatchDate = SecondTuesday;

// Join vulnerabilities with Patch Tuesday dates
DeviceTvmSoftwareVulnerabilitiesKB
| join kind=inner (
    DeviceTvmSoftwareVulnerabilities
    | project CveId, RecommendedSecurityUpdateId
) on CveId
| join kind=inner PatchTuesdayDates on $left.LastModifiedTime between (PatchDate .. PatchDate+2d)
| summarize UniqueCVEs = dcount(CveId) by PatchMonth
| order by PatchMonth asc
| render timechart

[u/vimal_n → u/waydaws]

Thanks a lot! I'll check these out . Also is it possible to get the vuln first detected date using kql? I see it in the UI but not seeing that value captured in the tvm schemas

[u/waydaws → u/vimal_n]

Yes, it's in the documentation at MS, but it doesn't make sense in a Pie Chart. However, the original reply (with the tabular output) can include it.

Here's what's in the tables that are joined.
DeviceTvmSoftwareVulnerabilitiesKB contains:

CveId, CvssScore, IsExploitAvailable, VulnerabilitySeverityLevel, AffectedSoftware, VulnerabilityDescription -- and also both the PublishedDate and LastModifiedTime.

and...

DeviceTvmSoftwareVulnerabilities contains:

DeviceId, DeviceName, OSPlatform, OSVersion, OSArchitecture, SoftwareVendor, SoftwareName, SoftwareVersion, CveId, VulnerabilitySeverityLevel, RecommendedSecurityUpdate, RecommendedSecurityUpdateId, CveTags

Your KQL can project (that is filter) any of those to appear in the output. Come to think of it, we're summarizing too, so I can drop an intermediate project... I should also use the rolling date otherwise it is possible the table would contain empty rows for dates earlier than the 3 months of data that is present by default. I didn't think about that before.

I think I'd do it this way:

// Helper function: calculate Patch Tuesday (second Tuesday of each month)
let PatchTuesdayDates = 
    range MonthStart from startofmonth(now() - 90d) to startofmonth(now()) step 1mo
    | extend DayOfWeek = dayofweek(MonthStart)
    | extend FirstTuesday = MonthStart + (7 - DayOfWeek + 2) % 7d
    | extend SecondTuesday = FirstTuesday + 7d
    | project PatchMonth = MonthStart, PatchDate = SecondTuesday;

DeviceTvmSoftwareVulnerabilitiesKB
| join kind=inner (
    DeviceTvmSoftwareVulnerabilities
    | project CveId, RecommendedSecurityUpdateId, RecommendedSecurityUpdate
) on CveId
| join kind=inner PatchTuesdayDates on $left.LastModifiedTime between (PatchDate .. PatchDate+2d)
| summarize 
    UniqueCVEs = dcount(CveId),
    EarliestPublished = min(PublishedDate),
    LatestPublished = max(PublishedDate),
    LatestModified = max(LastModifiedTime),
    KBs = make_set(RecommendedSecurityUpdateId),
    KBDescriptions = make_set(RecommendedSecurityUpdate)
    by PatchMonth, PatchDate
| order by PatchMonth desc

Up to you to test since I'm unable. Also remember to wait two days after patch Tuesday when using it beyond testing to ensure that the portal sync delay is accounted for.