Subsearch error

[anil1432]

Hii everyone,

Please can any one do splunk query optimization.

Phenomenon we are facing
The report count looks incorrect and we could see this error as below.
---
[subsearch]: Subsearch produced 2602757 results, truncating to maxout 2000000.

And this is my query

(host=wscreenapi3* OR host=tracking-api-release) name="RegisteredUserLog" earliest=-60d@d latest=-30d@d id!=3000000010 | fields event_id platform | fields - _raw | stats count by event_id platform | dedup event_id | rename event_id as easy_id | table easy_id platform | join type=left easy_id

[search (host=wscreenapi3* OR host=tracking-api-release) name="RegisteredUserLog" earliest=-30d@d latest=@d id!=3000000010 | fields event_id | fields - _raw | stats count by event_id | rename event_id as easy_id | table easy_id | eval retentionFlg=1]

| eval platform_str=if(platform="0","Android",if(platform="1","iPhone",if(platform="2","Web (Android)",if(platform="3","Web (iPhone)","Unknown"))))| stats count(easy_id) as basedUserCount sum(retentionFlg) as retentionUserCount by platform_str | addcoltotals labelfield=platform_str | eval customerChurnRate=(basedUserCount - retentionUserCount) / basedUserCount * 100 |eval baseUserListDateFrom = strftime(relative_time(now(),"-60d@d"), "%Y/%m/%d")." 00:00:00" |eval baseUserListDateTo = strftime(relative_time(now(),"-31d@d"), "%Y/%m/%d")." 23:59:59" |eval compareUserListDateFrom = strftime(relative_time(now(),"-30d@d"), "%Y/%m/%d") ." 00:00:00" |eval compareUserListDateTo = strftime(relative_time(now(),"-1d@d"), "%Y/%m/%d") ." 23:59:59" | table baseUserListDateFrom baseUserListDateTo compareUserListDateFrom compareUserListDateTo platform_str basedUserCount retentionUserCount customerChurnRate.

please provide me a solution

[isoutamo]

Hi

You should replace that sub search e.g. looking all those events in the same time. Then check if that event belongs to your current subsearch's timeframe and add this retentionFlg=1 otherwise set it retentionFlg=0. 

By that way it should works and actually it should works even faster with less resources.

Also try to avoid command table, use instead fields if possible as fields can run on indexer side instead of table which always run on SH level.

r. Ismo

[anil1432]

Sorry I didn't get your solution and

 what your   trying  to convey is some thing difficult to me  please explore in brief please