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"???
Try to modify query:
select *
from (Select * from table1
Union all
Select * from table2
Union all
Select * from table3
)
where date > last 1 hour
Make a view of the union of all three tables, then
Select * from view where {{ $rising_column$ > ?}}
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"
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