KQL

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3170603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EKQL%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3170603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3E%26nbsp%3BHi%2C%20I%20am%20trying%20to%20modify%20the%20below%20KQL%20query%20to%20use%20as%20a%20scheduled%20log%20analytics%20rule%20in%20Microsoft%20Sentinel%20to%20only%20trigger%20an%20incident%20when%20more%20than%2010%20emails%20have%20been%20sent%20on%20behalf%20of%20a%20user%20in%20a%20day.%20Any%20input%20or%20guidance%20will%20be%20highly%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EOfficeActivity%26lt%3B%5C%2FSPAN%26gt%3B%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Operation%20%3D%3D%20%22SendOnBehalf%22%26lt%3B%5C%2FSPAN%26gt%3B%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20by%20TimeGenerated%2C%20UserId%2C%20ClientIP%2C%20SendOnBehalfOfUserSmtp%2C%20SendAsUserSmtp%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3170603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EKusto%20Query%20Language%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3170603%22%20slang%3D%22en-US%22%3EKQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3170603%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BHi%2C%20I%20am%20trying%20to%20modify%20the%20below%20KQL%20query%20to%20use%20as%20a%20scheduled%20log%20analytics%20rule%20in%20Microsoft%20Sentinel%20to%20only%20trigger%20an%20incident%20when%20more%20than%2010%20emails%20have%20been%20sent%20on%20behalf%20of%20a%20user%20in%20a%20day.%20Any%20input%20or%20guidance%20will%20be%20highly%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EOfficeActivity%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Operation%20%3D%3D%20%22SendOnBehalf%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20by%20TimeGenerated%2C%20UserId%2C%20ClientIP%2C%20SendOnBehalfOfUserSmtp%2C%20SendAsUserSmtp%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3170603%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EKusto%20Query%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

 Hi, I am trying to modify the below KQL query to use as a scheduled log analytics rule in Microsoft Sentinel to only trigger an incident when more than 10 emails have been sent on behalf of a user in a day. Any input or guidance will be highly appreciated.

OfficeActivity
| where Operation == "SendOnBehalf"
| summarize by TimeGenerated, UserId, ClientIP, SendOnBehalfOfUserSmtp, SendAsUserSmtp

2 Replies

@tijan2018 You need to add a count command to your summarize on a unique value for each row that is a separate Email like ItemName.  Note that I do not have any data in my OfficeActivity with the needed operation so I cannot guarantee that is a good column.   Then you can filter where the count is greater than 10.  It should look something like what is shown below

OfficeActivity
| where Operation == "SendOnBehalf"
| summarize count(ItemName) by TimeGenerated, UserId, ClientIP, SendOnBehalfOfUserSmtp, SendAsUserSmtp
| where _count>10

 

Awesome Gary. Thanks for the feedback. Very much appreciated.