Splunk Search

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

SwatiApte
Path Finder

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.

Tags (2)
0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

Richfez
SplunkTrust
SplunkTrust

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.

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