topic Re: Add column value of matching fields in Dashboards & Visualizations

Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 08:07:50 GMT

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

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 09:29:18 GMT

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!

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 09:37:28 GMT

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.

Re: Add column value of matching fields

zacksoft — Tue, 29 Sep 2020 17:28:36 GMT

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.

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 09:58:36 GMT

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"

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:01:07 GMT

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!

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 10:11:05 GMT

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

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:17:47 GMT

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+)"

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:22:57 GMT

as you are not getting any error then one of the search is filtering out your events !
so tell me at after hitting which command you are not getting results.

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 10:24:40 GMT

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.

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:34:25 GMT

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.

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:35:37 GMT

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

Re: Add column value of matching fields

zacksoft — Tue, 29 Sep 2020 17:28:39 GMT

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.

Re: Add column value of matching fields

zacksoft — Tue, 29 Sep 2020 17:28:42 GMT

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.

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:50:12 GMT

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.

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 10:50:51 GMT

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?=* ?

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 10:52:11 GMT

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 ?

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 10:55:19 GMT

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

Re: Add column value of matching fields

zacksoft — Wed, 03 Jan 2018 11:06:50 GMT

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 ?

Re: Add column value of matching fields

mayurr98 — Wed, 03 Jan 2018 11:27:33 GMT

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