make_set_if

This page explains how to use the make_set_if aggregation function in APL.

The make_set_if aggregation function in APL allows you to create a set of distinct values from a column based on a condition. You can use this function to aggregate values that meet specific criteria, helping you filter and reduce data to unique entries while applying a conditional filter. This is especially useful when analyzing large datasets to extract relevant, distinct information without duplicates.

You can use make_set_if in scenarios where you need to aggregate conditional data points, such as log analysis, tracing information, or security logs, to summarize distinct occurrences based on particular conditions.

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.

Splunk SPL users

In Splunk SPL, you may use values with a where condition to achieve similar functionality to make_set_if. However, in APL, the make_set_if function is explicitly designed to create a distinct set of values based on a conditional filter within the aggregation step itself.

Splunk example

| stats values(field) by another_field where condition

ANSI SQL users

In ANSI SQL, you would typically use GROUP BY in combination with conditional aggregation, such as using CASE WHEN inside aggregate functions. In APL, the make_set_if function directly aggregates distinct values conditionally without requiring a CASE WHEN.

SQL example

SELECT DISTINCT CASE WHEN condition THEN field END
FROM table
GROUP BY another_field

Usage

Syntax

make_set_if(column, predicate, [max_size])

Parameters

• column: The column from which distinct values will be aggregated.
• predicate: A condition that filters the values to be aggregated.
• [max_size]: (Optional) Specifies the maximum number of elements in the resulting set. If omitted, the default is 1048576.

Returns

The make_set_if function returns a dynamic array of distinct values from the specified column that satisfy the given condition.

Use case examples

Log analysis

In this use case, you’re analyzing HTTP logs and want to get the distinct cities from which requests originated, but only for requests that took longer than 500 ms.

Query

['sample-http-logs']
| summarize make_set_if(['geo.city'], req_duration_ms > 500) by ['method']

Run in Playground

Output

method	make_set_if_geo.city
GET	[‘New York’, ‘San Francisco’]
POST	[‘Berlin’, ‘Tokyo’]

This query returns the distinct cities from which requests took more than 500 ms, grouped by HTTP request method.

In this use case, you’re analyzing HTTP logs and want to get the distinct cities from which requests originated, but only for requests that took longer than 500 ms.

Query

['sample-http-logs']
| summarize make_set_if(['geo.city'], req_duration_ms > 500) by ['method']

Run in Playground

Output

method	make_set_if_geo.city
GET	[‘New York’, ‘San Francisco’]
POST	[‘Berlin’, ‘Tokyo’]

This query returns the distinct cities from which requests took more than 500 ms, grouped by HTTP request method.

Here, you’re analyzing OpenTelemetry traces and want to identify the distinct services that processed spans with a duration greater than 1 second, grouped by trace ID.

Query

['otel-demo-traces']
| summarize make_set_if(['service.name'], duration > 1s) by ['trace_id']

Run in Playground

Output

trace_id	make_set_if_service.name
abc123	[‘frontend’, ‘cartservice’]
def456	[‘checkoutservice’, ‘loadgenerator’]

This query extracts distinct services that have processed spans longer than 1 second for each trace.

In security log analysis, you may want to find out which HTTP status codes were encountered for each city, but only for POST requests.

Query

['sample-http-logs']
| summarize make_set_if(status, method == 'POST') by ['geo.city']

Run in Playground

Output

geo.city	make_set_if_status
Berlin	[‘200’, ‘404’]
Tokyo	[‘500’, ‘403’]

This query identifies the distinct HTTP status codes for POST requests grouped by the originating city.

List of related aggregations

• make_list_if: Similar to make_set_if, but returns a list that can include duplicates instead of a distinct set.
• make_set: Aggregates distinct values without a conditional filter.
• countif: Counts rows that satisfy a specific condition, useful for when you need to count rather than aggregate distinct values.