topic Re: Join if one of two fields match a lookup in Splunk Search

Join if one of two fields match a lookup

ChrisCLewis — Wed, 05 Jun 2019 16:13:45 GMT

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

Re: Join if one of two fields match a lookup

maciep — Thu, 06 Jun 2019 00:13:26 GMT

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

Re: Join if one of two fields match a lookup

ChrisCLewis — Thu, 06 Jun 2019 12:40:10 GMT

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

Re: Join if one of two fields match a lookup

maciep — Thu, 06 Jun 2019 13:07:04 GMT

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

Re: Join if one of two fields match a lookup

ChrisCLewis — Thu, 05 Sep 2019 09:36:32 GMT

Got the wildcard sorted and working correctly now.