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.
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
That works for me except I had to use table_name and table_rows in the where clause.
Is there anything in your dbx.log file?
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