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.
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
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(*).
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
Good clarification @ITWhisperer about event ordering rather than the simple _time based default