Splunk Search
Highlighted

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

Path Finder

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

Highlighted

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

Super Champion

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

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

0 Karma
Highlighted

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

Path Finder

Thanks, in the end I found a cleaner alternative:

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

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

Highlighted

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

Path Finder

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

View solution in original post

Highlighted

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

Motivator

tl;dr keepempty=true

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

0 Karma