Splunk Search

How to fetch next row value to a separate field

mnarmada
Path Finder

Hi,

I am new to Splunk and I want to perform some calculation here. I have a data like:
WeeK RFS1 RFS2 RFS3 decision
W1 5 5 5
W2 5 5 6
W3 1 2 2

etc.,
For the First week i.e, W1, Decision should be Zero.
Decision = 0
I have a formula i.e., Decision(W2)=RFS3(W1)+RFS3(W2)-Decision( W1)
This formula should be applicable to all the next rows of my data.
That means, for my next week,
Decision(W3)=RFS3(W2)+RFS3(W3)-Decision( W2)

I am unable to do search on this.
Could you please help me to perform the above mentioned thing.

Thanks in Advance,
Narmada

Tags (1)
0 Karma
1 Solution

maciep
Champion

i think autoregress could work for you...maybe like this:

<your search>
| autoregress RFS3 as prev_RFS3 | autoregress decision as prev_decision
| eval decision = coalesce(prev_RFS3 + RFS3 - prev_decision,0)

View solution in original post

0 Karma

mnarmada
Path Finder

Hello,

Sorry I misunderstood the requirement.
I have 3 questions here.
1)
Code WeeK RFS1 RFS2 RFS3 decision
1234 W1 5 5 5
1234 W2 5 5 6
1234 W3 1 2 2

etc.,
For the First week i.e, W1, Decision should be Zero.
Decision = 0
I have a formula i.e., Decision(W2)=RFS3(W1)+RFS3(W2)-Decision( W1)
and
Decision(W3)=RFS3(W1+W2+W3)-Decision( W2+W1)
or
Decision(W3)=RFS3(sum of W1,W2,W3)-Decision( sum of W1, w2)
This should continues for all the weeks,
Like For 15th week,
Decision(W15)=RFS3(W1+W2+................+W14+W15)-Decision( W1+W2+.........+W14)

I have tried using addcols and sum commands but did not find the solution.

2)
After doing this, I have to create a column chart to show all the values on the column if I place cursor over that bar.
Like:
for week1, all the details like code, week, previous total of rfs3, current rfs3, previous decision and current decision.
I have used like below:

| eval All_details=tostring("Week: ")+'WEEK'+tostring(", CODE: ")+'CODE''+tostring(", Current Week RFS3: ")+'RFS3'+tostring(", Next Week RFS3: ")+'prev_RFS3'+tostring(", Previous decision: ")+'prev_decision'+tostring(", (Current Week RFS3+Next Week RFS3-Previous decision)= Current decision: ")+'decision'

It is giving me all the details in a single line. But I want code in a line, week in a line and rfs3 in a line and soon.

3)
Also If I use chart by count command for the concatenated value, it is showing 15 records of data in the column chart and next records of data like other. How to show all the records in the chart.

Please help me to solve all my queries.

Thanks &Regards,,
Narmada.

0 Karma

mnarmada
Path Finder

Hello,

Sorry I misunderstood the requirement.
For the below data,
code WeeK RFS1 RFS2 RFS3 decision
1234 W1 5 5 5
1234 W2 5 5 6
1234 W3 1 2 2

For the First week i.e, W1, Decision should be Zero.
Decision = 0
I have a formula i.e., Decision(W2)=RFS3(W1)+RFS3(W2)-Decision( W1)
This formula should be applicable to all the next rows of my data.
That means, for my next week,
Decision(W3)=RFS3(W1+W2)+RFS3(W3)-Decision( W1+W2)
or
Decision(W3)=RFS3(sum of W1, W2 and W3)-Decision( W1+W2)
Decision(W4)=RFS3(W1+W2+W3+W4)-Decision(W1+W2+W3)
Decision(W13)=RFS3(W1+..........+W12+W13)-Decision(W1+...........+W12)
and same for other weeks.

I have checked for addcols and sum documentation but did not find the solution.

And,

I have to show all the data in a visualization of column chart like:
If I place cursor on a column, It should show how the "decision" value came. i.e., It should show code, RFS3 previous value, added value and previous decision and current decision.

