Handling empty fields in format command

[ceedwlt]

I have a search that uses a subsearch to filter out certain kinds of logs. I'm using the format command to create the filter list for the base search, as so:

<base search> | where NOT [<subsearch> | fields <field> | format]

Everything works fine until there's a time period where there's nothing that needs filtering. Instead, format simply returns NOT () which causes the base search to fail with this message:

Error in 'where' command: The 'not' function is unsupported or undefined.

Is there a standard way to handle this situation? I've tried using fillnull with no success.

Thanks in advance.

[woodcock]

Get rid of the | where and it should work fine.

[ceedwlt]

This also works, I didn't have time to check it earlier. Thanks!

[somesoni2 · Accepted solution]

Just replace "| where" by "| search" and it should work fine.

<query> | search NOT [<sub-query> | fields <field> | format]

See this run anywhere sample search

index=_internal  earliest=-15m  | head 100 | search NOT [ search index=_internal32454  earliest=-15m | head 1| table sourcetype | makemv sourcetype | mvexpand sourcetype | format ]| stats count by sourcetype

Replace search by where to check that your error is replicated

[ceedwlt]

Perfect, that solved it - thanks!

[woodcock]

As my solution indicates, you should not need either clause.