Data is not being imported into the assigned index after migrating from Oracle to PostgreSQL.

[logalsplunk007]

Until now I was importing data to Splunk from Oracle. I have migrated from Oracle to Postgresql so I will no longer use Oracle and now need to import the data from Postgresql.

I have configured the JDBC driver, set the connection settings and credentials, and from Splunk DB connect I have created a new input.

When I click "Execute query" the data is displayed correctly, so the connection to PostgreSQL should be correct. The problem is when I finish creating the input, I schedule it and assign it an index to which it should dump the data. There is no error (or at least I can't find it) in the input health/input performance section, the data is simply not imported into the assigned index.

What could I do to solve this? I need to be able to import data from PostgreSQL.

[isoutamo]

Hi

can you give the basic information of your environment?

Like all OSs, Splunk , dbx version, standalone / distributed environment, Java, driver, postgresql etc. versions.

Also inputs.conf etc. is good starting point for Community to help you.

r. Ismo

[logalsplunk007]

Thank you for answering so quickly,
I enclose the information you ask for:

Splunk DB Connect

splunk_app_db_connect

3.3.1

Driver Postgresql

Yes

42.2

OS version: 18.04.5 LTS (GNU/Linux 4.15.0-112-generic x86_64)

Splunk standalone environment, version Splunk 8.0.2 (build a7f645ddaf91)

Java version:

openjdk version "1.8.0_265"
OpenJDK Runtime Environment (build 1.8.0_265-8u265-b01-0ubuntu2~18.04-b01)
OpenJDK 64-Bit Server VM (build 25.265-b01, mixed mode)

cat inputs.conf
# Version 8.0.2
# DO NOT EDIT THIS FILE!
# Changes to default files will be lost on update and are difficult to
# manage and support.
#
# Please make any changes to system defaults by overriding them in
# apps or $SPLUNK_HOME/etc/system/local
# (See "Configuration file precedence" in the web documentation).
#
# To override a specific setting, copy the name of the stanza and
# setting to the file where you wish to override it.
#
# This file contains possible attributes and values you can use to
# configure inputs, distributed inputs and file system monitoring.

[default]
index = default
_rcvbuf = 1572864
host = $decideOnStartup

[blacklist:$SPLUNK_HOME/etc/auth]

[blacklist:$SPLUNK_HOME/etc/passwd]

[monitor://$SPLUNK_HOME/var/log/splunk]
index = _internal

[monitor://$SPLUNK_HOME/var/log/watchdog/watchdog.log*]
index = _internal

[monitor://$SPLUNK_HOME/var/log/splunk/license_usage_summary.log]
index = _telemetry

[monitor://$SPLUNK_HOME/var/log/splunk/splunk_instrumentation_cloud.log*]
index = _telemetry
sourcetype = splunk_cloud_telemetry

[monitor://$SPLUNK_HOME/etc/splunk.version]
_TCP_ROUTING = *
index = _internal
sourcetype=splunk_version

[batch://$SPLUNK_HOME/var/run/splunk/search_telemetry/*search_telemetry.json]
move_policy = sinkhole
index = _introspection
sourcetype = search_telemetry
crcSalt = <SOURCE>
log_on_completion = 0

[batch://$SPLUNK_HOME/var/spool/splunk]
move_policy = sinkhole
crcSalt = <SOURCE>

[batch://$SPLUNK_HOME/var/spool/splunk/...stash_new]
queue = stashparsing
sourcetype = stash_new
move_policy = sinkhole
crcSalt = <SOURCE>

[fschange:$SPLUNK_HOME/etc]
#poll every 10 minutes
pollPeriod = 600
#generate audit events into the audit index, instead of fschange events
signedaudit=true
recurse=true
followLinks=false
hashMaxSize=-1
fullEvent=false
sendEventMaxSize=-1
filesPerDelay = 10
delayInMills = 100

[udp]
connection_host=ip

[tcp]
acceptFrom=*
connection_host=dns

[splunktcp]
route=has_key:_replicationBucketUUID:replicationQueue;has_key:_dstrx:typingQueue;has_key:_linebreaker:indexQueue;absent_key:_linebreaker:parsingQueue
acceptFrom=*
connection_host=ip

[script]
interval = 60.0
start_by_shell = true

[SSL]
# SSL settings
# The following provides modern TLS configuration that guarantees forward-
# secrecy and efficiency. This configuration drops support for old Splunk
# versions (Splunk 5.x and earlier).
# To add support for Splunk 5.x set sslVersions to tls and add this to the
# end of cipherSuite:
# DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:AES128-SHA
# and this, in case Diffie Hellman is not configured:
# AES256-SHA:AES128-SHA

sslVersions = tls1.2
cipherSuite = ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
ecdhCurves = prime256v1, secp384r1, secp521r1

allowSslRenegotiation = true
sslQuietShutdown = false

[isoutamo]

Hi

that inputs.conf seems to be from your system/default not from DBX. Then DB Connections inputs.conf is normally on $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/inputs.conf.

What you have on those splunk_app_db_connect*.log files? There should be entries if/when your DBX Inputs is active and those are polled.

r. Ismo

[logalsplunk007]

Oh, I'm sorry!
Here is the correct data:

cat /opt/splunk/etc/apps/splunk_app_db_connect/local/inputs.conf
[http://db-connect-http-input]
disabled = 0
token = eb26af12-bdde-4e8e-8082-80eea6840afd

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_audit_command.2020-08-27.log

Spoiler

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_audit_command.2020-08-27.log
2020-08-27 17:40:26.165 2061@testerserver [DBX-QUERY-WORKER-14] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT * FROM replica.CORE.PT_NODES_AUDIT'
2020-08-27 17:40:26.178 2061@testerserver [DBX-QUERY-WORKER-15] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT * FROM replica.CORE.PT_NODES_AUDIT'
2020-08-27 17:40:26.207 2061@testerserver [DBX-QUERY-WORKER-13] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT * FROM replica.CORE.PT_NODES_AUDIT'
2020-08-27 17:40:26.210 2061@testerserver [DBX-QUERY-WORKER-12] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT * FROM replica.CORE.PT_NODES_AUDIT'
2020-08-27 17:45:35.418 2061@testerserver [DBX-QUERY-WORKER-18] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/06/30 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 17:45:35.418 2061@testerserver [DBX-QUERY-WORKER-19] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/06/30 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 17:45:35.428 2061@testerserver [DBX-QUERY-WORKER-17] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/06/30 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 17:45:35.437 2061@testerserver [DBX-QUERY-WORKER-16] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/06/30 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 18:00:18.765 2061@testerserver [DBX-QUERY-WORKER-22] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/08/20 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 18:00:18.796 2061@testerserver [DBX-QUERY-WORKER-23] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/08/20 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 18:00:18.855 2061@testerserver [DBX-QUERY-WORKER-21] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/08/20 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)
2020-08-27 18:00:18.874 2061@testerserver [DBX-QUERY-WORKER-20] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_P ORDER BY CREATED;'ate(2020/08/20 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss)

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_audit_command.2020-08-28.log

Spoiler

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_audit_command.2020-08-28.log
2020-08-28 16:44:35.967 2061@testerserver [DBX-QUERY-WORKER-25] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 16:44:35.969 2061@testerserver [DBX-QUERY-WORKER-24] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 16:44:35.970 2061@testerserver [DBX-QUERY-WORKER-26] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 16:44:35.971 2061@testerserver [DBX-QUERY-WORKER-27] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 17:11:58.546 23246@testerserver [DBX-QUERY-WORKER-3] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 17:11:58.584 23246@testerserver [DBX-QUERY-WORKER-0] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 17:11:58.633 23246@testerserver [DBX-QUERY-WORKER-2] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'
2020-08-28 17:11:58.719 23246@testerserver [DBX-QUERY-WORKER-1] INFO com.splunk.dbx.connector.logger.AuditLogger - operation=dbxquery connection_name=REPLICA_POSTGRES stanza_name= state=success sql='SELECT to_char(CREATED, DD/MM/YYYY HH24:MI:SS.sssss) AS TIMESTAMP, CONTRACT_ID, GUID, DETAILS, SUBSTRING(DETAILS, for user \[(.*?)\]) AS USERID, SUBSTRING(DETAILS, from ip \[(.*?)\]) AS IP, SUBSTRING(DETAILS, and User Agent \[(.*)) AS http_user_agent FROM replica.CORE.PT_NODES_AUDIT WHERE CODE =CAPTCHA_VALIDATION_BEGIN AND CREATED >= to_date(2020/06/27 00:00:00, yyyy/mm/dd hh24:mi:ss.sssss) AND CREATED <= to_date(2020/07/01 23:59:59, yyyy/mm/dd hh24:mi:ss.sssss) ORDER BY CREATED;'

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_dbxquery.2020-08-27.log

Spoiler

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_dbxquery.2020-08-27.log
2020-08-27 17:40:25.388 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:40:25.389 [DBX-QUERY-WORKER-CONTROLLER] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= /opt/splunk/etc/apps/splunk_app_db_connect/local/identities.conf has been changed, invalidate cache
2020-08-27 17:40:25.390 [DBX-QUERY-WORKER-CONTROLLER] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= /opt/splunk/etc/apps/splunk_app_db_connect/local/db_connections.conf has been changed, invalidate cache
2020-08-27 17:40:25.462 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:40:25.533 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:40:25.592 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:45:34.886 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:45:34.955 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:45:35.024 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:45:35.089 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:46:01.370 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:46:01.427 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:46:01.482 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-27 17:46:01.540 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_dbxquery.2020-08-28.log

Spoiler

cat /opt/splunk/var/log/splunk/splunk_app_db_connect_dbxquery.2020-08-28.log
2020-08-28 16:44:34.930 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 16:44:35.008 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 16:44:35.066 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 16:44:35.126 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 17:09:01.412 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server_starts with max parallel 200 dbxquery requests and listening on port 9999
2020-08-28 17:11:56.067 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 17:11:56.220 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 17:11:56.285 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request
2020-08-28 17:11:56.567 [main] INFO com.splunk.dbx.command.DbxQueryServer - operation= connection_name= stanza_name= action=dbxquery_server got request