topic Re: How to search by a field which may have null value? in Splunk Search

How to search by a field which may have null value?

kingso_paypal — Fri, 29 Jul 2022 05:48:59 GMT

Below is the log events that I have. One has max_amount value and one has empty value. I want to find out the events that have transaction_amount > max_amount.

[Date=2022-07-29, max_amount=100, transaction_amount=120] [Date=2022-07-29, max_amount=100, transaction_amount=90] [Date=2022-07-29, transaction_amount=120]

I tried transaction_amount>max_amount but not working. I guess it is due to some records having no max_amount value.

index=<table_name> transaction_amount>max_amount | bucket Date span=day | fillnull value=null max_amount | stats count by Date, max_amount, transaction_amount

How to get the record #1?

Re: How to search by a field which may have null value?

gcusello — Fri, 29 Jul 2022 06:05:03 GMT

Hi @kingso_paypal,

Let me understand: you want to take events with transaction_amount>max_amount, but do you want to consider also events without max_amount or not?

If yes, you could fullfill missing values with zero, if not, you have to add a condition to the main search:

first case

index=<table_name> (transaction_amount>max_amount OR (transaction_amount>0 NOT max_amount=*)) | bucket Date span=day | fillnull value=null max_amount | stats count by Date, max_amount, transaction_amount

second case:

index=<table_name> (transaction_amount>max_amount max_amount=*) | bucket Date span=day | fillnull value=null max_amount | stats count by Date, max_amount, transaction_amount

Ciao.

Giuseppe

Re: How to search by a field which may have null value?

kingso_paypal — Fri, 29 Jul 2022 09:59:33 GMT

Both cases are true to me. But when I tried, both return 0 records to me. Why?

Re: How to search by a field which may have null value?

gcusello — Fri, 29 Jul 2022 10:08:37 GMT

Hi @kingso_paypal ,

running the main search, do you see both the fields in interesting fields panel?

If yes, have you values or not?

Ciao.

Giuseppe

Re: How to search by a field which may have null value?

kingso_paypal — Mon, 01 Aug 2022 04:14:22 GMT

It returns 0 events. This happens when I add a column which has no value at all unless I "fillnull" it, but I did it on max_amount field...

Re: How to search by a field which may have null value?

gcusello — Mon, 01 Aug 2022 06:41:42 GMT

Hi @kingso_paypal,

running this search:

index=<table_name> transaction_amount>max_amount

have you results?

do you see both the fields in interesting fields panel?

Ciao.

Giuseppe

Re: How to search by a field which may have null value?

yuanliu — Mon, 01 Aug 2022 07:04:07 GMT

This is because search command cannot dereference field value, not because of missing value in some events. Use where command instead.

index=<table_name> | eventstats values(max_amount) AS max_amount ``` only if max_amount is a fixed value ``` | where transaction_amount > max_amount

The above assumes that max_amount is a fixed value for every event.

Obviously, if you don't want to count those events that lacks max_amount, remove that eventstats. On the other hand, if max_amount could be different values in different events but you want to set a value in those events, you need to decide whether to replace value() with min(), max(), avg(), median(), or any other statistical function that returns a single value from multiple values.

Re: How to search by a field which may have null value?

gcusello — Wed, 03 Aug 2022 09:23:04 GMT

Hi @kingso_paypal,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