topic lookup from different source types based on a common value in Splunk Search

lookup from different source types based on a common value

lphirke — Tue, 29 Oct 2013 09:56:11 GMT

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

Re: lookup from different source types based on a common value

alacercogitatus — Tue, 29 Oct 2013 11:15:17 GMT

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

Re: lookup from different source types based on a common value

lphirke — Tue, 29 Oct 2013 12:02:19 GMT

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"

Re: lookup from different source types based on a common value

alacercogitatus — Tue, 29 Oct 2013 12:44:49 GMT

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