limit - Restricting Rows

Note

The limit clause restricts the number of rows returned - essential for development/testing and for creating Top N queries.

Overview

The limit clause controls how many rows appear in query results. It's one of the simplest clauses in NQL, but plays a critical role in query development and Top N analysis.

Primary uses:

Development - Test queries quickly with small result sets
Top N queries - Show top 10/20/50 items
Performance - Reduce result size during testing

Development Best Practice

Always add | limit 10 during query development, then remove or adjust for production.

This lets you validate query logic quickly without waiting for large result sets.

Basic Syntax

| limit <number_of_rows>

Example:

users during past 7d
| limit 15

Default Behavior (Without limit)

When you don't use limit: - UI default: Shows 50 results - "Load more" button: Loads additional sets of 50 - API: Different limits for execute vs export endpoints

Common Use Cases

Use Case 1: Development Testing

Start with small limits during development:

/* Step 1: Test with limit */
devices during past 7d
| include device_performance.events
| compute avg_memory = free_memory.avg()
| limit 5  # Quick validation

/* Step 2: Expand after validation */
devices during past 7d
| include device_performance.events
| compute avg_memory = free_memory.avg()
| limit 50  # Test with more data

/* Step 3: Production - remove or adjust */
devices during past 7d
| include device_performance.events
| compute avg_memory = free_memory.avg()
/* No limit - show all results */

Use Case 2: Top N Pattern

Combine sort and limit for Top N queries:

Top 10:

execution.events during past 7d
| summarize avg_cpu = cpu_time.avg() by device.name
| sort avg_cpu desc  # Highest first
| limit 10  # Top 10

Top 20:

binaries during past 24h
| where binary.name == "dllhost.exe"
| list name, version, platform, architecture, size
| sort size desc
| limit 10  # Top 10 largest binaries

Use Case 3: Sample for Testing

Test complex queries on a sample first:

/* Test on recent short window + small sample */
execution.events during past 1d  # Short time window
| where binary.name == "outlook.exe"
| limit 100  # Small sample

Use Case 4: Bottom N Pattern

Show lowest values (combine with sort asc):

/* Bottom 20 devices by disk space */
devices during past 7d
| include device_performance.events
| compute free_space = free_disk_space.last()
| sort free_space asc  # Lowest first
| limit 20  # Bottom 20

Limit Position in Query

limit is typically the last clause in a query:

/* Standard query order */
<table> during past Xd
| where <filter>
| with/include <events>
| compute <metrics>
| summarize <aggregations>
| list <fields>
| sort <field> desc
| limit <N>  # Last clause

Limit is Applied AFTER All Processing

limit only affects what's displayed, not what's processed.

It does NOT improve query performance - use where to filter early for speed.

Real-World Examples

Example: Quick Validation

Scenario: Test a new query logic quickly.

/* Quick test with limit */
devices during past 1d  # Short time window
| include execution.events
| compute avg_cpu = cpu_time.avg()
| list device.name, avg_cpu
| limit 3  # Just a few rows to verify it works

Output:

device.name  | avg_cpu
LAPTOP-001   | 45.2
LAPTOP-002   | 32.8
LAPTOP-003   | 67.1

After validation: Expand time window and remove/increase limit

Example: Top 20 Crash-Prone Applications

Scenario: Dashboard widget showing most problematic apps.

execution.crashes during past 7d
| summarize
crash_count = count(),
affected_devices = device.name.count()
  by application.name
| where crash_count > 5
| list application.name, crash_count, affected_devices
| sort crash_count desc
| limit 20  # Top 20 for dashboard

Example: Development Workflow

Scenario: Build query incrementally with limits.

/* Stage 1: Basic query with small limit */
execution.events during past 1d
| where binary.name == "outlook.exe"
| limit 10  # Validate filter works

/* Stage 2: Add aggregation, keep limit */
execution.events during past 1d
| where binary.name == "outlook.exe"
| summarize avg_cpu = cpu_time.avg() by device.name
| limit 10  # Validate aggregation

/* Stage 3: Expand time, keep limit */
execution.events during past 7d
| where binary.name == "outlook.exe"
| summarize avg_cpu = cpu_time.avg() by device.name
| sort avg_cpu desc
| limit 20  # Ready for production dashboard

Example: Devices Needing Immediate Attention

Scenario: Show 10 devices with least disk space.

