Outer join on lookup and subsearch timeout

[ferofox]

Hi all,

I am running into a timeout problem on one of my searches and now wanr to find out if there maybe is a better solution to my problem.

Task:
Take all values from a lookup table and search for their last appearance in the logs, but keep the remaining values as well.

Lookup-Table:
id,operator
123,OperatorA
234,OperatorB
345,OperatorC
[...]

Search:
sourcetype=id_log | fields id,source,timestamp | dedup id

Combined:
| inputlookup operator_lookup | join type=outer id [search sourcetype=id_log | fields id,source,timestamp | dedup id]
This search is running into a timeout issue:
[subsearch]: Search auto-finalized after time limit (60 seconds) reached.

Is there any other way to speed up the search, or at least to increase the timeout-value? Adding maxtime=600 to the join command does not work.

[woodcock · Accepted solution]

I think this will do it:

sourcetype=id_log | dedup id | fields id,source,timestamp  | eval type=search | append [ | inputlookup operator_lookup | eval type=lookup ] | stats values(*) AS * dc(type) AS numTypes by id | where type="lookup" OR numTypes=2

If my logic is wrong in the where part, just fix it to match the type of join you are trying to do.

[ferofox]

Allright, thank you! This totally did the trick! I changed the where part a bit, and now it gives the exact results I expected.