Splunk DB Connect 2: Why is no data being indexed with my current inputs.conf and outputs.conf?

[pavel_voropaev]

I've set up a DB Connect data input. From the surface, all is working properly and health shows successful connections, but I can't find any event in my index.
This is inputs.conf from DB Connect.
Splunk 6.2.4, db connect v2.
Setup is one search head and one indexer.

[root@splunksrv01 splunk]#  cat etc/apps/splunk_app_db_connect/local/inputs.conf
[rpcstart://default]
javahome = /root/apps/jdk1.8.0_51/jre
useSSL = 0
proc_pid = 5927
bindIP = *

[mi_input://SensorData]
connection = site-protector
description = Site protect "RealSecureDB"."dbo"."SensorData"
index = Security
input_timestamp_column_name = time
input_timestamp_column_number = 1
interval = 30
max_rows = 10000
mode = tail
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = SELECT\
        CONVERT(VARCHAR(50), S.AlertDateTime, 121) as time,\
        S.SensorDataRowID,\
        S.SensorDataID,\
        S.AlertName,\
        S.AlertID,\
        S.SensorName,\
        P.ProdName,\
        A.AlertTypeName,\
        S.AlertPriority,\
        S.AlertFlags,\
        CAST(ROUND( (cast(S.SensorAddressInt as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.SensorAddressInt as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.SensorAddressInt as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + \
        CAST((cast(S.SensorAddressInt as bigint) % 256 ) AS varchar(4)) as SensIP,\
        S.ProtocolID,\
        S.SourcePort,\
        S.ObjectName,\
        S.ObjectType,\
        S.SourcePortName,\
        S.DestPortName,\
        S.UserName,\
        S.ProcessingFlag,\
        S.Cleared,\
        S.HostGUID,\
        S.HostDNSName,\
        S.HostNBName,\
        S.HostNBDomain,\
        S.HostOSName,\
        S.HostOSVersion,\
        S.HostOSRevisionLevel,\
        V.VulnStatusDesc,\
        S.AlertCount,\
        S.ObservanceID,\
        S.ComponentID,\
        S.SensorGUID,\
        S.LicModule,\
        S.VLan,\
        S.VirtualSensorName,\
        S.TargetID,\
        S.SensorInterfaceName,\
        S.SrcIPv6High,\
        S.SrcIPv6Low,\
        S.DestIPv6High,\
        S.DestIPv6Low,\
        S.SensorIPv6High,\
        S.SensorIPv6Low,\
        S.CVSSBase,\
        S.CVSSTemporal,\
        S.CVSSScore,\
        S.ScanName,\
        S.Imported,\
        S.SourceLocationCode,\
        S.TargetLocationCode,\
        S.QuarantineName,\
        S.QuarantineGUID,\
        O.ObservanceTypeDesc,\
        CAST(ROUND( (cast(S.SrcAddressInt as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.SrcAddressInt as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.SrcAddressInt as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + \
        CAST((cast(S.SrcAddressInt as bigint) % 256 ) AS varchar(4)) as SrcIP,\
        CAST(ROUND( (cast(S.DestAddressInt as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.DestAddressInt as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +\
        CAST((ROUND( (cast(S.DestAddressInt as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + \
        CAST((cast(S.DestAddressInt as bigint) % 256 ) AS varchar(4)) as DestIP\
FROM "RealSecureDB"."dbo"."SensorData" AS S \
        JOIN "RealSecureDB"."dbo"."VulnStatus" AS V \
                ON S.VulnStatus = V.VulnStatus\
        JOIN "RealSecureDB"."dbo"."Products" AS P\
                ON S.ProductID = P.ProductID\
        JOIN "RealSecureDB"."dbo"."AlertType" AS A\
                ON S.AlertTypeID = A.AlertTypeID \
        JOIN "RealSecureDB"."dbo"."ObservanceType" AS O\
                ON A.ObservanceType = O.ObservanceType
source = site_protector
sourcetype = site_protector
tail_follow_only = 1
tail_rising_column_name = SensorDataRowID
tail_rising_column_number = 1
ui_query_catalog = RealSecureDB
ui_query_mode = advanced
ui_query_schema = dbo
ui_query_table = SensorData
tail_rising_column_checkpoint_value = 16000

outputs.conf:

[root@splunksrv01 splunk]#  cat etc/apps/splunk_app_db_connect/local/outputs.conf
[tcpout:indx1]
server=192.168.100.80:9997

[jcoates_splunk]

Your RPC server doesn't start because Java's in root but Splunk isn't running as root.

I'm totally guessing, but that's less silly than my first few answers 🙂

[osunjio]

Hi,
Which Db is this running on.
Check to make sure that your query runns well.
Check the data type of the columns in the table and any default value set for each data type
check rpc.log

[season88481]

At the first line of your sql query, you might not need to convert the time to vchar. Splunk might not able to identify the time format by using vchar.