topic Re: how to fill null values using another field value in Splunk Search

How to fill null values using another field value?

Laya123 — Thu, 28 Jul 2022 15:22:56 GMT

Hi,
I need small to fill null values in search results

I have search results like

ID host country
1 A CC
2 A CC
3 B AA
4 C CC
5 A

6 B AA
7 B AA
8 C CC
9 A CC
10 B

11 A

I want to fill blanks of country from other rows where the same host is there means for ID:5 host is 'A' but country is blank I want to fill that blank with 'CC' (the country name is same for same host for all IDs) same as B host for ID:10 is balnk wanto fill with 'AA' why because host 'B' country is 'CC' same for all blanks of country has to be filled with country of same host

thanks in advance

Re: how to fill null values using another field value

somesoni2 — Mon, 02 Nov 2015 15:50:45 GMT

Try something like this (assuming Country for a host is always same)

your current search giving above output | eventstats values(Country) as Country by host

Re: how to fill null values using another field value

HeinzWaescher — Mon, 02 Nov 2015 15:50:51 GMT

Does this solve yoour problem?

...
| eventstats values(country) country_2 BY host
| eval country=coalesce(country, country_2)
| fields- country_2

Re: how to fill null values using another field value

aholzer — Mon, 02 Nov 2015 16:19:26 GMT

You could use filldown command. You'd have to sort by host first. The end result would look something like:

... | sort host | filldown country

Re: how to fill null values using another field value

Laya123 — Tue, 03 Nov 2015 14:22:47 GMT

Thanks for imeediate response. its working. but its working for only if your query results are below 10000. if we have more than 10000 results sort command is truncating the results to 10000.

Re: how to fill null values using another field value

Machine247 — Thu, 28 Jul 2022 14:40:21 GMT

Sort has a default value of 10000

sort - Splunk Documentation

You would need to specify "sort 0" (zero) in the code, to remove any limits

Optional arguments

<count>

Syntax: <int> | limit=<int>

Description:

Specify the number of results to return from the sorted results.

If no count is specified, the default limit of 10000 is used.

If 0 is specified, all results are returned.

You can specify the count using an integer or precede the count with a label, for example limit=10

Using sort 0 might have a negative impact performance, depending on how many results are returned.