Why is my search using join returning zero results?

[stephenmoorhous]

hi i am trying to do something like

index=uk search [subsearch] | fields a b | join a [index=uk search | table a b c]

If i take everything before the join - so

index=uk search [subsearch] | fields a b

i get a list of results for a and b

if I take one value for field a and include it in the second part i get the results for a and c

index=uk inserted_value search | table a b c

but with the join i get no results - any ideas?
The field names for a are the same in both searches

thanks

[cmerriman]

if this is all in the same index, can you just avoid the join all together? It'd really be a lot easier if we knew more of the syntax, though.

index=uk (search [subsearch] ) OR (search originally in join)| stats values(b) as b values(c) as c by a

[stephenmoorhous · Accepted solution]

Thanks I have fixed it - and i should have given you the full search 🙂 in trying to simplify the question I simplified it too far.. as it was not the join logic that was at fault

The original full search was

index=tmol_uk sourcetype=apt.event [search index=tmol_uk "POST /metrics HTTP/1.1" referer="*/member/edit_profile" | fields sid] "email succesfully submitted to NTF" | fields sid email_address | join sid [search index=tmol_uk sourcetype=apt.event | rex field=_raw "pid.*?\t(?[0-9]*)" | fields mid sid email_address | table mid sid email_address]

The fixed version is

index=tmol_uk sourcetype=apt.event [search  sourcetype=pxy.access index=tmol_uk "POST /metrics HTTP/1.1" referer="*/member/edit_profile" | fields sid] "email succesfully submitted to NTF" | fields sid email_address | join sid [search index=tmol_uk sourcetype=apt.event | rex field=_raw "pid.*?\t(?[0-9]*)"] | table mid email_address sid

So basically it didn't like the fields followed by table, and I needed to close the join search before the table, to get the table to display

[DalJeanis]

Especially when you use a regex in your code, you really need to mark the code as such so the web engine will not delete your terms that are in angle brackets. I made a guess at the missing regex code and formatted it like this

index=tmol_uk sourcetype=apt.event 
   [search sourcetype=pxy.access index=tmol_uk "POST /metrics HTTP/1.1" referer="/member/edit_profile" 
    | fields sid] 
"email succesfully submitted to NTF" 
| fields sid email_address 
| join sid 
    [search index=tmol_uk sourcetype=apt.event 
    | rex field=_raw "pid.?\t(?<mid>[0-9]*)"]
| table mid email_address sid

so what your final search does is this -

a) search in sourcetype=pxy.access with referer="/member/edit_profile" for the words "POST /metrics HTTP/1.1"
from all those records, return only the sid and _time fields to the second search

( I prefer to return the values using "| table _time sid" rather than "| fields sid", so that I can be absolutely sure that no other returned values will affect the search. )

b) search in sourcetype=apt.event for the words "email succesfully submitted to NTF" on a record that has the sid and _time values from the first search.

c) pass _time, sid and email_address along

d) join that to the "mid" value pulled from the first record on sourcetype=apt.event that has the same sid

e) drop all other unneeded fields from the join

(I'd suggest that you put the commands "|table sid mid |dedup sid" at the end of the second subsearch, inside the brackets. Since you won't be using anything but the mid value that gets passed back, you want to tell splunk not to keep the other stuff.)

[DalJeanis]

It's okay to accept your own answer, so that people can tell which fix worked.

It looks like the issue was that there was no email field in existence on the subsearch, so using "fields" meant that none of the records passed that point.

Even if the records had passed, the blank or null email address would have overwritten the one calculated by the other search, giving you a different headache.

[richgalloway]

If the problem is solved, please accept an answer to help future readers.

[gvmorley]

As mentioned by the previous poster, the problem is a bit abstract without your specific data. But if you wanted to build yourself a simplified test, you could use the example below...

If you went with this for the first set of results:

| makeresults count=1 
| eval a="bob" 
| eval b="fred" 
| append 
    [| makeresults count=1 
    | eval a="jane" 
    | eval b="clare"] 

Which returns:

           _time             a     b
--------------------------- ---- -----
2017-02-02 12:46:31.000 ICT bob  fred
2017-02-02 12:46:31.000 ICT jane clare

And this for the second set of results:

| makeresults count=1 
| eval a="bob" 
| eval b="ellen" 
| eval c="anna"

Which returns:

           _time             a    b    c
--------------------------- --- ----- ----
2017-02-02 12:48:08.000 ICT bob ellen anna

You can then test the results of the join with:

| makeresults count=1 
| eval a="bob" 
| eval b="fred" 
| append 
    [| makeresults count=1 
    | eval a="jane" 
    | eval b="clare"] 
| join type=inner a 
    [| makeresults count=1 
    | eval a="bob" 
    | eval b="ellen"
    | eval c="anna"]

Which results in:

           _time             a    b    c
--------------------------- --- ----- ----
2017-02-02 12:50:25.000 ICT bob ellen anna

The field a & b are retained from the results of the first search and field c is join, as field a is a match.

If you change this to an outer join, you'll also retain the results which don't have a match. So you will get:

           _time             a     b    c
--------------------------- ---- ----- ----
2017-02-02 12:51:39.000 ICT bob  ellen anna
2017-02-02 12:51:39.000 ICT jane clare 

So for the join to work, the 'value' of the field named 'a' needs to match.

If you use this code as a simplified example, you can insert your own values for the fields to make sure everything works at a basic level first.

Or just post up some sample data, so that folks can in a bit more detail what the problem might be.

[DalJeanis]

Assuming that the value of a in the first search was "foo", use this syntax for your second search to verify the presence of the value of a record where a="foo" --

index=uk search a="foo" 
| table a b c

If that gets results, then check capitalization on the field names.

If it still all seems to check out, then try this -

index=uk search [subsearch] 
| fields a b 
| join type=left a 
    [index=uk search | table a b c 
    | rename b as Not_B
    ]

That will simultaneously show that you haven't lost anything from the left part of the join. and also make sure that the join itself is not trying to match by b (in addition to a).

[lguinn2]

This is lovely and abstract, so it may be obscuring the problem. Please show more of the real search.

Or, if this IS the real search - you don't need to be using join! And maybe you can still avoid the join and/or the subsearch, making things clearer and more efficient. Again, the community needs to see your search to offer any useful advice.