Skip to content

compute - Calculations

Note

The compute clause calculates per-object metrics from event data - it MUST follow with or include, and CANNOT be used after summarize.

Overview

The compute clause creates calculated metrics for each object (device, user, etc.) based on event data. It's essential for answering questions like "What's the average CPU usage per device?" or "How many crashes does each device have?"

Key characteristics:

  • Per-object calculations (one result per device/user/etc.)
  • Must follow with or include clause
  • Cannot follow summarize clause
  • Uses aggregation functions on event data

CRITICAL Rules for compute

  1. MUST follow with or include - Cannot be used alone
  2. CANNOT follow summarize - All calculations must be inline in summarize
  3. Mandatory after include - Include without compute does nothing useful
  4. Optional after with - Can be used to calculate metrics from joined events

Basic Syntax

/* With include (mandatory for effect) */
| include <event_table> during past Xd
| compute <new_metric> = <metric>.<aggregation_function>()

/* With 'with' (optional) */
| with <event_table> during past Xd
| compute <new_metric> = <metric>.<aggregation_function>()

/* Multiple computes */
| include <event_table> during past Xd
| compute
    <metric_1> = <field_1>.<function>(),
    <metric_2> = <field_2>.<function>()

When to Use compute

Use compute When:

✅ Calculating metrics per object - "Average CPU usage per device" - "Total memory consumption per application" - "Crash count for each device"

✅ After include for complete inventory - Shows ALL objects with computed metrics - Objects without events show 0 or null

✅ After with for filtered objects with metrics - Shows only objects WITH events - Can add metrics to those objects

DON'T Use compute When:

❌ Aggregating across all objects → Use summarize instead

❌ After summarize → Move calculation inline in summarize

❌ Without event join → Must have with or include first

Available Aggregation Functions

Function Description Returns Example
.avg() Average value Number cpu_time.avg()
.sum() Sum of all values Number memory.sum()
.max() Maximum value Number memory.max()
.min() Minimum value Number memory.min()
.last() Most recent value Number free_disk_space.last()
count() Count events Integer count()
<object>.count() Count unique objects (1 or 0) 0 or 1 device.count()

See Aggregation Functions for complete details.

compute with include

include keeps ALL objects in results - use compute to calculate metrics for each.

Basic Example

/* CPU usage per device (all devices, even those without events) */
devices during past 7d
| include execution.events
| compute avg_cpu = cpu_time.avg()  # One value per device
| list device.name, avg_cpu
| sort avg_cpu desc

Result: 142 rows (all devices), avg_cpu = null for devices without execution events

Multiple Computes

/* Multiple metrics per device */
devices during past 7d
| include execution.events during past 7d
| compute
    avg_cpu = cpu_time.avg(),
    total_memory = real_memory.sum(),
    execution_count = count()
| list device.name, avg_cpu, total_memory, execution_count

Multiple Event Joins

/* Metrics from multiple event sources */
devices during past 7d
| include execution.events during past 7d
| compute
    avg_cpu = cpu_time.avg(),
    execution_count = count()
| include device_performance.events during past 7d
| compute
    avg_memory = free_memory.avg(),
    boot_count = boot.number_of_boots.sum()
| list device.name, avg_cpu, execution_count, avg_memory, boot_count

compute with 'with'

with filters to only objects WITH events - compute is optional but useful for adding metrics.

Basic Example

/* Only devices that executed Outlook, with average CPU */
devices
| with execution.events during past 7d
| where binary.name == "outlook.exe"
| compute avg_cpu = cpu_time.avg()  # Optional - adds metric
| list device.name, avg_cpu

Result: ~50 rows (only devices that ran Outlook)

Without compute (Just Filtering)

/* Just find devices that crashed - no metrics needed */
devices
| with execution.crashes during past 7d
| list device.name  # compute not required for simple filtering

count() vs .count()

Understanding the difference is critical for correct results.

count() - Count Events

Returns the number of events for each object.

devices during past 7d
| include device_performance.boots during past 7d
| compute nb_boots = count()  # Counts boot events per device
| list device.name, nb_boots
/* Device with 5 boots → nb_boots = 5 */
/* Device with 0 boots → nb_boots = 0 */

