Custom detection in MDE

joomla3597 35

I am trying to create Custom Detection in Microsoft Security Center where my query has multiple Join and summarize statements.

Whenever I am running query its providing results but after saving in Custom Detection form and under its results section its giving below message, although I already have Timestamp, ReportId, DeviceId as an output coming.

"No events match the given event identifiers (a combination of ReportId, AlertId, BehaviorId, or DeviceId and Timestamp). Edit the query's aggregation expressions for these columns and try again."

Can anyone help me to understand how I can fix the above issue?

joomla3597 35 Reputation points

2024-04-24T07:39:57.05+00:00

Your suggestion helped me to update my query which I posted above. Thanks for your support.

Accepted answer

Givary-MSFT 32,991 Reputation points Microsoft Employee

2024-04-24T06:29:13.7966667+00:00

@Ankush Kumar Thank you for reaching out to us, for better understanding of the issue, if you can share the screenshot/query which you are trying to execute.
Please sign in to rate this answer.

1 person found this answer helpful.
joomla3597 35 Reputation points

2024-04-24T06:54:33.2366667+00:00

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

Givary-MSFT 32,991 Reputation points Microsoft Employee

2024-04-24T07:15:13.27+00:00

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

joomla3597 35 Reputation points

2024-04-24T07:16:57.7066667+00:00

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

joomla3597 35 Reputation points

2024-04-24T07:32:44.7533333+00:00

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

Givary-MSFT 32,991 Reputation points Microsoft Employee

2024-04-24T07:37:05.8766667+00:00

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

joomla3597 35 Reputation points

2024-04-24T07:38:55.0933333+00:00

yes your suggestion helped me to update my query which in the result solved my issue. I posted my updated query to help the community.

Givary-MSFT 32,991 Reputation points Microsoft Employee

2024-04-24T07:45:10.8633333+00:00

@Ankush Kumar Thats great, glad AI suggestion helped to resolve your issue, I have summarized our conversation as an answer to your question.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Jan Farwick 0 Reputation points

2024-09-26T12:56:11.6333333+00:00

I have a very similar (but probably simpler) query where I run into the same problem.

I actually only need an alarm if a user logs in several times (5 times a day) on his client as local admin

The following query works as a query, but not as a custom detection rule

DeviceLogonEvents

| where DeviceName startswith “NB”

| where ActionType==“LogonSuccess” and LogonType=="Interactive”

| extend parsing=parse_json(AdditionalFields)

| extend LocalLogon=parsing[“IsLocalLogon”]

| where LocalLogon=="true”

| where Timestamp > ago(1d)

| summarize LogonCount=count(), LatestTimestamp=max(Timestamp) by DeviceId, AccountName, bin(Timestamp, 1d), DeviceName

| where LogonCount >= 5

| where AccountName startswith “admin_”

| project DeviceId, DeviceName, AccountName, LatestTimestamp, LogonCount, ReportId = tostring(DeviceId), Timestamp = LatestTimestamp
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Custom detection in MDE

1 additional answer

Your answer