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!

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