Help to Defender XDR - KQL to Detection rule for Vulnerability Notification

[askvpb]

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. When I try to convert this rule to run as detection rule, I get the error "Can't save detection rule". Can someone help to me understand how I can fix the issues? 

// Date - 05-05-2024 - Helps to automate daily vulnerability notification alerts to be logged to servicedesk via emails (untill Defender Product gets native feature)
let Timestamp = now();
let ReportId = toint(rand() * 100000000);
DeviceTvmSoftwareVulnerabilities
| 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
| summarize 
    DesktopDeviceNameList = 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())
| project Timestamp, ReportId, CveId, VulnerabilitySeverityLevel, CvssScore, IsExploitAvailable, DesktopDeviceNameList, ServerDeviceNameList, DetailedDeviceList, PublishedDate, LastModifiedTime, VulnerabilityDescription, AffectedSoftware

[#2]

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

[#3]

Thanks Dylan, unfortunately I get he same error as the project ReportID and Timestamp value aren't available line 18

[#4]

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.

[#5]

all good.