Splunk Search

Optimizing Tweaks For Slow Queries

reverse
Contributor

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..

Tags (1)
0 Karma
1 Solution

Vijeta
Influencer

@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.

View solution in original post

Vijeta
Influencer

@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.

reverse
Contributor

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

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

0 Karma

vliggio
Communicator

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.

0 Karma

Vijeta
Influencer

@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.

0 Karma

Vijeta
Influencer

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

adonio
Ultra Champion

better use summary indexing for this use case

0 Karma

vliggio
Communicator

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.

reverse
Contributor

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

@Vijeta Could you please look into this ?

0 Karma

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

reverse
Contributor

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

0 Karma

reverse
Contributor

didnt work

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 ...