topic Re: Reconfigure how timestamps appear in raw data in Getting Data In

Reconfigure how timestamps appear in raw data

sheaross — Tue, 04 Jun 2019 15:19:03 GMT

I have a date in a column with the name of Date and Time.
The current format is 02/04/19 12:50:49, but it really should be 04/02/2019 12:50:49.

I've looked in the Splunk documentation "Reconfigure how timestamps appear in raw data", but I can't seem to get it to work.
Any help would be great.

Thanks,

Shea

Re: Reconfigure how timestamps appear in raw data

amitm05 — Tue, 04 Jun 2019 17:52:26 GMT

If you are referring to the documentation provided here -
https://docs.splunk.com/Documentation/Splunk/7.2.6/Data/Configuretimestamprecognition#Configure_how_timestamps_appear_in_search_results

Additionally,
You'd have to take care of the sequence of rules you define in props and transforms which in this case basically means your timestamp should have been already processed which is formatting your timestamp currently to "02/04/19 12:50:49" format.
And your new rule should be applied after that.
Also check for correct Stanza names.

In case this does not work out, please paste here the rules you are defining to process the timestamps for your logs.

Re: Reconfigure how timestamps appear in raw data

sheaross — Wed, 30 Sep 2020 00:47:34 GMT

props.conf
[sourcetype]
FIELD_DELIMITER = ,
INDEXED_EXTRACTIONS = csv
TRANSFORMS-sortdate = resortdate

transform.conf
[resortdate]
REGEX = ^(\d{2}/\d{2}/\d{2} \d{2}:\d{2}:\d{2})
FORMAT = $2/$1/$3
DEST_KEY = _raw

This is what I have to convert the raw data to a better date format like mm/dd/yyyy.
What am I missing?

Re: Reconfigure how timestamps appear in raw data

sheaross — Wed, 30 Sep 2020 00:47:37 GMT

Sorry about that, I placed my info into the wrong area. Below is what I have so far.

props.conf
[sourcetype]
FIELD_DELIMITER = ,
INDEXED_EXTRACTIONS = csv
TRANSFORMS-sortdate = resortdate

transform.conf
[resortdate]
REGEX = ^(\d{2}/\d{2}/\d{2} \d{2}:\d{2}:\d{2})
FORMAT = $2/$1/$3
DEST_KEY = _raw

This is what I have to convert the raw data to a better date format like mm/dd/yyyy.
What am I missing?

Re: Reconfigure how timestamps appear in raw data

amitm05 — Wed, 05 Jun 2019 06:03:14 GMT

Seems to be a problem here with the REGEX you are using.
You are using ^(\d{2}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}) and then a Format of $2/$1/$3 to provide you mm/dd/yyyy
At $3 you are extracting 2 digits only.

Re: Reconfigure how timestamps appear in raw data

sheaross — Wed, 30 Sep 2020 00:49:17 GMT

This is what I have now, it's strange it works somewhat. It will resort the data on some but not all of them for the month. Once I do a search between dates, it will not display all records for that month because some dates were not resorted. Any ideas?

[sourcetype]
TRANSFORMS-sortdate = resortdate
FIELD_DELIMITER = ,
NO_BINARY_CHECK = true
TIMESTAMP_FIELDS = Date and Time
category = Custom
disabled = false

[resortdate]
REGEX = ^(\d{2})\/(\d{2})\/(\d{2})\s([^/]+)
FORMAT = $2/$1/$3 $4
DEST_KEY = _raw

Re: Reconfigure how timestamps appear in raw data

amitm05 — Thu, 06 Jun 2019 05:27:36 GMT

Either it should be processing all OR none. Can you check the ones that arent getting processed have the same time format as the others that are getting processed.
If not, the solution would still be with your Regex.

I think $4 might not be usable here at all and can be removed.

Re: Reconfigure how timestamps appear in raw data

sheaross — Wed, 30 Sep 2020 00:49:31 GMT

