KQL - Check value every hour to see if it's higher than the week average

Question

I'm new to kql and defender, looking for help in creating a hunting kql query which checks the avg number of alerts in the last 7 days on defender for endpoint and if at any hour the number of generated alerts spikes and goes above the 1week average number of alerts, it should trigger an alert. For now I have this, but it just checks against a fixed threshold (10), is it possible to change the fixed threshold into a 1week average? And check every hour if in the last hour more alerts were generated Then the week average number of alerts?

let Threshold = 10;
let starttime = 7d;
let endtime = 0d;
let timeframe = 1h;
AlertEvidence
| join (AlertInfo) on AlertId
| project Timestamp, AlertId, Severity, ServiceSource, EntityType, DeviceId, DeviceName, Categories, DetectionSource, Title
| where Timestamp between (startofday(ago(starttime))..startofday(ago(endtime)))
| summarize Total = dcount(AlertId) by Timestamp=bin(Timestamp, 1h), Severity
| extend AboveThreshold = iff(Total > Threshold, "YES", "NO")
| where AboveThreshold=="YES"

Thank you for any help in learning kql

Answer 1

How about this example, which you can adapt?

AppEvents | where TimeGenerated between ( ago(14d) .. ago(1d) ) // avg for 13 days | summarize avg_=count()/13 by Name | join ( AppEvents | where TimeGenerated between ( ago(1d) .. now() ) // count for today only | summarize count() by Name ) on Name // only show those above the daily average | where count_ >= avg_

https://portal.azure.com#@4bd2cd73-7c32-48aa-8a02-646c8bc0d343/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA42QMQ%252BCQAyFd37FG48FQnTFhMG4ObmTQypg5EqOA4Lxx9sDgqudXtPXd18v67rzSMb1wQdTTZZwa1q6kCGrHZUoyE1EBgq6YpUcyxBRtGqRYRDH0GOFB1skB5R69kH90LbaNm%252Fyszy982CcCmMxFDOuuiXxPLkxCFQAqWyH8N1fICuH4Ul5Cr8mJMtDC4tjQQGb17xF%252FpA2mh0lFNuqJMFvoK95gqu59%252Fw8kmiS0xoZ6VFwKsL%252BW0tajlO6nPoFgiy5R04BAAA%253D