TH2.1 Percentile and Statistical Deviation for Outlier Detection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

// Which users are above the 95th percentile for sign-in volume?
// Users in the top 5% may warrant investigation — or may be
// admins and service accounts. The statistic identifies them;
// the analyst determines the context.
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize SignInCount = count() by UserPrincipalName
| extend P95 = toscalar(
    SigninLogs
    | where TimeGenerated > ago(7d)
    | where ResultType == 0
    | summarize SignInCount = count() by UserPrincipalName
    | summarize percentile(SignInCount, 95))
| where SignInCount > P95
| project UserPrincipalName, SignInCount, P95
| sort by SignInCount desc
// Results: users whose sign-in volume exceeds the 95th percentile
// Not all are suspicious — but all are statistically unusual
// Enrich with user context (TH1.4) before drawing conclusions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// Sign-in volume distribution across all users
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize SignInCount = count() by UserPrincipalName
| summarize
    P50 = percentile(SignInCount, 50),
    P75 = percentile(SignInCount, 75),
    P90 = percentile(SignInCount, 90),
    P95 = percentile(SignInCount, 95),
    P99 = percentile(SignInCount, 99),
    Max = max(SignInCount),
    UserCount = count()
// The shape of the distribution tells you about your environment:
// P50 = the typical user's sign-in count
// P95 = the threshold above which only 5% of users fall
// P99 = the extreme tail — these users are doing something unusual
// Max = the single highest volume user — investigate this one

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

// Z-score for per-user file download volume
// Identifies users whose download behavior is statistically anomalous
CloudAppEvents
| where TimeGenerated > ago(7d)
| where ActionType == "FileDownloaded"
| summarize DownloadCount = count() by AccountId
| extend PopulationMean = toscalar(
    CloudAppEvents
    | where TimeGenerated > ago(7d)
    | where ActionType == "FileDownloaded"
    | summarize DownloadCount = count() by AccountId
    | summarize avg(DownloadCount))
| extend PopulationStdev = toscalar(
    CloudAppEvents
    | where TimeGenerated > ago(7d)
    | where ActionType == "FileDownloaded"
    | summarize DownloadCount = count() by AccountId
    | summarize stdev(DownloadCount))
| extend ZScore = (DownloadCount - PopulationMean) / PopulationStdev
| where ZScore > 3.0
// Z-score > 3 = more than 3 standard deviations above the mean
// In a normal distribution, this captures the top 0.13% of values
// These users are extreme outliers in download volume
| project AccountId, DownloadCount, ZScore
| sort by ZScore desc

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// Per-user sign-in volume: this week vs their 30-day average
let baseline = SigninLogs
| where TimeGenerated between (ago(37d) .. ago(7d))
| where ResultType == 0
| summarize BaselineDaily = count() / 30.0 by UserPrincipalName;
let current = SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize CurrentDaily = count() / 7.0 by UserPrincipalName;
current
| join kind=inner baseline on UserPrincipalName
| extend DeviationRatio = CurrentDaily / BaselineDaily
| where DeviationRatio > 3.0
// Users whose current daily sign-in rate is 3x their 30-day average
// A user who normally signs in 10 times/day now signing in 30 times/day
//   is suspicious regardless of whether 30/day is normal for other users
| project UserPrincipalName, CurrentDaily, BaselineDaily, DeviationRatio
| sort by DeviationRatio desc