Search for a URL from a datamodel NOT in a lookup

[mztopp]

For example:

|  tstats count from datamodel=test where * by test.url, test.user 

| rename test.* AS *

| search NOT

    [ | inputlookup list_of_domains

       | rename domain AS url

       | fields url ]

| table url, user

I want this to show me the urls from the DM that do NOT appear in the lookup, and then give me the corresponding usernames from the DM. But, this is not working properly. When I run this search, I still see some of the urls that are in the lookup. Please help!

[zonistj · Accepted solution]

The problem is you want to compare one value from one field across all values of a field in a lookup table. The best way I know to do this is to make your lookup table field a multivalue field then regex search that field with your datamodel field as the regex string in a case / match function.

Something like this:

|  tstats count from datamodel=test where * by test.url, test.user 

| rename test.* AS *

| eval match = “true”

| join type=left match

    [ | inputlookup list_of_domains

       | rename domain AS url

       | eval match = “true”

        | stats values(url) AS urls by match]

| eval matching = case(match(‘urls’,’url’),”true”,1=1,”false”)

| search matching = “false”

| table url, user

I’m typing on my phone so the ticks in the case statement might not be the right characters.

[mztopp]

This appears to be exactly what I needed. Thanks! It is REALLY expensive though, any idea on the limits Splunk has for this type of thing or how I can measure/manage that aspect of this? Thanks again!

[ITWhisperer]

Does this help?

|  tstats count from datamodel=test where * by test.url, test.user 
| rename test.* AS *
| search NOT
    [ | inputlookup list_of_domains
       | rename domain AS url
       | fields url 
       | format ]
| table url, user

[mztopp]

The format command did not change my results unfortunately.