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....
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.
