topic Re: Splunk Lookup in Splunk Search

How to count or match fields?

David_Arnold — Wed, 20 Sep 2023 18:39:21 GMT

I have a CSV of URLs I need to search against my proxy index (the url field), I want to be able to do a count or match of the URLs.
my csv looks like this (with the header of the column called kurl)
kurl
splunk.com
youtube.com
google.com

So far, I have this SPL but it's only counting the matches, i need the URLs that don't exist to count 0

index="web_index" [| inputlookup URLs.csv | fields kurl | rename kurl as url] | stats count by url

Re: Splunk Lookup

yuanliu — Wed, 20 Sep 2023 00:05:03 GMT

You selected lookup as label, but are using inputlookup😃. You would have the answer if you stick to lookup.

index="web_index" | lookup URLs.csv kurl as url output kurl as match | eval match = if(isnull(match), 0, 1) | stats sum(match) as count by url

Re: Splunk Lookup

David_Arnold — Wed, 20 Sep 2023 01:37:18 GMT

That also returned every url not on my lookup. I guess I could just take the hits and do a duplicate compare in excel but it'd be nice to see it all in splunk.

Yeah, sorry about the tag I wasn't paying attention.

Re: Splunk Lookup

yuanliu — Wed, 20 Sep 2023 05:04:06 GMT

Maybe you can explain the requirement further? You said of your original SPL "it's only counting the matches, i need the URLs that don't exist to count 0." So, I thought you would want every url not with a match. Could you mock up some data in web_index and explain why the output doesn't meet the requirements?

Here is my emulation

| makeresults | fields - _time | eval url = mvappend("google.com", "foo.com", "bar.com", "google.com") | mvexpand url ``` the above emulates index="web_index" ```

url

google.com

foo.com

bar.com

google.com

Using the exact mock lookup you give, my search will give

url	count
bar.com	0
foo.com	0
google.com	2

Is this not what you want, bar.com and foo.com count to 0?

Below is the full example

Re: Splunk Lookup

David_Arnold — Wed, 20 Sep 2023 05:48:05 GMT

So my first SPL, it gets me the URLs I'm looking for but doesn't list the URLs (in the lookup) that don't get any results.
So to break it down, say my csv has 120 URLs and I would like to know what my users are hitting and not hitting, when i run my first example search I'm getting 43 results back, but I also want to know what are the URLs (out of the 120) not hitting? Or should I create a search only telling me the URLs that don't match in the index?

Whilst that example does make sense, in the stats table I get all these other URLs that aren't even in my lookup list that I care about. So I go from expecting 120 results to (18,000).

Does that make more sense?

Re: Splunk Lookup

yuanliu — Wed, 20 Sep 2023 16:17:17 GMT

So my first SPL, it gets me the URLs I'm looking for but doesn't list the URLs (in the lookup) that don't get any results.

The parenthesis "(in the lookup)" is key here. My first reading of "only counting the matches, i need the URLs that don't exist to count 0" took it to mean URLs in the index that didn't have a match. This new description makes perfect sense. And your search is more efficient.

Alternatively, you can do

Re: Splunk Lookup

David_Arnold — Wed, 20 Sep 2023 22:08:47 GMT

Yeah it still doesn't mark the URLs in my list that don't exist in the index as 0.

I'll just accept your solution though, thanks for your help 😃

Re: Splunk Lookup

yuanliu — Wed, 20 Sep 2023 23:36:37 GMT

Actually, I made a mistake when I emulate data. (I had a spurious "match" field in emulation which your index subsearch will not give.) Here is the correct solution:

Here is the corrected emulation

This gives

_time	index	url
2023-09-20 16:20:05	web_index	google.com
2023-09-20 16:20:05	web_index	google.com

(If your results do not meet the requirement, it would be useful to compare real data with emulation.) Combined simulation is then

I get

url	count
google.com	2
splunk.com	0
youtube.com	0

However, I still don't understand why the first option (stats before append) would not give you the correct output. Here's my full emulation:

Again It would help if you can run data part of emulation and let me know the difference with real data.

Re: Splunk Lookup

David_Arnold — Wed, 20 Sep 2023 23:46:33 GMT

THIS WORKS!!!!!!!!!!
Thanks so much!!!!