How to write a search to return hosts that have no results in a map search?

[ollie920049]

I have a search, lets say:

sourcetype=foo earliest=-1d@d | map search="search host=$host$ earliest=@d sourcetype=bar | count by host | table host, count"

This returns the opposite of what I want, all hosts from the first search that have results in the mapped search.

I'm interested in the opposite. I want hosts that return from

sourcetype=foo earliest=-1d@d

which don't have any events in the mapped search

Is there a way to achieve this?

[musskopf · Accepted solution]

I don't think you need to use map command there. Maybe try:

sourcetype=foo earliest=-1d@d [ search sourcetype=bar earliest=@d | fields host | dedup host ]
 | stats count by host

You should get all content from sourcetype=foo that have host="the hosts from the sub-search". You can easily invert the order of the main and the subsearch if necessary.

[ollie920049]

It worked with your suggestion, however, the dataset I was returning in the subsearch was huge. I tweaked it a little and ended up with the following:

sourcetype=foo earliest=-1d@d
| ... (data needed manipulating before I could filter out hosts)
| search NOT [
| metadata type=hosts sourcetype=bar

| eval day_ago=relative_time(now(), "-1d")
| where latestTime > day_ago
| fields host
| dedup host
| format ]
| table host

That performed very well. Thanks for your help.