Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results forย

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results forย

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Search help: remove intersection of two sets from ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Genti

Splunk Employee

โ06-20-2011
03:38 PM

A customer asked this search question a few days ago. I thought it was a good one for answers.

Assume you have two datasets where you can use a JOIN to find the intersection (based on a field). How can i then modify the search to list the events in the first data set that do not also show up in the second dataset?

Here's a simple example:

`set1.log`

May 31 23:00:01 MAC=a

May 31 23:00:05 MAC=b

May 31 23:00:06 MAC=c

May 31 23:00:07 MAC=d

`set2.log`

Jun 01 00:00:01 MAC=d

Jun 01 00:00:05 MAC=e

Jun 01 00:00:06 MAC=f

Jun 01 00:00:07 MAC=g

Using:

`source=set1.log | JOIN MAC [search set2.log] `

i should get the intersection: d

What should my search be, in order to get set1 - intersection of set1 and set2 (in other words, set1 - set2)?

1 Solution

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Genti

Splunk Employee

โ06-20-2011
04:03 PM

This can be achieved by the following search:

`source="set1.log" | JOIN type=left MAC [search source="set2.log" | eval x=1] | Where NOT x=1`

Explanation:

the subsearch will find events in set2.log and add a new field, x=1 to the event. (that is for D, E, F, G, the field x=1 will be associated to the events)

Then, were doing a LEFT JOIN, on the field MAC (which will return A B C D) , but we are leaving out those events for which x=1 (hence we are leaving out D).

The final result then becomes: A, B and C, that is, set1 - set2

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

kristian_kolb

Ultra Champion

โ11-18-2011
12:30 PM

I'd assume that the following would be more efficient, but I haven't tried it out.

```
source=set1.log NOT [search source=set2.log | dedup MAC | fields + MAC ]
```

i.e find all MACs from set2, then show all events from set1 whose MAC addresses do not match the subsearch.

/kristian

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Genti

Splunk Employee

โ06-20-2011
04:03 PM

This can be achieved by the following search:

`source="set1.log" | JOIN type=left MAC [search source="set2.log" | eval x=1] | Where NOT x=1`

Explanation:

the subsearch will find events in set2.log and add a new field, x=1 to the event. (that is for D, E, F, G, the field x=1 will be associated to the events)

Then, were doing a LEFT JOIN, on the field MAC (which will return A B C D) , but we are leaving out those events for which x=1 (hence we are leaving out D).

The final result then becomes: A, B and C, that is, set1 - set2