Splunk Search

Search a lookup with wildcard in values

Poojitha
Path Finder

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

Labels (1)
Tags (2)
0 Karma
1 Solution

tscroggins
Motivator

@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

View solution in original post

Poojitha
Path Finder

@tscroggins 

What if there are more columns in url_requested.csv 

example : 

http_url host source_ipsource_portdestip destportuseragent
*yahoo*
test100.1.1.16767101.1.1.180chrome
*002redir023.dns04*test100.2.2.28787102.1.1.180chrome


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 :

urldescriptionsourceipsourceportdestipdestportuseragent
http://002redir023.dns04.commalicious100.2.2.28787102.1.1.180chrome

 

Please help me !

0 Karma

tscroggins
Motivator

@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
Path Finder

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


Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...