splunk dbconnect query issue

[bhavye20]

Hi

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.

[aelliott]

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

[richgalloway]

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

[richgalloway]

Is there anything in your dbx.log file?

[MuS]

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