topic Splunk Left join not working in Splunk Dev

Splunk Left join not working

angadbagga — Mon, 03 Aug 2020 23:06:52 GMT

I have a query like below. Can i use something else than join

index=hello sourcetype="logs A" source="C:\\football\ab*" OR source="C:\\Tennis\cd*" OR source="C:\\Cricket\eb*"
| rex (something)
|eval (something)
| join type=left
[search sourcetype = "logs B" source="C:\\football\ab*" OR source="C:\\Tennis\cd*" OR source="C:\\Cricket\eb*"
|rex (something)
|eval (somthing)]
table

Re: Splunk Left join not working

angadbagga — Mon, 03 Aug 2020 23:16:34 GMT

I want different _time values for both the source types "logsA" and "logsB" in the tabe

Re: Splunk Left join not working

diogofgm — Tue, 04 Aug 2020 00:23:35 GMT

Hi angadbagga

Can you share more details? What's the format/fields of you data? What's the expected output? Have you tried to state field you want to join?

Re: Splunk Left join not working

angadbagga — Tue, 04 Aug 2020 00:49:35 GMT

Yes, i tried to state the field i wanted to join. I am writing this query with dummy fields. In short there are two source types and i want CompletionTime_Dep and CompletionTime_CD in the final table

index=abc sourcetype="xyz" "efp" source="hello1" OR source="hello2*" OR source="hello3*"
| rex field=source (?<Tweet>"\b\w{2}\b")
| eval Steel = case(Tweet == "EV", "Steel1", Tweet == "SV", "Steel2")
| table Tweet Steel _time
| rename _time as CompletionTime_Dep
| convert ctime(CompletionTime_Dep)
| join Steel type=left
[ search sourcetype="ufh" "stn" source="hello1" OR source="hello2*" OR source="hello3*"
rex field=source (?<Tweet>"\b\w{2}\b")
| eval Steel = case(Tweet == "EV", "Steel1", Tweet == "SV", "Steel2")
| table Tweet Steel _time
| rename _time as CompletionTime_CD
| convert ctime(CompletionTime_CD)]

Re: Splunk Left join not working

bowesmana — Tue, 04 Aug 2020 03:05:04 GMT

If both independent searches work and there is a common 'Steel' to join on, then the search looks ok. What is the size of the data set and what results (if any) do you get.

However, to answer your question, yes you can always use something other than join (and probably should)

index=abc ((sourcetype="xyz" "efp") OR (sourcetype="ufh" "stn")) source="hello1" OR source="hello2*" OR source="hello3*" | rex field=source (?<Tweet>"\b\w{2}\b") | eval Steel = case(Tweet == "EV", "Steel1", Tweet == "SV", "Steel2") | table Tweet Steel _time | eval CompletionTime_Dep=if(sourcetype="xyz", _time, null()) | eval CompletionTime_CD=if(sourcetype="ufh", _time, null()) | convert ctime(CompletionTime_Dep) | convert ctime(CompletionTime_CD) | stats values(CompletionTime_*) as CompletionTime_* by Tweet Steel

However, in your example, will you have many rows where Steel has the same value (Steel1/Steel2)?

If so, then is the original intention of the join to bring all sub matches from the subsearch to each of the outer rows. If so, then the above 'stats' may not be what you intended.

Re: Splunk Left join not working

aa70627 — Sun, 23 Aug 2020 00:09:08 GMT

I would recommend going with @bowesmana solution instead of a left join. Left joins in splunk is more problems than its worth.

Re: Splunk Left join not working

angadbagga — Sun, 23 Aug 2020 03:39:32 GMT

Thanks!!