Skip to content

with vs include

Note

Understanding the critical difference between with and include - when to filter objects by events vs. when to compute metrics for all objects.

Overview

with and include are both used to join event data to objects (like devices), but they behave very differently. Choosing the wrong one is one of the most common NQL mistakes.

Simple rule:

  • with = Filter to only objects that HAVE events → Reduces scope
  • include = Keep ALL objects, compute metrics from events → Full inventory with metrics

The Critical Difference

with - Filtering by Event Existence

When you use with, you're saying: "Show me only the objects that have these events"

Objects without matching events are excluded from results.

include - Computing Metrics for All Objects

When you use include, you're saying: "Show me all objects, and compute metrics from events where they exist"

Objects without events are kept in results (metrics show as null or 0).

Critical Concept

  • with changes the set of objects you're working with (filtering)
  • include does not change the set of objects (unless you use compute)

Think of with as a WHERE clause and include as a LEFT JOIN

Comparison Table

Feature with include
Objects without events ❌ Excluded ✅ Included
Scope modification Yes - filters objects No - unless compute used
Primary use case Filtering by event conditions Computing metrics for all objects
compute requirement Optional Mandatory for effect
Example "Devices that crashed" "Crash count per device (including 0)"

Syntax

/* with syntax - filters objects */
<table>
| with <event_table> during past Xd

/* include syntax - computes metrics */
<table>
| include <event_table> during past Xd
| compute metric = field.sum()  # Required!

Real-World Examples

Example: Devices That Crashed (with)

Scenario: Find only the devices that have experienced crashes.

/* Returns ~15 devices (only those with crashes) */
devices
| with execution.crashes during past 7d
| list device.name

Result: 

device.name
-----------
LAPTOP-001
DESKTOP-042
LAPTOP-099
...
(15 devices total)

Explanation: Only devices with at least one crash are returned. If a device had no crashes, it's excluded entirely.

Example: Crash Count Per Device (include)

Scenario: Show crash count for ALL devices, including those with zero crashes.

/* Returns ~142 devices (all devices in inventory) */
devices
| include execution.crashes during past 7d
| compute crash_count = count()
| list device.name, crash_count
| sort crash_count desc

Result: 

device.name      crash_count
-----------      -----------
LAPTOP-099       47
DESKTOP-042      23
LAPTOP-001       12
...
SERVER-100       0  # Devices with no crashes included!
LAPTOP-200       0
...
(142 devices total)

Explanation: All devices are shown. Devices without crashes display crash_count = 0.

When to Use Each

Use with When:

✅ Filtering for active objects - "Devices that executed this application" - "Users who accessed this website" - "Devices that connected to this server"

✅ Finding problematic objects - "Devices that crashed" - "Devices with failed connections" - "Devices that rebooted"

✅ You want to reduce scope - Only interested in objects that have the event - Don't care about objects without events

Examples: 

/* Find devices running Outlook */
devices
| with execution.events during past 7d
| where binary.name = "outlook.exe"
| list device.name

/* Find devices with high connection failures */
devices
| with connection.events during past 7d
| where event.failed_connection_ratio >= 0.15
| list device.name

Use include When:

✅ Computing metrics for inventory - "CPU usage per device (all devices)" - "Crash count per device (including 0)" - "Average memory usage per device"

✅ Percentage calculations - "What % of devices have crashes?" - "Devices with low activity"

✅ Comparing active vs inactive - Need to see objects both with and without events - Identifying devices missing expected events

Examples: 

/* CPU usage for all devices */
devices
| include device_performance.events during past 7d
| compute avg_cpu = cpu_usage.avg()
| list device.name, avg_cpu
| sort avg_cpu desc

/* Find devices NOT running expected software */
devices
| include execution.events during past 7d
| compute has_antivirus = binary.name.countif(binary.name = "antivirus.exe")
| where has_antivirus == 0  # Devices without the software
| list device.name

Common Patterns

Pattern 1: Filter Then Count (with)

/* How many devices executed Outlook? */
devices
| with execution.events during past 7d
| where binary.name = "outlook.exe"
| summarize device_count = count()

Pattern 2: Count Per Device (include)

/* Execution count per device for all devices */
devices
| include execution.events during past 7d
| compute execution_count = count()
| list device.name, execution_count
| sort execution_count desc

Pattern 3: Percentage of Total (include)

/* What percentage of devices crashed? */
devices
| include execution.crashes during past 7d
| compute has_crash = count()
| summarize
    total_devices = count(),
    devices_with_crashes = has_crash.countif(has_crash > 0),
    crash_percentage = (has_crash.countif(has_crash > 0) * 100) / count()

Tips & Tricks

Test Both to Understand the Difference

Run the same query with with and include to see how results differ: 

/* Version 1: with (filters) */
devices
| with execution.crashes during past 7d
| summarize device_count = count()
/* Might return: 15 devices */

/* Version 2: include (all devices) */
devices
| include execution.crashes during past 7d
| compute crash_count = count()
| summarize
total_devices = count(),
devices_with_crashes = crash_count.countif(crash_count > 0)
/* Returns: 142 total devices, 15 with crashes */

include Requires compute

Using include without compute doesn't do anything useful: 

/* BAD - include without compute does nothing */
devices
| include execution.events during past 7d
| list device.name
/* Just returns all devices (events ignored) */

/* GOOD - include with compute */
devices
| include execution.events during past 7d
| compute execution_count = count()
| list device.name, execution_count
/* Returns all devices with their execution counts */

Common Mistake: Using with for Metrics

/* BAD - Excludes devices without events (incomplete picture) */
devices
| with device_performance.events during past 7d
| compute avg_cpu = cpu_usage.avg()
| list device.name, avg_cpu
/* Missing devices that had no performance data! */

/* GOOD - Shows all devices */
devices
| include device_performance.events during past 7d
| compute avg_cpu = cpu_usage.avg()
| list device.name, avg_cpu
/* Includes all devices, even those with no data (null) */

with Can Dramatically Change Results

/* Query 1: All devices in department */
devices
| where device.department = "IT"
| summarize device_count = count()
/* Returns: 50 devices */

/* Query 2: Only devices that executed something */
devices
| where device.department = "IT"
| with execution.events during past 7d
| summarize device_count = count()
/* Returns: 42 devices */
/* 8 devices in IT had no execution events (offline? new?) */

Decision Flowchart

Do you need ALL objects in results?
│
├─ YES → Use `include`
│        └─ Add `compute` to calculate metrics
│
└─ NO  → Do you want to filter by event existence?
          │
          ├─ YES → Use `with`
          │        └─ Optionally add `compute` for metrics
          │
          └─ NO  → Don't use event join at all
                   └─ Query the object table directly

Additional Resources