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!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out >> As our brave ...