.count() - Presence Indicator (1 or 0)

Returns 1 if events exist for this object, 0 otherwise.

devices during past 7d
| include device_performance.boots during past 7d
| compute has_boots = device.count()  # 1 if boots exist, 0 otherwise
| summarize devices_with_boots = has_boots.sum()
/* Device with 5 boots → has_boots = 1 */
/* Device with 0 boots → has_boots = 0 */
/* Sum = count of devices that booted */

Use <object>.count() when you want to count unique objects in summarize:

devices during past 7d
| include execution.crashes during past 7d
| compute has_crashes = device.count()  # Returns 1 or 0
| summarize
    total_devices = count(),
    devices_with_crashes = has_crashes.sum(),  # Sum the 1s
    crash_rate = (has_crashes.sum() * 100.0) / count()

Real-World Examples

Example: Disk Space Monitoring

Scenario: Find devices with low disk space.

/* Current and average free space per device */
devices during past 7d
| include device_performance.events
| compute
current_free_space_mb = free_disk_space.last(),  # Latest reading
avg_free_space_mb = free_disk_space.avg()        # Average over period
| where current_free_space_mb < 10000  # Less than ~10GB
| list device.name,
   current_free_space_mb,
   avg_free_space_mb
| sort current_free_space_mb asc

Explanation:

  • include keeps all devices
  • compute calculates current (.last()) and average disk space
  • where filters after computation
  • Shows devices running out of space

Example: Crash Analysis Per Device

Scenario: How many crashes does each device have?

/* Crash count per device (including devices with 0 crashes) */
devices during past 7d
| include execution.crashes during past 7d
| compute crash_count = count()  # Count crash events per device
| list device.name, crash_count
| sort crash_count desc

Result: All 142 devices listed, crash_count = 0 for devices without crashes

Example: Application Memory Usage Per Device

Scenario: Monitor Outlook memory consumption across devices.

/* Only devices running Outlook, with memory metrics */
devices
| with execution.events during past 7d
| where binary.name == "outlook.exe"
| compute
avg_memory_mb = real_memory.avg(),
max_memory_mb = real_memory.max()
| where avg_memory_mb > 500  # Over 500MB average
| list device.name,
   avg_memory_mb.as(format = bytes),
   max_memory_mb.as(format = bytes)
| sort avg_memory_mb desc

Explanation:

  • with filters to only devices that ran Outlook
  • compute calculates average and peak memory
  • where after compute finds high memory devices
  • Result: Only Outlook devices with high memory usage

Example: Boot Behavior Analysis

Scenario: Find devices that never reboot.

/* Devices with no boots in past 30 days */
devices during past 30d
| include device_performance.boots during past 30d
| where boot.type == full_boot
| compute num_of_boots = boot.number_of_boots.sum()
| where num_of_boots == null  # No boots recorded
| list device.name,
   device.last_seen,
   device.operating_system.name
| sort device.last_seen desc

Explanation:

  • include ensures all devices are considered
  • compute sums boot count (null if no boots)
  • where num_of_boots == null finds devices that never booted
  • Potential indicators of always-on servers or stuck devices

Example: Production DLP Monitoring

Scenario: Monitor Microsoft Purview DLP process memory (verified production example).

execution.events during past 7d
| where binary.name in ["sensedlpprocessor.exe", "mssense.exe", "mpdlpservice.exe"]
| summarize
avg_memory_used = real_memory.avg(),
last_memory_used = real_memory.last(),
process_count = binary.name.count()
  by device.name
| list device.name,
   avg_memory_used.as(format = bytes),
   last_memory_used.as(format = bytes),
   process_count
| sort process_count desc
| limit 100

Note: This uses summarize instead of compute because we're aggregating by device (grouping), not calculating per execution event.

Comparing compute vs summarize

The key difference is per-object vs aggregate:

Aspect compute summarize
Level Per object (per device/user) Aggregate (total or groups)
Output Rows One row per object One row total OR one per group
Requires Join Yes (with or include) No
Use For Individual metrics Totals, averages across many
Example "CPU per device" "Average CPU across all"

Visual Example:

