Forum Discussion
Help to Defender XDR - KQL to Detection rule for Vulnerability Notification
The query essentially functions as part of a monitoring, designed to identify and summarize list of vulnerable applications within a set time frame—particularly, events recorded in the current month....
Hey askvpb
I think I see what you’re trying to do and I’m guessing in the results the Timestamp and ReportId column are not being added to every row? The quick and simple fix would be to use the extend operator instead of the “let”.
So:
DeviceTvmSoftwareVulnerabilities
| extend Timestamp = now()
| extend ReportId = toint(rand() * 100000000)
....
This way it gets applied to every row.
Best,
Dylan
Thanks Dylan, unfortunately I get he same error as the project ReportID and Timestamp value aren't available line 18
- Ahh, forgive me, I see the issue. There’s a ‘summarize’ command. Place those two extends after the summarize block you have. Maybe even right before the ‘project’ in case there are any changes made in the future.
Hi DylanInfosec ,
I am experiencing the same issue. When I use the summarize parameter, it doesn't work properly as a KQL alert.
I tried to recreate the situation described by "askvpb", and although I can see the results of the KQL query, the alert run status fails when I trigger it.
Now, I'm trying to troubleshoot the code, but the error remains the same:
DeviceTvmSoftwareVulnerabilities| where VulnerabilitySeverityLevel == "Critical"|where CveId == "CVE-2024-9392"| extend OSFamily = case(OSPlatform in ("Windows10", "Windows11", "Windows10wVD"), "Desktop",OSPlatform in ("WindowsServer2012R2", "WindowsServer2016", "WindowsServer2019", "WindowsServer2022"), "Server","Other")| where OSFamily != "Other" // Only processing Desktops and Servers| where DeviceName !="" and DeviceName != " " // Exclude blank and space-only DeviceNames| summarizeDesktopDeviceNameList = make_list(iif(OSFamily == "Desktop", DeviceName, "")),ServerDeviceNameList = make_list(iif(OSFamily == "Server", DeviceName, "")),DetailedDeviceList = make_list(bag_pack("DeviceName", DeviceName, "DeviceId", DeviceId, "OSPlatform", OSPlatform)),take_any(SoftwareName, SoftwareVersion, VulnerabilitySeverityLevel, RecommendedSecurityUpdate) by CveId| lookup DeviceTvmSoftwareVulnerabilitiesKB on CveId| where startofmonth(PublishedDate) == startofmonth(now())| extend Timestamp = now()| extend ReportId = toint(rand() * 100000000)| project Timestamp, ReportId, CveId, VulnerabilitySeverityLevel, CvssScore, IsExploitAvailable, DesktopDeviceNameList, ServerDeviceNameList, DetailedDeviceList, PublishedDate, LastModifiedTime, VulnerabilityDescription, AffectedSoftware- all good.
