How can I fill different values according to different conditions ?

[WXY]

Hi ,

Here's my SPL:

index="last_f" 
     | stats count by level,sys_name _time 
     | eval rate=case(
         level== "critical", 0.5,
         level== "high", 0.3,
         level== "medium", 0.2,
         level== "low", 0) 
     | eval score=count*rate 
     | stats sum(score) as SCORE by sys_name  _time 
     | sort - SCORE 
     | streamstats latest(SCORE) as a by sys_name  current=f 
     | fillnull value=0 a 
     | eval aa = SCORE-a 
     | stats latest(_time) as _time count latest(a) as last_score latest(SCORE) as final_score latest(aa) as c_value by sys_name 
     | sort - final_score

I can get a table such as :

sya_name         _time             count     last_score      final_score     c_value
 sys_n1      2018/10/11 10:02:12        3         2400             500               -1900
 sys_n2      2018/09/10 12:09:22        1         0                    179                179
 sys_n3      2018/11/01 17:02:34        2         400                500                100
 sys_n4      2018/08/12 14:02:42        2         550                450                -100
 sys_n5      2018/10/15 12:08:11        2         660                660                0

But I want to this:

If count==1 c_value="Only One"
if count>1&&c_value<0 c_value="Level Up" 
if count>1&&c_value>0  c_value="Level Down" 
if count>1&&c_value==0 c_value="Normal"

For example:

sya_name       time                   count     last_score      final_score     c_value
 sys_n1      2018/10/11 10:02:12        3           2400           500          Level Down
 sys_n2      2018/09/10 12:09:22        1           0                 179            Only One
 sys_n3      2018/11/01 17:02:34        2           400             500           Level Up
 sys_n4      2018/08/12 14:02:42        2           550             450          Level Down
 sys_n5      2018/10/15 12:08:11        2           660             660          Normal

What should I do?

[msivill_splunk · Accepted solution]

Not sure I've got the logic right but you could add the following line to your existing SPL.

| eval c_value = CASE ( count==1, "Only One", (count>1 and c_value>1), "Level Up", c_value<0, "Level Down", (count>1 and c_value==0), "Normal" )

Hopefully the above shows the principle.

A self-contained example showing this so people can test on thier own Splunk instances (everything upto the first eval c_value is generating the data)

| makeresults count=5
| streamstats count as row 
| eval _time = _time - row 
| eval count = CASE (row==1,3, row==2,1, 1==1, 2) 
| eval c_value = CASE ( row==1,-1900,row==2,170,row==3,100,row==4,-100,row==5,0)
| eval c_value = CASE ( count==1, "Only One", (count>1 and c_value>1), "Level Up", c_value<0, "Level Down", (count>1 and c_value==0), "Normal" )

[WXY]

Thank you very much

[msivill_splunk]

Thank you for accepting the answer