Query : index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 )
| stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by ABC | fillnull PQR value="NULL"
| search XYZ=DEF | timechart span=1d count by PQR
If this search,
DEF <index and sourcetype and service terms to narrow down to just DEF events> | stats count by ABC
returns relatively quickly, and with only a few hundred or thousand rows,
and especially if the number of events coming out of your main stats clause is being radically filtered down by the | search XYZ="def"
clause,
then you should absolutely switch this to a subsearch use case.
index=INDEXA earliest=-7d@d latest=@d sourcetype=GHI "service=randomservice" (api_name=API1 OR api_name=API2 ) [DEF <index and sourcetype and service terms to narrow down to just DEF events> | fields ABC | dedup ABC]
| stats first(_time) as _time, values(XYZ) AS XYZ, values(PQR) AS PQR by ABC | fillnull PQR value="NULL"
| timechart span=1d count by PQR
Thanks sideview, this answer greatly improved the search/storage efficiency. However your query is working accurately only for a maximum time range of about 24 hours. I need to search over a period of a week. I am getting "[subsearch]: Search auto-finalized after time limit (60 seconds) reached" and consequently i am not able to obtain the accurate results. Do you have any suggestions or any other way to optimize the query?