topic Re: Rex and time conversion in Splunk Search

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

bsanjee — Thu, 10 Feb 2022 03:23:04 GMT

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?

Re: Rex and time conversion

richgalloway — Sat, 05 Feb 2022 00:58:29 GMT

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.

Re: Rex and time conversion

bsanjee — Sun, 06 Feb 2022 23:11:18 GMT

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

Re: Rex and time conversion

richgalloway — Mon, 07 Feb 2022 00:54:25 GMT

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.

Re: Rex and time conversion

bsanjee — Mon, 07 Feb 2022 03:41:17 GMT

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,

Re: Rex and time conversion

bsanjee — Mon, 07 Feb 2022 15:20:03 GMT

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

Re: Rex and time conversion

richgalloway — Mon, 07 Feb 2022 18:49:29 GMT

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.

Re: Rex and time conversion

bsanjeeva — Mon, 07 Feb 2022 20:22:05 GMT

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

Re: Rex and time conversion

richgalloway — Mon, 07 Feb 2022 22:35:38 GMT

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

Re: Rex and time conversion

bsanjee — Tue, 08 Feb 2022 20:20:30 GMT

@richgalloway , Thanks! it worked