How can I append different fields from a subsearch?

[russell120]

Hello,

I'm trying to search within another sourcetype and append fields oxygen, and rock to a CSV base search. I'm attempting to match them by id with this search:

|inputlookup environmentSample.csv |append [search sourcetype="environmentData" |fields oxygen rock id]
|table nitrogen bacteria id oxygen rock

environmentSample.csv:

nitrogen     bacteria    id
   5      c. perfringens 111
   7      B. cereus      222

sourcetype=environmentData:

oxygen    rock    id
  3      pyrite   111
 2.6    basalt    222

What query do I need to see this result -- where the results appear in the SAME ROW based on id?:

nitrogen     bacteria    id    oxygen    rock  
   5      c. perfringens 111     3      pyrite   
   7      B. cereus      222    2.6     basalt   

[cvssravan]

Try this:

|inputlookup environmentSample.csv |join id [search sourcetype="environmentData" |fields oxygen rock id]

[russell120]

I prefer to stay away from |join since it's an expensive command. In my real query so I have 3 subsearches; using |join would require me to use 3 |join commands, which would take a long time.

[cvssravan]

If you want the output in your requested format, I think you should use join to link the data. Will watch for any alternative answers.

[vnravikumar]

Hi @russell120

Try

your query...| selfjoin  id

[vnravikumar]

Try with

|inputlookup environmentSample.csv |append [search sourcetype="environmentData" |fields oxygen rock id] |selfjoin id |table nitrogen bacteria id oxygen rock

[russell120]

This didn't work unfortunately. I think it may be because id in my sourcetype data has the id number as a multivalue for some reason, where it looks like:

id
111
111

222
222

[kamlesh_vaghela]

@russell120
Can you please try this?

|inputlookup environmentSample.csv |append [search sourcetype="environmentData" |fields oxygen rock id] |stats  values(nitrogen) as nitrogen values(bacteria) as bacteria values(oxygen) as oxygen values(rock) as rock by id 

[russell120]

Would by id work if the CSV had

id
111
222

and the sourcetype=environmentData had the below as multivalues?

id
111
111

222
222