Splunk Search

How to deal with missing data while trying a self join to compare each result with the immediate preceding result?

asherman
Path Finder

Hi,

I am trying to self join some data so that I can compare every result with the immediate preceding result. E.g.:

Data for days 1,2,3,4,5,6 - compare day 1 to day 2, 2 to 3, 3 to 4, etc.

I can do this simply by using:

| rename field1 as oldfield | eval field1=old_field+1, and then self-joining along "field1"

However, how can I do this if I am missing data and want to still provide consistent results? E.g.:

Data for 1,2,3,5,6 - compare 1 to 2, 2 to 3, 3 to 5, etc.

With the method I provide above, I will lose the comparison of 3 to 5 since there is no '4' value to join with.

Is there some function that can map my data to the largest value that is smaller than it?

  • I.e., f(n)=The largest m such that m<n and both m and n are contained in the same field.

Alternative approaches are, of course, welcome.

My query, for reference:

index=official voltage=900 temp=100
| join [search index=official voltage=900 temp=100
         | rename build as last_build 
             | eval build=last_build+1 
         | rename val as old_val 
         | fields name, path, build, old_val]
| eval val_trend=val/old_val | chart avg(val_trend) as "Trend" over build by block

Data sample:

build=1,name=name1,block=block1,path=A1,voltage=900,temp=100,val=32.33
build=2,name=name1,block=block1,path=A1,voltage=900,temp=100,val=32.53

Thanks!

0 Karma
1 Solution

ramdaspr
Contributor
.. | sort build | delta val as Difference

the delta command can provide the difference between result rows as a new field.
The above code will provide you a difference between build 2 and 1 as a new row called Difference and you can eval it to create a percentage or whatever you like.

View solution in original post

ramdaspr
Contributor
.. | sort build | delta val as Difference

the delta command can provide the difference between result rows as a new field.
The above code will provide you a difference between build 2 and 1 as a new row called Difference and you can eval it to create a percentage or whatever you like.

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...