All Apps and Add-ons

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

pavel_voropaev
Engager

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
0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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 🙂

0 Karma

osunjio
New Member

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

0 Karma

season88481
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...