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