CODEWITHSUNDEEP
azure-data-explorerkqlkusto-explorer
TheftAuto
TheftAuto

Reputation: 3

Grouping by Username in MS Sentinel

How do I group by Username in Sentinel? I get confused with the project, extend, join, and summerize operations. Sanitized KQL Code and Picture attached. This is for mass downloads of files, but number has been lowered to trigger for MSP.

let threshold = 100;
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded"]);
let starttime = 10m;
let endtime = 600m;
let historicalActivity =
OfficeActivity
| where TimeGenerated between(ago(starttime)..ago(endtime))
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize historicalCount = count() by ClientIP, RecordType, Operation;
let recentActivity = OfficeActivity
| where TimeGenerated > ago(endtime)
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize min(Start_Time), max(Start_Time), recentCount = count() by ClientIP, RecordType, Operation;
let RareIP = recentActivity | join kind= leftanti ( historicalActivity ) on ClientIP, RecordType, Operation
// More than 100 downloads/uploads from a new IP
| where recentCount > threshold;
OfficeActivity 
| where TimeGenerated >= ago(endtime) 
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| join kind= inner (RareIP) on ClientIP, RecordType, Operation
| where Start_Time between(min_Start_Time .. max_Start_Time)
| summarize StartTimeUtc = min(min_Start_Time), EndTimeUtc = max(max_Start_Time) by RecordType, Operation, UserType, UserId, ClientIP, OfficeWorkload, Site_Url, OfficeObjectId, UserAgent, IPSeenCount = recentCount
| extend timestamp = StartTimeUtc, AccountCustomEntity = UserId, IPCustomEntity = ClientIP, URLCustomEntity = Site_Url
| order by IPSeenCount desc, ClientIP asc, Operation asc, UserId asc
| where UserAgent <> "FileZip/1.0"
| where Site_Url contains "https://abc.sharepoint.com/sites/"

Results in image: https://ibb.co/QjW308q. I am trying to group by UserIDs or "AccountCustomIdentity" and not have seperate rows for each. Many thanks for any help!

Upvotes: 0

Views: 1424

Answers (1)

Avnera
Avnera

Reputation: 7618

If you are not interested to see the userIds, you can simply remove it from the "summarize" line here (this is the applicable line without it):

| summarize StartTimeUtc = min(min_Start_Time), EndTimeUtc = max(max_Start_Time) by RecordType, Operation, UserType, ClientIP, OfficeWorkload, Site_Url, OfficeObjectId, UserAgent, IPSeenCount = recentCount

Once you do this, these line will show and error under the UserId expressions:

| extend timestamp = StartTimeUtc, AccountCustomEntity = UserId, IPCustomEntity = ClientIP, URLCustomEntity = Site_Url
| order by IPSeenCount desc, ClientIP asc, Operation asc, UserId asc

You should remove these expressions from these lines as well and run the query. The UserIds will be gone.

As a side note, UserIds in this query is duplicated and show up twice in the results, once as UserId and once as AccountCustomEntity - this is strange.

Upvotes: 1

Related Questions