Monitoring Splunk

splunk-db-connect_314 does not create index

patelmc
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

patelmc
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

koshyk
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

patelmc
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

patelmc
Explorer

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.

0 Karma

koshyk
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 "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
  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

patelmc
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

koshyk
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

patelmc
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

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...