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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!