Archive
Highlighted

splunk-db-connect_314 does not create index

Explorer

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.

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Super Champion

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

  1. 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 "MYINDEXESAPP" 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
  2. 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
  3. 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
0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Explorer

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.

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Super Champion

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

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Explorer

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

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Super Champion

(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
MYdatabaseindex/local/indexes.conf

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

 [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.

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Explorer

when I run dbxquery I get the data from DB.

| dbxquery query="SELECT * FROM \"event\".\"public\".\"allevents\" WHERE state='CLOSED' AND timereceived > ? ORDER BY timereceived DESC" connection="XXXTESTPOSTDB_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.

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Explorer

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?

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Super Champion

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

0 Karma
Highlighted

Re: splunk-db-connect_314 does not create index

Explorer

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.

0 Karma