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 |
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 |
malicious |
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
@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 !
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
@tscroggins Thanks for helping me with the answer 🙂 It worked 🙂 Thumbs up !