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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...