Splunk Search

query is reaching memory limit and auto-finalizing, is there a way to optimize the query and prevent this from happening?

tareddy
Explorer

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

Tags (1)
0 Karma

sideview
SplunkTrust
SplunkTrust

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

tareddy
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...