Inputlookup subsearch and join

[darioapis]

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.

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

[HiroshiSatoh]

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)

[starcher]

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

[gcusello]

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!

[tiagofbmm]

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

index=windows | lookup default_user_accounts.csv OUTPUT user

[darioapis]

Would that be faster than a regular join?

[tiagofbmm]

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