Splunk Search

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

richnavis
Contributor

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?

Tags (1)
0 Karma
1 Solution

Shan
Builder

@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

View solution in original post

j_cabanillas
Explorer

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 ?

0 Karma

richnavis
Contributor

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

0 Karma

richnavis
Contributor

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

0 Karma

j_cabanillas
Explorer

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

0 Karma

Shan
Builder

@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

richnavis
Contributor

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

0 Karma

richnavis
Contributor

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...