subquery not returning results

[surekhasplunk]

Hi,

I have two queries one from 1st_index and another from 2nd_index both are separately are giving correct outputs but when i combine them i get 0 results.

index="1st_index" 
| eval name=upper(name) 
| search name=ABCD  
|search  index="2nd_index" 
| fillnull value="Other" 
| mvexpand infrastructure{}.name 
| rename infra{}.name as "Infrastrucure Name" name as Nom infra{}.type as type 
| table "Infrastrucure Name" Nom type 
| mvexpand type 
| eval Nom=upper(Nom)

I want the name from 1st output to be the searched in the second subquery.
And at the end show few columns from 1st query and few from 2nd query.

[PavelP]

Hello @surekhasplunk,

please try infra_name instead of infra{}.name. By default splunk "cleans" all field names, all dots and brakets are replaced with "_".

[richgalloway · Accepted solution]

You don't have a subsearch in your query. The search command is processing the results from 1st_index. Since only events with index=1st_index have been fetched, a search for index=2nd_index will return nothing.

A subsearch must be enclosed in square brackets. Something like this:

index="1st_index" 
| eval name=upper(name) 
| search name=ABCD  
|[ search  index="2nd_index" 
  | fillnull value="Other" 
  | mvexpand infrastructure{}.name 
  | rename infra{}.name as "Infrastrucure Name" name as Nom infra{}.type as type ]
| table "Infrastrucure Name" Nom type 
| mvexpand type 
| eval Nom=upper(Nom)

Before combining two searches, run what will be the subsearch by itself with | format appended. See if the results make sense when appended to what comes before the subsearch.