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

[paritoshs24]

Hi  All, 

I have this data in index 1 

input	active	Idle
a	d	g
b	e	h
c	f	i

I have this  data in index 2 

input	TEST	pwr
a	d	1
b	e	2
c	f	3
a	g	4
b	h	5
c	i	6

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

input	TEST	pwr
a	active	1
b	active	2
c	active	3
a	idle	4
b	idle	5
c	idle	6

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

[gcusello]

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]

Thanks  !! 

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

[ITWhisperer · Accepted solution]

<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]

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