Splunk Search
Highlighted

How to create a search for lookup to get results in single search?

Explorer

Hi,

I have uploaded a csv file with fields User Name, A, B, C.

First I need to perform lookup with another file with fields 'User Name', 'Person', 'First Name', 'Last Name', 'Complete Name'.

After first lookup, I need to perform lookup with another file with fields 'Person', Email-ID, D, E, F

I need to have search results with User Name, First Name, Complete Name and Email-ID. Can you please help?

I managed to do it with some lookup configuration in settings but when trying to re-create the same, unable to do it.

Tags (2)
0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Esteemed Legend

Try this:

|inputlookup first.csv
| rename "* *" AS *_*
| eval which="first"
| appendpipe [
|inputlookup second.csv
| rename "* *" AS *_*
| eval which="second" ]
| stats values(*) AS * BY UserName
| appendpipe [
|inputlookup third.csv
| rename "* *" AS *_*
| eval which="third" ]
| stats values(*) AS * dc(which) AS whichCount BY Person
0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Explorer

First csv file has been uploaded as sourcetype using Upload option in 'Add Data' file

0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Esteemed Legend

OK, so then it becomes this:

index=YouShouldAlwaysSpecifyAnIndex sourcetype=AndSourcetypeToo
| eval which="first"
| appendpipe [
|inputlookup second.csv
| rename "* *" AS *_*
| eval which="second" ]
| stats values(*) AS * BY UserName
| appendpipe [
|inputlookup third.csv
| rename "* *" AS *_*
| eval which="third" ]
| stats values(*) AS * dc(which) AS whichCount BY Person
0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Explorer

There are 200 records in the main uploaded file, but it is showing 392 records in the table with email-address as null.

source="UserList.csv" host="prd-p-bjs8j4b5tcmc" sourcetype="USR02"
| eval which="first" | appendpipe [
|inputlookup FullName.csv
|rename "Full Name" as full_name, "First Name" as first_name
|eval which="second" ]
| stats values(*) AS * by "User Name"
| appendpipe [
| inputlookup EmailID.csv
| rename "E-Mail Address" as email-address
| eval which="third" ]
| stats values(*) as * dc(which) AS whichCount BY Person
| table "User Name", email-address
0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Esteemed Legend

Field names with spaces are EVIL and hyphens are almost as bad. If something is not the way that it should be, then it is because your field names are not aligned.

0 Karma
Highlighted

Re: How to create a search for lookup to get results in single search?

Moderator
Moderator

Hi @vineeth_jain

Were you able to test out @woodcock solution? Did it work? If yes, please don't forget to resolve this post by clicking on "Accept". If you still need more help, please provide a comment with some feedback.

Thanks!

0 Karma