topic Re: Splunk DB Connect App not putting data in Splunk index in All Apps and Add-ons

Splunk DB Connect App not putting data in Splunk index

raidercom — Tue, 06 Aug 2013 13:22:42 GMT

Hi:
I'm trying to get SplunkDB Connect app to pull data from an Oracle database into Splunk.

Working:

Database Connection

DB Info

DB Query with the SQL statement I'm using

Not Working:
When I go to setup the query to actually pull data into a Splunk index, I cannot seem to get anything to go into the index from the query.

What I've done:

Created a new index for the data

Specify the SQL query in a new database input

Scheduled query

New index in the spunk app & new index in the dbx app

Every 1/2 hour & auto

dump (not tailed, wanted to keep it simple to start)

no source type & tried with a new sourcetype

Output formatting:

Key-Value format

Output timestamp with proper field and value & no output timestamp specified

Before restarting Splunk after specifying the new index, I've gotten this message, with different variations on the information contained within based on what I've chosen on the query:
This message has seemed to go away after rebooting Splunk

earch peer has the following message: received event for unconfigured/disabled/deleted index='dbxtest' with source='source::dbmon-dump://' host='host::' sourcetype='sourcetype::dbmon:kv' (3 missing total)

Things I have not tried:

Custom source type, don't see the point

Look-up table, again, don't see the point

Maybe I'm looking at this the wrong way, but I'm trying to test whether there is data in the index by searching for index=dbxtest range:all time from within the splunk dbx app

Thank you for any information/assistance you could provide.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Tue, 06 Aug 2013 13:42:39 GMT

Try to configure your oracle input like this:

Input Type: Tail

Database: Select the database

SQL Query:

SELECT * FROM enterqueryname

{{WHERE $rising_column$ > ?}} Note: enter this statement exactly as written - no subsitutes.

Rising Column: enter a timestamp column in the correct case

sourcetype: dbmon:kv

Index: enteryourindexname

Host field value: enterhostname

Output timestamp: check, and enter timestamp column name entered above

Timestamp format: yyyy-MMM-dd HH:mm:ss.SSS

Interval: 0/2 * * * * Note: that is cron for every two minutes, good for testing.

If you thing you have everything right, and it is still not working, try restarting splunk.

Monitor the splunkd logs for problems.

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Tue, 06 Aug 2013 20:22:56 GMT

Just testing my latest iteration of the database input with your suggestions.

When you say enterqueryname, does that have to be a saved query within Oracle?
If I have a full SQL query to paste in there, I'd imagine that I can't just paste that in there, as the query would be out of order.

The reason I wanted to get a straight dump working first was for simplicity.
-Jeff

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Tue, 06 Aug 2013 20:29:52 GMT

What we do is create and name the query on the Oracle side, and just call that. The DBX query interpreter can be a bit picky.
What you want to do works, but for simplicities sake keep the query simple. You can also test the query with the DBX query window.
Just remember to include {{WHERE $rising_column$ > ?}} at the end.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Tue, 06 Aug 2013 20:31:46 GMT

Just remember to include {{WHERE $rising_column$ > ?}} at the end. - when you configure that database input that is. Don't use when you're testing the query.

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Mon, 28 Sep 2020 14:31:06 GMT

Hmm...
splunkd.log seems to be littered with these messages now.

08-06-2013 16:30:21.575 -0400 WARN DateParserVerbose - A possible timestamp match (Tue Aug 2 18:59:59 2016) is outside of the acceptable time window. If this timestamp is correct, consider adjusting MAX_DAYS_AGO and MAX_DAYS_HENCE. Context: source::dbmon-dump://DMART/|host::|dbmon:kv|

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Mon, 28 Sep 2020 14:31:09 GMT

I've added MAX_DAYS_AGO = 999 to the props.conf of the dbx app $splunk/etc/apps/dbx/local/props.conf, and restarted the splunk server.

The part I don't understand, is that the dates it is referencing are a couple days ago, and should be within the default MAX_DAYS_AGO for Splunk. I don't understand why it is rejecting events that are recent.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Mon, 28 Sep 2020 14:31:13 GMT

