Splunk Search

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

vineeth_jain
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

woodcock
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

vineeth_jain
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

Anam
Community Manager
Community Manager

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

woodcock
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

vineeth_jain
Explorer

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

0 Karma

woodcock
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...