Splunk Search

Date and time conversion

nate_washburn
Engager

Hi, in my index I have a couple time fields that are returned via a simple search

  1. _time = 1/20/2022 1:38:55.000 PM (the Splunk-generated time)
  2. body.timestamp = 2022-01-20T21:38:45.7774493Z (the transaction time from our log)

I am trying to format the time output with the convert function but can only get the first result to return.

| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS timestamp = 2022-01-20 21:38:55
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(body.timestamp) AS timestamp2 = none

Am I missing something for the second timestamp to be returned? Thanks!

0 Karma

nate_washburn
Engager

Thanks @diogofgm  you got me started down the correct path.  With a little tweeking, here is what worked for me.

| eval body.timestamp=strptime('body.timestamp',"%Y-%m-%dT%H:%M:%S.%7NZ")
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(body.timestamp) as timestamp2
0 Karma

diogofgm
SplunkTrust
SplunkTrust

Yes, you are. :slightly_smiling_face: ctime expects an epoch which _time is and your body.timestamp is not. 

Fear not as you can change that with strptime. Also you need to rename the field to something without "." as some things do not with with . on field names.

 

 

| makeresults 
| eval body.timestamp="2022-01-20T21:38:45.7774493Z"
| rename "body.timestamp" AS timestamp2
| eval timestamp2 = strptime(timestamp2,"%Y-%m-%dT%H:%M:%S.%7NZ")

 

 


After this you can use ctime or strftime to format the timestamp to what you want.

------------
Hope I was able to help you. If so, some karma would be appreciated.
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...