Splunk Search

How to extract startTime using query and then convert the extracted startTime- which is in GMT to PST?

bsanjee
Explorer

Hi Splunkers,

Below is my sample event,

[2021-02-06 15:30:03] production.INFO: {"uri":"https:\/\/platform.ringcentral.com\/restapi\/v1.0\/account\/5706\/call-log\/SU7GHYajgzUA?view=Simple","id":"SU7GHYaeMpjgzUA","sessionId":"886240004","startTime":"2022-02-04T07:27:31-08:00","duration":36,"type":"Voice","internalType":"TollFreeNumber","direction":"Inbound","action":"Phone Call","result":"Rejected","to":{"phoneNumber":"+18558"},"from":{"name":"EAR NOS","phoneNumber":"+1509","location":"Spokane, WA"},"telephonySessionId":"s-a0d16c80326f9z135c880000"}

1. I have to extract startTime using query

2. I have to convert the extracted startTime which is in GMT to PST, again using query

I want to do 1 and 2 in the same query.

How to do this?

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The strptime function will convert the timestamp into GMT.  The strftime function will convert it into your selected time zone.

| eval displayTime = strftime(startTS, "%Y-%m-%d %H:%M:%S %Z")
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

It would help to know what you've tried already.

Use the rex command to extract the startTime field like this:

| rex "startTime\":\"(?<startTime>[^\"]+)"

There is no need to convert it to PST since it's already in PST.  The "-0800" means eight hours behind GMT, which is PST.

---
If this reply helps you, Karma would be appreciated.

bsanjee
Explorer

@richgalloway , thanks for the regex. I am also getting another set of events whose time is GMT. May I know how this can be achieved in the same. Query?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Extracting the same is the same.  Converting it to epoch form (so it can be manipulated) may be different, depending on the exact format.  Please share a sample GMT timestamp.

---
If this reply helps you, Karma would be appreciated.

bsanjee
Explorer

Here is the sample event with time in GMT, 

[2021-02-06 15:30:03] production.INFO:{"uri":"https:\/\/platform.ringcentral.com\/restapi\/v1.0\/account\/5706\/call-log\/SU7GHYajgzUA?view=Simple","id":"SU7GHYaeMpjgzUA","sessionId":"886240004","startTime":"2021-05-05T11:23:39.426Z","duration":36,"type":"Voice","internalType":"TollFreeNumber","direction":"Inbound","action":"Phone Call","result":"Rejected","to":{"phoneNumber":"+18558"},"from":{"name":"EARNOS","phoneNumber":"+1509","location":"Spokane, WA"},"telephonySessionId":"s-a0d16c80326f9z135c880000"}

thanks,

0 Karma

bsanjee
Explorer

@richgalloway , This is the GMT timeformat that i need to convert. 2021-05-05T11:23:39.426Z

0 Karma

richgalloway
SplunkTrust
SplunkTrust

As it turns out, those two timestamp formats are just different enough to need two different conversion strings.  It can be done in SPL like this:

| eval startTS = if(isnotnull(strptime(startTime,"%Y-%m-%d-T%H:%M:%S.%3N%Z"), strptime(startTime, "%Y-%m-%d-T%H:%M:%S.%3N%Z"), strptime(startTime, "%Y-%m-%d-T%H:%M:%S.%3N%z"))

The if function test to see if the timestamp matches one of the formats and, if it does (isnotnull) then performs the conversion using that format; otherwise, the conversion uses the other format.

---
If this reply helps you, Karma would be appreciated.
0 Karma

bsanjeeva
Explorer

@richgalloway , Please let me know how to convert time in GMT to PST using query if am receiving logs with only GMT time(ignore the other time format you are seeing in my first post). Do I have to subtract 8 hrs?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The strptime function will convert the timestamp into GMT.  The strftime function will convert it into your selected time zone.

| eval displayTime = strftime(startTS, "%Y-%m-%d %H:%M:%S %Z")
---
If this reply helps you, Karma would be appreciated.

bsanjee
Explorer

@richgalloway , Thanks! it worked

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...