Splunk Search
Highlighted

How to compare index data with a lookup?

Builder

Hi,

I have a lookup with URL's, I have to compare with the index data to find count of the URL's in list. How to edit my search to get if domain matches in URL? For example, I have splunk.com in URL list and in an index www.splunk.com/answers, it should match it. Any advice?

| `web_proxy_tstats`  count from datamodel=Web where host=*  by Web.dest |search [|inputlookup wsa_url.csv] 
0 Karma
Highlighted

Re: How to compare index data with a lookup?

SplunkTrust
SplunkTrust

You would need to use a lookup definition to use a wildcard so that you can match URLFromLookup* in your data. Use following link to setup the lookup.
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

Once setup, run you rsearch like this (assuming the lookup definition you created is wsa_url and lookup has field URL)

| `web_proxy_tstats` count from datamodel=Web where host=* by Web.dest |lookup wsa_url.csv URL as  "Web.dest" OUTPUT URL as ismatch | where isnotnull(ismatch) | fields - ismatch
Highlighted

Re: How to compare index data with a lookup?

Esteemed Legend

Like this:

| `web_proxy_tstats`  count from datamodel=Web where host=*  BY Web.dest
| appendpipe [|inputlookup wsa_url.csv | rename URL as Web.dest | eval count=0]
| stats sum(count) AS count BY Web.dest

This will add the missing 0 counts.

Or maybe you mean this (you must torn your CSV into a lookup first):

| `web_proxy_tstats`  count from datamodel=Web where host=*  BY Web.dest
| lookup wsa_url_lookup URL AS Web.dest OUTPUT URL AS matched
| eval matched=if(isnotnull(matched), "YES", "NO")
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.