The timestamp is not being read properly. Sometimes folks have this problem, and sometimes they don't. Try using to_char(log_date,'YYYY-MM-DD HH24:MI:SS') in your query to convert the timestamp. We did this on the oracle side, but you should be able to get it to work on the dbx side. Also, make sure the timestamp column is listed first in your query.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Tue, 06 Aug 2013 20:54:53 GMT

Your timestamp is being read as 2016, but don't worry about correcting for it with max days hence because once you get it right it won't matter.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Tue, 06 Aug 2013 21:01:05 GMT

Here is some info regarding output.timestamp.parse.format http://splunk-base.splunk.com/answers/71485/splunk-db-connect-timestamp-not-working, but I did not need to use it.
More words of wisdom: always access the dbx input configuration from the dbx app. If you go from another app to the manager to the db inputs then the inputs.conf will get written somewhere else, and you will either not be able to find them, or worse you will end up with conflicting input.conf files.

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Wed, 07 Aug 2013 13:59:09 GMT

OK, just testing the changes.
Thank you VERY much for your assistance!
-Jeff

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Wed, 07 Aug 2013 19:18:12 GMT

Hmm... The query seems to be running successfully (the Oracle date conversion you provided removed the invalid date that Splunk was trying to use), but same thing, no output in the Splunk index. Is there a reason you are suggesting to use tail rather than a full dump? I figured for testing a dump would be easiest.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Wed, 07 Aug 2013 19:22:24 GMT

When you say the 'query' do you mean from the query tab, or do you mean the database input.
Dump is easier, maybe a little too easy.

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Thu, 08 Aug 2013 13:55:42 GMT

From the database input tab.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Thu, 08 Aug 2013 14:04:19 GMT

Does the dbx.log do you see something like:
dbmon-tail://database/input, resultCount=integer, success=true ?
If so then the data is in your index - try a search for all time.
If you don't see that entry in your log, then what are you seeing?

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Thu, 08 Aug 2013 14:59:09 GMT

I am seeing that entry in the log:

2013-08-08 10:30:05.568 dbx2490:INFO:DumpDatabaseMonitor - Database monitor=[dbmon-dump://] finished with status=true resultCount=226391 in duration=5567 ms
2013-08-08 10:30:05.568 monsch1:INFO:Scheduler - Execution of input=[dbmon-dump://] finished in duration=5567 ms with resultCount=226391 success=true continueMonitoring=true

When I go to Splunk Web, and go to app\Splunk DB Connect, then go to search, and search for
index= for all time, Splunk shows nothing. Am I doing my query wrong then?
Thanks
-Jeff

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Thu, 08 Aug 2013 15:13:54 GMT

Your data is there, 226391 records of it. The question is where.
Verify your index is correct, and that you don't have a conflicting inputs.conf.
Search from now to a month in the future (I don't think all time does the future).

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Thu, 08 Aug 2013 19:39:19 GMT

Tested from 2007->2014 search for the specified index. Finds nothing.

I tried with another, new index, same thing. I have to admit that I do not get this...

there is only 1 inputs.conf in the dbx app, and all of the references to indexes in it are pointing to a single index, the correct index.

Re: Splunk DB Connect App not putting data in Splunk index

lukejadamec — Thu, 08 Aug 2013 20:09:34 GMT

No doubt dbx can be frustrating.
When I said conflicting inputs.conf I meant in different app directories. If you go to Manager > Inputs from the Launcher screen and save an input for dbx it will save the input stanza in launcher/local/inputs.conf, if you get there from search, it will save it in search/local/inputs.conf. I had many copies of my input stanzas when I started with dbx.

Re: Splunk DB Connect App not putting data in Splunk index

raidercom — Fri, 09 Aug 2013 19:31:51 GMT

The inputs.conf files are pushed by puppet to the splunk server, and the dbx inputs.conf is not. None of the other inputs.conf are able to contain something related to the dbx files unless it is added to the puppet template. The only inputs.conf that has dbx related stuff in it is the one in the dbx directory\apps\dbx\local\inputs.conf
-Jeff