I'm trying to search within another sourcetype and append fields
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
nitrogen bacteria id 5 c. perfringens 111 7 B. cereus 222
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
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.
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