The where operator in APL is used to filter rows based on specified conditions. You can use the where operator to return only the records that meet the criteria you define. It’s a foundational operator in querying datasets, helping you focus on specific data by applying conditions to filter out unwanted rows. This is useful when working with large datasets, logs, traces, or security events, allowing you to extract meaningful information quickly.

​
For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.

​
Usage

​
Syntax

| where condition

​
Parameters

  • condition: A Boolean expression that specifies the filtering condition. The where operator returns only the rows that satisfy this condition.

​
Returns

The where operator returns a filtered dataset containing only the rows where the condition evaluates to true.

​
Use case examples

In this use case, you filter HTTP logs to focus on records where the HTTP status is 404 (Not Found).

Query

['sample-http-logs']
| where status == '404'

Run in Playground

Output

_timeidstatusmethodurireq_duration_msgeo.citygeo.country
2024-10-17 10:20:0012345404GET/notfound.html120SeattleUS

This query filters out all HTTP requests except those that resulted in a 404 error, making it easy to investigate pages that were not found.

​
where * has

The * has pattern in APL is a dynamic and powerful tool within the where operator. It offers you the flexibility to search for specific substrings across all fields in a dataset without the need to specify each field name individually. This becomes especially advantageous when dealing with datasets that have numerous or dynamically named fields.

where * has is an expensive operation because it searches all fields. For a more efficient query, explicitly list the fields in which you want to search. For example: where firstName has "miguel" or lastName has "miguel".

​
Basic where * has usage

Find events where any field contains a specific substring.

['sample-http-logs'] 
| where * has "GET"

Run in Playground

​
Combine multiple substrings

Find events where any field contains one of multiple substrings.

['sample-http-logs'] 
| where * has "GET" or * has "text"

Run in Playground

​
Use * has with other operators

Find events where any field contains a substring, and another specific field equals a certain value.

['sample-http-logs'] 
| where * has "css" and req_duration_ms == 1

Run in Playground

​
Advanced chaining

Filter data based on several conditions, including fields containing certain substrings, then summarize by another specific criterion.

['sample-http-logs']
| where * has "GET" and * has "css"
| summarize Count=count() by method, content_type, server_datacenter

Run in Playground

​
Use with aggregations

Find the average of a specific field for events where any field contains a certain substring.

['sample-http-logs']
| where * has "Japan"
| summarize avg(req_duration_ms)

Run in Playground

​
String case transformation

The has operator is case insensitive. Use has if you’re unsure about the case of the substring in the dataset. For the case-sensitive operator, use has_cs.

['sample-http-logs']
| where * has "mexico"
| summarize avg(req_duration_ms)

Run in Playground

  • count: Use count to return the number of records that match specific criteria.
  • distinct: Use distinct to return unique values in a dataset, complementing filtering.
  • take: Use take to return a specific number of records, typically in combination with where for pagination.