topic Re: How to search for field values that are returned in one search that don't appear in another search? in Splunk Search

How to search for field values that are returned in one search that don't appear in another search?

djconroy — Mon, 10 Nov 2014 22:38:57 GMT

I am trying to come up with the search syntax that would get me the the values of a field that exist in one search that don't exist in another search. IE:

SourceIP="*" earliest=-48h@h latest=-24h@h | stats count by SourceIP | fields SourceIP

and

SourceIP="*" earliest=-24h@h | stats count by SourceIP | fields SourceIP

I'm looking for the values of SourceIP that were present in search 1 that are not present in search 2.

Is there a way to do this that can process quickly? There should be about 11,000 unique values coming from each search. I just want to know which ones didn't occur today.

Re: How to search for field values that are returned in one search that don't appear in another search?

aljohnson_splun — Mon, 10 Nov 2014 22:48:35 GMT

Have you looked at the diff command?

Re: How to search for field values that are returned in one search that don't appear in another search?

djconroy — Tue, 11 Nov 2014 15:25:21 GMT

I am looking at it now... theoretically, how would I send the values from the search into the set diff command?

Essentially I would need to pipe 11,000 different values into each subsearch.

Also, I see that the set diff command works for less than 10,000 results.

Re: How to search for field values that are returned in one search that don't appear in another search?

djconroy — Tue, 11 Nov 2014 16:27:33 GMT

After looking through the set diff docs and searching Google I found this previous quesion:
http://answers.splunk.com/answers/33791/compare-field-from-2-sources-and-return-when-source1-has-no-match-in-source2.html

That gave me the guidance to use a join outer and an if-match command to determine which clients were not communicating in the last 24 hours that should be:

| index=myindex source=mysource sourcetype=mysourcetype * SourceIP="*" earliest=-48h@h latest=-24h@h
| dedup SourceIP
|  eval Client=SourceIP
| eval status1="Active"
| join Client type=outer [search index=myindex source=mysource sourcetype=mysourcetype * SourceIP="*" earliest=-24h@h
| dedup SourceIP
| eval Client=SourceIP
| eval status2 = "Active" ]
| eval Status = if(match(status1,status2), "Active", "Not Responding")
| table Client Status
| where Status="Not Responding"

Re: How to search for field values that are returned in one search that don't appear in another search?

woodcock — Thu, 26 May 2016 04:49:56 GMT

All other answers are subject to very low limits (50K rows) and will be incorrect for even modest set sizes. Here is a way to do it in a vastly less limited way:

|multisearch
[ SourceIP="*" earliest=-48h@h latest=-24h@h | stats count by SourceIP | fields SourceIP | eval type="keepers" | outputcsv keepers.csv]
[ SourceIP="*" earliest=-24h@h | stats count by SourceIP | fields SourceIP | eval type="droppers" | outputcsv droppers.csv]
| search thisFieldWillNeverExist="So this will drop all events"
| appendpipe [|inputcsv keepers.csv]
| appendpipe [|inputcsv droppers.csv]
| stats values(*) AS * BY SourceIP
| search type="keepers" NOT type="droppers"