topic Re: Splunk DB Connect: How does the rising column work for the "union all" in this query? in All Apps and Add-ons

Splunk DB Connect: How does the rising column work for the "union all" in this query?

ash2l — Wed, 18 Jan 2017 20:21:49 GMT

How does the rising column work for the "union all" in this query?

Example:

Select * from table1 where date > last 1 hour 
Union all
Select * from table2 where date > last 1 hour
Union all
Select * from table3 where date > last 1 hour

Rising column: date

In the above case, Splunk is allowing the rising column only at one of the query. So if I use the rising column with the first query (like below), I am receiving full and duplicate data for table2 and table3.

Select * from table1 where date > last 1 hour {{AND $rising_column$ > ?}}
Union all
Select * from table2 where date > last 1 hour
Union all
Select * from table3 where date > last 1 hour

Currently I have to create 3 separate inputs for this so was wondering if there is an easy way to fix this and have all queries accommodated into single input using "union all"???

Re: Splunk DB Connect: How does the rising column work for the "union all" in this query?

jplumsdaine22 — Mon, 30 Jan 2017 13:32:46 GMT

Make a view of the union of all three tables, then

 Select * from view  where  {{ $rising_column$ > ?}}

Re: Splunk DB Connect: How does the rising column work for the "union all" in this query?

ash2l — Wed, 08 Feb 2017 05:23:30 GMT

We dont have access to create a view in the production database. Do you think of any other option? If not, I guess we can have separate data inputs for each queries instead of using "union all"

Re: Splunk DB Connect: How does the rising column work for the "union all" in this query?

davebrooking — Wed, 08 Feb 2017 09:11:05 GMT

I don't know if the recent versions of the ODBC drivers used in DB Connect support Common Table Expressions, a form of pseudo views, but here are a couple of articles explaining how to use them

Oracle Common Table Expression
SQL Server Common Table Expression

I seem to recall I had difficulties using them in earlier versions of DB Connect

Dave

Re: Splunk DB Connect: How does the rising column work for the "union all" in this query?

splunk6161 — Wed, 29 Jan 2020 13:42:49 GMT

Try to modify query:

select *
from (Select * from table1 
    Union all
    Select * from table2
    Union all
    Select * from table3
    )
where date > last 1 hour