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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...