All Apps and Add-ons

Splunk DB Connect: In a dashboard, what is the best way to reveal when the last data was indexed?

Path Finder

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

0 Karma

Champion

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.

0 Karma

Path Finder

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"

0 Karma

Champion

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

0 Karma

Path Finder

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

0 Karma

Champion

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!