Splunk Search

combine csv lookup table (list of users) to ldapsearch

SGun
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

isabel_ycourbe
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

SGun
Explorer

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

not working

0 Karma

maciep
Champion

i think you want lookup, not inputlookup

SGun
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

maciep
Champion

what errors exactly?

0 Karma

SGun
Explorer

The A1 field is called "member"

0 Karma

493669
Super Champion

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

0 Karma

SGun
Explorer

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

0 Karma

493669
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

SGun
Explorer

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

0 Karma

493669
Super Champion

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

0 Karma

SGun
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

493669
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

493669
Super Champion

only one column is present in your lookup?

0 Karma

SGun
Explorer

yep only one

0 Karma

493669
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

SGun
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

elliotproebstel
Champion

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. 🙂

SGun
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

elliotproebstel
Champion

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

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