topic Is it possible to query a temp table using Splunk DB Connect? in Splunk Search

Is it possible to query a temp table using Splunk DB Connect?

hxa27 — Mon, 28 Sep 2020 17:53:28 GMT

Hi,

I am trying to use Splunk to monitor my process by using the db connect. My problem is I am using the following query:
Select *
from My_table
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
Thanks

Re: Is it possible to query a temp table using Splunk DB Connect?

pmdba — Wed, 15 Oct 2014 21:25:14 GMT

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.

Re: Is it possible to query a temp table using Splunk DB Connect?

hxa27 — Wed, 15 Oct 2014 22:05:42 GMT

Thanks for the comment that helps somehow. Do you know if there is a way I can run multiple queries at the same time in db connect ?

Re: Is it possible to query a temp table using Splunk DB Connect?

jcoates_splunk — Wed, 15 Oct 2014 22:56:57 GMT

Hi, if you set up dbmon input stanzas you can schedule as you need.

Re: Is it possible to query a temp table using Splunk DB Connect?

pmdba — Thu, 16 Oct 2014 15:40:42 GMT

I believe each query must be scheduled and run as a separate input. There is no way to post multiple queries as part of the same input.

Re: Is it possible to query a temp table using Splunk DB Connect?

hxa27 — Fri, 17 Oct 2014 14:27:22 GMT

So, what would be a better option to run multiple queries ?

Re: Is it possible to query a temp table using Splunk DB Connect?

pmdba — Fri, 17 Oct 2014 15:21:35 GMT

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.