Axiom
Overview

countif

Use countif when you want to count occurrences of certain conditions, such as HTTP status codes, errors, or actions in telemetry traces.

Usage#

Syntax#

countif(condition)

Parameters#

  • condition: A boolean expression that filters the records based on a condition. Only records where the condition evaluates to true are counted.

Returns#

The function returns the number of records that match the specified condition.

Use case examples#

In log analysis, you might want to count how many HTTP requests returned a 500 status code to detect server errors.

Query

['sample-http-logs']
| summarize countif(status == '500')

Run in Playground

Output

count_errors
72

This query counts the number of HTTP requests with a 500 status, helping you identify how many server errors occurred.

In OpenTelemetry traces, you might want to count how many requests were initiated by the client service kind.

Query

['otel-demo-traces']
| summarize countif(kind == 'client')

Run in Playground

Output

count_client_kind
345

This query counts how many requests were initiated by the client service kind, providing insight into the volume of client-side traffic.

In security logs, you might want to count how many HTTP requests originated from a specific city, such as New York.

Query

['sample-http-logs']
| summarize countif(['geo.city'] == 'New York')

Run in Playground

Output

count_nyc_requests
87

This query counts how many HTTP requests originated from New York, which can help detect traffic from a particular location for security analysis.

  • count: Counts all records in a dataset without applying a condition. Use this when you need the total count of records, regardless of any specific condition.
  • sumif: Adds up the values of a field for records that meet a specific condition. Use sumif when you want to sum values based on a filter.
  • dcountif: Counts distinct values of a field for records that meet a condition. This is helpful when you need to count unique occurrences.
  • avgif: Calculates the average value of a field for records that match a condition, useful for performance monitoring.
  • maxif: Returns the maximum value of a field for records that meet a condition. Use this when you want to find the highest value in filtered data.

Other query languages#

Splunk SPL users

In Splunk SPL, conditional counting is typically done using the eval function combined with stats. APL provides a more streamlined approach with the countif function, which performs conditional counting directly.

Splunk example

| stats count(eval(status="500")) AS error_count

APL equivalent

['sample-http-logs']
| summarize countif(status == '500')
ANSI SQL users

In ANSI SQL, conditional counting is achieved by using the COUNT function with a CASE statement. In APL, countif simplifies this process by offering a direct approach to conditional counting.

SQL example

SELECT COUNT(CASE WHEN status = '500' THEN 1 END) AS error_count
FROM sample_http_logs

APL equivalent

['sample-http-logs']
| summarize countif(status == '500')

Updated

Was this page helpful?