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

[anthonycopus]

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

[anthonycopus · Accepted solution]

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

[nick405060]

tl;dr keepempty=true

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

[anthonycopus]

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

[lukejadamec]

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.