DB dump not generating results

[theouhuios]

Hello

I am using DB Connect app to get data from a Oracle DB. Everything works fine, but when it runs this query it says no results are generated. But this query does have data in the DB. Is there something wrong with my config? Do I need to add anything else to this config? Also if I don't use sourcetype, then where does it dump the csv? Default index I guess, am I right?

[dbmon-dump://SIMPROD:UCSTDB/UCSTDB]
interval = 60000
query = select * from (select cluster_name,count(pk_id) from ccadmin.uc_phone where tk_model = 503 group by cluster_name) order by cluster_name
table = ccadmin.uc_phone
output.format = csv
sourcetype = sucstdb
output.fields = cluster_name count(pk_id)
output.timestamp = true
output.timestamp.column = RECORD_INSERTED
output.timestamp.parse.format = %s

Update : Got this error dbx5894:ERROR:DumpDatabaseMonitor - Error while executing database monitor: java.lang.NullPointerException
java.lang.NullPointerException

Update2: Made changes to inputs with output time in configs.. No change yet.

[pmdba]

I noticed that your timestamp column isn't actually included in your query; If you want Splunk to insert a timestamp, then you don't need to specify a column name at all. If you want your timestamp to come from the database query, then the output.timestamp should be "false".

Also, the output.timestamp.parse.format of "%s" is not a recognized format (they're looking for a DB date/time format filter, not a printf specification), and your are missing the output.timestamp.format parameter completely.

Finally, there is no need to wrap a query within a query in your syntax. You would be fine with the following (assuming the "record_inserted" date is being provided by Splunk):

select 
  cluster_name, 
  count(pk_id) "count_pk_id"
from ccadmin.uc_phone
where tk_model = 502 
group by cluster_name
order by cluster_name

If you need to select the date from the database, then make sure that column is first in the result set, and format it like this if the column is part of the source table:

to_char(record_inserted,'YYYY-MM-DD HH24:MI:SS') "record_inserted"

or like this if getting the current system date:

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "record_inserted"

Set the following parameters manually in the inputs.conf file where your query is defined. Make sure you specify a hostname and an index for the results:

query = select cluster_name, count(pk_id) "count_pk_id" from ccadmin.uc_phone where tk_model = 502 group by cluster_name order by cluster_name
output.table = ccadmin.uc_phone
output.fields = cluster_name count_pk_id
output.timestamp = true
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
host = hostname
index = index_name
sourcetype = sucstdb

or, if getting the timestamp from the database:

query = select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "record_inserted", cluster_name, count(pk_id) "count_pk_id" from ccadmin.uc_phone where tk_model = 502 group by sysdate, cluster_name order by cluster_name
output.table = ccadmin.uc_phone
output.fields = record_inserted cluster_name count_pk_id
output.timestamp = false
output.timestamp.column = record_inserted
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
host = hostname
index = index_name
sourcetype = sucstdb