devices during past 7d
| include device_performance.events
| compute free_space_gb = free_disk_space.last() / 1000
| where free_space_gb < 50  # Less than 50GB
| list device.name, free_space_gb.as(format = bytes)
| sort free_space_gb asc  # Least space first
| limit 10  # Top 10 most critical

Common Patterns

Pattern: Top N by Metric

<query>
| summarize metric = field.agg() by dimension
| sort metric desc  # Highest first
| limit N  # Top N

Pattern: Bottom N by Metric

<query>
| summarize metric = field.agg() by dimension
| sort metric asc  # Lowest first
| limit N  # Bottom N

Pattern: Development Testing

/* During development */
<query>
| limit 10  # Quick validation

/* Production */
<query>
| limit 50  # Dashboard widget
/* OR remove limit entirely for full results */

Pattern: Sample for Complex Queries

/* Test with sample first */
<complex_query>
| limit 100  # Test on subset

/* Then remove limit for full execution */
<complex_query>
/* No limit */

Tips & Tricks

Use During Development, Adjust for Production

Development:

devices during past 1d
| include execution.events
| compute metric = field.avg()
| limit 5  # Quick testing

Production:

devices during past 7d
| include execution.events
| compute metric = field.avg()
| limit 50  # Dashboard widget
/* OR remove limit for full report */

Combine with Short Time Windows for Speed

During development, use BOTH short time windows AND limits:

/* Fast testing combo */
execution.events during past 1d  # Short window
| where binary.name == "outlook.exe"
| limit 10  # Small result set
/* Validates logic quickly */

Remember to Remove/Adjust for Production

Don't forget to remove or adjust development limits before deploying:

/* Development version */
devices
| include execution.crashes during past 7d
| compute crashes = count()
| limit 10  # TODO: Remove for production!

/* Production version */
devices
| include execution.crashes during past 7d
| compute crashes = count()
/* Limit removed - show all devices */

Top N Requires BOTH sort AND limit

/* Top 20 pattern - need both */
| sort metric desc  # Order highest first
| limit 20          # Take first 20

Common Mistake: Limit Before Filtering

/* ❌ WRONG - Limit before filter */
devices during past 7d
| limit 10  # Limits to 10 devices first
| where operating_system.platform == windows  # Then filters
/* May return 0-10 Windows devices (random sample) */

/* ✅ CORRECT - Filter before limit */
devices during past 7d
| where operating_system.platform == windows  # Filter first
| limit 10  # Then limit filtered results
/* Returns 10 Windows devices */

Common Mistake: Limit Before Aggregation

/* ❌ WRONG - Limit before aggregation */
execution.events during past 7d
| limit 100  # Only 100 events!
| summarize crash_count = count() by device.name
/* Aggregating only 100 events - not representative! */

/* ✅ CORRECT - Limit after aggregation */
execution.events during past 7d
| summarize crash_count = count() by device.name
| sort crash_count desc
| limit 20  # Top 20 devices
/* Aggregated all events, showing top 20 results */

Common Mistake: Forgetting Development Limit

/* Development query - looks complete */
devices during past 7d
| include execution.events
| compute avg_cpu = cpu_time.avg()
| list device.name, avg_cpu
| sort avg_cpu desc
| limit 5  # ⚠️ Still has development limit!

/* For dashboard, should be: */
| limit 20  # Or remove entirely

Performance Considerations

Limit Does NOT Reduce Query Cost

Important: limit only affects the result set displayed, not the data processed.

/* This query processes ALL 30 days of data */
execution.events during past 30d
| limit 10  # Only shows 10 rows, but processed all data!

/* To reduce processing, use where (filtering) */
execution.events during past 30d
| where binary.name == "outlook.exe"  # Reduces data processed
| limit 10

Best practices:

Use where to reduce processing (performance)
Use limit to reduce output (usability)
During development: use BOTH short time windows AND limits

Comparing Approaches

Goal	Use This	Example
Reduce query time	`where` + short time window	`past 1d \| where name = "x"`
Quick testing	Short time + `limit`	`past 1d \| limit 10`
Top N results	`sort` + `limit`	`sort metric desc \| limit 20`
Dashboard widget	`sort` + `limit` (keep in production)	`sort desc \| limit 50`
Full report	Remove `limit`	No limit clause

sort - Ordering Results - Combine with limit for Top N
where - Filtering Data - Filter to reduce query cost (limit doesn't)
Query Performance Guide - Optimizing query speed

Additional Resources

NQL Syntax Cheat Sheet - Quick limit reference
Common Query Templates - Templates using limit patterns