Source from inputlookup to negative match against index

[coryjett]

Hello all!

I am trying to source from a CSV, do a negative lookup against an index, and then output anything from the CSV that did not return results in the index.

Our CSV contains a list of web applications. I want to take this list and run it against an index that contains web logs and output applications haven't produced logs in the past 30 days.

The CSV contains the following columns with app being the application name:
guid,app,version,epoch_time

The index has a field called url_host that exists on web log events and is the application name.

Here is what I have so far that is sort of working:

| inputlookup application_names.csv | dedup app | eval url_host=app | fields url_host | search NOT [search index=dev_applications | dedup url_host | fields url_host ] | sort url_host

In my output, I am still getting application names that have events in the dev_applications index so it appears not everything is getting filtered out. Not sure if I am doing something wrong or possibly hitting a subsearch limit of some kind.

I've been banging my head against this for a few hours...any help is appreciated!

[somesoni2]

Try like this

index=dev_applications 
| stats count by url_host 
| table url_host | rename url_host as app | eval hasEvent=1
| append [| | inputlookup application_names.csv | table app| dedup app | eval hasEvent=0]
| stats max(hasEvent) as hasEvent by app
| where hasEvent=0