Search a lookup with wildcard in values

[Poojitha]

Hi,

I am stuck implementing below use case , please help me on this :

I have a lookup say url_requested.csv. 

http_url	host
*002redir023.dns04*	test
*yahoo*	test

Another csv file :  malicious.csv

url	Description
xyzsaas.com	C&C
http://002redir023.dns04.com	malicious

I have to check the url values in "url_requested.csv" with that in "malicious.csv" and get only those url and description which has a match in "malicious.csv" . url_requested.csv lookup has url column with wildcard prefixed and suffixed.

I have added the wildcard configuration in transforms.conf following this :
https://community.splunk.com/t5/Splunk-Search/Can-we-use-wildcard-characters-in-a-lookup-table/m-p/9....


My query :

| inputlookup malicious.csv
| table url description
| lookup url_requested.csv  http_url as url outputnew host
| search host=*
| fields - host

I am getting no results running this query. Please let me know where I am going wrong and help me with the solution.

Result I am looking for :

url	Description
http://002redir023.dns04.com	malicious

[Poojitha]

@tscroggins 

What if there are more columns in url_requested.csv 

example : 

http_url	host	source_ip	source_port	destip	destport	useragent
*yahoo*	test	100.1.1.1	6767	101.1.1.1	80	chrome
*002redir023.dns04*	test	100.2.2.2	8787	102.1.1.1	80	chrome

I have to get all columns in url_requested.csv  when there is a match in malicious.csv. I am newbie to splunk. I tried with append as well as with the above approach you gave, still not getting expected result 😐

Result I am looking for :

url	description	sourceip	sourceport	destip	destport	useragent
http://002redir023.dns04.com	malicious	100.2.2.2	8787	102.1.1.1	80	chrome

Please help me !

[tscroggins · Accepted solution]

@Poojitha 

You can use inputlookup in a subsearch to filter search results using field-value pairs:

| inputlookup malicious.csv
| table url description
| search [| inputlookup url_requested.csv | table http_url | rename http_url as url ]

Since your values contain wildcards, the resulting search becomes e.g.:

| inputlookup malicious.csv
| table url description
| search ( ( url=*002redir023.dns04* ) OR (url=*yahoo*) )

You can optimize this into your initial lookup:

| inputlookup malicious.csv where [| inputlookup url_requested.csv | table http_url | rename http_url as url ]
| table url description

[Poojitha]

@tscroggins  Thanks for helping me with the answer  🙂 It worked 🙂 Thumbs up !