Custom detection in MDE

DeviceLogonEvents
| where Timestamp >=ago(1d)
| where ActionType=="LogonSuccess" and LogonType=="Interactive"
| extend parsing=parse_json(AdditionalFields)
| extend LocalLogon=parsing["IsLocalLogon"]
| where LocalLogon=="true"
| summarize count(), Timestamp=max(Timestamp) by DeviceName, AccountName, DeviceId, ReportId
| project DeviceName, AccountName, DeviceId, ReportId, Timestamp
| join kind=inner (DeviceInfo
| where Timestamp >=ago(1d)
| where MachineGroup=="Joomla"
| project MachineGroup, DeviceName, Timestamp, DeviceId, ReportId) on DeviceName
| join kind=inner (DeviceNetworkInfo
| where Timestamp >=ago(1d)
| where NetworkAdapterType startswith "Wireless" and ConnectedNetworks !=""
| extend SSID=todynamic(ConnectedNetworks)
| mvexpand SSID
| extend SSID=tostring(SSID.Name)
| where SSID contains "Jumbo"
| summarize count() by DeviceName, SSID, Timestamp,ReportId, DeviceId
| project DeviceName, SSID, Timestamp, ReportId, DeviceId) on DeviceName
| project DeviceName, AccountName, SSID, Timestamp, ReportId, DeviceId
| summarize dc_devices=dcount(DeviceName), DeviceName=max(DeviceName), ReportId=max(ReportId), Timestamp=max(Timestamp), DeviceId=max(DeviceId) by AccountName, SSID
| where dc_devices>=5

@Ankush Kumar Thank you for sharing the query, checking internally on this issue, however I used one of our internal AI tool to review your ask, here is the response, would recommend checking if the below response helps to resolve your issue, however I am checking internally as well to get more inputs on the error message you mentioned above.

The error message you are seeing indicates that the aggregation expressions for the columns ReportId, AlertId, BehaviorId, or DeviceId and Timestamp are not correct.

In your query, you are already selecting ReportId, DeviceId, and Timestamp in the final projection. However, you need to make sure that the aggregation expressions for these columns are correct in the summarize statements.

For example, in the first summarize statement, you are aggregating by DeviceName, AccountName, DeviceId, and ReportId. You are also selecting Timestamp using the max aggregation function. This is correct.

However, in the second summarize statement, you are aggregating by DeviceName, SSID, Timestamp, ReportId, and DeviceId. You are not selecting Timestamp using an aggregation function. You need to change this to Timestamp=max(Timestamp) to make sure that the aggregation expression is correct.

Similarly, in the final summarize statement, you are aggregating by AccountName and SSID. You are selecting ReportId and Timestamp using the max aggregation function. This is correct.

Once you have made these changes, you should be able to save the query as a Custom Detection in Microsoft Security Center without any issues.

Let me try this one; I will keep you posted about results. Many Thanks.

DeviceLogonEvents
| where Timestamp >=ago(1d)
| where ActionType=="LogonSuccess" and LogonType=="Interactive"
| extend parsing=parse_json(AdditionalFields)
| extend LocalLogon=parsing["IsLocalLogon"]
| where LocalLogon=="true"
| summarize count() by DeviceName, AccountName
| project DeviceName, AccountName
| join kind=inner (DeviceInfo
| where Timestamp >=ago(1d)
| where MachineGroup=="Joomla"
| project MachineGroup, DeviceName) on DeviceName
| join kind=inner (DeviceNetworkInfo
| where Timestamp >=ago(1d)
| where NetworkAdapterType startswith "Wireless" and ConnectedNetworks !=""
| extend SSID=todynamic(ConnectedNetworks)
| mvexpand SSID
| extend SSID=tostring(SSID.Name)
| where SSID contains "jumbo"
| summarize count(), Timestamp=max(Timestamp) by DeviceName, SSID,ReportId, DeviceId
| project DeviceName, SSID, Timestamp, ReportId, DeviceId) on DeviceName
| summarize dc_devices=dcount(DeviceName), DeviceName=max(DeviceName), ReportId=max(ReportId), Timestamp=max(Timestamp), DeviceId=max(DeviceId) by AccountName, SSID
| where dc_devices>=5
| summarize by dc_devices, DeviceName, ReportId, Timestamp, DeviceId, AccountName, SSID

@Ankush Kumar Noticed you changed the kusto query, did the above suggestion helped or still facing the issue?