Streamed search execute failed because: Error in 'lookup' command: Cannot find the destination field '_delete' in the lookup table 'identity_lookup_expanded'

Applicability

Description

The error below can occur if the KVstore collection size exceeds the value of max_memtable_bytes:

Streamed search execute failed because: Error in 'lookup' command: Cannot find the destination field '_delete' in the lookup table 'identity_lookup_expanded'

This is because if it exceeds max_memtable_bytes, internal fields such as _delete are not indexed to disk.

However, it can also occur due to misconfigurations in the automatic lookup for the particular lookup mentioned in the error.

This knowledge base article will address the latter issue.

Investigation

1. Check the size of the KVStore collection

| rest /services/server/introspection/kvstore/collectionstats
| mvexpand data
| spath input=data
| rex field=ns "(?<App>.*)\.(?<Collection>.*)"
| eval dbsize=round(size/1024/1024, 2)
| eval indexsize=round(totalIndexSize/1024/1024, 2)
| stats first(count) AS "Number of Objects" first(nindexes) AS Accelerations first(indexsize) AS "Acceleration Size (MB)" first(dbsize) AS "Collection Size (MB)" by App, Collection
| search Collection="identities_expanded"

2. If the size is lower than the max_memtable_bytes, check props.conf for any sourcetype that uses the lookup as an automatic lookup and verify that it is configured correctly.

3. The sample below shows a misconfiguration, where OUTPUTNEW does not specify any fields. As a result, it will try to output all fields, including _delete.

etc/apps/search/local/props.conf LOOKUP-User_name = identity_lookup_expanded identity AS User_Name OUTPUTNEW

Resolution

1. Configure the OUTPUT fields in the Automatic Lookups settings via UI if required. 

Related Links

Avoid creating automatic lookup reference cycle