combine csv lookup table (list of users) to ldapsearch

[SGun]

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.

[SGun]

ok I have tried

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

but no data

I need to search data under sAMAccountName

[SGun]

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

[elliotproebstel]

Can you share the structure of the results from that ldapsearch string? Do the results contain a field called "member"? If not, the lookup will never work.

[SGun]

A1 member
A2 userID
A3 userID
A4 userID

and so on.

[SGun]

Do I need to tell the lookup where to look for the userID in the "sAMAccountName"?

[isabel_ycourbe]

You need to take into account the fields names. @eliotproebstel ask you data structure but I think you missed the columns name which is very important, I assume

A1 member
A2 userID
A3 userID
A4 userID

are your data or are A1 member your headers ?

Does your csv has headers too ?

[SGun]

yes A1 is the header

A2 and so on is the userID that I need to lookup.

[isabel_ycourbe]

Then your input in your lookup will be the field A1

| your ldap search
| lookup <lookup-table-name> <lookup-field> AS <event-field>

where <lookup-field> will be your csv header you want to match and <event-field> will be in this case A1

[SGun]

CSV file looks like this

     A                  B

1 member
2 userID
3 userID
3 userID

Do I need to change the format?

[isabel_ycourbe]

A lookup should always be like

id,member
1,user1
2,user2
3,user3

you need a header row at the top, those will be your <lookup-field>. If your event-field is the same as you lookup-field you don't need to specify it in the lookup command, otherwise you just need to map it with <lookup-field> AS <event-field>

[SGun]

I only know the userID , so in the csv - column A, row 1 I have the word "member" as the header and the after that in column A, row 2 the userID that I want to lookup.

Does there need to be a second column B called "name" that will get populated?

[isabel_ycourbe]

A lookup will search for your value in your column <lookup-field> and will join the matching row to your dataset. The problem in the case of a single column CSV is that you don't have any value to join. You should add a B column to your CSV like is_member and set it to true or 1 for all your row, so if your lookup finds a match it will join the value is_member to it, then you can make an even like if(is_member="true", <then something>)

[SGun]

Ok I finally got it working and have a better understanding, thanks to everyone for your support.

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

| lookup user_lookupnew.csv member AS displayName OUTPUT member AS name

| where isnotnull(name)

ldapsearch

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

Lookup CSV

| lookup user_lookupnew.csv

member column A1 header

member AS

ldapsearch display name

displayName

[493669]

try |where isnotnull(found_member)

[493669]

if you could share sample output and what field name are present in output:

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

and

|inputlookup user_lookupnew.csv

what output you are receiving by running above two query separately?

[SGun]

No errors if run individually

[493669]

by what field name you are trying to join your base search with lookup?