Hi,
I use Splunk DB Connect to execute a search every day on my database and insert the data in a sourcetype.
In my dashboard, I have to show only the last data extraction. For example: if my query is scheduled at 12 AM and I access to my dashboard at 10, it shows yesterday's data; if I access to my dashboard at 15, it show today's data.
To do this I execute the following search, but have some performance problems. Do you have any idea what is the best practice to do this in Splunk?
sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione" | eval LatestTime=[search sourcetype="import_clone_tot_clob" | stats latest(_time) as latestTime | return $latestTime] | eval LatestTime=strftime(LatestTime, "%Y-%m-%d") | where ( strftime(_time, "%Y-%m-%d")=LatestTime) | stats count by segmento
Thanks,
Aniello
I think I understand what you're asking, but I apologize if not. What about something like this (untested).
sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione" [| tstats latest(_time) as earliest where sourcetype="import_clone_tot_clob" | eval earliest = relative_time(earliest,@d)] | stats count by segmento
So the idea being that use use a subsearch to find the latest event of the sourcetype and then use the relative_time (sp?) function to get the beginning of that day. And then by naming earliest, it should (I think) end up in your main search as just another parameter, e.g. earliest=[whatever that evaluates to].
So then your search will only return results corresponding to the latest day of data.
OK clear but if run this command it doesn't return anything:
sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione" [| tstats latest(_time) as LatestTime where sourcetype="import_clone_tot_clob"]
If I run the following command separately works correctly:
----> sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione"
---->| tstats latest(_time) as LatestTime where sourcetype="import_clone_tot_clob"
so when you run a subsearch like that (before the first pipe), Splunk is going to add the results to the search criteria. So the way you're trying to run it won't work. It will result in something like
sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione" LatestTime="[some time]"
LatestTime isn't a field in your dataset, so it won't return anything. Did you try running what I had, so that the main search would end up being this?
sourcetype="import_clone_tot_clob" REQUEST_TYPE = "Attivazione" earliest="[some time]"
Because earliest is a valid field and should then limit your search to just the latest day's worth of data. Does that make sense? You can review the search log to see how Splunk resolves those subsearches
The problem is to add this condition (earliest=[whatever that evaluates to]) I have to change every day. my search works every day and recover the last day execution
I think the tstats subsearch does just that. It will automatically find the day of the latest event and add that to your main search as the earliest parameter.