topic Re: Creating a join when first search contains multiple values for a single field in Splunk Search

Creating a join when first search contains multiple values for a single field

richnavis — Fri, 20 Jul 2018 01:25:53 GMT

Hey all, this one has be stumped. I'm trying to join two searches where the first search includes a single field with multiple values. The matching field in the second search ONLY ever contains a single value. The search ONLY returns matches on the join when there are identical values for search 1 and search 2. In other words if search 1 has a field named id, and contains id=a and id=b and the second search contains id=b, no results will be returned. The search will ONLY return results if search 1 contains a single value for id.
Does anyone have any suggestion on how to join a search with multiple values?

Re: Creating a join when first search contains multiple values for a single field

Shan — Fri, 20 Jul 2018 06:49:10 GMT

@richnavis - If you have multiple values in outer query and single value in inner query also the join condition will work . please take a look into below sample code. kindly replace with a test index in inner query and test it ..

| makeresults 
| eval mytrimexaxis =mvappend("1531981800","1531982400","1531982700","1531983000","1531983600") 
| mvexpand mytrimexaxis 
| table mytrimexaxis
| join type=inner mytrimexaxis  [ index=*** (replace with ur tetsing index)
| eval mytrimexaxis="1531981800"
| table mytrimexaxis
] 
 | table mytrimexaxis

Re: Creating a join when first search contains multiple values for a single field

j_cabanillas — Fri, 20 Jul 2018 06:57:27 GMT

PLease share your query, that way is easier to understand why is not working,

the logic of the join command should be that for each value on search 1 for the specific field you should have results in search 2 with additional information (other fields) .

What do you want to get from search 2 to include on search 1 ?

Re: Creating a join when first search contains multiple values for a single field

richnavis — Fri, 20 Jul 2018 16:33:25 GMT

Here's a simplified version of my search. Note, that this doesn't return results the way I would like either. It only returns values where the number of id values are equal, and the id values match
index=myindex "instances{}.id"="" | rename "instances{}.id as id
| join inner id [search index=myindex2 earliest=-1d id=]

The reason for the join is that index2 contains a "name" field that I want to include in my report. both contain ID fields, although I have to rename the one from index1 since it has a different name

Re: Creating a join when first search contains multiple values for a single field

richnavis — Fri, 20 Jul 2018 16:34:42 GMT

Note, there's an asterisk at the end of the equal size in both searches... the html isn't showing it.

Re: Creating a join when first search contains multiple values for a single field

j_cabanillas — Fri, 20 Jul 2018 17:05:42 GMT

I think I know why it's not giving you what you need.
In Search 2 you should include a table or stats of your results .

something like [search index=myindex2 earliest=-1d id=*|stats values(name) as name by id]

This will give you results that search 1 can use

you query should be something like this index=myindex "instances{}.id"="" | rename "instances{}.id as id  | join inner id [search index=myindex2 earliest=-1d id=*|stats values(name) as name by id] | table id name and other fields you want to include

Re: Creating a join when first search contains multiple values for a single field

richnavis — Fri, 20 Jul 2018 21:46:17 GMT

Brilliant! The key to making it work was to add the mvexpand into my first search. I did not realize that this command existed, but once I added that into my first search, the first and second search joined just like I wanted them to. Thanks so much for the help

Re: Creating a join when first search contains multiple values for a single field

richnavis — Fri, 20 Jul 2018 21:50:12 GMT

shankarananth, if you could convert your comment to an answer, I will accept that as the answer