Splunk Search
Highlighted

How can I create a new_column based on new_column(row-1 previous row) and another_column condition?

Engager

Hi,

I'm fairly new to Splunk and lately I've tried to create a new_column trying to do the following condition:

if( column2 == 0 AND ( column3 == 1 OR new_column[row-1] == 1), 1, 0)

Basically - the tricky part for me so far - is a condition that checks a value on another column but also checks on the same column but one row above.

On the table below the condition above would be displayed like that:

column2 | column3 | new_column
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 1 | 1
0 | 0 | 1
0 | 0 | 1
1 | 0 | 0
1 | 0 | 0
0 | 0 | 0

I already have column2 and column3, as mentioned above, i'm particularly struggling on new_column creation based on the condition stated.

Any advice is appreciated

Thanks,
Theo

Highlighted

Re: How can I create a new_column based on new_column(row-1 previous row) and another_column condition?

SplunkTrust
SplunkTrust

Hi @theocarvalho,

Get the previous row value to current row and compare , for e.g.

your search | fields column_2 , column_3|streamstats current=f last(column_3) as prev
|eval new_column=if( column_2 == 0 AND ( column_3 == 1 OR prev== 1), 1, 0)

The first row's prev value will be null as expected. You can fillnull with some value based on your requirement.

0 Karma
Highlighted

Re: How can I create a new_column based on new_column(row-1 previous row) and another_column condition?

Legend

@renjith.nair, I think you are missing window=1 in the streamstats command.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: How can I create a new_column based on new_column(row-1 previous row) and another_column condition?

Engager

Hi @renjith.nair,

Thank you for your suggestion. However, I've just tried what you posted and only worked partially.

I indeed created a newcolumn but it referred to 'last column3' instead of 'last new_column'.

The consequently output was:

column2 | column3 | new_column
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 0 | 0
0 | 1 | 1
0 | 0 | 1
0 | 0 | 0 instead of 1
1 | 0 | 0
1 | 0 | 0
0 | 0 | 0

In this output, the newcolumn keeps not depending on newcolumn[row-1] condition.

As mentioned before any advice is appreciated.

btw, thanks for the fillnul tip.

Theo

0 Karma