Splunk Search

Use the results of subsearch to rename those results with other name in new search

paritoshs24
Path Finder

Hi  All, 

I have this data in index 1 

inputactive Idle
adg
beh
cfi


I have this  data in index 2 

inputTESTpwr
ad1
be2
cf3
ag4
bh5
ci6

 

Now  i want to change these d , e, f   to active  and  g, h, i  to idle 

so my data in index looks like this

inputTESTpwr
aactive1
bactive2
cactive3
aidle4
bidle5
cidle6

 

and then i want to run my final search.
I tried sub searches and all, but  unable to do this.

I have given  small example  there are 100s of active and idle entries

 

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
<search index 1>
| table input active idle
| untable input state TEST
| append
    [search <index 2>
    | table input TEST pwr]
| stats values(state) as state values(pwr) as pwr by input TEST
| fields - TEST
| rename state as TEST

View solution in original post

gcusello
Legend

Hi @paritoshs24,

if the results of the first query is fixed or doesn't change frequently, you could save them in a lookup and use the lookup command to change the values of the second query, this is the best and easiest solution.

Otherwise if they are dynamic you could use the join command but I don't like it or group results using stats, something like this:

| makeresults
| eval input="a", active="d", idle="g"
| append [ 
   | makeresults
   | eval input="b", active="e", idle="h" ]
| append [ 
   | makeresults
   | eval input="c", active="f", idle="i" ]
| append [ 
   | makeresults
   | eval input="a", TEST="d", pwr="1" ]
| append [ 
   | makeresults
   | eval input="b", TEST="e", pwr="2" ]
| append [ 
   | makeresults
   | eval input="c", TEST="f", pwr="3" ]
| append [ 
   | makeresults
   | eval input="a", TEST="g", pwr="4" ]
| append [ 
   | makeresults
   | eval input="b", TEST="h", pwr="5" ]
| append [ 
   | makeresults
   | eval input="c", TEST="i", pwr="6" ]
| fields - _time
| eval first=TEST."|".pwr
| stats values(first) AS first values(active) AS active values(idle) As idle BY input
| mvexpand first
| rex field=first "^(?<TEST>[^\|]*)\|(?<pwr>.*)"
| eval TEST=if(active=TEST,"Active","idle")
| table input TEST pwr
| sort TEST

obviously the first rows are to populate my search, you have to consider after the fields command row.

In your case:

(index=index1) OR (index=index2)
| eval first=TEST."|".pwr
| stats values(first) AS first values(active) AS active values(idle) As idle BY input
| mvexpand first
| rex field=first "^(?<TEST>[^\|]*)\|(?<pwr>.*)"
| eval TEST=if(active=TEST,"Active","idle")
| table input TEST pwr
| sort TEST

Ciao.

Giuseppe

paritoshs24
Path Finder

Thanks  !! 

My queries/data base is not fixed.
I used selfjoin though as it made my life simpler thanks  for your explanation too.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
<search index 1>
| table input active idle
| untable input state TEST
| append
    [search <index 2>
    | table input TEST pwr]
| stats values(state) as state values(pwr) as pwr by input TEST
| fields - TEST
| rename state as TEST

paritoshs24
Path Finder

Thanks for  your answer...at the end i used selfjoin command ......Voila !  ITs done ! 🎉

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...