We are using many savedsearches to perform daily detection queries over huge datasets. Concretely, the anatomy of our queries is always the same. We have transactions and events related to many IDs (for example, bank account movements, and the ID would be the bank account). So, our searches login wants to find "yesterday's bank accounts that have already done N or more transactions in the past". Therefore, this would be an example of our SPL query:
@alvaromari83, since your subquery search index=transactions earliest=-1d@d latest=-0d@d | fields accountID | dedup accountID | table accountID, needs to actually run only once per day, can you save the same as daily scheduled search after midnight and push the accountIDs to a lookup file using outputlookup command?
This way you would need to run only the main search against your index and your sub search (in the base query will change to inputlookup command.
index=transactions [ | inputlookup dailyaccountIDs.csv | format maxresults=100000 ]
| ... rest of the query
This approach would for sure save some time in the subsearch filter, but keeps having the issue of using a subsearch, hitting the default 10,000 limit. So, format command with much higher limit size would be required... and then, the format parsing for the outer search it muuuch slower.
I've made some test with a csv of 10000 accountIDs, and with: