DBConnect - Is there a way to limit first-time data read volume?

[SwatiApte]

Hi,

We are using DBConnect version 1.1.6 to fetch data from an Oracle Database. We have a huge amount of data present in the Database table, let's say past 1 year's data. When Splunk executes the SQL query for the first time, it fetches ALL the past 1 year's data and indexes within Splunk. But we want to be able to restrict the data, to fetch and index only a subset of the entire data, let's say past 1 week's data.

Also, if we want to apply a filter condition while fetching the data, for eg. adding a 'where' clause to the query (this will have to be added within the curly braces along with the Rising Column condition), this where condition does not apply to the data fetched by the query that Splunk executes for the first time.

Could you please suggest a way of restricting the data, based on the timestamp, or based on a certain 'where' clause.

[Richfez · Accepted solution]

Yes.

I'm not sure of the exact Oracle syntax, but you'll want to create a specific query. Here's what I did for a similar circumstance.

select myfield1, myfield2...
from mytable where ID>2075 {{ AND $rising_column$ > ?}}

I have used ID as my rising column as well in this particular example. What this does is during the INITIAL data load, everything outside the {{... }} is run. So that first pass will run the query to be ... where ID>2017. You could use ...where somedate >= 6/1/2015 - this is where you'll have to use Oracle-specific syntax.

Then, once the initial data is in, subsequent requests will include the {{AND $rising_column$ > ?}} which will just keep grabbing stuff bigger/newer/larger (as the case may be) than the last value of your rising column.

So in my case above, first pass it'll read all from the table where the ID is greater than 2075 (ignoring older rows), then from then on it'll just keep track of ID and keep reading in the new records.