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

[kingso_paypal]

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?

[yuanliu · Accepted solution]

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.

[kingso_paypal]

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

[gcusello]

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

[kingso_paypal]

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...

[gcusello]

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

[gcusello]

Hi @kingso_paypal,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[gcusello]

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