topic Two Queries That Return Results Do not Return Results After Join in Splunk Search

Two Queries That Return Results Do not Return Results After Join

ryanmcdermott12 — Wed, 30 Sep 2020 01:33:48 GMT

I have written two individual queries that both return the expected results.
A.
tag=tag name
location="location name"
message="error message"
status=400
| rex field=raw "string from log (?\w+[@]\S+)@string from log"
| rex field=_raw "(?\w+)]"

B.
tag=tag name
"string from log"
"\"status\":400"
"string from log"
| rex field=field name "_(?\w+)"

However, when I join the two queries, no results are returned.
C.
tag=tag name
location="location name"
message="error message"
status=400
| rex field=raw "string from log (?\w+[@]\S+)@string from log"
| rex field=_raw "(?\w+)]"
| join type=inner login_dye
[search
tag=tag name
"string from log"
"\"status\":400"
"string from log"
| rex field=field name "_(?\w+)"
| eval Date=strftime(_time, "%m-%d-%y")]
| eval user_email="'".user_email."',"
| dedup Date, user_email
| table Date, user_email

The main search returns a large number of results. However, the sub-search returns a relatively small number of results, so the sub-search should not be timing out. I also get no messages indicating such, and no other errors or messages.

Re: Two Queries That Return Results Do not Return Results After Join

Sukisen1981 — Wed, 31 Jul 2019 17:29:54 GMT

you are performing an inner type join, which means you are looking only for common results in both the searches.
if you remove your join field login_dye are you receiving results?

Re: Two Queries That Return Results Do not Return Results After Join

gcusello — Wed, 30 Sep 2020 01:33:59 GMT

Hi ryanmcdermott1208,
at first check if there are common values in both the searches fir the field login_die.
then try to tranform in uppercase (o lowercase) both the login_die fields and retry.
at least check if the number of results of the subsearch is greater than 50,000, because there's this limit in subsearches.

Anyway, join isn't a performant command, try something different: use stats count.
I could help you but you should copy your searches using the Code Sample button (the one neat the attach button), because otherwise I cannot correctly read your question.

Bye.
Giuseppe

Re: Two Queries That Return Results Do not Return Results After Join

ryanmcdermott12 — Thu, 01 Aug 2019 15:00:15 GMT

thank you sukisen, but i think removing the join would not return the results i need. i only want the subset of the main query that includes the results from the sub query.

tag=tag name
location="location name"
message="error message"
status=400
| rex field=raw "string from log (?\w+[@]\S+)@string from log"
| rex field=raw "(?\w+)]"
| join type=inner login_dye
[search
tag=tag name 
"string from log"
"\"status\":400"
"string from log"
| rex field=field name "(?\w+)"
| eval Date=strftime(_time, "%m-%d-%y")]
| eval user_email="'".user_email."',"
| dedup Date, user_email
| table Date, user_email

Re: Two Queries That Return Results Do not Return Results After Join

ryanmcdermott12 — Wed, 30 Sep 2020 01:34:26 GMT

hank you giuseppe. the main query returns over 50k results, but the sub-query does not. i should have included that the values being extracted for "login_dye" are strictly numerical. the value being extracted from the main query matches the value being extracted from the sub-query.

here is the query in code sample:
tag=tagname
location="location name"
message="error message"
status=400
| rex field=raw "string from log (?\w+[@]\S+)@string from log"
| rex field=raw "(?\w+)]"
| join type=inner login_dye
[search
tag=tag name
"string from log"
"\"status\":400"
"string from log"
| rex field=field name "(?\w+)"
| eval Date=strftime(_time, "%m-%d-%y")]
| eval user_email="'".user_email."',"
| dedup Date, user_email
| table Date, user_email