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

[bsanjee]

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?

[richgalloway]

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.

[bsanjee]

@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?

[richgalloway]

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.

[bsanjee]

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,

[bsanjee]

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

[richgalloway]

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.

[bsanjeeva]

@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?

[richgalloway · Accepted solution]

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")

[bsanjee]

@richgalloway , Thanks! it worked