Add column value of matching fields

[zacksoft]

The table output of my Splunk search gives me an output like this.
There are two columns "fruit" and "rotten_time".

fruit | rotten_time
nyf   | 97 sec
mse   | 16 sec
sem   | 20 sec
ert   | 33 sec
dhg   | 21 sec

I want a "add column value" of the column rotten_time, but it shouldn't add all the values. It should only add if the corresponding fruit value starts with 'mse' and ends with 'ert'. That mean it should only add 16+20+33

[mayurr98 · Accepted solution]

hey try this:

base_search_which_gives_table_output i.e. stats values(rotton_time) as rotten_time by fruit 
| rex field=rotten_time "(?P<rotten_time>\d+)" 
| streamstats sum(rotten_time) as sum_between_mse_&_ert reset_before="("fruit==\"mse\"")" reset_after="("fruit==\"ert\"")" reset_on_change=true 
| search fruit=ert 
| fields sum_between_mse_&_ert

let me know if this helps you!

[zacksoft]

Just as you know, there is no pipe symbol (|) between the column fruit and rotten_time. They are two separate columns . I have added them pipe mark to separete them for understanding here in the forum.

[zacksoft]

I get an error
"Error in 'streamstats' command: The argument 'sum_between_/mse/tup/1_&_/ert/tup/2' is invalid."

Also, my fruit names are little bit complex. It is not just "mse". It is rather /mse/tup/1.
Similarly "ert" is actually /ert/tup/2.

So i tried to encapsulate them in double quotes, yet I got the error.

[mayurr98]

yes i have considered both of them as separate columns only

try this

base_search_which_gives_table_output i.e. stats values(rotton_time) as rotten_time by fruit 
| rex field=rotten_time "(?P<rotten_time>\d+)" 
| streamstats sum(rotten_time) as "sum_between_/mse/tup/1_&_/ert/tup/2" reset_before="("sourcetype==\"/mse/tup/1\"")" reset_after="("sourcetype==\"/ert/tup/2\"")" reset_on_change=true 
| search fruit="/ert/tup/2" 
| fields "sum_between_/mse/tup/1_&_/ert/tup/2"

[zacksoft]

@mayurr98 Thank you.

[mayurr98]

also if you get an error with this search please show me your query as this query I have tested and its working fine at my end!

[zacksoft]

It runs now without any error.
But I am not able to see my result (of sum). In the statistics tab it doesn't show anything..

[mayurr98]

try to troubleshoot at which point you are not getting results start with the first pipe and see results. if you are getting results then add the second query to second pipe like this check for every pipe results and see at which point you are not getting results for eg.
check for below query if you are getting output and then add next query

base_search_which_gives_table_output i.e. stats values(rotton_time) as rotten_time by fruit 
 | rex field=rotten_time "(?P<rotten_time>\d+)" 

[zacksoft]

I am getting the output (in the events tab). It's just not in a tabular format or graphs to be able to see in the Statistics or Visualization tab.

[mayurr98]

are you doing stats values(rotton_time) as rotten_time by fruit before streamstats command?
because streamstats works on events in order to show it in table you need to first get that table as an input to this command.

[mayurr98]

can you give me what output you get stats values(rotton_time) as rotten_time by fruit
command

[zacksoft]

I see in fruit, the /ert/tup/2 value is a bit different.
Sometime the value is /ert/tup/2?=4 , other time it is /ert/tup/2?=7, /ert/tup/2?=3 etc,,,
Can I replace the /ert/tup/2 keywords as this /ert/tup/2?=* ?

[mayurr98]

I do not know about these but you can always try and see what results in you get .this query that I gave is based on the inputs you gave but logically * would work but see if you getting desired results

[zacksoft]

I ran the stats values(rotton_time) as rotten_time by fruit .
and it shows me a table with two coumns . fruit and rotten_time.

[zacksoft]

I added ,
| stats values(rotten_time) as rotten_time by fruit
between the rex fields line and streamstats line.and before the rex field I have the table command whose output I had originally posted.

[mayurr98]

so after doing rex you are getting sec is removed right? i.e you are getting numerical values i.e. 20 16 30?
if you getting these values then just | stats sum(rotten_time)
just to see if stats command is working after rex or not.

[zacksoft]

In my base query itself I have removed the sec now. Now my query gives only numeric figure , with out the 'sec' appended to it.
Does this mean, is it okay to remove the rex statement from the query ?

[mayurr98]

okay so if you have removed sec then remove rex (rex command i have used to remove sec) then try running streamstats command after stats command which I gave to you and see the output you get

[zacksoft]

I kept until streamstats command and remove that last two lines.
It seems to give me some result.
What were the role of the last two lines ? Will it impact anything that I'm looking for ? and what about the ert thing .... as per different queries /ert .. thing gives different value.
Is it okay to do something like this (/ert/tup/2?=*), I mean putting an asterisk in the changing value part ?

[mayurr98]

see i am assuming that you have only one mse and only one ert after streamstats command you will get output like
stats ouput
fruit | rotten_time
nyf| 97
mse | 16
sem | 20
ert | 33
dhg | 21

streamstats output
fruit | rotten_time
nyf| 97
mse | 16
sem | 36
ert | 69
dhg | 21
69 is the output you want. just check theoutput of streamstats command is getting adding ?
so mse to ert the sum is 69 in order to display only 69 i have written last two lines!
see the output of streamstats and look the sum