All Apps and Add-ons

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

Path Finder

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"???

0 Karma

Path Finder

Try to modify query:

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

Influencer

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

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

Path Finder

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"

0 Karma

Contributor

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!