Splunk Enterprise

converting splunks time

rid1
New Member

Hi there,

I have log with format like this
"timestamp_mrt": "2017-12-03T15:30:36.208Z"

but I would like to change the output becoming
"timestamp_mrt": "2017-03-12 15:30:36.208"

Do I need to convert to something first before converting to that desired result?

*edit
I tried with adding the Timestamp format %Y-%m-%d\s%H:%M:%S.%3Q
but got an error saying "could not use strp time to parse ..."

Thx

Tags (1)
0 Karma
1 Solution

mayurr98
Super Champion

okay so first anonymize data using sedcmd

| makeresults 
| eval time="\"timestamp_mrt\": \"2017-12-03T15:30:36.208Z\"" 
| rex field=time mode=sed "s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g"

[<your sourcetype>]
SEDCMD-timestamp = s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g

Refer this for more info:
https://docs.splunk.com/Documentation/Splunk/7.0.2/Data/Anonymizedata#Anonymize_data_through_a_sed_s...
Once you do this changes this will remove T OR Z from the logs.

and then apply timestamp

TIME_FORMAT = %Y-%m-%d %H:%M:%S.%3Q

Restart the server and see the changes in recent events. first see that in the logs you should get "timestamp_mrt": "2017-12-03 15:30:36.208" format and then do the TIME_FORMAT = %Y-%m-%d %H:%M:%S.%3Q to change the _time field.

Do this step by step.
let me know if this helps!

View solution in original post

0 Karma

mayurr98
Super Champion

okay so first anonymize data using sedcmd

| makeresults 
| eval time="\"timestamp_mrt\": \"2017-12-03T15:30:36.208Z\"" 
| rex field=time mode=sed "s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g"

[<your sourcetype>]
SEDCMD-timestamp = s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g

Refer this for more info:
https://docs.splunk.com/Documentation/Splunk/7.0.2/Data/Anonymizedata#Anonymize_data_through_a_sed_s...
Once you do this changes this will remove T OR Z from the logs.

and then apply timestamp

TIME_FORMAT = %Y-%m-%d %H:%M:%S.%3Q

Restart the server and see the changes in recent events. first see that in the logs you should get "timestamp_mrt": "2017-12-03 15:30:36.208" format and then do the TIME_FORMAT = %Y-%m-%d %H:%M:%S.%3Q to change the _time field.

Do this step by step.
let me know if this helps!

0 Karma

rid1
New Member

the 1st until 3rd line, I dont understand.
is that a spl query?

0 Karma

mayurr98
Super Champion

This is spl query.

 | makeresults 
 | eval time="\"timestamp_mrt\": \"2017-12-03T15:30:36.208Z\"" 
 | rex field=time mode=sed "s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g"

this is just a workaround to show if this regex is correct or not.

[<your sourcetype>]
 SEDCMD-timestamp = s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g

This is what you should do.
Refer this for more info:
https://docs.splunk.com/Documentation/Splunk/7.0.2/Data/Anonymizedata#Anonymize_data_through_a_sed_s...

refer the above link.

0 Karma

rid1
New Member

| makeresults
| eval time="\"timestamp_mrt\": \"2017-12-03T15:30:36.208Z\""
| rex field=time mode=sed "s/(\"timestamp_mrt\":\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g"

this part I dont understand, is it the spl query?

0 Karma

mayurr98
Super Champion

yes sir, it is a run anywhere SPL query. just to show you that using s/(\"timestamp_mrt\"\:\s\"[^(T|Z)]+)(T|Z)([^(T|Z)]+)(T|Z)/\1 \3/g expression, you can change timestamp in the desired format.

to do this you need to do changes in props.conf as mentioned in this link

0 Karma

493669
Super Champion

try this:

|eval timestamp_mrt=strftime(strptime(timestamp_mrt,"%Y-%d-%mT%H:%M:%S.%3NZ"),"%Y-%m-%d %H:%M:%S.%3N")

Here if your time is in year, date and month format and you need to convert it into year, month and date format then use this ... strptime convert first into epoch(in seconds) and then strftime convert it into your desired format.

0 Karma

rid1
New Member

apologise, where should I put those? in props file?

0 Karma

493669
Super Champion

I have suggested this in spl search query.
try this run anywhere search

| makeresults |eval time="2017-12-03T15:30:36.208Z"|eval time=strftime(strptime(time,"%Y-%d-%mT%H:%M:%S.%3NZ"),"%Y-%m-%d %H:%M:%S.%3N")
0 Karma

rid1
New Member

it works in spl search query but I need the latter format when I finish loading the log.

Is there anyway to do this? so splunk result will show "timestamp_mrt": "2017-03-12 15:30:36.208"
this one instead (something like parsing done during loading)

0 Karma

493669
Super Champion

if you are expecting timestamp_mrt should be your _time in splunk
then try this in props.conf:

[sourcetypename]
TIME_PREFIX = timestamp_mrt\":\s 
TIME_FORMAT = %Y-%d-%mT%H:%M:%S.%3NZ

reference: http://docs.splunk.com/Documentation/Splunk/7.0.2/Data/Configuretimestamprecognition

0 Karma

rid1
New Member

yes, expecting it to be my time, but also I want to change the format. without the "T" and "Z".
like this "timestamp_mrt": "2017-03-12 15:30:36.208"

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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