Archive
Highlighted

DB Connect rising column combination of two columns

Influencer

I don't have a single column to configure as rising column in DB Connect. But I have two columns one of which is date and the other one is time. Is there a way to specify the combination of these two columns as rising column?

Adding a column to the table in the database is not an option for me at this time.

Highlighted

Re: DB Connect rising column combination of two columns

Splunk Employee
Splunk Employee

As of now this is not possible in the current version of DB Connect.

I opened an enhancement request (DBX-564) that we'll consider for a later version of the app.

View solution in original post

Highlighted

Re: DB Connect rising column combination of two columns

Influencer

Hi araitz & mgarcia_splunk, do we have any ETA on when this would be available?

0 Karma
Highlighted

Re: DB Connect rising column combination of two columns

New Member

Hello .. im having the exact same issue at the moment.. Any help would be much appreciated. Have tried to use the SQL Query to concatenate the two columns but aliases dont seem to work as well with rising columns?

0 Karma
Highlighted

Re: DB Connect rising column combination of two columns

Explorer

There is a Way to Trick Splunk with 2 Columns,

WRITE a sub query within your query

Select A, B, CD
from
(Select A,
B,
A||B as CD
from table) as query1

CD becomes your Rising Column

Highlighted

Re: DB Connect rising column combination of two columns

Influencer

In the past we tried something similar to the above solution and Splunk complained rising column CD doesn't exist. Is the above approach tested and worked? I'll test when I find some time..

Highlighted

Re: DB Connect rising column combination of two columns

Explorer

We have this working on production for one of our ingests.
(just make sure when you connect the two fields together that they are still in date format for rising column)

You cannot do a single query and just connect the fields and alias it as it will not work. somehow splunk gets confused with your alias as it is not real column.
bit if you actually query your sub query and call the field as a column.
Splunk will in this case think its a regular column and then it can us it for the rising column.

This is very similar if you would of created a view on the database and splunk is only aware of the column your calling from the view. It does not know it was made up from logic behind the scenes.

0 Karma
Highlighted

Re: DB Connect rising column combination of two columns

Communicator

Hello, I know this thread is quite old but I have a question. I tried this query with a table that has a composite key and it's worked but the query did a "table access full" so it did not use the DB indexes.

Is it possible to do something similar but using the DB indexes?

Best regards.

0 Karma
Highlighted

Re: DB Connect rising column combination of two columns

Explorer

Good question,

if your table is small then this is not a problem.
if your table is of large size and you want to eliminate full table scans or minimize footprint of your query

lets assume your using oracle for a second... most databases have similar perks.
you could also check with your DBA about optimizing your search queries as well for best performance.

But here are a couple options that come to my mind.
option 1 - You could create a materialized view on the database which only holds no more then x amount of time and then have Splunk query the materialized view with or without the index.

option 2 - You can create a "function based" index on the regular table and allow Splunk to query the tables normally with its concatenated time field used for filtering which should match the function based index created.
basically, the same way you manipulated the field that you are trying to use for filtering could be created as an index.

option 3 - if you have no access to the database or its a vendor databases and your trying to query with limited access.
in your base sub-query
- You could add a second date filter to the original field unmodified that is only date without time and force filter it to less than 24 hours of data. Your Splunk filter will full scan what comes out which is only 24 hours of data in this case instead of full table scan without further indexes available.
Example :
Select A, B, CD
from
(Select A,
B,
A||B as CD
from table Where ENTRY_DATE > (select TRUNC(sysdate)-1 from dual) ) as query1
{{ where ... rising...column....}}

0 Karma
Highlighted

Re: DB Connect rising column combination of two columns

Communicator

Hello Bruno, thank you very much for your answer.

Yes, you assumed well, I am trying to connect to an Oracle database.

I have limited access to the database, I cannot create views, index, and so on but I liked the option 2, I'm going to investigate about "function based index", maybe the DBA can do it for me.

I am also contemplating the option to do my own script to extract those data from the database, I had many problems with dbconnect (Version 1 and 2) like using DATE and VARCHAR fields for rising column and I do not have enough time.

Thanks a lot for your response it helped me a lot.

0 Karma