I have used like this:
| eval All_details=tostring("Week: ")+'WEEK'+tostring(", CODE: ")+tostring(", Current Week RFS3: ")+'RFS3'+tostring(", Next Week RFS3: ")+'prev_RFS3'+tostring(", Previous DEC_RFS: ")+'prev_decision'+tostring(", (Current Week RFS3+Next Week RFS3-Previous DEC_RFS)= Current DEC_RFS: ")+'decision'

This is giving all the data by separating with comma "," in a single line , but I want all the data in new lines.
Like, first line should be Week then in the next line code etc.,

Please help me.

Thanks &Regards,
Narmada M

0 Karma

mnarmada
Path Finder

Hello,

Thanks for the response. It is working.

Regards,
Narmada

0 Karma

maciep
Champion

Good to hear...be sure to accept the answer if it solved your problem.

0 Karma

mnarmada
Path Finder

Hello,

Sorry I misunderstood the requirement.
I have 3 questions here.
1)
Code WeeK RFS1 RFS2 RFS3 decision
1234 W1 5 5 5
1234 W2 5 5 6
1234 W3 1 2 2

etc.,
For the First week i.e, W1, Decision should be Zero.
Decision = 0
I have a formula i.e., Decision(W2)=RFS3(W1)+RFS3(W2)-Decision( W1)
and
Decision(W3)=RFS3(W1+W2+W3)-Decision( W2+W1)
or
Decision(W3)=RFS3(sum of W1,W2,W3)-Decision( sum of W1, w2)
This should continues for all the weeks,
Like For 15th week,
Decision(W15)=RFS3(W1+W2+................+W14+W15)-Decision( W1+W2+.........+W14)

I have tried using addcols and sum commands but did not find the solution.

2)
After doing this, I have to create a column chart to show all the values on the column if I place cursor over that bar.
Like:
for week1, all the details like code, week, previous total of rfs3, current rfs3, previous decision and current decision.
I have used like below:

| eval All_details=tostring("Week: ")+'WEEK'+tostring(", CODE: ")+'CODE''+tostring(", Current Week RFS3: ")+'RFS3'+tostring(", Next Week RFS3: ")+'prev_RFS3'+tostring(", Previous decision: ")+'prev_decision'+tostring(", (Current Week RFS3+Next Week RFS3-Previous decision)= Current decision: ")+'decision'

It is giving me all the details in a single line. But I want code in a line, week in a line and rfs3 in a line and soon.

3)
Also If I use chart by count command for the concatenated value, it is showing 15 records of data in the column chart and next records of data like other. How to show all the records in the chart.

Please help me to solve all my queries.

Thanks ℜgards,,
Narmada.

0 Karma

maciep
Champion

Yes, i saw your other question too...still thinking about it. Please keep in mind that these are not simple questions and so it might take the community a while to get you answers, especially since we don't have access to the data like you do...and we have day jobs too.

That said, could you please work out maybe 10 iterations of your formula by hand on "paper" and share it here. Because your decision is defined by RFS3, it seemed to me that after the first couple iterations - decision just equals rfs3. So I'm probably misunderstanding the formula so an example output of 10 iterations might help me/us understand better - just a column for RFS3 and Decision should be enough.

I don't think you can accomplish number 2 w/o javascript, because that's just how splunk works...i could be wrong.

For number 3, if you have 15 unique values of your concatenated string, then you're going to get 15 results when counting by that field. Maybe just concentrate on #1 for now...

0 Karma

maciep
Champion

i think autoregress could work for you...maybe like this:

<your search>
| autoregress RFS3 as prev_RFS3 | autoregress decision as prev_decision
| eval decision = coalesce(prev_RFS3 + RFS3 - prev_decision,0)

View solution in original post

0 Karma

Sukisen1981
Champion

Hi - Please check this out, you need to use streamstats

https://answers.splunk.com/answers/150909/how-can-i-get-the-previous-value-of-the-field-that-im-comp...

|streamstats current=f window=1 last(Week) as prev_Week

prev_Week will be your previous values

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.