I am trying to execute this search but 90% of the times this search does not complete and returns incomplete results. The count is different for example this search will return 4,63,000 events, 4,20,000 or 3,60,000 etc results. I believe that my search is a bit heavy. Can anyone please help me to optimize this search.
let me understand, is the problem that you continously have different results or that the search doesn't complete its run?
For the different number of results, try to use a defined time period without latest=now, in other words, try using yesterday or a past hour (earliest=-2h@h latest=-h@h).
if the search isn't complete, check using the job inspector and the Monitoring Console if the search has problems.
A basic question: have you sufficient resources for your Splunk: 12 CPUs, 12 GB RAM, storage with at least 800 IOPS?
Are you running in a clustered index environment? If so, the rirst thing to do is to replace 'table' with 'fields', otherwise the mvexpand will be running on the search head. If you use fields, it will run on the indexer.
Doing mvexpand followed by dedup and then stats is going to pump memory, first creating a load of events then removing them.
With 'finding', what's the relationship to sid - is it 1:1? If so, you don't need dedup.
Check the job inspector to see where the time is going.
If you're getting variable result counts, but using the identical time window, then it's probably mvexpand and memory and perhaps running on the SH.
Try to minimise the data volume before you mvexpand - is there a way to do what you're trying to do with stats without expanding the data?
What is the cardinality of all the values() fields you are collecting - that will also consume a lot of memory if high.