topic Re: Timestamp fixing in Getting Data In

Timestamp fixing

uagraw01 — Fri, 08 Nov 2024 03:50:19 GMT

Hello Splunkers!!

I want to extract the _time and match it to the events fields' timestamp while ingesting to Splunk. However, even after applying the props.conf attributes setting, the results still do not match after ingestion. Please advise me on the proper settings and assist me in fixing this one.

Raw events:

2024-11-07 18:45:00.035, ID="51706", IDEVENT="313032807", EVENTTS="2024-11-07 18:29:43.175", INSERTTS="2024-11-07 18:42:05.819", SOURCE="Shuttle.DiagnosticErrorInfoLogList.28722.csv", LOCATIONOFFSET="0", LOGTIME="2024-11-07 18:29:43.175", BLOCK="2", SECTION="A9.18", SIDE="-", LOCATIONREF="10918", ALARMID="20201", RECOVERABLE="False", SHUTTLEID="Shuttle_069", ALARM="20201", LOCATIONDIR="Front"

Existing props setting:

CHARSET = UTF-8
DATETIME_CONFIG =
LINE_BREAKER = [0-9]\-[0-9]+\-[0-9]+\s[0-9]+:[0-9]+:[0-9]+.\d+
NO_BINARY_CHECK = true
category = Custom
TIME_PREFIX = EVENTTS="
TIME_FORMAT = %Y-%m-%d %H:%M:%S.%N
MAX_TIMESTAMP_LOOKAHEAD = 30
TZ = UTC

In the below screeshot still we can see _time is not properly extracted with the matching timestamp of the field name "EVENTTS".

Re: Timestamp fixing

yuanliu — Fri, 08 Nov 2024 04:47:08 GMT

The screenshot shows the timestamp as "2024-11-07 18:45:00.035", the event time as "11/7/24 6:45:00.035 PM". What exactly do not match?

Re: Timestamp fixing

uagraw01 — Fri, 08 Nov 2024 05:11:24 GMT

@yuanliu EVENTTS="2024-11-07 18:29:43.175"

I want to match _time with above timestamps.

Re: Timestamp fixing

yuanliu — Fri, 08 Nov 2024 06:47:32 GMT

I see. TIME_PREFIX is a regex. So, you need to escape quotation marks.

TIME_PREFIX = EVENTTS=\"

Re: Timestamp fixing

uagraw01 — Fri, 08 Nov 2024 06:42:12 GMT

@yuanliu You mean like as below ?

TIME_PREFIX= EVENTTS=\"

Re: Timestamp fixing

yuanliu — Fri, 08 Nov 2024 06:46:07 GMT

Yes. Sorry I erased = when editing text

Re: Timestamp fixing

uagraw01 — Fri, 08 Nov 2024 10:17:28 GMT

@yuanliu Hi, I have made the suggested changes but still _time is not matching with the raw event field (EVENTTS) timestamp. Please suggest me to do the needful.

Re: Timestamp fixing

yuanliu — Sat, 09 Nov 2024 03:26:38 GMT

Using your illustrated event as input, this is my test output

Displayed event time is 11/7/24 6:29:43.175 PM, which matches EVENTTS value of 2024-11-07 18:29:43.175 and differs from the log's timestamp of 2024-11-07 18:45:00.035.

This is my sourcetype entry:

[test-eventts] DATETIME_CONFIG = LINE_BREAKER = ([\r\n]+) NO_BINARY_CHECK = true TIME_PREFIX = EVENTTS=\" category = Custom description = https://community.splunk.com/t5/Splunk-Search/Timestamp-fixing/m-p/703912#M238560 pulldown_type = 1

The sourcetype is created from default except TIME_PREFIX. (Pro tip: Splunk's default timestamp detection is very versatile and often not worth overriding.)

Re: Timestamp fixing

uagraw01 — Sat, 09 Nov 2024 04:02:24 GMT

@yuanliu You have not used both below attributes. Can I also skip these two? Not using it will not have any impact on the consistency of data parsing, right?

TIME_FORMAT

MAX_TIME_LOOKAHEAHD

Thanks in advance and acknowledging your valuable time.

Re: Timestamp fixing

PickleRick — Sat, 09 Nov 2024 07:41:08 GMT

@yuanliuYou should normally not need to escape quotes. It's not a rex command in SPL.

@uagraw01How are you ingesting your data and where do you put those props? (On which server?)

Re: Timestamp fixing

uagraw01 — Sat, 09 Nov 2024 07:51:42 GMT

@PickleRick I am putting props setting under

/app/local

Note : Data is ingesting to Splunk from DB connect app. So I have applied all the props settings under /db_connect_ap/local

Re: Timestamp fixing

PickleRick — Sat, 09 Nov 2024 09:04:20 GMT

Ha!

So it's a modular input. With modular inputs time processing works a bit differently. See https://dev.splunk.com/enterprise/docs/developapps/manageknowledge/custominputs/modinputsscript

You need to configure your database input properly

https://docs.splunk.com/Documentation/DBX/3.18.1/DeployDBX/Createandmanagedatabaseinputs

or - if you can't find suitable combination of parameters - you need to use INGEST_EVAL to modify the _time field after initial parsing stages during ingestion.

Re: Timestamp fixing

uagraw01 — Sat, 09 Nov 2024 16:10:48 GMT

@PickleRick Thanks for your wonderful suggestion in the shared doc link. However, timestamp specification setting is only available in "Batch type" not available in "Rising Column Type". Is there any other suggestion or idea to apply this with rising column type also to avoid duplication ingestion of events?

Re: Timestamp fixing

PickleRick — Sat, 09 Nov 2024 16:28:23 GMT

As I said, if you can't configure your input that it assigns _time automatically, you're limited to using INGEST_EVAL to find the timestamp within your event and then strptime it.