topic Re: how to get a count with dedup in Splunk Search

How to get a count with dedup?

tazzvon — Thu, 10 Mar 2022 05:07:20 GMT

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.

Re: how to get a count with dedup

bowesmana — Thu, 10 Mar 2022 04:43:27 GMT

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(*).

Re: how to get a count with dedup

ITWhisperer — Thu, 10 Mar 2022 07:49:22 GMT

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

Re: how to get a count with dedup

bowesmana — Mon, 14 Mar 2022 00:50:49 GMT

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