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..
@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 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.
@Vijeta
you are the MOST awesome person on this forum !!
It worked like charm .. reduced my search from 32 seconds to 0.08 seconds
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.
@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.
@reverse I have converted this to answer, please accept the correct answer. I am glad it worked for you 🙂
better use summary indexing for this use case
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.
true but I wanted to try abovementioned behaviour .. wanted to know why query is not executing
@Vijeta Could you please look into this ?
idea is to get last_executed_time and assign as earliest to the joined query to get correct value of xyz
Tried this | inputcsv TEMP [search index="AAA" earliest=last_executed_time latest=now | stats count(abc) as xyz1 | eval total = xyz+xyz1]
didnt work