Incorrect Analytic calcluations.

[CyberGumShew]

Describe the bug
I believe the logic on some of the analytic rules may be incorrect. For example "SharePoint_Downloads_byNewIP" but others using similar logic.

In particular, I find the below method of calculating a "baseline" to be incorrect. I believe to achieve the desired effect, there should be some form of bin/bucketing the time by 1 day (since the recent counts are calculated by 1 day intervals in the searches). The first summarize to get a Count is performed over a 13 day period (-14 to -1 days) across only UserId, Operation, Site_Url, ClientIP. This is the total count. Then, the second summarize takes the average of that count over the same fields, which is just that count, not an average over any period of time. This makes the baseline average calculation incorrect.

  let userBaseline = OfficeActivity
  | where TimeGenerated between(ago(starttime)..ago(endtime))
  | where RecordType =~ szSharePointFileOperation
  | where Operation in~ (szOperations)
  | where isnotempty(UserAgent)
  | summarize Count = count() by UserId, Operation, Site_Url, ClientIP
  | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url, ClientIP;

The solution:
Use a bucket for time of 1 day spans and the first summarize should count by those time buckets. Then the second summarize will effectively average out the count.

I believe there are other analytics in this repo using this same or similar logic. They would all have the same issue.

[v-visodadasi]

Hi @CyberGumShew , Thanks for flagging this issue, we will investigate this issue and get back to you with some updates. Thanks!

[v-visodadasi]

Hi @CyberGumShew , Just to confirm, you would like me to modify the SharePoint_Downloads_byNewIP analytic rule to calculate the baseline average count of file operations correctly.
Specifically, you want me to:

1. Use 1-day time buckets to count the file operations.
2. Calculate the average count of the file operations over these 1-day time buckets.

Is that correct? Please let me know if I've understood your request accurately.

[CyberGumShew]

That is what I perceive as the intent of the detection but I would defer to the original author of the rule if that is a misunderstanding.

[v-visodadasi]

Hi @CyberGumShew , Sorry We are still not getting your request. It would be really great if you could give more descriptive on this issue. It would be really helpful for us if required to reach out to query author and share these details.

Based on your issue description we have modified the below query to meet your expectations:

let userBaseline = OfficeActivity
| where TimeGenerated between(ago(starttime)..ago(endtime))
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| where isnotempty(UserAgent)
| summarize Count = count() by UserId, Operation, Site_Url, ClientIP, bin(TimeGenerated, 1d)
| summarize AvgCount = avg(Count) by UserId, Operation, Site_Url, ClientIP;

[CyberGumShew]

Yes, I believe that would meet the intent of the detection.