topic Re: Optimizing Tweaks For Slow Queries in Splunk Search

Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 16:35:44 GMT

I have a simple query

| stats count(abc) as xyz

Now since it is taking too much time- i decided to tweak it a bit ..
For the time it has run already i am saving it to a CSV with 2 values...

last_executed_time and xyz

Lets say outputcsv ran @10am.
Now it is 10:30 am.. I want to take data from csv till 10 am and since 10am till now .. add the xyzs ...
Please help..

Re: Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 16:37:26 GMT

idea is to get last_executed_time  and assign as earliest to the joined query to get correct value of xyz

Re: Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 16:42:38 GMT

Tried this | inputcsv TEMP [search index="AAA" earliest=last_executed_time latest=now | stats count(abc) as xyz1 | eval total = xyz+xyz1]

Re: Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 16:45:30 GMT

didnt work

Re: Optimizing Tweaks For Slow Queries

vliggio — Thu, 18 Jul 2019 16:54:03 GMT

What are you trying to figure out? | stats count is not going to be particularly efficient as it's a transforming command and must run on the search head and so is relatively inefficient as all the data has to come back to that single search head (especially if you have a ton of data and a lot of indexers). You might be better off looking into the tstats command, which will be orders of magnitude faster than stats (it looks at the index metadata (tsidx files), not the actual data).

See https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/tstats for info on tstats and how to format that query.

Refer to https://docs.splunk.com/Documentation/Splunk/latest/Search/Typesofcommands for more info on types of commands and where they run.

Re: Optimizing Tweaks For Slow Queries

adonio — Thu, 18 Jul 2019 16:54:24 GMT

better use summary indexing for this use case

Re: Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 17:26:27 GMT

true but I wanted to try abovementioned behaviour .. wanted to know why query is not executing

@Vijeta Could you please look into this ?

Re: Optimizing Tweaks For Slow Queries

Vijeta — Thu, 18 Jul 2019 17:51:38 GMT

@reverse Try this,

 index=<your index> [|inputlookup TEMP| eval earliest=strptime(last_executed_time,"%m/%d/%Y %H:%M:%S")| return earliest]| stats count(abc) as xyz | append[|inputlookup TEMP]| stats sum(xyz) as total

Please note the time format you convert from should be same in your lookup table.

Re: Optimizing Tweaks For Slow Queries

reverse — Thu, 18 Jul 2019 18:19:48 GMT

@Vijeta
you are the MOST awesome person on this forum !!

It worked like charm .. reduced my search from 32 seconds to 0.08 seconds

Re: Optimizing Tweaks For Slow Queries

Vijeta — Thu, 18 Jul 2019 18:20:41 GMT

@reverse I have converted this to answer, please accept the correct answer. I am glad it worked for you 🙂

Re: Optimizing Tweaks For Slow Queries

vliggio — Thu, 18 Jul 2019 19:16:09 GMT

Note that while this solves the query about how to do the lookup, it's still a horrible way of doing an event count, especially in large environments. I ran an environment that got conservatively 4 billion events a day (still a relatively small environment compared to some of the massive ones out there). People doing stats count on their data caused so many issues (when you have 100's of users doing bad queries, it adds up). You really should learn tstats if you're doing any sort of count of events.

Re: Optimizing Tweaks For Slow Queries

Vijeta — Thu, 18 Jul 2019 19:36:42 GMT

@viggio I agree to you, using tstats is a better option instead of creating a lookup and doing stats. But I am not sure what exactly the use case id, will he be using stats count by fields in actual query or will be performing count on a field which is not part of metadata.