Splunk Search

lookup from different source types based on a common value

lphirke
New Member

I want to search from multiple sources having one field in common and the query should return all the fields from both the sources..
Just like the vlookup function in excel..
e.g. i have two csv's having following data
csv-1 - IP Address, User Name
csv-2 - IP Address, Host Name
now the query should lookup for the common IP Address in both the csv's and return the result having following columns:
IP Address, Host Name, User Name

Tags (1)
0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

Assuming you have the files in the correct lookup location and named accordingly:

your_search | lookup csv-1.csv IP_FIELD_IN_CSV AS IP_FIELD_IN_SEARCH OUTPUT USERNAME_FIELD_IN_CSV | lookup csv-2.csv IP_FIELD_IN_CSV AS IP_FIELD_IN_SEARCH OUTPUT HOSTNAME_FIELD_IN_CSV | stats count by IP_FIELD_IN_SEARCH HOST_NAME_FIELD_IN_CSV USERNAME_FIELD_IN_CSV

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

ah ok, so you aren't using lookups, I misunderstood. Transaction and where is an expensive command, try this:
sourcetype=A OR sourcetype=B | stats values(hostname) values(username) by clientip

0 Karma

lphirke
New Member

Thanks "alacercogitatus" for the reply..
however I got my query resolved with the following condition:
"transaction parameter-A parameter-B connected=f | where parameter-A=parameter-B"

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...