I checked and all dates and times are in the same format. The regex is changing the actual raw data for the dates. So that's working. It just that some dates are converting over to the _time to where I can do a search on these dates. I have about 30 records that are not converting over to the _time. Maybe this is causing the issue with the dates converting to _time. On one record the file date is 11/04/19, the raw data is 04/11/19, but the _time data is 5/4/19 with this error.

A possible timestamp match (Mon Nov 4 09:08:30 2019) is outside of the acceptable time window. If this timestamp is correct, consider adjusting MAX_DAYS_AGO and MAX_DAYS_HENCE.

Failed to parse timestamp in first MAX_TIMESTAMP_LOOKAHEAD(17) characters of event. Defaulting to timestamp of previous event(Sat May 4 06:19:26 2019).

It's very strange, because the record right after the one listed above has the exact same date and time and the _raw data and _time data works.

That's just one example, another example is on one record the file date is 02/04/19, the raw data is 04/02/19, but the _time data is 2/4/19.

Re: Reconfigure how timestamps appear in raw data

sheaross — Thu, 06 Jun 2019 15:53:21 GMT

This setting in the transform config file will convert the date to what I need in the raw data.

REGEX = ^(\d{2})\/(\d{2})\/(\d{2})\s([^/]+)
FORMAT = $2/$1/$3 $4

But, when I add the data and on the create sourcetype page to check the data, switch to raw data only the date will show and not the rest of the raw data. I think you are right, it has something to do with the regex.

Re: Reconfigure how timestamps appear in raw data

amitm05 — Fri, 07 Jun 2019 06:01:36 GMT

Can you paste the 2 raw events here. One that is getting correct time format and the one that is not .

Re: Reconfigure how timestamps appear in raw data

sheaross — Fri, 07 Jun 2019 12:23:47 GMT

These two rows will process to _raw data, but not to _time data.
02/04/19 12:50:49,userid,Processing,REV,10,,Dimension 2 Code,Modification,2024,2024,2033,2033
05/04/19 06:19:26,userid,Account,000000,,,Last Date Modified,Modification,2018-11-05,11/05/18,2019-04-05,04/05/19

These two rows have the same date, the second row will process to _raw data and to _time data. The first row is where I get the error mentioned earlier to _raw data, but for _time data it reverts back to the previous record and converts the _time to 05/04/19.
11/04/19 09:08:30,userid,General,,,,Allow From,Modification,2019-03-01,03/01/19,2019-04-01,04/01/19
11/04/19 09:08:30,userid,General Setup,,,,Allow To,Modification,2019-04-30,04/30/19,2019-05-01,05/01/19

Re: Reconfigure how timestamps appear in raw data

harsmarvania57 — Fri, 07 Jun 2019 13:11:28 GMT

Sorry to jump in this conversation but timestamp parsing happen in Aggregation Queue and transforms.conf REGEX & FORMAT happen in Typing Queue. And flow is like ... -> Aggregation Queue -> Typing Queue -> .... so as per above config _time will not change.
Have a look at how Indexing works https://wiki.splunk.com/Community:HowIndexingWorks so you'll get better idea.

Additionally when you use INDEXED_EXTRACTIONS it will skip certain queues.

Re: Reconfigure how timestamps appear in raw data

sheaross — Mon, 10 Jun 2019 12:39:45 GMT

Thank you for the input, but how do I get the date resorted to where it's in the raw data and _time?

Re: Reconfigure how timestamps appear in raw data

sheaross — Mon, 10 Jun 2019 13:36:06 GMT

I added this in the props.conf and it converted the _raw data and _time data along with the regex in the transforms.conf.

props.conf
TIME_FORMAT = %d/%m/%y %H:%M:%S

transforms.conf
REGEX = ^(\d{2})\/(\d{2})\/(\d{2})\s(.*)
FORMAT = $2/$1/$3 $4

TIME_FORMAT = %d/%m/%y %H:%M:%S