topic Re: Splunk DB connect issue in Splunk Enterprise

Splunk DB connect issue

uagraw01 — Tue, 26 Nov 2024 04:21:56 GMT

Hello Splunkers!!

I am facing one issue while data getting ingested from DB connect plugin to Splunk. I have mentioned scenarios below. I need your help to fixing it.

In DB connect, I obtain this value at the latest with the STATUS value "FINISHED".

However, when the events come into Splunk, getting the values with the STATUS value "RELEASED" without latest timestamp (UPDATED)

What I am doing so far:

I am using rising column method to get the data into Splunk to avoid duplicate in ingestion.

Re: Splunk DB connect issue

uagraw01 — Tue, 26 Nov 2024 07:20:37 GMT

Is someone can support me on this topic ?

Re: Splunk DB connect issue

PickleRick — Tue, 26 Nov 2024 08:16:32 GMT

Most probably your DB query initially returned one status which got ingested from the input but later something within your DB changed the status. But since the TASKID is the primary identifier for the ingested records, the same TASKID will not be ingested again. Hence the discrepancy between the DB contents and the indexed data.

Re: Splunk DB connect issue

uagraw01 — Tue, 26 Nov 2024 08:38:04 GMT

Hi @PickleRick, If I replace the TASKID column with UPDATED column to rising column method, will it make a difference?

FYI : I also increased the checkpoint value from 1 to 2 and even after the second time STATUS change is RELEASED to FINISHED, that row is not ingested in splunk.

Re: Splunk DB connect issue

PickleRick — Tue, 26 Nov 2024 11:12:47 GMT

It's not about a field but more about the general layout and variability of data in your DB. Splunk works differently - once you ingest an event, it's immutable whereas the contents of a particular row in DB can change. So regardless of how you decide that one row of your results has already been ingested, it won't be ingested again even if some "secondary" fields change their values.

