topic Re: splunk dbconnect query issue in Splunk Search

splunk dbconnect query issue

bhavye20 — Thu, 20 Mar 2014 11:49:02 GMT

I m working on monitoring some mysql and mssql databases. All I need is a list of tables with number of rows in each one of them. I fired a database query in the database itself for the same purpose and it worked fine. When I fire the same query in splunk it shows syntactical errors.

show table status where name like 'smpp_queue%' or name like 'smppmessagedetailstemp%' and rows > 10

The above query doesnt work.

Please help.

Re: splunk dbconnect query issue

MuS — Thu, 20 Mar 2014 13:18:33 GMT

Hi bhavye20,

did you enter this directly in the Splunk search field? If so, this will not work this way.
If you want to query a DB with sql you need to do it like this:

| dbquery "YourSQLDataBaseConnectionNameYouHaveConfiguredInDBConnect" "show table status where name like 'smpp_queue%' or name like 'smppmessagedetailstemp%' and rows > 10"

For more information please take a look at the docs of DBX commands

hope this helps ...

cheers, MuS

Re: splunk dbconnect query issue

richgalloway — Thu, 20 Mar 2014 14:51:50 GMT

Is there anything in your dbx.log file?

Re: splunk dbconnect query issue

aelliott — Mon, 28 Sep 2020 16:11:47 GMT

Try this:
SELECT table_name as name, table_rows as rows FROM information_schema.tables
where table_name like 'smpp_queue%' or table_name like 'smppmessagedetailstemp%' and table_rows > 10

Re: splunk dbconnect query issue

richgalloway — Mon, 28 Sep 2020 16:11:49 GMT

That works for me except I had to use table_name and table_rows in the where clause.