I am trying to use Splunk to monitor my process by using the db connect. My problem is I am using the following query:
where proc >= today-2
and user_id= ' '
into temp My_temp_table with no log;
which has a temp table to insert records into. Is it possible to query this? whenever I query this it gives me this error
"command="dbquery", A database error occurred: Method only for queries" or this if I run the whole query
"command="dbquery", A database error occurred: The specified table (My_temp_table) is not in the database."
Also, at the end of the query, I am dropping the temp table
Note: I am running this against informix database
Any help is appreciated
I'm not as experienced with Informix (it's been a few years), but in general DB Connect cannot perform DDL types of operations (that would create/drop objects). Basic SELECT statements are the norm; if the database connection is not defined as read-only, you may also be able to perform DML (insert/update/delete) operations.
Also, just an observation but in general I have not had much luck with Splunk indexing queries that aren't fully qualified. It is generally considered bad programming practice to use "select *"; it is preferred to select only the columns you need, by name. With Splunk it is often necessary to detail timestamp columns and rising value columns by name in the input definition, regardless.
Each query provides a unique set of data, presumably with different fields/columns, so I'm not sure you want to combine multiple queries into a single input. Defining multiple inputs allows you flexibility in scheduling and in determining other parameters like source type and field names.
If you really need to combine output from multiple queries that would have common field names, etc., you could use a UNION type of construct to combine their data sets into a single set for return to Splunk. I'm not sure what the exact Informix syntax would be, but perhaps something like this:
select column_1, column_2 from table_1 where column_1 = x union select column_1, column_2 from table_2 where column_2 = y
or like this:
with query_1 as (select column_1, column_2 from table_1 where column_1 = x union select column_1, column_2 from table_2 where column_2 = y) select column_1, column_2 from query_1
or perhaps combine the queries into a view in the database and select from the view in your input.