Splunk Search

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

ludoz13
Path Finder

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

Tags (1)
0 Karma

somesoni2
Revered Legend

Try this

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

ludoz13
Path Finder

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,

0 Karma

tdiestel
Path Finder

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

0 Karma

srioux
Communicator

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!