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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...