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.
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
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.
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.
Your question consists of multiple queries. Will try one by one
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.
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
(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
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.