Splunk Search

Join if one of two fields match a lookup

ChrisCLewis
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

maciep
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

maciep
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

ChrisCLewis
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

ChrisCLewis
Communicator

Got the wildcard sorted and working correctly now.

0 Karma

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