topic Search a lookup with wildcard in values in Splunk Search

Search a lookup with wildcard in values

Poojitha — Mon, 27 Dec 2021 11:38:40 GMT

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/94513 .

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

Re: Search a lookup with wildcard in values

tscroggins — Mon, 27 Dec 2021 16:22:47 GMT

@Poojitha

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

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:

Re: Search a lookup with wildcard in values

Poojitha — Thu, 30 Dec 2021 19:07:51 GMT

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

Re: Search a lookup with wildcard in values

Poojitha — Thu, 13 Jan 2022 14:00:02 GMT

@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 !