How to compare index data with a lookup?

[kiran331]

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] 

[woodcock]

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")

[somesoni2]

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