How to get a count with dedup?

[tazzvon]

i have the following in a statistical table on a dashboard

index=* <do search> | dedup B C | table _time B C D E F J | sort-_time

I would like to have a count at the end of each row telling how many it deduped.

[bowesmana]

In order to get the count of events and do dedup at the same time, you could do

index=* <do search> 
| stats latest(*) as * count as duplicates by B C 
| table _time B C D E F J duplicates
| eval duplicates = duplicates - 1
| sort - _time

 This should give you what you want - remember that dedup will normally give you the latest event from any duplicates, hence using latest(*).

[ITWhisperer · Accepted solution]

Actually, dedup will give you the first event it finds in the event pipeline for each unique set of values.

This is often the same as latest because the events returned by the search are often in descending time order (but it depends on what else is in the search before the dedup).

The other thing is that _time (and other fields beginning with _) as not included in * so these should be explicitly catered for.

A more accurate way to do this might be

index=* <do search> 
| stats first(*) as * first(_time) as _time count as duplicates by B C
| table _time B C D E F J duplicates
| eval duplicates = duplicates - 1
| sort - _time

[bowesmana]

Good clarification @ITWhisperer about event ordering rather than the simple _time based default