Splunk Search

How to compare index data with a lookup?

kiran331
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

woodcock
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

somesoni2
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...