Custom detection rule

Question

We see that 90% of the SPAM geared toward students comes from fake Gmail accounts. In Advanced Hunting I created a KQL query to find any Gmail account that sent more than 40 emails from the same account I saved it as a Custom Detection Rule.  EmailEvents

| where ingestion_time() > ago(3h)

| where SenderFromDomain == "gmail.com"

| summarize (Timestamp, ReportId)=arg_max(Timestamp, ReportId), EmailCount = count() by SenderFromAddress, DeliveryLocation ,Subject

| where EmailCount >= 40

| sort by EmailCount

This is working great and after a month of testing/running, I wanted to change this so it would trigger the rule to automatically soft delete the emails found. I added the necessary tables (NetworkMessageId, RecipientEmailAddress), which caused the query to fail.

Below is the updated query: EmailEvents

| where ingestion_time() > ago(3h)

| where SenderFromDomain == "gmail.com"

| summarize (Timestamp, ReportId)=arg_max(Timestamp, ReportId), EmailCount = count() by SenderFromAddress, DeliveryLocation ,Subject, NetworkMessageId, RecipientEmailAddress

| where EmailCount >= 40

| sort by EmailCount

What am I doing wrong?

Answer 1

Both queries work in my lab. I am using union Email* since I was unsure of the target table. Try this.

union Email*
| where ingestion_time() > ago(3h)
| where SenderFromDomain contains "gmail"
| summarize (Timestamp, ReportId)=arg_max(Timestamp, ReportId), EmailCount = count() by SenderFromAddress, DeliveryLocation ,Subject, NetworkMessageId, RecipientEmailAddress
| where EmailCount >= 40
| sort by EmailCount