Axiom
Overview

dcountif

Use dcountif in scenarios where you need a distinct count but only for a subset of the data, such as counting unique users from a specific region, unique error codes for specific HTTP statuses, or distinct traces that match a particular service type.

Usage#

Syntax#

dcountif(column_name, condition)

Parameters#

  • column_name: The name of the column for which you want to count distinct values.
  • condition: A boolean expression that filters the records. Only records that meet the condition will be included in the distinct count.

Returns#

The function returns the count of distinct values that meet the specified condition.

Use case examples#

In log analysis, you might want to count how many distinct users accessed the service and received a successful response (HTTP status 200).

Query

['sample-http-logs']
| summarize dcountif(id, status == '200')

Run in Playground

Output

distinct_successful_users
50

This query counts the distinct users (id field) who received a successful HTTP response (status 200), helping you understand how many unique users had successful requests.

In OpenTelemetry traces, you might want to count how many unique trace IDs are recorded for a specific service, such as frontend.

Query

['otel-demo-traces']
| summarize dcountif(trace_id, ['service.name'] == 'frontend')

Run in Playground

Output

distinct_frontend_traces
123

This query counts the number of distinct trace IDs that belong to the frontend service, providing insight into the volume of unique traces for that service.

In security logs, you might want to count how many unique IP addresses were logged for requests that resulted in a 403 status (forbidden access).

Query

['sample-http-logs']
| summarize dcountif(['geo.city'], status == '403')

Run in Playground

Output

distinct_cities_forbidden
20

This query counts the number of distinct cities (geo.city field) where requests resulted in a 403 status, helping you identify potential unauthorized access attempts from different regions.

  • dcount: Counts distinct values without applying any condition. Use this when you need to count unique values across the entire dataset.
  • countif: Counts records that match a specific condition, without focusing on distinct values. Use this when you need to count records based on a filter.
  • dcountif: Use this function to get a distinct count for records that meet a condition. It combines both filtering and distinct counting.
  • sumif: Sums values in a column for records that meet a condition. This is useful when you need to sum data points after filtering.
  • avgif: Calculates the average value of a column for records that match a condition. Use this when you need to find the average based on a filter.

Other query languages#

Splunk SPL users

In Splunk SPL, counting distinct values conditionally is typically achieved using a combination of eval and dc in the stats function. APL simplifies this with the dcountif function, which handles both filtering and distinct counting in a single step.

Splunk example

| stats dc(eval(status="200")) AS distinct_successful_users

APL equivalent

['sample-http-logs']
| summarize dcountif(id, status == '200')
ANSI SQL users

In ANSI SQL, conditional distinct counting can be done using a combination of COUNT(DISTINCT) and CASE. APL's dcountif function provides a more concise and readable way to handle conditional distinct counting.

SQL example

SELECT COUNT(DISTINCT CASE WHEN status = '200' THEN user_id END) AS distinct_successful_users
FROM sample_http_logs

APL equivalent

['sample-http-logs']
| summarize dcountif(id, status == '200')

Updated

Was this page helpful?