topic Re: Inputlookup subsearch and join in Splunk Search

Inputlookup subsearch and join

darioapis — Wed, 30 Sep 2020 01:12:04 GMT

I have a question about two searches. The first one is much more faster than the second one, but I think that they do the same thing so I am wondering am I right about that assumption.

Re: Inputlookup subsearch and join

tiagofbmm — Tue, 09 Jul 2019 07:16:10 GMT

Why aren't you trying the | lookup command on that ?

index=windows | lookup default_user_accounts.csv OUTPUT user

Re: Inputlookup subsearch and join

darioapis — Tue, 09 Jul 2019 07:52:45 GMT

Would that be faster than a regular join?

Re: Inputlookup subsearch and join

tiagofbmm — Tue, 09 Jul 2019 07:54:03 GMT

You can try it yourself but that should be the fastest way from my experience

Re: Inputlookup subsearch and join

gcusello — Tue, 09 Jul 2019 07:55:05 GMT

Hi darioapis,
the join command it isn't a fast command, so it must be used only when you haven't any other solution!
In addition the lookup command is substancially a join command, so you don't need to use the join command, but it's very faster the lookup command.
So I suggest to use something like this:

index=windows 
| lookup default_user_accounts.csv user OUTPUT my_fields

Beware that the key field must be the same both in search and lookup, if not, use the option lookup_user AS user after the lookup definition.
For more information see https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Lookup .

Bye.
Giuseppe

P.S. a quick tip: when you use Splunk, forget your DB approach, Splunk thinks different!

Re: Inputlookup subsearch and join

HiroshiSatoh — Tue, 09 Jul 2019 09:27:21 GMT

Normally, JOIN is not used in extraction.

First search

index=windows | join user [| inputlookup default_user_accounts.csv | fields user ]

The default is INNER JOIN, so logs that are not JOIN will be deleted. It's slow because it will join. It is not usually used as an extraction condition.

Second search

index=windows [| inputlookup default_user_accounts.csv | fields user ]
↓
index=windows (user=A OR user=b OR user=c)

As it is converted as above and search is fast.

Do this if you want to use lookups. Lookup is faster than JOIN.

 index=windows 
| lookup default_user_accounts.csv user OUTPUT my_fields
| where notisnull(my_fields)

Re: Inputlookup subsearch and join

starcher — Tue, 09 Jul 2019 13:24:51 GMT

Also join has limits and will clip your results. Friends don't let friends use JOIN