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

SGun
Explorer

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

0 Karma

SGun
Explorer

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

0 Karma

elliotproebstel
Champion

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.

0 Karma

SGun
Explorer

A1 member
A2 userID
A3 userID
A4 userID

and so on.

0 Karma

SGun
Explorer

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

0 Karma

isabel_ycourbe
Path Finder

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 ?

0 Karma

SGun
Explorer

yes A1 is the header

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

0 Karma

isabel_ycourbe
Path Finder

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
Explorer

CSV file looks like this

     A                  B

1 member
2 userID
3 userID
3 userID

Do I need to change the format?

0 Karma

isabel_ycourbe
Path Finder

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
Explorer

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?

0 Karma

isabel_ycourbe
Path Finder

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
Explorer

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

0 Karma

493669
Super Champion

try |where isnotnull(found_member)

0 Karma

493669
Super Champion

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?

0 Karma

SGun
Explorer

No errors if run individually

0 Karma

493669
Super Champion

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

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>