How to create a search and subsearch to exclude results in a query?

pc1234 — Tue, 18 Oct 2022 13:59:05 GMT

I need to create a search and subsearch to exclude results in a query.

the primary search is a lookup table. the subsearch is a query on events that extracts a field I want to use to join to the primary search. the common field is hostname.

If a given hostname in the lookup table is found in the subsearch i want to discard it.

primary search

| inputlookup hosts.csv

field = hostname

output:

host1

host2

host3

subsearch

index=abc message="for account" sourcetype=type1

rex field=names"(?<hostname>\S+)

field hostname

output:

host3

I want the following output:

hostname

host1

host2

I want to discard host3 since its in the subquery.

How do I correlate the searches to do this? I can't use a join because the hostname in the subsearch is not computed until the subquery is executed.

Thanks in Advance.

Re: subsearch join

richgalloway — Mon, 17 Oct 2022 23:58:12 GMT

You wrote what you need to do - a search, a subsearch, and exclude (NOT).

| inputlookup hosts.csv where NOT [ index=abc message="for account" sourcetype=type1 | rex field=names"(?<hostname>\S+) ]

It also can be done with a join, but that's not preferred.

| inputlookup hosts.csv | join type=left hostname [ index=abc message="for account" sourcetype=type1 | rex field=names"(?<hostname>\S+) ]

topic How to create a search and subsearch to exclude results in a query? in Splunk Search

How to create a search and subsearch to exclude results in a query?

Re: subsearch join