How to keep the same value of a field in each row until the value of the field changes?

[ludoz13]

Hi all,

I'd like to keep value on a field until the value of this field changes. Please see the following example:

Explanation: I have:

• 9/25/14 2:05:55.000, PM field1=abc, field2=abc2, field3=xyz3
• 9/25/14 2:05:54.000, PM field1=abc, field2=def2
• 9/25/14 2:05:53.000, PM field1=abc, field2=ghi2
• 9/25/14 2:05:52.000, PM field1=jkl, field2=mno2, field3=vw3
• 9/25/14 2:05:51.000, PM field1=jkl, field2=pqr2
• 9/25/14 2:05:50.000, PM field1=jkl, field2=stu2
• 9/25/14 2:05:49.000, PM field1=test, field2=tst2, field3=tre3
• 9/25/14 2:05:48.000, PM field1=test, field2=psq2
• 9/25/14 2:05:47.000, PM field1=test, field2=aaz2

I would like to do

• 9/25/14 2:05:55.000, PM field1=abc, field2=abc2, field3=xyz3
• 9/25/14 2:05:54.000, PM field1=abc, field2=def2, field3=xyz3
• 9/25/14 2:05:53.000, PM field1=abc, field2=ghi2, field3=xyz3
• 9/25/14 2:05:52.000, PM field1=jkl, field2=mno2, field3=vw3
• 9/25/14 2:05:51.000, PM field1=jkl, field2=pqr2, field3=vw3
• 9/25/14 2:05:50.000, PM field1=jkl, field2=stu2, field3=vw3
• 9/25/14 2:05:49.000, PM field1=test, field2=tst2, field3=tre3
• 9/25/14 2:05:48.000, PM field1=test, field2=psq2, field3=tre3
• 9/25/14 2:05:47.000, PM field1=test, field2=aaz2, field3=tre3

Would anyone have any idea?

Thanks a lot for your help,

Regards,

Ludovic

[somesoni2]

Try this

your base search with _time field1, field2, field3 | eventstats first(field3) as field3 by field1

[ludoz13]

Hello somesoni2,

Thank you for your help but it is more complicated because that can be happen that another field3 appear with the same field1 value, for example :

• 9/25/14 2:05:57.000, PM field1=abc, field2=ghi2
• 9/25/14 2:05:56.000, PM field1=abc, field2=def2
• 9/25/14 2:05:55.000, PM field1=abc, field2=abc2, field3=xyz3
• 9/25/14 2:05:48.000, PM field1=abc, field2=ghi2
• 9/25/14 2:05:47.000, PM field1=abc, field2=def2
• 9/25/14 2:05:46.000, PM field1=abc, field2=abc2, field3=pzo3

And with your search, I have this :

• 9/25/14 2:05:57.000, PM field1=abc, field2=ghi2, field3=pzo3
• 9/25/14 2:05:56.000, PM field1=abc, field2=def2, field3=pzo3
• 9/25/14 2:05:55.000, PM field1=abc, field2=abc2, field3=pzo3
• 9/25/14 2:05:48.000, PM field1=abc, field2=ghi2, field3=pzo3
• 9/25/14 2:05:47.000, PM field1=abc, field2=def2, field3=pzo3
• 9/25/14 2:05:46.000, PM field1=abc, field2=abc2, field3=pzo3

And I would like to have this :

• 9/25/14 2:05:57.000, PM field1=abc, field2=ghi2, field3=xyz3
• 9/25/14 2:05:56.000, PM field1=abc, field2=def2, field3=xyz3
• 9/25/14 2:05:55.000, PM field1=abc, field2=abc2, field3=xyz3
• 9/25/14 2:05:48.000, PM field1=abc, field2=ghi2, field3=pzo3
• 9/25/14 2:05:47.000, PM field1=abc, field2=def2, field3=pzo3
• 9/25/14 2:05:46.000, PM field1=abc, field2=abc2, field3=pzo3

Any idea ?

Thanks for your help,

Regards,

[tdiestel]

Running into the same issue. Did you find any proper solution?

Appreciate any help as this would make my life 1,000 times easier.

Thanks,
Tyler

[srioux]

If possible, I'd recommend updating the original code or system to just record that info. That said, it's not always possible, so you could go with something like:

base searchy... | streamstats current=f last(field3) AS newfield | eval field3=if(isnull(field3),newfield,field3) | table _time field1 field2 field3

The streamstats command will carry forward the value; the eval basically checks to see if it already existed, and if so, retain the new value. Bit of a roundabout way to do it, there might be a better way.