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
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
Sorry I didn't get your solution and
what your trying to convey is some thing difficult to me please explore in brief please