Using KUSTO Query Language (KQL) to Analyze Common M365 Alerts

In today’s complex Microsoft 365 (M365) environments, security teams face an unprecedented volume of security alerts and signals. As organizations increasingly rely on cloud-based productivity and collaboration tools, effectively analyzing security telemetry has become essential for identifying genuine threats amid the noise. Kusto Query Language (KQL) has emerged as the powerful query language that enables security analysts to transform raw alert data into actionable security intelligence within the Microsoft 365 Defender and Microsoft Sentinel ecosystems.

The M365 Security Alert Challenge

Security teams monitoring Microsoft 365 environments face several critical challenges:

• Alert volume: Thousands of daily signals across identity, email, endpoint, and applications
• False positives: Distinguishing genuine threats from benign anomalies
• Context limitations: Basic alerts often lack the surrounding context needed for analysis
• Correlation complexity: Connecting related events across different M365 services
• Investigation inefficiency: Manual analysis processes that don’t scale

These challenges create significant operational burdens—from analyst fatigue and missed threats to inefficient triage and delayed response times. The solution lies in leveraging KQL to transform alert analysis through powerful, customized queries that extract maximum value from security telemetry.

Understanding KQL for M365 Security Analysis

Kusto Query Language (KQL) is a read-only query language optimized for log analytics with a syntax that blends SQL-like familiarity with powerful data exploration capabilities. When applied to M365 security, KQL enables security teams to:

graph TD
    A[Raw Alert Data] --> B[KQL Processing]
    B --> C[Filtering]
    B --> D[Aggregation]
    B --> E[Correlation]
    B --> F[Visualization]
    C --> G[Actionable Insights]
    D --> G
    E --> G
    F --> G
    G --> H[Security Response]

This analysis workflow transforms basic alerts into comprehensive security insights through sophisticated data manipulation. Let’s explore how KQL can be applied to common M365 alert scenarios.

Core KQL Capabilities for M365 Alert Analysis

KQL provides security analysts with a robust toolkit for alert investigation. The key capabilities can be understood through this comprehensive mindmap:

mindmap
  root((KQL Capabilities))
    Filtering
      Time-based
      Entity-focused
      Severity-based
    Aggregation
      Count
      Summarize
      Distinct
    Joining
      Alert correlation
      Entity enrichment
      Threat context
    Visualization
      Time charts
      Entity graphs
      Heatmaps
    Pattern Detection
      Anomalies
      Frequencies
      Baselines

These capabilities enable security teams to implement sophisticated alert analysis across various M365 security scenarios.

Essential KQL Queries for Common M365 Alerts

Let’s explore practical KQL applications for analyzing common M365 security alerts:

1. Suspicious Sign-In Activity Analysis

Microsoft 365 environments generate numerous authentication alerts. This KQL query helps identify potentially compromised accounts by analyzing sign-in patterns:

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize 
    SignInCount=count(),
    CountryCount=dcount(LocationDetails.countryOrRegion),
    Countries=make_set(LocationDetails.countryOrRegion),
    IPAddresses=make_set(IPAddress)
    by UserPrincipalName
| where CountryCount > 3
| order by CountryCount desc

This query identifies successful logins from multiple countries within a seven-day period—a classic indicator of potential account compromise. By focusing on accounts with successful authentications from three or more countries, security teams can quickly identify high-risk scenarios that warrant immediate investigation.

The output might reveal patterns like this:

UserPrincipalName     SignInCount    CountryCount    Countries                                IPAddresses
-------------------   ------------   ------------    -------------------------------------    ------------------
john.smith@company.com     52             4         ["US", "UK", "Singapore", "Australia"]    ["192.168.1.1", "10.0.0.2", ...]
finance@company.com        37             5         ["US", "Russia", "China", "Brazil"...]    ["172.16.0.5", "192.168.2.1", ...]

2. Email Phishing Campaign Detection

Email remains the primary attack vector for most organizations. This KQL query helps identify potential phishing campaigns targeting multiple users:

