- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Report Inappropriate Content
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
- Report Inappropriate Content
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