All Apps and Add-ons

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

aniello_cerrato
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

maciep
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

aniello_cerrato
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

maciep
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

aniello_cerrato
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

maciep
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
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...