Splunk Search

## can we use field name for comparison in case statement?

Engager

Hi Splunkers!

I am try to evaluate few things by using query below-

index=* sourcetype=* | stats values(OPENINT) as int by
OPTION
TYP STRIKEPR | appendcols [|search index=* sourcetype=*
OPTION
TYP=XX | eval a1=CLOSE-(CLOSE75)/10000|eval a2=CLOSE+(CLOSE75)/10000|
eval i2=CLOSE-(CLOSE25)/1000 | eval o2=CLOSE+(CLOSE25)/1000 |table a1 a2 i2 o2 CLOSE]|
eval ty=case(STRIKEPR>=9839.46 AND STRIKEPR<10016,"IN",STRIKE_PR>=10016 AND STRIKEPR<10167,"AT"
,STRIKE
PR>=10167 AND STRIKE_PR<=10344,"OUT",1==1, NULL) | search ty!=NULL |

I need to use the values of fields a1 a2 i2 o2 in the case statement written above, such that my statement appears like-
eval ty=case(STRIKEPR>=i2 AND STRIKEPR=a1 AND STRIKEPR=a2AND STRIKEPR<=o2,"OUT",1==1, NULL) | search ty!=NULL |

But splunk doesn't give me any results when i use fields name instead of the numeric value.

Can someone figure out what the problem is?

Tags (2)
1 Solution
Champion

Yes, you can use fields in case statements. Here is a simple example that proves it.

``````| makeresults
| fields - _time
| eval thresh = 3, value = 3
| eval result = case(thresh > value, "lower", thresh < value, "higher", thresh == value, "equal", 1==1, "0")
``````

Without having your actual data, one suggestion I would make is replace `NULL` with `NULL()` in the case statement. Then change the following search to `| WHERE isnotnull(ty)`. Maybe the fact that you are using `search` against a table of field values and not `_raw` is the issue.

So the modified search would be

``````index= sourcetype=
| stats values(OPEN_INT) as int by
OPTION_TYP STRIKE_PR
| appendcols [|search index= sourcetype=
OPTION_TYP=XX | eval a1=CLOSE-(CLOSE*75)/10000|eval a2=CLOSE+(CLOSE*75)/10000|
eval i2=CLOSE-(CLOSE*25)/1000 | eval o2=CLOSE+(CLOSE*25)/1000 |table a1 a2 i2 o2 CLOSE]
| eval ty=case(STRIKE_PR>=9839.46 AND STRIKE_PR<10016,"IN",STRIKE_PR>=10016 AND STRIKE_PR<10167,"AT"
,STRIKE_PR>=10167 AND STRIKE_PR<=10344,"OUT",1==1, NULL())
| where isnotnull(ty)
``````
Champion

Yes, you can use fields in case statements. Here is a simple example that proves it.

``````| makeresults
| fields - _time
| eval thresh = 3, value = 3
| eval result = case(thresh > value, "lower", thresh < value, "higher", thresh == value, "equal", 1==1, "0")
``````

Without having your actual data, one suggestion I would make is replace `NULL` with `NULL()` in the case statement. Then change the following search to `| WHERE isnotnull(ty)`. Maybe the fact that you are using `search` against a table of field values and not `_raw` is the issue.

So the modified search would be

``````index= sourcetype=
| stats values(OPEN_INT) as int by
OPTION_TYP STRIKE_PR
| appendcols [|search index= sourcetype=
OPTION_TYP=XX | eval a1=CLOSE-(CLOSE*75)/10000|eval a2=CLOSE+(CLOSE*75)/10000|
eval i2=CLOSE-(CLOSE*25)/1000 | eval o2=CLOSE+(CLOSE*25)/1000 |table a1 a2 i2 o2 CLOSE]
| eval ty=case(STRIKE_PR>=9839.46 AND STRIKE_PR<10016,"IN",STRIKE_PR>=10016 AND STRIKE_PR<10167,"AT"
,STRIKE_PR>=10167 AND STRIKE_PR<=10344,"OUT",1==1, NULL())
| where isnotnull(ty)
``````
Engager

Hi,