topic Re: How to remove limitations of join command in Splunk Search

How to remove limitations of join command

mahbs — Wed, 17 Jan 2018 10:04:21 GMT

Hi,

I'm using the join command to join to searches based on a common field called ITEM. Based on this join, I want to return results from both searches only in instances where ITEM values match. However, I'm not sure it's working correctly. Could you please have a look at my query and let me know where I'm going wrong and what I could do to avoid using a join command:

index=index sourcetype=csv source=src1 host=host1| stats list(field1) as F_1 list(field2) as F_2 list(field3) as F_3 BY ITEM| eval source1=mvzip(F_1,mvzip(F_2,F_3)) | mvexpand source1 | rex field=source1 "(?<F_1>\d+),(?<F_2>\d+),(?<F_3>\d+)" | join ITEM [search index=index sourcetype=csv source=src2 host=host2| stats list(c_1) as C_1 list(c_2) as C_2 list(c_3) as C_3 BY SKU | eval source2=mvzip(C_1 ,mvzip(C_2,C_3)) | mvexpand source2| rex field=source2 "(?<C_1 >\d+),(?<C_2 >\d+),(?<C_3>\d+)" |rename SKU as ITEM] | eval DIFF1=F1-C_1 | eval DIFF2=F_2-C_2 | sort limit=0 ITEM |table ITEM, F_1, F_2, F_3, C_1, C_2, C_3, DIFF1, DIFF2

Can someone please check my query as I think there may be a mistake in there somewhere when attempting to create new records for instances where there are multiple values in a single field.

Thanks!

Re: How to remove limitations of join command

mayurr98 — Tue, 29 Sep 2020 17:44:21 GMT

I do not understand your query. from the query i think you are extracting numbers i.e. (?<F_1>\d+),(?<F_2>\d+),(?<F_3>\d+) and from src2 you are extracting (?<C_1 >\w+),(?<C_2 >\w+),(?<C_3>\w+) words and then you are substracting integer-string?
Also in addition to that F_1 F_2 F_3 ITEM are in one event for src1 and same for src2?

Re: How to remove limitations of join command

mahbs — Wed, 17 Jan 2018 10:34:29 GMT

Hi mayurr98, apologies, it shouldn't be w+, it should be d+. As for your second question, yes thats correct, but ITEM in src2 is called SKU, which I am renaming as ITEM so I can do the join

Re: How to remove limitations of join command

mahbs — Wed, 17 Jan 2018 10:51:50 GMT

@mayurr98, do you have any idea what I might be doing wrong here?

Re: How to remove limitations of join command

mayurr98 — Wed, 17 Jan 2018 11:20:41 GMT

hey try this

index=index sourcetype=csv source=src1 host=host1 
| stats count by field1 field2 field3 field3 ITEM 
| fields - count 
| rename field1 as F_1 field2 as F_2 field3 as F_3 
| join ITEM 
    [ search index=index sourcetype=csv source=src2 host=host2 
    | stats count by c_1 c_2 c_3 SKU 
    | fields - count 
    | rename SKU as ITEM] 
| eval DIFF1=F1-c_1 
| eval DIFF2=F_2-c_2 
| sort limit=0 ITEM 
| table ITEM, F_1, F_2, F_3, c_1, c_2, c_3, DIFF1, DIFF2

let me know if this helps !

Re: How to remove limitations of join command

mahbs — Wed, 17 Jan 2018 11:26:16 GMT

Quick question, why did you add ITEM at the end? Because field1 is basically my item field

Re: How to remove limitations of join command

mayurr98 — Wed, 17 Jan 2018 11:32:47 GMT

it does not matter here, list command grabs everything into one command, while this will separate out everything so ITEM will get repeated many times.I do not think there is any problem with this search. Are you getting results?

Re: How to remove limitations of join command

mahbs — Wed, 17 Jan 2018 12:15:06 GMT

@mmayurr98 its taking a very long time to produce the results. I still haven't received anything.

Re: How to remove limitations of join command

mahbs — Thu, 18 Jan 2018 08:21:19 GMT

@mayurr98, Hi this works partially. It's not separating one of the fields in the sub search. It's just repeating it.

Re: How to remove limitations of join command

kamlesh_vaghela — Thu, 18 Jan 2018 17:29:10 GMT

Can you please try this search?

index=index sourcetype=csv source=src1 host=host1 
| stats list(field1) as F_1 list(field2) as F_2 list(field3) as F_3 BY ITEM 
| eval source1=mvzip(F_1,mvzip(F_2,F_3)) 
| mvexpand source1 
| rex field=source1 "(?<F_1>\d+),(?<F_2>\d+),(?<F_3>\d+)" 
| append  
    [ search index=index sourcetype=csv source=src2 host=host2 
    | stats list(c_1) as C_1 list(c_2) as C_2 list(c_3) as C_3 BY SKU 
    | eval source2=mvzip(C_1 ,mvzip(C_2,C_3)) 
    | mvexpand source2 
    | rex field=source2 "(?<C_1 >\d+),(?<C_2 >\d+),(?<C_3>\d+)" 
    | rename SKU as ITEM] 
| stats values(F_1) as F_1 values(F_2) as F_2 values(F_3) as F_3 values(C_1) as C_1 values(C_2) as C_2 values(C_3) as C_3 by ITEM
| eval DIFF1=F_1-C_1 
| eval DIFF2=F_2-C_2 
| sort limit=0 ITEM 
| table ITEM, F_1, F_2, F_3, C_1, C_2, C_3, DIFF1, DIFF2