count_distinct

The count_distinct function returns the number of rows that have distinct values within a group. It is often used with match to get counts for specific groups in the data.

Note: Missing or uninitialized Unified Data Model (UDM) fields default to zero values (such as "", 0, or "UNKNOWN_ACTION") instead of NULL. Because count_distinct evaluates zero values as valid data, it counts them as unique items. If a field is uninitialized in some events, count_distinct counts the zero value as an additional distinct item, increasing your total count by one. To exclude default values from your count, add a filter in the events section of your query (for example, $userid != "").

Syntax	Param data types	Return type
count_distinct(expression)	STRING	NUMBER

Code Samples

• Return count of distinct user logins

  Return the count of distinct successful user logins over time.

    metadata.event_type = "USER_LOGIN"
    $security_result = security_result.action
    $security_result = "ALLOW"
    $date = timestamp.get_date(metadata.event_timestamp.seconds, "America/Los_Angeles")
    match:
        $security_result, $date
    outcome:
        $event_count = count_distinct(metadata.id)
  

• Exclude default values from distinct counts

  The following example filters out uninitialized user IDs so count_distinct doesn't include default empty strings in the total count:

  metadata.event_type = "USER_LOGIN"
  $userid = principal.user.userid
  $userid != ""
  match:
      metadata.event_type
  outcome:
      $distinct_users = count_distinct($userid)