Splunk Search

How to update column values if results found in another column?

akrishnam
Engager

There are two columns with headings "new image Name" and "source image Name".  The new images are derived from source images. Also, occasionally, images are created from existing images as well. Please see sample data below. 

 

 

 

new image Name | source image Name
----------------------------------
image1	       | baseline
image2	       | baseline
image3	       | image1
image4	       | baseline
image5 	       | image3
image6 	       | imageX

 

 

 

  
Observations:

  1. From above table, we see that "image3" is derived from "image1" which is derived from "baseline". 
  2. For "image5" in col1, it is derived from "image3", which in turn is derived from "image1". 
  3. For "image6" in col1, it is derived from "imageX" which can be an "Unknown" source

Requirements:

  • Wherever we see that it is not "baseline" in col2, we need to check if corresponding value exists in col1 and populate its col2 value.
    • If we get "baseline" as output, then we need to mark it "baseline"
    • If we get another image name as the col2 value, then we need to mark it "Unknown"
    • If we get an imageX as the col2 value, then we need to mark it "Unknown"

Final result:

 

 

 

new image Name | source image Name
----------------------------------
image1	       | baseline
image2	       | baseline
image3	       | baseline
image4	       | baseline
image5 	       | Unknown
image6 	       | Unknown

 

 

 

 
Please help.

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

This does what you say you want:

| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eval sourceImageName=if(sourceImageName!="baseline","unknown","baseline")

However, is it really what you want as image5 is derived from image 3, which is derived from baseline. If you want to know which images are ultimately derived from baseline, you need to repeat the first two lines (for as many depths as you need).

| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eval sourceImageName=if(sourceImageName!="baseline","unknown","baseline")

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

This is a non-trivial task. Splunk processes events in a pipeline. In order to do what you want, the pipeline has to be processed multiple times. If you know what the maximum depth of references is, you could set up a series of essentially the same commands finding the next reference back, until you find a baseline, or assume that it is unknown.

Do you know the maximum depth of references?

0 Karma

akrishnam
Engager

@ITWhisperer  Thanks for the quick response. Depth reference of 3 is good for us. It would be great if you can suggest a query that can help us. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This does what you say you want:

| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eval sourceImageName=if(sourceImageName!="baseline","unknown","baseline")

However, is it really what you want as image5 is derived from image 3, which is derived from baseline. If you want to know which images are ultimately derived from baseline, you need to repeat the first two lines (for as many depths as you need).

| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eventstats values(eval(if(sourceImageName="baseline",newImageName,null()))) as derived
| eval sourceImageName=if(in(sourceImageName, derived),"baseline",sourceImageName)
| eval sourceImageName=if(sourceImageName!="baseline","unknown","baseline")
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 ...