Splunk Search

Find incommon values within two Inputlookups without JOIN / Search NOT

moonie
Explorer

Hello,

I have a search where I need to combine two inputlookups to find incommon values in a field they both have. 
The inputlookups are quite big so my current search with JOIN or Search NOT are not working most of the time since they result in a timeout. 
Is there a better way to find incommon values, without join or search not? 
My current search with join looks like this:

| inputlookup table1 
| join type=left "ip" 
[| inputlookup table2 | mvexpand ip | eval xy="xy" | table ip xy]
| where isnull(xy)
| table ip

I've tried another search with NOT but it's working even worse:

| inputlookup table1 | search NOT
([| inputlookup table2 | return 10000 ip])

As I said,  both searches result in a timeout. I've been stuck with this problem for hours, so any help would be highly appreciated! 

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Append/stats

| inputlookup table2
| mvexpand ip 
| eval xy="xy"
| append [
  | inputlookup table1 
  | eval xy="ab"
]
| stats values(xy) as xy by ip
| where mvcount(xy)=1 AND xy="ab"

Will this work?

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

Append/stats

| inputlookup table2
| mvexpand ip 
| eval xy="xy"
| append [
  | inputlookup table1 
  | eval xy="ab"
]
| stats values(xy) as xy by ip
| where mvcount(xy)=1 AND xy="ab"

Will this work?

View solution in original post

moonie
Explorer

Yes that works perfectly!! Thank you so so  much! 🙂 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| inputlookup table1
| append [| inputlookup table2]
| stats values(*) as * by commonfield

moonie
Explorer

Unfortunately  I didn't get correct results with this solution, but thank you anyways! 
This helped me find common values instead of incommon ones 

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!