Splunk Search

combine csv lookup table (list of users) to ldapsearch

Explorer

Trying to search with ldapsearch a list of specific users.

| ldapsearch domain="default" search="(&(samAccountType=805306368))"

with inputlookup user_lookupnew.csv which is my list of specific user.

any help would be great.

0 Karma

Path Finder

You need to use lookup and not inputlookup.

lookup will take an input field and will fetch the other values found in your csv for the matching row to your data.

inputlookup can only be used at the beginning of a query as a datasource or with the parameter append=T but this will just append the csv to your base search.

0 Karma

Explorer

| ldapsearch domain="default" search="(&(samAccountType=805306368))" | inputlookup user_lookup.csv

not working

0 Karma

Champion

i think you want lookup, not inputlookup

Explorer

| ldapsearch domain="default" search="(&(samAccountType=805306368))" | lookup user_lookup.csv

Still get errors.

I will look at the documentation around the lookup command. Thanks.

0 Karma

Champion

what errors exactly?

0 Karma

Explorer

The A1 field is called "member"

0 Karma

Super Champion

does this member field is present in your base search as well as lookup?

0 Karma

Explorer

it is present in the top bar then starting at 1 is the first user.

0 Karma

Super Champion

if same field name is present in lookup as well then try this:

| ldapsearch domain="default" search="(&(samAccountType=805306368))" 
| lookup user_lookupnew.csv member as member OUPUT <your required lookup field name>

here add your lookup field name which has list of specific user

Explorer

Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table

0 Karma

Super Champion

could you specify your lookup fields...also what query did you try?

0 Karma

Explorer

A1 = member
A2 = user ID

A3 = user ID

and so on, looking to search all the users in the list.

| ldapsearch domain="default" search="(&(samAccountType=805306368))"
| lookup user_lookupnew.csv member as member OUPUT

= a field in the csv or the a field for the ldapsearch?

0 Karma

Super Champion

also can you share output of base query as i just want to know which field is common in both
also if in lookup only one field is their i.e.memberwhich is in common then what is use of join?

0 Karma

Super Champion

only one column is present in your lookup?

0 Karma

Explorer

yep only one

0 Karma

Super Champion

also can you share output of base query as i just want to know which field is common in both
also if in lookup only one field is their i.e.memberwhich is in common then what is use of join?

0 Karma

Explorer

| ldapsearch domain="default" search="(&(samAccountType=805306368))"
| lookup user_lookupnew.csv member as member OUTPUT member

This search shows all user rather than just the ones in the CSV

0 Karma

Let's dig into this:

| lookup user_lookupnew.csv member as member OUTPUT member

What that tells Splunk to do:
(1) Take the field member from the events returned by the earlier part of the search,
(2) Consult the lookup file called user_lookupnew.csv for a matching value in the column member
(3) If found, output the same matching value found in step 2, and place it back into the field member in the source events.

So, basically, it does nothing.

Try this instead:

| lookup user_lookupnew.csv member as member OUTPUT member AS found_member

Steps 1&2 remain the same, but now step 3 will place the found items in a new field called found_member. You can then filter your result set by adding this at the end:

| where isnotnull(found_member)

That tells Splunk to retain only the events that had a matching member value.

So in total, your query would look like this:

| ldapsearch domain="default" search="(&(samAccountType=805306368))" 
| lookup user_lookupnew.csv member as member OUTPUT member AS found_member
| where insnotnull(found_member)

If you'd like to better understand the lookup command, this is the most comprehensive answer I've written about it:
https://answers.splunk.com/answers/588630/understanding-the-lookup-command.html#answer-587710

I'm sure there are lots of other good explanations, but that's the one I had handy. 🙂

Explorer

| ldapsearch domain="default" search="(&(samAccountType=805306368))"
| lookup user_lookupnew.csv member as member OUTPUT member AS found_member

This part works fine.

The last part creates an error

| where insnotnull(found_member)

Error in 'where' command: The 'insnotnull' function is unsupported or undefined.

0 Karma

Yeah, that's a typo, sorry. Should be | where isnotnull(found_member)