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!

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