/* compute - Per-device metrics */
devices during past 7d
| include execution.events
| compute avg_cpu = cpu_time.avg()
| list device.name, avg_cpu
/* Returns: 142 rows */
/* DEVICE-001 | 45.2 */
/* DEVICE-002 | 32.1 */
/* DEVICE-003 | 67.8 */
/* ... */

/* summarize - Aggregate across all */
execution.events during past 7d
| summarize overall_avg_cpu = cpu_time.avg()
/* Returns: 1 row */
/* 48.7 */

/* summarize with grouping - Aggregate per group */
execution.events during past 7d
| summarize avg_cpu = cpu_time.avg() by device.name
/* Returns: ~50 rows (only devices WITH execution events) */
/* DEVICE-001 | 45.2 */
/* DEVICE-005 | 32.1 */
/* DEVICE-012 | 67.8 */

Common Patterns

Pattern: Include + Compute + Filter

/* Metric for all objects, filter by threshold */
devices during past 7d
| include device_performance.events
| compute avg_cpu = cpu_usage.avg()
| where avg_cpu > 50
| list device.name, avg_cpu

Pattern: With + Compute (Optional)

/* Filter to objects with events, optionally add metrics */
devices
| with execution.crashes during past 7d
| compute crash_count = count()  # Optional
| list device.name, crash_count

Pattern: Multiple Computes from Different Sources

/* Combine metrics from multiple event tables */
devices during past 7d
| include execution.events
| compute avg_cpu = cpu_time.avg()
| include device_performance.events
| compute avg_memory = free_memory.avg()
| list device.name, avg_cpu, avg_memory

Pattern: Presence Check for Aggregation

/* Count devices WITH crashes (not crash count) */
devices during past 7d
| include execution.crashes during past 7d
| compute has_crashes = device.count()  # 1 or 0
| summarize devices_with_crashes = has_crashes.sum()

Tips & Tricks

include Requires compute

Using include without compute doesn't do anything useful: 

/* ❌ Pointless - events joined but not used */
devices during past 7d
| include execution.events during past 7d
| list device.name
/* Just returns all devices (events ignored) */

/* ✅ Useful - compute metrics from events */
devices during past 7d
| include execution.events during past 7d
| compute avg_cpu = cpu_time.avg()
| list device.name, avg_cpu

Filter After compute for Thresholds

/* Compute metric, then filter by threshold */
devices during past 7d
| include device_performance.events
| compute free_space_gb = free_disk_space.last() / 1000
| where free_space_gb < 10  # Filter computed value
| list device.name, free_space_gb

Use .last() for Current State

/* Get most recent value (current state) */
devices during past 7d
| include device_performance.events
| compute current_memory = free_memory.last()
| list device.name, current_memory

Common Mistake: compute After summarize

/* ❌ WRONG - Cannot use compute after summarize! */
execution.events during past 7d
| summarize
total_cpu = cpu_time.sum(),
total_duration = execution_duration.sum()
| compute cpu_percent = (total_cpu * 100) / total_duration  # ERROR!

/* ✅ CORRECT - All calculations inline in summarize */
execution.events during past 7d
| summarize
cpu_percent = ((cpu_time.sum()) * 100) / execution_duration.sum()

Common Mistake: compute Without Join

/* ❌ WRONG - No event join before compute */
devices during past 7d
| compute avg_memory = free_memory.avg()  # ERROR!

/* ✅ CORRECT - Must have with/include first */
devices during past 7d
| include device_performance.events
| compute avg_memory = free_memory.avg()

Common Mistake: Using count() Instead of object.count()

/* ❌ WRONG - Counts events, not presence */
devices during past 7d
| include execution.crashes during past 7d
| compute crash_indicator = count()
| summarize devices_with_crashes = crash_indicator.sum()
/* Sums crash counts, not device count! */

/* ✅ CORRECT - Use device.count() for presence */
devices during past 7d
| include execution.crashes during past 7d
| compute has_crashes = device.count()  # 1 or 0
| summarize devices_with_crashes = has_crashes.sum()
/* Sums the 1s = count of devices with crashes */

Additional Resources

Copyright © 2025 GoodZira