I don't know your data, I don't know what it represents. If you reconfigure your DB data onboarding process to ingest both states of your DB record (or whatever result set you're getting), you'll have in Splunk two separate partly duplicated events and will have to handle it somehow in search-time.

Re: Splunk DB connect issue

uagraw01 — Fri, 06 Dec 2024 11:23:04 GMT

My database contains two types of events, and I want to ensure that only the latest row for each unique TASKID is ingested into Splunk with the following requirements:

Latest Status: Only the most recent status for each TASKID should be captured, determined by the UPDATED timestamp field.
Latest Date: The row with the most recent UPDATED timestamp for each TASKID should be ingested into Splunk.
Single Count: Each TASKID should appear only once in Splunk, with no duplicates or older rows included.

Please help me achieve this requirement. Currently method I am using is "Rising column update" method. But still splunk is not ingesting a row with the latest status.

I am using below query in SQL input under DB connect.

SELECT *
FROM "DB"."KSF_OVERVIEW"
WHERE TASKIDUPDATED > ?
ORDER BY TASKIDUPDATED ASC

Below are the sample events from the database.

=====Status "FINISHED"

2024-12-06 11:50:22.984, TASKID="11933815411", TASKLABEL="11933815411", TASKIDUPDATED="11933815411 2024/12/05 19:40:47", TASKTYPEKEY="PACKGROUP", CREATED="2024-12-05 14:18:18", UPDATED="2024-12-05 19:40:47", STATUSTEXTKEY="Dynamic|TaskStatus.key{FINISHED}.textKey", CONTROLLERSTATUSTEXTKEY="Dynamic|TaskControllerStatus.taskTypeKey{PACKGROUP},key{EXECUTED}.textKey", STATUS="FINISHED", CONTROLLERSTATUS="EXECUTED", REQUIREDFINISHTIME="2024-12-06 00:00:00", STATION="PAL/Pal02", REQUIRESCUBING="0", REQUIRESQUALITYCONTROL="0", PICKINGSUBTASKCOUNT="40", TASKTYPETEXTKEY="Dynamic|TaskType.Key{PACKGROUP}.textKey", OPERATOR="1", MARSHALLINGTIME="2024-12-06 06:30:00", TSU="340447278164799274", FMBARCODE="WMC000000000341785", TSUTYPE="KKP", TOURNUMBER="2820007682", TYPE="DELIVERY", DELIVERYNUMBER="17620759", DELIVERYORDERNUMBER="3372948211", SVSSTATUS="DE_FINISHED", STORENUMBER="0000002590", STACK="11933816382", POSITION="Bottom", LCTRAINID="11935892717", MARSHALLINGAREA="WAB"

=====Status "RELEASED"

2024-12-05 14:20:13.290, TASKID="11933815411", TASKLABEL="11933815411", TASKIDUPDATED="11933815411 2024/12/05 14:18:20", TASKTYPEKEY="PACKGROUP", CREATED="2024-12-05 14:18:18", UPDATED="2024-12-05 14:18:20", STATUSTEXTKEY="Dynamic|TaskStatus.key{RELEASED}.textKey", CONTROLLERSTATUSTEXTKEY="Dynamic|TaskControllerStatus.taskTypeKey{PACKGROUP},key{CREATED}.textKey", STATUS="RELEASED", CONTROLLERSTATUS="CREATED", REQUIREDFINISHTIME="2024-12-06 00:00:00", REQUIRESCUBING="0", REQUIRESQUALITYCONTROL="0", PICKINGSUBTASKCOUNT="40", TASKTYPETEXTKEY="Dynamic|TaskType.Key{PACKGROUP}.textKey", OPERATOR="1", MARSHALLINGTIME="2024-12-06 06:30:00", TSUTYPE="KKP", TOURNUMBER="2820007682", TYPE="DELIVERY", DELIVERYNUMBER="17620759", DELIVERYORDERNUMBER="3372948211", SVSSTATUS="DE_CREATED", STORENUMBER="0000002590", STACK="11933816382", POSITION="Bottom", MARSHALLINGAREA="WAB"

Re: Splunk DB connect issue

PickleRick — Fri, 06 Dec 2024 11:29:48 GMT

Again - there is no way to update an existing event within Splunk. So you can't have only the latest status. As simple as that.

You can try to walk around that by maybe ingesting the state periodically and hold the state in a lookup or something similar but this approach doesn't scale well.

Re: Splunk DB connect issue

uagraw01 — Sun, 08 Dec 2024 14:32:34 GMT

@PickleRick I am using field name "TASKIDUPDATED" which is the combination of TASKID and UPDATED column and it is always dynamic in nature. I have given this field in the rising column and this field is changing in every run. Even after this, duplicate data is being ingested.

Re: Splunk DB connect issue

PickleRick — Sun, 08 Dec 2024 20:34:32 GMT

Whay do you mean by "duplicate" in this context? Two different values for the same TASKID? That's expected.

Re: Splunk DB connect issue

uagraw01 — Mon, 09 Dec 2024 15:06:57 GMT

@PickleRick I mean to say. The value of the TASKIDUPDATED field is always unique value after applying checkpoint value event should be ingested only once and not multiple times.

Below is the setting I am currently using for db connect.

connection = VIn disabled = 0 index = group_data index_time_mode = current interval = */10 * * * * max_rows = 0 mode = rising query = SELECT * FROM "WMCDB"."KLDGSF_ROUPOVERVIEW"\ WHERE TASKIDUPDATED < ?\ ORDER BY TASKIDUPDATED DESC query_timeout = 30 sourcetype = overview_packgroup tail_rising_column_init_ckpt_value = {"value":null,"columnType":null} tail_rising_column_name = TASKIDUPDATED tail_rising_column_number = 3 input_timestamp_column_number = 10 input_timestamp_format =

Re: Splunk DB connect issue

PickleRick — Mon, 09 Dec 2024 11:38:10 GMT

OK. Let's back up a little.

You have a record with

TASKID=1
UPDATED=1
VALUE="A"
TASKIDUPDATED="1-1"

You update the VALUE and the UPDATED field and the TASKIDUPDATED field is updated as well so you have

TASKID=1
UPDATED=2
VALUE="B"
TASKIDUPDATED="1-2"

From Splunk's point of view it's a completely different entity since your TASKIDUPDATED changed (even though from your database point of view it can still be the same record). Splunk doesn't care about state of your database. It just fetches some results from database query.

You can - to some extent - compare it to the file monitor input. If you have a log file which Splunk is monitoring and you change some sequence of bytes in the middle of that file to a different sequence, Splunk has no way of knowing that something changed - the event which had been read from that position and ingested into Splunk stays the same. (of course there can be issues when Splunk notices file that file has been truncated and decides to reread whole file or just stops reading from the file because it decides it reached the end of the file but these are beside the main point).

BTW, remember that setting a non-numeric column to bee your rising column may yield unpredictible results due to quirkness of sorting.

EDIT warning - previous version of this reply mistakenly used the same field name twice.