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?

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
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...