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".
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.
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?
@yuanliu EVENTTS="2024-11-07 18:29:43.175"
I want to match _time with above timestamps.
I see. TIME_PREFIX is a regex. So, you need to escape quotation marks.
TIME_PREFIX = EVENTTS=\"
@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
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.
@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?
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.
@yuanliu You mean like as below ?
TIME_PREFIX= EVENTTS=\"
Yes. Sorry I erased = when editing text
@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.
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.)
@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.