Splunk Search

Join if one of two fields match a lookup

Communicator

Hi there, many thanks for reading this far and for any insights you can give.

I have a base search which returns a number of fields including "Website" and "IP address"

In addition, there is a lookup table that has two fields:
Address (with either a website or IP address)
Date (which strangely has a date).

What I would like to do is add the Date to records where the Address is like either "Website" OR "IP address"

(e.g. Address could contain BBC to cover bbc.co.uk, bbc.com, bbc.co.uk/news, bbc.com/sports etc.).

If there is another way to avoid using join at all that would be a bonus

My search is currently along line of the following but it returns 0 results:

|base search |table Website "IP address"
|join type=left 
[inputlookup input.csv |fields Address Date |eval Website= Address |eval "IP address"= Address]
|table Website "IP address" Date

I have tried putting fields names on the Join part and renaming Address, but this only joins for one field not either.

Chris

0 Karma
1 Solution

Champion

what about maybe just using the lookup command...maybe something like this?

| base search 
|table Website "IP address"
| lookup input.csv Address as Website OUTPUT Date
| lookup input.csv Address as 'IP address' OUTPUTNEW Date
| table Website "IP address" Date

View solution in original post

Champion

what about maybe just using the lookup command...maybe something like this?

| base search 
|table Website "IP address"
| lookup input.csv Address as Website OUTPUT Date
| lookup input.csv Address as 'IP address' OUTPUTNEW Date
| table Website "IP address" Date

View solution in original post

Communicator

Good afternoon,
Thank you for your answer

It is working perfectly where there is an exact match. The trouble is is for entries such as "bbc" (which are needed to cover multiple sites bbc.co.uk and bbc.com).

At present the fields in the lookup are not set for wildcard use so can't use "bbc", and not sure if I can get the "holders of the keys" to make the changes

0 Karma

Communicator

Got the wildcard sorted and working correctly now.

0 Karma

Champion

yeah, if you can't get the wildcard option for the lookup, this problem does become a bit different. Would the wildcard match always be the domain prefix? If so, then you could maybe create a field for that and add another lookup. Maybe like this?

| base search 
| table Website "IP address"
| eval Domain  = mvindex(split(Website,"."),0)
| lookup input.csv Address as Website OUTPUT Date
| lookup input.csv Address as Domain OUTPUTNEW Date
| lookup input.csv Address as 'IP address' OUTPUTNEW Date
| table Website "IP address" Date
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!