Introduction
CloudWatch Logs Insights can answer "distinct" questions, but not with the exact SQL syntax many people expect. The right solution depends on whether you want one representative log event per value or you only want the count of unique values.
Distinct Rows and Distinct Counts Are Different
In Logs Insights, there are two main patterns:
That difference matters because the output is different. dedup still returns log events, while count_distinct returns an aggregate number.
For example, if you want the most recent event for each user ID, you should use dedup. If you want to know how many unique users appeared in the last hour, you should use count_distinct.
Use dedup to Keep One Event Per Value
dedup removes duplicates based on the fields you name. The important detail is that it keeps the first matching event according to the current sort order, so sorting first is usually the correct move.
fields @timestamp, userId, action, @message
| filter isPresent(userId) | sort @timestamp desc | dedup userId | limit 20 ``` This query means: - Keep only events that actually have a `userId` - Sort newest to oldest - Return one event for each unique `userId` - Stop after 20 results If one user appears ten times, only the newest event survives because the query was sorted descending by `@timestamp` before `dedup` ran. You can also deduplicate on multiple fields: ```sql fields @timestamp, service, statusCode, requestId | sort @timestamp desc | dedup service, statusCode | limit 50 ``` That returns one event for each unique `service` and `statusCode` combination. ## Use `count_distinct` When You Need a Number If you do not need the actual log event and only want the number of unique values, `stats` is the better tool. ```sql fields userId | filter isPresent(userId) | stats count_distinct(userId) as unique_users ``` That produces a single aggregate result. You can also group by another field: ```sql fields userId, apiName | filter isPresent(userId) | stats count_distinct(userId) as unique_users by apiName ``` Now you get one row per API name with a distinct user count for each group. For time-based views, combine it with `bin`: ```sql fields clientIp | filter isPresent(clientIp) | stats count_distinct(clientIp) as unique_ips by bin(5m) ``` This is useful for dashboards because it shows how the number of unique clients changes over time. One practical note: distinct counts over very high-cardinality fields can be approximate rather than perfectly exact. That is fine for trend analysis, but it is worth remembering if you are validating billing or audit numbers. ## Choosing Between `dedup` and Aggregation A simple rule helps: - Use `dedup` when the result should still look like log records. - Use `stats count_distinct(...)` when the result should look like a report. Another difference is query flow. After `dedup`, the only command you can use is `limit`, so filtering and sorting must happen before it. Aggregation queries with `stats` are more flexible for summaries and grouped counts. That means this style is valid: ```sql fields @timestamp, requestId, userId | filter isPresent(userId) | sort @timestamp desc | dedup userId | limit 10 ``` But this style is not the pattern you want: ```sql fields @timestamp, requestId, userId | dedup userId | stats count(*) by userId ``` If the goal is counting, start with `stats`. If the goal is one record per unique field, finish with `dedup`. ## Handling Nulls and Missing Fields Missing fields often make distinct queries look wrong. With `dedup`, null values are not treated as duplicates in the usual way, so rows with missing values can remain in the result set. The safest habit is to filter them out first: ```sql fields @timestamp, sessionId | filter isPresent(sessionId) | sort @timestamp desc | dedup sessionId | limit 25 ``` This gives you cleaner output and prevents "distinct" results from being cluttered with incomplete events. ## Common Pitfalls - Looking for a SQL `DISTINCT` keyword instead of using `dedup` or `count_distinct`. - Forgetting to sort before `dedup`, which changes which log event is kept. - Trying to place more query commands after `dedup`; in practice, `limit` is the only command that should follow it. - Using `dedup` when you really want a numeric report. In that case, `stats count_distinct(...)` is simpler. - Ignoring missing fields, which can leave null-heavy rows in the result. ## Summary - In CloudWatch Logs Insights, "distinct" usually means either `dedup` or `stats count_distinct(...)`. - '`dedup` returns one log event per unique value or value combination.' - '`count_distinct` returns the number of unique values and is better for reports and charts.' - Sort before `dedup` so the record you keep is the one you actually want. - Filter missing fields early to keep distinct queries clean and predictable.
