KQL: Tips and Tricks for M365 Analysts

In the complex world of Microsoft 365 security and operations, the ability to effectively query and analyze data is no longer optional—it’s essential. Kusto Query Language (KQL) has become the lingua franca for M365 analysts, providing the power to transform vast amounts of raw telemetry into actionable insights. While basic KQL knowledge enables fundamental analysis, mastering advanced techniques can dramatically enhance an analyst’s effectiveness. This article explores powerful KQL tips and tricks specifically designed for M365 analysts working with Microsoft 365 Defender, Microsoft Sentinel, and Azure Monitor.

The M365 Analyst’s Challenge

M365 analysts face unique data challenges that require specialized query approaches:

• Data volume: Processing terabytes of telemetry across diverse M365 services
• Multi-table analysis: Connecting insights across dozens of related data tables
• Complex patterns: Identifying subtle security and operational signals amid noise
• Performance constraints: Optimizing queries against time and resource limitations
• Reporting needs: Transforming technical data into business-relevant insights

These challenges require going beyond basic KQL syntax to implement sophisticated query patterns. Let’s explore the advanced techniques that can transform an analyst’s capabilities.

Query Optimization Techniques

The foundation of effective KQL usage is query optimization. The following workflow illustrates how analysts should approach query development:

graph TD
    A[Identify Data Needs] --> B[Draft Initial Query]
    B --> C[Apply Filters Early]
    C --> D[Optimize Joins]
    D --> E[Materialize When Needed]
    E --> F[Test Performance]
    F --> G{Performance OK?}
    G -->|No| C
    G -->|Yes| H[Implement in Production]

Let’s examine key optimization techniques that follow this workflow:

1. Early Filtering: The Most Critical Optimization

The most impactful KQL optimization is applying filters as early as possible in the query. Compare these two approaches:

// Inefficient approach - filtering after processing
SigninLogs
| extend IsExternalUser = UserPrincipalName has "#EXT#"
| where TimeGenerated > ago(30d)
| where IsExternalUser == true
| summarize TotalSignIns = count() by UserPrincipalName
| order by TotalSignIns desc

// Optimized approach - filtering first
SigninLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName has "#EXT#"
| summarize TotalSignIns = count() by UserPrincipalName
| order by TotalSignIns desc

The second query dramatically reduces the data processed by filtering for time range and external users before performing any transformations or aggregations.

2. Strategic Materialization with let Statements

The let statement with materialize() allows you to store intermediate results, preventing redundant processing:

// Without materialization - processes data twice
let timeFrame = 7d;
let suspiciousIPs =
    SigninLogs
    | where TimeGenerated > ago(timeFrame)
    | where ResultType == 0
    | where IPAddress startswith "203.0.113" or IPAddress startswith "198.51.100";
