topic Need to dedup results based on id only if the field is not empty in Splunk Search

Need to dedup results based on id only if the field is not empty

anthonycopus — Thu, 24 Oct 2013 14:31:57 GMT

Hi,

I need help deduplicating in a search where only half the data contains an id. Basically, the old data has a field ses_id : "" whilst the new data will be populated ses_id : "123"

The search ends up with a table where we need a count which only deduplicates the entries which have a number in the ses_id field. A normal dedup is not good enough as it will count all the entries with "" as a single one obviously.

My search is as follows:

index="testindex" app="testapp" action="view" | stats count as views, max(_time) as _time by name

Any help would be appreciated,

Thanks

Re: Need to dedup results based on id only if the field is not empty

lukejadamec — Mon, 28 Sep 2020 15:03:50 GMT

You could try to search for ses_id is not null, dedup the results, and then join a second search that finds those ses_id that are null.

There is not enough info to tell you what field to join on.

Re: Need to dedup results based on id only if the field is not empty

anthonycopus — Mon, 28 Sep 2020 15:03:53 GMT

Thanks, in the end I found a cleaner alternative:

| eval ses_id=nullif(ses_id,"") | dedup ses_id keepempty=true

Changing the "" in the field to null meant I could use the keepempty function in the search

Re: Need to dedup results based on id only if the field is not empty

anthonycopus — Thu, 24 Oct 2013 15:32:49 GMT

Found an easy fix by changing the field populated with "" to null using the following search in total:

index="testindex" app="testapp" action="view" | eval ses_id=nullif(ses_id,"") | dedup ses_id keepempty=true | stats count as views, max(_time) as _time by name

Changing the field to null is there was no entry meant the keepempty property in the dedup can be used successfully

Re: Need to dedup results based on id only if the field is not empty

nick405060 — Thu, 14 Mar 2019 19:24:00 GMT

tl;dr keepempty=true

I have no idea how I have used Splunk for this long and not known about this. Thanks.