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

[theocarvalho]

Hi,

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

if( column_2 == 0 AND ( column_3 == 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:

column_2 | column_3 | 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 column_2 and column_3, as mentioned above, i'm particularly struggling on new_column creation based on the condition stated.

Any advice is appreciated

Thanks,
Theo

[renjith_nair]

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.

[theocarvalho]

Hi @renjith.nair,

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

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

The consequently output was:

column_2 | column_3 | 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 new_column keeps not depending on new_column[row-1] condition.

As mentioned before any advice is appreciated.

btw, thanks for the fillnul tip.

Theo

[niketn]

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