topic Re: How to keep the same value of a field in each row until the value of the field changes? in Splunk Search

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

ludoz13 — Thu, 25 Sep 2014 16:02:30 GMT

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

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

srioux — Thu, 25 Sep 2014 17:36:33 GMT

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.

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

somesoni2 — Thu, 25 Sep 2014 21:36:31 GMT

Try this

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

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

ludoz13 — Fri, 26 Sep 2014 09:16:28 GMT

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,

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

tdiestel — Wed, 11 Mar 2015 23:06:06 GMT

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