Splunk Search

splunk dbconnect query issue

New Member

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.

Tags (3)
0 Karma

Motivator

Try this:
SELECT tablename as name, tablerows as rows FROM informationschema.tables
where table
name like 'smppqueue%' or tablename like 'smppmessagedetailstemp%' and table_rows > 10

0 Karma

SplunkTrust
SplunkTrust

That works for me except I had to use tablename and tablerows in the where clause.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

Is there anything in your dbx.log file?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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