EmailEvents
| where TimeGenerated > ago(24h)
| where ThreatTypes has "Phish"
| summarize 
    RecipientCount=dcount(RecipientEmailAddress),
    Recipients=make_set(RecipientEmailAddress, 100)
    by SenderFromAddress, Subject
| where RecipientCount > 5
| project-reorder SenderFromAddress, Subject, RecipientCount
| order by RecipientCount desc

This query analyzes email events from the past 24 hours, focusing on those flagged as potential phishing attempts. By aggregating these events by sender and subject, it reveals coordinated phishing campaigns targeting multiple recipients across the organization.

Visualizing this data reveals potential attack patterns:

chart
    title="Potential Phishing Campaigns by Recipient Count"
    x=SenderFromAddress
    y=RecipientCount

3. Privilege Escalation Detection

Monitoring privilege escalation is crucial for protecting administrative access. This KQL query identifies potential privilege escalation activities:

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName has "Add member to role"
| where Result == "success"
| extend Role = tostring(TargetResources[0].modifiedProperties[1].newValue)
| where Role has "Admin" or Role has "Administrator"
| project
    TimeGenerated,
    Actor=InitiatedBy.user.userPrincipalName,
    TargetUser=TargetResources[0].userPrincipalName,
    Role,
    IPAddress=InitiatedBy.user.ipAddress
| order by TimeGenerated desc

This query examines audit logs for successful role assignments that include administrative privileges. By capturing the actor, target user, specific role, and source IP address, security teams can quickly identify suspicious elevation activities that might indicate insider threats or compromised accounts.

A time-series visualization helps identify unusual patterns:

chart
    title="Administrative Role Assignments Over Time"
    x=TimeGenerated
    y=count()
    series=Role

4. Data Exfiltration Analysis

Detecting potential data exfiltration is critical for protecting sensitive information. This KQL query identifies unusual file access and download patterns:

union SharePointFileOperation, OneDriveFileOperation
| where TimeGenerated > ago(7d)
| where Operation in ("FileDownloaded", "FileAccessed")
| summarize
    FileCount=count(),
    FileSet=make_set(FileName, 100),
    Operations=make_set(Operation)
    by UserId, ClientIP
| where FileCount > 100
| order by FileCount desc

This query combines SharePoint and OneDrive file operations, focusing on download and access events. By aggregating these events by user and client IP, it identifies potentially suspicious access patterns that might indicate data theft or unauthorized access.

Visualizing these patterns by user reveals potential exfiltration attempts:

chart
    title="High-Volume File Access by User"
    x=UserId
    y=FileCount

5. Cross-Service Attack Chain Analysis

Sophisticated attacks often span multiple M365 services. This KQL query correlates events across services to identify potential attack chains:

let timeRange = 48h;
let suspiciousIPs = materialize(
    SigninLogs
    | where TimeGenerated > ago(timeRange)
    | where ResultType == 0
    | where Location has_any ("Russia", "China", "North Korea", "Iran")
    | distinct IPAddress
);
union withsource=Source
    (EmailEvents | where TimeGenerated > ago(timeRange) | extend Entity = SenderFromAddress, EntityType = "Email"),
    (SigninLogs | where TimeGenerated > ago(timeRange) | extend Entity = UserPrincipalName, EntityType = "Identity")
| where Source == "EmailEvents" or (Source == "SigninLogs" and IPAddress in (suspiciousIPs))
| order by Entity, TimeGenerated asc
| summarize
    EmailCount = countif(Source == "EmailEvents"),
    SignInCount = countif(Source == "SigninLogs"),
    Timeline = make_list(pack("Time", TimeGenerated, "Source", Source, "IP", IPAddress))
    by Entity, EntityType
| where EmailCount > 0 and SignInCount > 0

This sophisticated query first identifies successful logins from high-risk locations, then correlates these with email events to identify potential attack sequences that begin with phishing emails and lead to successful authentications.

A timeline visualization helps analysts understand the sequence of events:

