topic Re: How do I complete a search including common and unique data fields from two different sources? in Getting Data In

How do I complete a search including common and unique data fields from two different sources?

jredsama — Wed, 09 Aug 2017 20:19:54 GMT

Hello,

I would like to run a query that includes results from our main index as well as an uploaded CSV. I don't think I want to join, as it seems to leave out the unique data fields/values.

Here's a made up example of what I mean:

Main index contains -
ID
time
amount
result

CSV contains -
ID
time
amount
rating

So what I'd like is to search the common fields 'ID', 'time' and 'amount' and receive all transactions (from both sources) that contain values for those fields AS WELL AS receive the unique fields 'result', 'rating' from each data source in a single search result.

Further simplified, I want to search ID= time= amount= and in my results see data from both sources, along with the fields 'result' and 'rating' where applicable.

Thanks in advance!

Re: How do I complete a search including common and unique data fields from two different sources?

jkat54 — Wed, 09 Aug 2017 21:18:47 GMT

You can do an outer join

 ...| join type=outer

Or you can load both sets of data in the pipe (best method)

 (index=mainIndex) OR (index=csvIndex)  ID=xyz time=xyz amount=xyz| ...

Re: How do I complete a search including common and unique data fields from two different sources?

DalJeanis — Wed, 09 Aug 2017 22:24:38 GMT

At its most trivial, you want something like this

 ( search for event type 1) OR (search for event type 2) 
| fields _time time ID amount result rating
| stats values(*) as * by ID

For more complete description of what and why, look at martin_mueller's and my answers here -
https://answers.splunk.com/answers/561130/sql-analogy-for-my-log-search-join-two-tables-wher.html#answer-561172