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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...