topic Re: 2 searches with dedup returning different results when using additional explicit field in Splunk Search

2 searches with dedup returning different results when using additional explicit field

arielofri — Mon, 17 Feb 2020 15:21:20 GMT

Hi, I'm running the following searches and getting different results for the same time range (All time) when comparing projects.

For example:

For this search, I'm getting many projects and their total unique "Defect ID"s. For the ACTIVATION project, I'm getting 23 results:

index="my_index" source="my_csv.csv"
| dedup "Defect ID"
| stats count  by "Project Name"

For this search, I'm getting 36 results.:

index="my_index" source="my_csv.csv" "Project Name"=ACTIVATION 
| dedup "Defect ID"
| stats count  by "Project Name"

Why when I'm adding the "Project Name"=ACTIVATION to the search I'm getting MORE results?

When adding the | search "Project Name"=ACTIVATION somewhere after the dedup command I'm still getting 23

index="my_index" source="my_csv.csv" "Project Name"=ACTIVATION 
| dedup "Defect ID" 
| search "Project Name"=ACTIVATION
| stats count  by "Project Name"

Re: 2 searches with dedup returning different results when using additional explicit field

manjunathmeti — Mon, 17 Feb 2020 16:51:28 GMT

Which count is correct 23 OR 36? can you post some sample data of file my_csv.csv?

Re: 2 searches with dedup returning different results when using additional explicit field

to4kawa — Tue, 18 Feb 2020 09:53:43 GMT

I see. so, another "Project Name" has same "Defect ID" .

Re: 2 searches with dedup returning different results when using additional explicit field

to4kawa — Tue, 18 Feb 2020 09:53:44 GMT

Because there is the event with "Project Name"=ACTIVATION has no "Defect ID" .

Re: 2 searches with dedup returning different results when using additional explicit field

arielofri — Tue, 18 Feb 2020 09:53:45 GMT

All events with "Project Name"=ACTIVATION have a "Defect ID"

Re: 2 searches with dedup returning different results when using additional explicit field

arielofri — Tue, 18 Feb 2020 12:24:30 GMT

The 36 is the correct one. Unfortunately, I can't share the data.

Re: 2 searches with dedup returning different results when using additional explicit field

manjunathmeti — Tue, 18 Feb 2020 13:39:33 GMT

Instead of dedup try dc and check:

 index="my_index" source="my_csv.csv"
| stats dc("Defect ID") by "Project Name"

Re: 2 searches with dedup returning different results when using additional explicit field

arielofri — Tue, 18 Feb 2020 13:54:40 GMT

It's working! do you know why? What the difference between the dc() and the dedup?

Re: 2 searches with dedup returning different results when using additional explicit field

manjunathmeti — Tue, 18 Feb 2020 15:08:43 GMT

Reason: As @to4kawa commented above in your data another "Project Name" value has same "Defect ID". And dedup removes duplicate values for field "Defect ID" irrespective of "Project Name". Distinct count or dc returns the count of distinct values of the field "Defect ID" by "Project Name". Even below query also works for you. But you should avoid using dedup whenever possible.

index="my_index" source="my_csv.csv" | dedup "Defect ID", "Project Name" | stats count  by "Project Name"