TH2.13 Reusable Functions and Saved Queries

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

// Inline function: per-user IP baseline for the last 30 days
let UserIPBaseline = (baselineStart:timespan, baselineEnd:timespan) {
    SigninLogs
    | where TimeGenerated between (ago(baselineStart) .. ago(baselineEnd))
    | where ResultType == 0
    | summarize
        KnownIPs = make_set(IPAddress, 30),
        KnownCountries = make_set(
            tostring(LocationDetails.countryOrRegion), 10)
        by UserPrincipalName
};
// Use it in the hunt query:
let baseline = UserIPBaseline(37d, 7d);
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| join kind=inner baseline on UserPrincipalName
| where not(IPAddress in (KnownIPs))
// The function encapsulates the baseline logic
// Change the baseline window by changing the parameters
// The hunt query stays clean and focused on the detection logic

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

// Create this as a saved function named "HuntUserIPBaseline"
// In Log Analytics: Save → Save as function
// Function name: HuntUserIPBaseline
// Parameters: BaselineDays:int = 30, GapDays:int = 7
SigninLogs
| where TimeGenerated between (
    ago(totimespan(strcat(BaselineDays + GapDays, "d")))
    .. ago(totimespan(strcat(GapDays, "d"))))
| where ResultType == 0
| summarize
    KnownIPs = make_set(IPAddress, 30),
    KnownCountries = make_set(
        tostring(LocationDetails.countryOrRegion), 10),
    AvgDailySignIns = count() / BaselineDays
    by UserPrincipalName

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Call the saved function — clean, readable hunt query
let baseline = HuntUserIPBaseline(30, 7);
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| join kind=inner baseline on UserPrincipalName
| where not(IPAddress in (KnownIPs))
| summarize NewIPCount = dcount(IPAddress),
    NewIPs = make_set(IPAddress, 5)
    by UserPrincipalName
| where NewIPCount > 0

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

// Save as function: HuntUserDownloadBaseline
// Parameters: BaselineDays:int = 30, GapDays:int = 7
CloudAppEvents
| where TimeGenerated between (
    ago(totimespan(strcat(BaselineDays + GapDays, "d")))
    .. ago(totimespan(strcat(GapDays, "d"))))
| where ActionType == "FileDownloaded"
| summarize
    BaselineTotal = count(),
    BaselineDaily = count() / BaselineDays,
    BaselineSites = make_set(Application, 20)
    by AccountId
// Per-user download volume and site access baseline
// Call in TH8: HuntUserDownloadBaseline(30, 7)
// Compare current week against baseline to find exfiltration spikes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

// Save as function: HuntGetAdminUsers
// No parameters — returns current admin role holders
IdentityInfo
| where TimeGenerated > ago(1d)
| where AssignedRoles has_any (
    "Global Administrator", "Exchange Administrator",
    "Security Administrator", "Conditional Access Administrator",
    "Privileged Role Administrator", "User Administrator")
| distinct AccountUPN, AssignedRoles
// If IdentityInfo is not ingested, alternative:
// AuditLogs → extract users with active role assignments
// Use in hunts:
//   Exclude admins: | where UserPrincipalName !in (HuntGetAdminUsers())
//   Target admins only: | where UserPrincipalName in (HuntGetAdminUsers())

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// Create a watchlist "HuntExclusions_VPNIPs" with columns:
//   IPRange (string), Description (string), AddedDate (datetime)
// Query it in any hunt:
let vpnIPs = _GetWatchlist('HuntExclusions_VPNIPs')
| project IPRange;
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where not(IPAddress has_any (vpnIPs))
// All hunt queries reference the same watchlist
// Update the watchlist once → all queries use the updated list
// No query editing required when VPN infrastructure changes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

// Watchlist for approved OAuth applications (TH5/TH6/TH11 exclusion)
// Applications on this list are pre-approved and should not
//   trigger OAuth consent hunting alerts
let approvedApps = _GetWatchlist('HuntExclusions_ApprovedApps')
| project AppName;
AuditLogs
| where TimeGenerated > ago(90d)
| where OperationName == "Consent to application"
| extend AppName = tostring(TargetResources[0].displayName)
| where AppName !in (approvedApps)
// Only consents for UNAPPROVED applications — the hunting targets
// Review and update the approved apps watchlist quarterly
//   New legitimate apps get added; deprecated apps get removed
//   Each addition should be documented with business justification

1
2
3
4
5
6
7
8

// Watchlist for known admin accounts (exclude from non-admin hunts)
let adminAccounts = _GetWatchlist('HuntExclusions_Admins')
| project UPN;
AuditLogs
| where TimeGenerated > ago(7d)
| where InitiatedBy.user.userPrincipalName !in (adminAccounts)
// Hunts for non-admin behavior automatically exclude admin accounts
// Admin-specific hunts (TH7) use the same list as the inclusion filter