Skip to content

sort - Ordering Results

Note

The sort clause orders query results in ascending (lowest first) or descending (highest first) order - essential for Top N queries and trend analysis.

Overview

The sort clause controls the order of rows in your query results. It's typically used after aggregations to show top/bottom values or to display time-series data chronologically.

Common uses:

  • Top N queries - Show highest/lowest values
  • Time-series - Display trends chronologically
  • Alphabetical lists - Sort names A-Z or Z-A
  • Dashboard rankings - Order by importance

Basic Syntax

/* Ascending order (lowest to highest) */
| sort <field_name> asc

/* Descending order (highest to lowest) */
| sort <field_name> desc

Direction is Required

You must specify asc or desc - it's not optional! 

/* ❌ WRONG - Missing direction */
| sort device.name

/* ✅ CORRECT - Direction specified */
| sort device.name asc

Sort Directions

asc - Ascending (Lowest to Highest)

For numbers: Smallest to largest For text: A to Z alphabetically For dates: Oldest to newest

users during past 7d
| list username, type
| sort username asc  # Alphabetical A-Z

Output: 

username
--------
adoe
bwilson
jsmith
...

desc - Descending (Highest to Lowest)

For numbers: Largest to smallest For text: Z to A (reverse alphabetical) For dates: Newest to oldest

users during past 7d
| list username, type
| sort username desc  # Reverse alphabetical Z-A

Output: 

username
--------
zthompson
...
jsmith
bwilson
adoe

Common Use Cases

Use Case 1: Top N Pattern (Highest Values)

The most common use of sort - finding top performers/offenders.

/* Top 20 devices by CPU usage */
execution.events during past 7d
| summarize avg_cpu = cpu_time.avg() by device.name
| sort avg_cpu desc  # Highest CPU first
| limit 20

Use Case 2: Bottom N Pattern (Lowest Values)

Finding lowest values - useful for disk space, performance issues.

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

Use Case 3: Alphabetical Sorting

Organizing names alphabetically.

/* Alphabetical device list */
devices
| list device.name, operating_system.name
| sort device.name asc  # A to Z

Use Case 4: Time-Series (Chronological)

CRITICAL for time-series: Always sort by start_time asc for trends.

/* Daily crash trend (chronological) */
execution.crashes during past 7d
| summarize crash_count = count() by 1d
| sort start_time asc  # Oldest to newest (chronological)

Sorting Different Field Types

Numeric Fields

/* Sort by memory size */
devices
| list device.name, device.hardware.memory
| sort device.hardware.memory desc  # Largest memory first

Text Fields

/* Sort by OS name */
devices
| list device.name, operating_system.name
| sort operating_system.name asc  # Alphabetical

Aggregated Values

/* Sort by calculated metric */
web.page_views during past 7d
| where application.name == "Confluence"
| summarize avg_load_time = page_load_time.backend.avg() by device.name
| list device.name, avg_load_time
| sort avg_load_time desc  # Slowest devices first

Computed Metrics

/* Sort by computed value */
devices during past 7d
| include execution.events
| compute avg_cpu = cpu_time.avg()
| list device.name, avg_cpu
| sort avg_cpu desc  # Highest CPU first

Real-World Examples

Example: Top 10 Largest Binaries

Scenario: Find the largest executable files.

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

Example: Crash Trend (Chronological)

Scenario: Display daily crash trend for charting.

execution.crashes during past 30d
| where binary.binary.name == "outlook.exe"
| summarize crash_count = count() by 1d
| sort start_time asc  # Chronological for line chart

Why asc: Charts display left-to-right (oldest to newest)

Example: Devices Needing Attention

Scenario: Find devices with lowest disk space (most urgent).

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

Example: Application Crash Leaderboard

Scenario: Show which applications crash the most.

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  # Most problematic first
| limit 20

Sort Position in Query

sort typically comes after filtering and aggregation, before limit:

/* Standard order */
devices during past 7d
| where operating_system.platform == windows  # Filter
| include execution.events
| compute avg_cpu = cpu_time.avg()            # Compute
| where avg_cpu > 50                          # Filter computed
| list device.name, avg_cpu                   # Select fields
| sort avg_cpu desc                           # Order results
| limit 20                                    # Restrict rows

Common Patterns

Pattern: Top N by Metric

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

Pattern: Bottom N by Metric

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

Pattern: Chronological Time-Series

<event_table> during past Xd
| summarize metric = field.agg() by 1d
| sort start_time asc  # Always asc for chronological

Pattern: Alphabetical List

<query>
| list fields
| sort name_field asc  # A-Z

Tips & Tricks

Always Sort Time-Series by start_time asc

For trends and charts, always sort chronologically: 

execution.crashes during past 7d
| summarize count = count() by 1d
| sort start_time asc  # Chronological order

Sort After Aggregation, Not Before

Sorting individual events before aggregation is meaningless: 

/* ❌ WRONG - Sorting events before aggregation */
execution.events during past 7d
| sort cpu_time desc  # Meaningless on individual events
| summarize avg_cpu = cpu_time.avg() by device.name

/* ✅ CORRECT - Sort aggregated results */
execution.events during past 7d
| summarize avg_cpu = cpu_time.avg() by device.name
| sort avg_cpu desc  # Sort the aggregated values

Combine with limit for Top/Bottom N

/* Top 10 pattern */
| sort metric desc
| limit 10

/* Bottom 10 pattern */
| sort metric asc
| limit 10

Field Doesn't Need to Be in list

You can sort by a field that's not displayed (though it's clearer to include it): 

/* Works, but not obvious */
devices
| list device.name
| sort hardware.memory desc  # Memory not shown

/* Better - include sort field in list */
devices
| list device.name, hardware.memory.as(format = bytes)
| sort hardware.memory desc

Common Mistake: Missing Direction

/* ❌ WRONG - Missing asc/desc */
| sort device.name

/* ✅ CORRECT - Direction required */
| sort device.name asc

Common Mistake: Wrong Order for Time-Series

/* ❌ WRONG - Descending shows newest first (backwards chart) */
execution.crashes during past 7d
| summarize count = count() by 1d
| sort start_time desc  # Chart will be backwards!

/* ✅ CORRECT - Ascending for chronological charts */
execution.crashes during past 7d
| summarize count = count() by 1d
| sort start_time asc  # Proper chronological order

Performance Considerations

sort has minimal performance impact - it's applied to the result set after all data processing.

Best practices:

  • Sort doesn't reduce query cost (filtering does)
  • Position doesn't matter much (applied to final results)
  • Common pattern: filter → compute/summarize → sort → limit

Additional Resources