Splunk Search

Inputlookup subsearch and join

darioapis
Explorer

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 ]

0 Karma

HiroshiSatoh
Champion

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
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Influencer

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

index=windows | lookup default_user_accounts.csv OUTPUT user

0 Karma

darioapis
Explorer

Would that be faster than a regular join?

0 Karma

tiagofbmm
Influencer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...