How to show, get data from Oracle DB without indexing data

[phamxuantung]

Hello,

So basically I want to use Splunk as an BI tool, reading and getting data from our backend Oracle database without indexing it ( because our Splunk capacity can't store past 30 days and there is a requirement to see data from 100 days ago).

I want to search across that data in Splunk using SPL without indexing it, is there anyway to work around it?

[gcusello · Accepted solution]

Hi @phamxuantung,

you can execute SQL queries executing dbxquery command from the app DB-Connect but I don't hint this solution because it's very very slow.

Instead saving queries results in an index gives you best performances in your searches.

This solution could be useful only to avoid to consume license, the reason that you share of a retention of 30 days can be solved changing the retention of the index where you store dbxquery results.

If you cannot change the retention of your indexes, you can save data (after indexing9 in a Summary index hat you can maintain all the time you need.

As I said using runtime queries avoid only to consume license but has unacceptable performances.

Ciao.

Giuseppe

[phamxuantung]

Yes, dbxquery is the answer that I'm looking for, and the reason is disk space and license.

But this solution is not feasible on my end because we have DBConnect app on a separate server that only admin can access, and I don't want general user to have access to this. And I also realized after a few tests that it's not efficient at all and time consuming on a large database as you said.

I will mark your answer as Solution but also warn other user against this method.

[gcusello]

Hi @phamxuantung,

as me and @PickleRick said, the run time execution of dbxquery isn't the solution, the correct solution is to schedule the execution of the input query storing results in an index, even if this consume license.

But you have this problems also using different solutions you can find (e.g. save query results is a csv) because Splunk is very efficient if you have data in indexes.

See next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[PickleRick]

Yep, we could tell you "we told you so" 😉 but I prefer to explain.

If you have a dbconnect input, you're running it on a HF and ingest only a limited subset of records at a time. Those records get stored at indexers within your splunk infrastructure and can be efficiently searched as any other type of events.

Even if you configured dbconnect app on your searchhead(s), you would still have to call your Oracle server and fetch results with every dbxquery call (and if you have so many results that would indeed significantly impact your license usage, that would be highly inefficient).

Also - to be honest I wouldn't be too sure how dbxquery fetches the data - does it stream the events from the source query or does it have to fetch all the rows first into a temporary storage and then return it all to the calling search. If it needs some intermediate storage (wasn't working in streaming mode), could be susceptible to additional limiting factors.

So while dbxquery is there in the dbconnect app and can be used for some small use cases, it's definitely not the recommended way to make your Splunk Enterprise search head a "frontend" for the RDBMS.

[PickleRick]

Right! Forgot about the dbxquery. But you confirmed what I suspected - it's highly inefficient compared to normal indexed data.

Anyway, @phamxuantung the most important questions here is what do you want to achieve and why would you want to do it with Splunk if not using the core of Splunk's capacities.

[PickleRick]

I'm not sure (and I pretty much doubt so) that there is something already made for this.

But theoretically, you could write custom command to send a query to the DB and fetch the results. I doubt that it would be very efficient however. Especially if you wanted to fetch huge result sets.