Splunk Dev

Splunk Left join not working

angadbagga
Explorer

HI

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 

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

angadbagga
Explorer

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

0 Karma

diogofgm
SplunkTrust
SplunkTrust

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?

 

------------
Hope I was able to help you. If so, some karma would be appreciated.
0 Karma

angadbagga
Explorer

Hi

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)]

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

angadbagga
Explorer

Thanks!!

0 Karma

aa70627
Communicator

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

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...