Forum Discussion
Detection Rule based on Kusto Query
Hi,
I'm new to KQL and trying now to write a query to find Windows Servers that are known to Defender and which are not Onboarded. I've got my query ready and working, which is like this:
DeviceInfo
| where OnboardingStatus contains "Can be onboarded" and MachineGroup contains "Windows Server"
| where Timestamp > ago(1d)
| summarize count() by DeviceName, OSDistribution, OnboardingStatus, DeviceId
| order by DeviceNameAnd this is fine as it returns 1 ocurrence of each server which is not onboarded. But if I go to "Create detection rule", I got the message that I miss the "Timestamp" and "ReportId".
The problem is if I had those to the query, then instead of having 1 ocurrence of each server I have a lot of them, because the table has a lot of those records on different times.
Is there a way to add the necessary "Timestamp" and "ReportId" and keep it displaying just 1 ocurrence of each server, for example, the last one?
Thanks
Hello dmarquesgn ,
The best way to accomplish this would be to use either the arg_max() or arg_min() operator. Both of these operators bring back a single row and as many columns as you want when the specified value is maximized. In your case you would want to minimize the Timestamp column to find the first time it was seen.
Try this:
DeviceInfo | where OnboardingStatus contains "Can be onboarded" and MachineGroup contains "Windows Server" | summarize arg_min(Timestamp, DeviceName, OSDistribution, OnboardingStatus, ReportId) by DeviceId
- MichaelJMelone
Microsoft
Hello dmarquesgn ,
The best way to accomplish this would be to use either the arg_max() or arg_min() operator. Both of these operators bring back a single row and as many columns as you want when the specified value is maximized. In your case you would want to minimize the Timestamp column to find the first time it was seen.
Try this:
DeviceInfo | where OnboardingStatus contains "Can be onboarded" and MachineGroup contains "Windows Server" | summarize arg_min(Timestamp, DeviceName, OSDistribution, OnboardingStatus, ReportId) by DeviceId- Thanks Michael, that was what I needed.
