Archive

Splunk DB Connect - dbmon-tail input not working as expected

Explorer

My DB Connect is working fine. Java bridge runs ok.
But the dbmon-tail input do not work continuously.

When I checked it in more detail, I found that the state.xml for this input in "$SPLUNK_HOME\var\lib\splunk\persistentstorage\dbx\" is getting corrupted.
I am using the a char(24) field as the rising column. This char field actually contains the datetime. And in my input settings I parse the rising column field to datetime format. Following are my input settings -

[dbmon-tail://PerfMonitor/CData]
host = testdb
index = main
output.format = kv
output.timestamp = 1
output.timestamp.column = CounterDateTime
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
query = Select GUID\r\n ,CounterID\r\n ,RecordIndex\r\n ,CounterDateTime\r\n ,CounterValue\r\n ,FirstValueA\r\n ,FirstValueB\r\n ,SecondValueA\r\n ,SecondValueB\r\n ,MultiCount from CounterData {{WHERE $rising_column$ > ?}}
sourcetype = mssql
tail.rising.column = CounterDateTime
table = CData
interval = 300

Now when i ran my splunk service the first time, it fetched all data from this table and indexed it. But after indexing this batch, it stopped as the state.xml in persistent storage had appended some special characters to the rising column value. As shown below -

<value class="string">2014-12-15 15:42:02.414&#x0;</value>

Then I have to remove those special chars and restart the service and it indexes then the next data again untill the last entry of the table. And again it updates the value with latest field value and appends the special chars. so the indexing stops again. So, infact the tailed input is actually working for me as the dumped input.
Any idea where am I doing it wrong ?

0 Karma

Splunk Employee
Splunk Employee

looks like you're getting a null character appended that you'd need to remove in SQL.

0 Karma

Explorer

Thanks for the suggestion mate. But -
1. How do i confirm that there is a null character appended ?
2. Is there a way I can handle this in splunk because the changes in database might not be in my hands but in splunk

0 Karma

Splunk Employee
Splunk Employee

1) We can see the null character in your sample.
2) you don't need to be the db admin, you just need to alter your SQL query. The details of doing that can be simple or complex depending on the database in question. Googling for "sql remove null characters" looks like a good start.

0 Karma