Getting Data In

Data input sql - latest indexed time

rahulphadnis
New Member

I created a data input from Manager. The input is a sql query that retrieves data from database.
The refresh type is full dump and happens every hour. The dump retrieves around 250+ rows (the count will vary at every refresh).The frequency is not exactly important. The most important thing is the last run (event).

What i want - i want to get the most recent event (set of records).

for e.g.
I get 210 rows at 1 pm and then 250 at 2 pm.
I want to get the most recently indexed event, that is 250 rows that were generated at 2pm.
thanks,
Rahul

0 Karma

lguinn2
Legend

Updated answer based on comment. Try this, it works across the last 60 days:

source=yoursourcename earliest=-60d@d
    | eval now =[search source=yoursourcename earliest=-60d@d | head 1 | eval now=_time | return $now] 
    | eval earlier = relative_time(now,"-5m") 
    | where _time > earlier 

The subsearch identifies the most recent event in your source data, and then returns the time of that event as "now". Splunk then searches the last 60 days of your data and eliminates all but the data that has a timestamp within 5 minutes of the most recent event.

This would return no events if there has not been a refresh in the last 60 days, but you can change that easily on the first line. It would have been cool to use the metadata command as well, but I could not get it to work in an eval statement.

You could set earliest=0 to search all time, but that would be horribly inefficient. Try to pick some maximum timespan to search: 60 days, 90 days or whatever makes sense.

0 Karma

rahulphadnis
New Member

I have a sql in the "data input" and is set to run at a particular frequency (may be 120 minutes or 1200 minutes or even 10 minutes) .The data input has a sql.

the sql typically retreives 200+ rows (may vary every time the sql gets run).

So, for e.g.

the data input sql ran at 8 am -> gave 200 rows.

the data input sql ran at 10 am -> gave 300 rows.

the data input sql ran at 12 -> gave 400 rows.

I want the set of 400 rows that was most recently run.

Depending on the most recent result set (i.e. 400 rows) -> i would like to do some group by & where and embed that search in the xml.

0 Karma

rahulphadnis
New Member

thanks for the answer.
It wont exactly work because i need the result of the last run(or indexed).
The last instance might have happened 1 hour back or 1 month back. The 1 hour that i have considered was just for example.If the last indexed even is 1 month back then those set of records are required.Also note that the result is a set of records and NOT just 1 record.
if for the last run the sql output had 200 records then doing head 1 gives only 1 record. I need 200 records that were produced/indexed in the last refresh.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...