topic Re: splunk-db-connect_314 does not create index in Monitoring Splunk

splunk-db-connect_314 does not create index

patelmc — Mon, 13 May 2019 18:07:13 GMT

Hi, I am running splunk 7.2.0 single server instance running on RHEL 6.8. I wanted to get data from one of our postgresql DB, so installed splunk-db-connect_314 on this splunk single server. During configuration I was able to see the data from the sql query and I did not see any error. However, for some reason index has not been created for this data. Also, what sourcetype need to use for postgressql DB query data? During configuration it gives few choices but none seems to be appropriate. So, I created new sourcetype but it did help either getting data into splunk.
Any help will be appreciated.

Re: splunk-db-connect_314 does not create index

koshyk — Wed, 30 Sep 2020 00:33:30 GMT

Your question consists of multiple queries. Will try one by one

It is good practice for Add-on's NOT to create index of its own. This is because lot of organisation have naming standards for indexes and permissions etc. Also in clustered environment, your DBconnect installed server is not normally your indexer. So best thing for you to do is to create an app "MY_INDEXES_APP" and create all indexes.conf with your company standards and retention policies etc. Then collect database data using DBconnect using a Heavy Forwarder and just redirect to your specific index
postgressql sourcetype => The only close addon I could find is https://splunkbase.splunk.com/app/1732/ . Please download and see the sourcetype within it and check if the extractions fit your purpose. Else, please create a sourcetype of your own and extract fields accordingly. Please check how you build http://dev.splunk.com/view/SP-CAAAFD7
It might be good idea to check if you are retrieving data from the database. Run a simple simulation in DBconnect GUI to see it can fetch data

Re: splunk-db-connect_314 does not create index

patelmc — Mon, 13 May 2019 18:33:38 GMT

Hi Koshyk,
I followed documentation to installed db connect on single server. This is a test environment and we are using only one splunk server to test. I provided index name during configuration and I believe it should have created the index with that name. when I run query from DBConnect GUI, I get the data from DB.

Re: splunk-db-connect_314 does not create index

patelmc — Wed, 30 Sep 2020 00:33:38 GMT

when I run dbxquery I get the data from DB.

| dbxquery query="SELECT * FROM \"event\".\"public\".\"all_events\" WHERE state='CLOSED' AND time_received > ? ORDER BY time_received DESC" connection="XXX_TEST_POST_DB_Connection" maxrows=1000 params="\"2018-01-01 00:00:00.000\"" paramstype="\"93\"" timeout=30

But when I look for index it does not exist and search using that index does not return any rows.

Re: splunk-db-connect_314 does not create index

koshyk — Mon, 13 May 2019 20:06:49 GMT

what's the index name you using?

please do a
/opt/splunk/bin/splunk cmd btool list indexes --debug > /tmp/indexes.btool.txt

Please select the stanza for your index and paste it here

Re: splunk-db-connect_314 does not create index

patelmc — Mon, 13 May 2019 20:24:02 GMT

I ran /opt/splunk/bin/splunk btool indexes list with and without --debug option but I do not see stanza for the index I used during DB connect config.
The config file its using is /opt/splunk/etc/system/default/indexes.conf

Re: splunk-db-connect_314 does not create index

patelmc — Mon, 13 May 2019 20:39:46 GMT

I created index manually and used that index name in metadata and now I see data under that index.

However, I still have a question about which sourcetype to use for postgresql DB?

Re: splunk-db-connect_314 does not create index

koshyk — Wed, 30 Sep 2020 00:33:53 GMT

(you should never Ever amend /opt/splunk/etc/system/default configs under ANY circumstances)
Since you can't see, it means the index is not present

Please create an app and create indexes.conf
MY_database_index/local/indexes.conf

Paste below entries into it & Restart your server (assuming your index is my_database_index)

 [my_database_index]
 datatype = metric
  homePath   = volume:home/my_database_index/db
  coldPath = volume:cold/my_database_index/colddb
  thawedPath = volume:cold/my_database_index/thaweddb
  maxTotalDataSizeMB = 87600
  # 1 years x 365 days * 24 hrs * 60mins * 60secs days total retention
  frozenTimePeriodInSecs = 31536000
  repFactor = auto

Ensure your DBconnect put this into this index
and RESTART your server.

Re: splunk-db-connect_314 does not create index

koshyk — Tue, 14 May 2019 09:50:58 GMT

I thought I had posted the answer in the main reply,

postgressql sourcetype => The only close addon I could find is https://splunkbase.splunk.com/app/1732/ . Please download and see the sourcetype within it and check if the extractions fit your purpose. Else, please create a sourcetype of your own and extract fields accordingly. Please check how you build http://dev.splunk.com/view/SP-CAAAFD7

Re: splunk-db-connect_314 does not create index

patelmc — Tue, 14 May 2019 12:56:37 GMT

This addon is to monitor postgresql DB which includes log files. The DB connect is actually getting business data from DB tables so this addon would not help.
I created a new sourcetype and it seems to be working now.