How to tell Splunk to collect results into an index only after a certain time?

[sjanwity]

I use dbconnect to push some database data into splunk. The data contains a timestamp of when it was updated. I want to create a scheduled collect where each day the new entries from the previous day are collected into a Splunk index.

I think he first part is easy; it's just running a scheduled report each day at, say, 8am. But how do I tell splunk to only get results from the previous day? I already do some logic on my SQL query (namely, where UPDATE_TIME >= sysdate -1 but I want to be doubly sure as I don't want duplicate results appearing in my splunk index.

I've also been told that the SQL command isn't 100% precise, as latency or other network issues could result in a delay in the command being executed, leading to some records inserted during the poll time to be missed. So I would prefer to create a general SQL query and then use splunk to filter.

[grijhwani]

In search terms @d means the most recent preceding midnight, and you can use modifiers to produce ranges, so for instance if you set the end-date of your search as @d and the start-date as @d-1d you will get the previous day from midnight to midnight. You could run from 06:00 to 06:00 by using @d+6h and @d-18h. You use the earliest=... and latest=... terms to specify a range within the search.

I'm not sure if this helps with your query, though, unless you use a Splunk db query to generate your indexable results.

[sjanwity]

this doesn't seem to work, probably because results from dbconnect natively does not understand the time field, even if you explicity set _time to equal a column...