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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...