topic Re: How to negate Join Command in Splunk Search

How to negate Join Command

mahbs — Fri, 26 Jan 2018 11:17:08 GMT

Hi,

I have two sets of records, let's call them V1 and V2. They both share a common field called ITEM. I basically need a way of saying return to me to items that are not common. So for instance, I would use a join command to join item values that are common - I need the opposite of that, wherein where items dont match, return that data.

Thanks

Re: How to negate Join Command

mayurr98 — Fri, 26 Jan 2018 11:43:01 GMT

you want uncommon values from both v1 and v2?

Re: How to negate Join Command

mahbs — Fri, 26 Jan 2018 11:43:30 GMT

Yesss! Is it possible?

Re: How to negate Join Command

mayurr98 — Fri, 26 Jan 2018 11:46:42 GMT

try this run anywhere search

| makeresults 
 | eval item="cereal cloths pen pencil" 
 | makemv item
 | mvexpand item
 | eval check="csv1" 
 | append 
     [| makeresults 
     | eval item="tv tiffin brush cereal cloths eraser" 
     | makemv item
     | mvexpand item
     | eval check="csv2" ] 
 | fields- _time 
 | stats dc(check) as count by item
 | where count=1 
 | fields item
 | rename item as "missing item list"

In your environment, you should write

index=<your_index> v1=* | dedup ITEM | table ITEM |eval ITEM=lower(ITEM)
  | eval check="v1" 
  | append 
      [search index=<your_index>  v2=*  | dedup ITEM | table ITEM |eval ITEM=lower(ITEM)
      | eval check="v2" ] 
  | stats dc(check) as count by ITEM
  | where count=1 
  | fields ITEM
  | rename ITEM as "missing ITEM list"

let me know if this helps!

Re: How to negate Join Command

mayurr98 — Fri, 26 Jan 2018 11:52:07 GMT

totally possible bro! check my query

Re: How to negate Join Command

gcusello — Fri, 26 Jan 2018 11:52:35 GMT

Hi mahbs,
I suppose that in V1 and V2 you have only one occurrance of ITEM so it's possible to compare one record of V1 with one record of V2

searchV1
| rename field1 AS field1_v1 field2 AS field2_v1 field3 AS field3_v1
| join ITEM [ search searchV2 | rename field1 AS field1_v1 field2 AS field2_v1 field3 AS field3_v1 ]
| search NOT (field1_v1=field1_v2 OR field2_v1=field2_v2 OR field3_v1=field3_v2)
| table _time field1_v1 field1_v2 field2_v1 field2_v2 field3_v1 field3_v2

or better

searchV1 OR searchV2
| eval 
    field1_v1=if(searchV1,field1,""),
    field2_v1=if(searchV1,field2,""),
    field3_v1=if(searchV1,field3,""),
    field1_v2=if(searchV2,field1,""),
    field2_v2=if(searchV2,field2,""),
    field3_v2=if(searchV2,field3,""),
| stats 
   values(field1_v1) AS field1_v1 
   values(field2_v1) AS field2_v1 
   values(field3_v1) AS field3_v1 
   values(field1_v2) AS field1_v2 
   values(field2_v2) AS field2_v2 
   values(field3_v2) AS field3_v2 
  BY ITEM
  | search NOT (field1_v1=field1_v2 OR field2_v1=field2_v2 OR field3_v1=field3_v2)
  | table _time field1_v1 field1_v2 field2_v1 field2_v2 field3_v1 field3_v2

The second one is quicker.

Bye.
Giuseppe

Re: How to negate Join Command

DavidHourani — Fri, 26 Jan 2018 12:50:06 GMT

Hi mahbs,

Think simple and avoid using join whenever possible.

Supposing that your sets of data have different indexes, a simple stats like this should do :

index=V1index OR index=V2index | stats count(index) as unique by ITEM | where unique < 2

This will give you all the ITEM that are in either in data set v1 or v2 but not both.

Another easy way to do it is:

index=V1index OR index=V2index | stats values(index) as type by ITEM | search NOT (type="v1" AND type="v2")

here you will have the unique items, the set to which it belongs.

Regards,
David