Forum Discussion
chetan787
Jun 24, 2021Copper Contributor
KQL query for abnormal account creation
hi all #KQL #kusto would request some help on KQL query to check on abnormal account creation (spam or malicious). the logic is to check the X number of accounts created in Y amount of time then ...
CliveWatson
Jun 25, 2021Former Employee
Some ideas
let threshold_ = 100;
AppTraces
| where TimeGenerated > startofday(ago(7d))
| where Message == 'New Request Received'
| where OperationName == 'GET /Employees/Create'
| summarize count() by bin(TimeGenerated,1d)
| order by TimeGenerated asc
| extend growth_ = prev(count_)
// are there more counts than yesterday?
| extend tooMany_ = iif(growth_ - count_ >= threshold_,strcat("Over threshold of:", threshold_),"ok")
| extend tooMany_ = iif(isempty(growth_),"",tooMany_)
or when you go past a threshold like 1500 of these for any day in the past week
AppTraces
| where TimeGenerated > startofday(ago(7d))
| where Message == 'New Request Received'
| where OperationName == 'GET /Employees/Create'
| summarize count() by bin(TimeGenerated,1d)
| where count_ > 1500
or using ML, you will have to read the links, validate and modify this!!!
// https://docs.microsoft.com/azure/data-explorer/anomaly-detection#time-series-anomaly-detection
// Anomaly scores above 1.5 or below -1.5 indicate a mild anomaly rise or decline respectively. Anomaly scores above 3.0 or below -3.0 indicate a strong anomaly.
AppTraces
| where Message == 'New Request Received'
| where OperationName == 'GET /Employees/Create'
| make-series count_ = count() on TimeGenerated from ago(90d) to now() step 1d by OperationName
| extend (anomalies, score, baseline) = series_decompose_anomalies(count_, 1.5, 7, 'linefit', 1, 'ctukey', 0.01)
| where anomalies[-1] == 1 or anomalies[-1] == -1
| extend Score = score[-1]
//| where Score > 2 or Score < -2
- chetan787Jun 27, 2021Copper Contributorthank you very much