Entity              EmailCount    SignInCount    Timeline
----------------    ----------    -----------    ---------------------------------
user@company.com          3            2         [{Time: "2023-02-01T10:15:00Z", Source: "EmailEvents", IP: "203.0.113.1"},
                                                 {Time: "2023-02-01T10:45:22Z", Source: "SigninLogs", IP: "198.51.100.2"},
                                                 ...]

Implementing Advanced KQL Techniques for M365 Alert Analysis

Beyond basic queries, several advanced KQL techniques can enhance M365 alert analysis:

1. Time Window Join Patterns

Correlating events within specific time windows helps identify related activities:

let suspiciousEmails = 
    EmailEvents
    | where TimeGenerated > ago(7d)
    | where ThreatTypes has "Phish";
let timeWindow = 1h;
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| join kind=inner (suspiciousEmails) on $left.UserPrincipalName == $right.RecipientEmailAddress
| where SigninLogs.TimeGenerated between (EmailEvents.TimeGenerated .. EmailEvents.TimeGenerated + timeWindow)
| project
    TimeReceived = EmailEvents.TimeGenerated,
    TimeSignedIn = SigninLogs.TimeGenerated,
    TimeDelta = SigninLogs.TimeGenerated - EmailEvents.TimeGenerated,
    User = UserPrincipalName,
    IPAddress = SigninLogs.IPAddress,
    EmailSubject = EmailEvents.Subject

This technique correlates phishing emails with successful authentication events that occur within a specific time window (one hour), potentially identifying cases where credentials were compromised through phishing.

2. Baseline Deviation Detection

Identifying deviations from normal patterns helps detect subtle anomalies:

let baseline = materialize(
    SigninLogs
    | where TimeGenerated between(ago(30d) .. ago(7d))
    | summarize
        NormalCountries=make_set(LocationDetails.countryOrRegion),
        NormalIPAddresses=make_set(IPAddress),
        NormalCountryCount=dcount(LocationDetails.countryOrRegion)
        by UserPrincipalName
);
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize
    RecentCountries=make_set(LocationDetails.countryOrRegion),
    RecentIPAddresses=make_set(IPAddress),
    RecentCountryCount=dcount(LocationDetails.countryOrRegion)
    by UserPrincipalName
| join kind=inner (baseline) on UserPrincipalName
| extend NewCountries = set_difference(RecentCountries, NormalCountries)
| where array_length(NewCountries) > 0

This technique establishes a baseline of normal sign-in patterns over a 30-day period, then identifies recent activity that deviates from this established baseline—a powerful method for detecting account compromise without relying on fixed thresholds.

Operationalizing KQL for M365 Security

To maximize the value of KQL for M365 alert analysis, security teams should:

1. Develop a KQL query library: Build and maintain a repository of proven queries for common scenarios
2. Implement scheduled analytics: Configure automated KQL-based detection rules in Microsoft Sentinel
3. Create custom dashboards: Develop visualization dashboards for ongoing monitoring
4. Establish automated workflows: Use KQL query results to trigger automated response actions
5. Continuously refine queries: Regularly update and improve queries based on new threats and false positive feedback

By systematically applying these practices, security teams can transform their M365 alert analysis from reactive investigation to proactive threat hunting.

Conclusion

Kusto Query Language (KQL) represents a transformative capability for security teams monitoring Microsoft 365 environments. By implementing sophisticated KQL queries that filter, aggregate, correlate, and visualize alert data, organizations can dramatically improve their ability to detect genuine threats, reduce false positives, and accelerate investigation workflows.

The future of M365 security lies in increasingly sophisticated analytics that leverage the full power of KQL to identify complex attack patterns across the Microsoft cloud ecosystem. Organizations that embrace this approach will be better positioned to defend their digital assets despite the growing sophistication of threat actors targeting Microsoft 365 environments.

By investing in KQL expertise and implementing a structured approach to query development, security teams can transform Microsoft 365 alert analysis from an overwhelming challenge into a strategic security capability that provides comprehensive protection for their cloud-based productivity and collaboration environments.