How to create an If/Else statement inside an eval statement?

[tonahoyos]

Hello All,

I am running the following search:

index="ledata_2017" NOT Project="60*"
| stats sum(ActualPTDCostsAMT) , sum(LEThisMthCostsAMT) 
| eval ActualTotal=-ActualTotal, LETotal=-LETotal, n=max(1,2,3,4,5,6,7,8,9,10,11,0,MonthNum)
| eval YTDAvg=(ActualTotal/n), YTGAvg=(LETotal-ActualTotal)/(15-n)
| table Project,Ratio,Number

I want to be able to include an if-else statement inside line 4, where I can indicate:

If projectA, then 14-n
else if projectB then n-3,
else if 15-n (for the rest of the projects) 

Is this possible?

Thank you all!

[niketn · Accepted solution]

@tonahoyos, you ca try the following, however keep in mind the following:

1) All fields to be used after the stats pipe must be included in the stats command like Project MonthNum etc.
2) Project!="60*" and NOT Project="60*" are different. Make sure you use correct one in your base search.
3) Ratio and Number fields in the final table pipe are not calculated in previous pipes.

index="ledata_2017" Project!="60*"
| stats sum(ActualPTDCostsAMT) as ActualTotal , sum(LEThisMthCostsAMT) as LETotal by Project MonthNum
| eval ActualTotal=-ActualTotal, LETotal=-LETotal, n=max(1,2,3,4,5,6,7,8,9,10,11,0,MonthNum)
| eval divisor = case(Project=="projectA",14-n,Project=="projectB",n-3,true(),15-n)
| eval YTDAvg=(ActualTotal/n), YTGAvg=(LETotal-ActualTotal)/divisor

Following is the run anywhere search based on Splunk's _internal index on similar lines as per the question:

index="_internal" log_level!="INFO"
| stats sum(date_second) as ActualTotal , sum(date_hour) as LETotal by log_level date_month
| eval ActualTotal=-ActualTotal, LETotal=-LETotal, n=max(1,2,3,4,5,6,7,8,9,10,11,0,date_mday)
| eval divisor = case(log_level=="ERROR",14-n,log_level=="WARN",n-3,true(),15-n)
| eval YTDAvg=(ActualTotal/n), YTGAvg=(LETotal-ActualTotal)/divisor

[tonahoyos]

This is how I included your recommendation, thank you! I will double check my results and see if there is anything wrong. Let me know if you see any inconsistencies in the code. Thanks again!

| stats sum(ActualPTDCostsAMT) as ActualTotal, sum(LEThisMthCostsAMT) as LETotal by Project
| eval ActualTotal=-ActualTotal, LETotal=-LETotal, n=max(1,2,3,4,5,6,7,8,9,10,11,0,MonthNum)
| eval divisor1= case(Project=="1405688",14-n, true(),15-n),
divisor2= case(Project=="1408525",n-3,Project=="1410522",n-4,Project=="1404501",n-4,

Project=="1409599",n-3, true(),n)
| eval YTDAvg=(ActualTotal/divisor2), YTGAvg=(LETotal-ActualTotal)/divisor1
| eval Ratio=YTGAvg/YTDAvg
| eval Number=1
| table Project,Ratio,Number

[niketn]

@tonahoyos, slight correction in your stats command | stats .... by Project MonthNum, since MonthNum is used in deciding n in subsequent eval. I think rest looks fine. Let us know if anything does not work!

[tonahoyos]

Thank you! Everything is acting well, so far!

[493669]

You can use case statement:

|eval fieldA = case(Project=="projectA","14-n",Project=="projectB","n-3",1==1,"15-n")

now fieldA has required output ...You can use as per requirement

[tonahoyos]

What does the 1==1 do?

[493669]

if it does not match first two conditions then else condition is specified by 1==1