SigninLogs
| where TimeGenerated > ago(timeFrame)
| where IPAddress in (suspiciousIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, Location

// With materialization - processes once and reuses
let timeFrame = 7d;
let suspiciousIPs = materialize(
    SigninLogs
    | where TimeGenerated > ago(timeFrame)
    | where ResultType == 0
    | where IPAddress startswith "203.0.113" or IPAddress startswith "198.51.100"
    | distinct IPAddress
);
SigninLogs
| where TimeGenerated > ago(timeFrame)
| where IPAddress in (suspiciousIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, Location

The materialized approach computes the suspicious IP list once and reuses it, significantly improving performance for complex queries.

3. Optimizing Join Operations

Joins are often necessary but can be performance-intensive. Optimize them with these techniques:

// Inefficient join - large datasets on both sides
SigninLogs
| where TimeGenerated > ago(7d)
| join (
    AuditLogs
    | where TimeGenerated > ago(7d)
) on $left.UserPrincipalName == $right.UserPrincipalName

// Optimized join - filter before joining and specify join kind
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, IPAddress, Location
| join kind=inner (
    AuditLogs 
    | where TimeGenerated > ago(7d)
    | where OperationName has "Add member to role"
    | where Result == "success"
    | project TimeGenerated, UserPrincipalName, Operation=OperationName
) on UserPrincipalName

The optimized approach filters both datasets before joining, projects only necessary columns, and specifies the join type explicitly.

Advanced Data Transformation Techniques

Beyond optimization, M365 analysts need sophisticated data transformation techniques. This mindmap illustrates key transformation capabilities:

mindmap
  root((KQL Transformations))
    Dynamic Data Handling
      parse_json()
      bag_unpack()
      array extraction
    Custom Functions
      series analysis
      string processing
      datetime functions
    Pivoting & Unpivoting
      summarize
      make-series
      pivot
    Complex Aggregation
      make_list()
      make_set()
      percentiles
    Window Functions
      prev()
      next()
      row_cumsum()

Let’s explore practical applications of these transformation techniques:

1. Unpacking Dynamic Fields in M365 Data

Many M365 tables contain nested JSON data that requires special handling:

// Extract nested properties from AlertInfo
SecurityAlert
| where TimeGenerated > ago(7d)
| extend AlertDetail = parse_json(Entities)
| mv-expand AlertDetail
| extend EntityType = tostring(AlertDetail.Type)
| where EntityType == "account"
| extend 
    UserSid = tostring(AlertDetail.Properties.SID),
    UserName = tostring(AlertDetail.Properties.Name),
    DomainName = tostring(AlertDetail.Properties.NTDomain)
| project TimeGenerated, AlertName, UserName, DomainName, UserSid

This technique extracts useful information from the nested Entities field in SecurityAlert, making it accessible for analysis.

2. Time Series Analysis for Behavioral Baselines

Detecting anomalies often requires comparing current activity to historical patterns:

// Create a time series baseline and compare recent activity
let baselinePeriod = 28d;
let analysisPeriod = 1d;
let granularity = 1h;
SigninLogs
| where TimeGenerated > ago(baselinePeriod)
| where ResultType == 0
| summarize HourlyCount=count() by bin(TimeGenerated, granularity), UserPrincipalName
| summarize 
    AvgCount = avg(HourlyCount),
    StdDev = stdev(HourlyCount),
    recentActivity = make_list_if(HourlyCount, TimeGenerated > ago(analysisPeriod))
    by UserPrincipalName
| mv-expand RecentCount = recentActivity
| extend ZScore = (RecentCount - AvgCount) / StdDev
| where ZScore > 3 // 3 standard deviations above normal
| project UserPrincipalName, RecentCount, AvgCount, ZScore

This time series analysis establishes a baseline of normal authentication patterns and highlights recent activity that deviates significantly from the historical norm.

3. Custom Column Creation with extend Patterns

Creating derived columns enables deeper analysis of M365 data:

// Enrich sign-in data with additional context and risk calculations
SigninLogs
| where TimeGenerated > ago(7d)
| extend 
    // Categorize risk level
    RiskLevel = case(
        RiskLevelDuringSignIn == "high", 3,
        RiskLevelDuringSignIn == "medium", 2,
        RiskLevelDuringSignIn == "low", 1,
        0),
    // Check if admin account
    IsAdmin = UserPrincipalName has "admin" or AppDisplayName has "Admin",
    // Categorize by authentication type
    AuthType = case(
        AuthenticationRequirement == "singleFactorAuthentication", "Single Factor",
        AuthenticationRequirement == "multiFactorAuthentication", "MFA",
        "Unknown"),
    // Detect after-hours activity
    TimeOfDay = datetime_part("hour", TimeGenerated),
    IsAfterHours = TimeOfDay < 6 or TimeOfDay > 18
| where IsAdmin == true and IsAfterHours == true
| project TimeGenerated, UserPrincipalName, IPAddress, Location, AuthType, RiskLevel, TimeOfDay

This technique enriches raw sign-in data with business-relevant context like risk levels, admin status, and after-hours detection.

Visualization-Optimized Queries

Effective M365 analysis often requires preparing data specifically for visualization. Here are techniques to create visualization-ready outputs:

1. Creating Time-Based Heat Maps

This technique creates data structured for heat map visualizations:

// Generate authentication heatmap data by hour and day of week
SigninLogs
| where TimeGenerated > ago(30d)
| extend 
    Hour = datetime_part("hour", TimeGenerated),
    DayOfWeek = dayofweek(TimeGenerated)
| summarize AuthCount = count() by Hour, DayOfWeek
| order by DayOfWeek asc, Hour asc
| render heatmap with (title="Authentication Activity by Hour and Day")

This query structure enables heat map creation that clearly shows authentication patterns by time of day and day of week:

DayOfWeek  0   1   2   ... 22  23   (Hour)
0 (Sun)    12  8   3   ... 45  32
1 (Mon)    15  10  5   ... 85  67
...
6 (Sat)    10  7   4   ... 37  29

2. Multi-Series Time Charts

For trend analysis, structure data to support multi-series time charts:

// Compare successful vs. failed authentication attempts over time
SigninLogs
| where TimeGenerated > ago(14d)
| summarize
    SuccessfulSignIns = countif(ResultType == 0),
    FailedSignIns = countif(ResultType != 0)
    by bin(TimeGenerated, 1h)
| render timechart with (title="Authentication Success vs. Failure Trends")

This creates perfectly structured data for a time series chart showing the relationship between successful and failed authentication attempts over time.

Advanced Cross-Table Analysis Techniques

Some of the most valuable M365 insights come from connecting data across multiple tables. Here are advanced techniques for cross-table analysis:

1. Union-Based Entity Analysis

When analyzing an entity across multiple data sources, use union with withsource:

// Track user activity across multiple data sources
let userToTrack = "user@company.com";
let timeFrame = 7d;
union withsource=SourceTable
    (SigninLogs | where TimeGenerated > ago(timeFrame) | where UserPrincipalName == userToTrack),
    (EmailEvents | where TimeGenerated > ago(timeFrame) | where RecipientEmailAddress == userToTrack or SenderFromAddress == userToTrack),
    (OfficeActivity | where TimeGenerated > ago(timeFrame) | where UserId == userToTrack),
    (AuditLogs | where TimeGenerated > ago(timeFrame) | where InitiatedBy.user.userPrincipalName == userToTrack)
| extend 
    CommonTime = TimeGenerated,
    CommonUser = userToTrack
| project SourceTable, CommonTime, Activity=iff(SourceTable=="SigninLogs", "Sign-In", 
    iff(SourceTable=="EmailEvents", "Email", 
    iff(SourceTable=="OfficeActivity", "Office", "Azure AD")))
| order by CommonTime asc

This technique creates a unified timeline of user activity across multiple M365 services, enabling comprehensive user behavior analysis.

2. Intelligent Join Chaining

For complex entity relationships, use multiple joins in sequence:

// Track alert -> user -> device -> additional activity chain
let timeWindow = 24h;
SecurityAlert
| where TimeGenerated > ago(7d)
| where AlertSeverity == "High"
| join kind=inner (
    IdentityInfo
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, *) by AccountName
) on $left.CompromisedEntity == $right.AccountName
| join kind=inner (
    DeviceInfo
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, *) by DeviceName
) on $left.DeviceName == $right.DeviceName
| join kind=inner (
    DeviceNetworkEvents
    | where TimeGenerated > ago(timeWindow)
) on $left.DeviceId == $right.DeviceId
| project
    AlertTime = SecurityAlert.TimeGenerated,
    AlertName = AlertName,
    UserName = AccountName,
    DeviceName = DeviceName,
    NetworkConnectionTime = DeviceNetworkEvents.TimeGenerated,
    RemoteIP = RemoteIP,
    RemotePort = RemotePort

This sophisticated join chain connects an alert to a user, the user to their device, and then analyzes the device’s network connections, creating a comprehensive view of potentially malicious activity.

Conclusion

For M365 analysts, mastering these advanced KQL tips and tricks transforms the analysis process from basic reporting to sophisticated intelligence generation. By implementing query optimization techniques, advanced data transformations, visualization-optimized structures, and cross-table analysis patterns, analysts can extract maximum value from Microsoft 365 telemetry.

The future of M365 analysis lies in increasingly sophisticated KQL implementations that combine these techniques to provide comprehensive visibility across complex cloud environments. Organizations that develop these capabilities within their security and operations teams will be better positioned to manage their Microsoft 365 environments effectively.

By investing in advanced KQL expertise and implementing these tips and tricks systematically, M365 analysts can transform from data processors into insight generators, providing business-critical intelligence that drives security and operational excellence across the Microsoft cloud ecosystem.