Need Help With time conversion and adding Timezone

[coldwolf7]

Hello,

I need some help. Manipulating time is something I have struggled with 

Below is the code I have 

 ((index="desktop_os") (sourcetype="itsm_remedy")) earliest=-1d@d
| search ASSIGNED_GROUP IN ("Desktop_Support_1", "Remote_Support")
``` Convert REPORTED_DATE to epoch form ```
| eval REPORTED_DATE2=strptime(REPORTED_DATE, "%Y-%m-%d %H:%M:%S")
``` Keep events reported more than 12 hours ago so are due in < 12 hours ```
| where REPORTED_DATE2 <= relative_time(now(), "-12h")
| eval MTTRSET = round((now()-REPORTED_DATE2)/3600)
| dedup INCIDENT_NUMBER
| stats values(REPORTED_DATE) AS Reported, values(DESCRIPTION) AS Title, values(ASSIGNED_GROUP) AS Group, values(ASSIGNEE) AS Assignee, LAST(STATUS_TXT) as Status,values(MTTRSET) as MTTRHours, values(STATUS_REASON_TXT) as PendStatus by INCIDENT_NUMBER
| search Status IN ("ASSIGNED", "IN PROGRESS", "PENDING")
| sort Assignee
| table Assignee MTTRHours INCIDENT_NUMBER Reported Title Title Status PendStatus 

this code runs and gives us the results we need, but the issue is that REPORTED_DATE field is off by 5 hours due to time zone issue. that is a custom field from out ticketing system that is stuck on GMT and the output looks like 

2024-01-08 09:22:49.0

I need to get that field produce a correct timezone for EST. I am struggling with making it work.

I looked at this thread but that is not working for us: Solved: How to convert date and time in UTC to EST? - Splunk Community

Any help is appreciated.

Thanks

[sshelly_splunk]

Do you have the ability to modify the sourcetype for the ticketing system data? 

You can add a single config to the input / sourcetype: 

# The following props.conf entry sets Eastern Time Zone if host matches nyc*.
[host::nyc*]
TZ = US/Eastern

Is your Splunk environment Splunk Cloud, or self-hosted? 

If cloud, you should be able to go to "Settings"->"Source Types", click on the specific sourcetype and add a key/value pair in the advanced section key="TZ", value ="US/Eastern" 

[coldwolf7]

I do not have access to update that. So I was trying to figure out how to do it with SPL

[dtburrows3 · Accepted solution]

First off, I would suggest doing what @sshelly_splunk said if possible. If not possible then you can try this method with SPL.

I see this question come over a lot and people usually respond with "its complicated", and it is.
With that said, I have been working on trying to standardize a solution by using macros and think I have a good first iteration worked out, but I'm sure still needs some more regression testing.

Here is what results look like using your sample timestamp that is assumed to be GMT but because of the user running the query's timezone preference is set to something else the epoch conversion isn't working as expected.

You can see inputs of the first macro `convert_timestamp_to_epoch(3)` are
$timestamp_field$ ----> REPORTED_DATE
$timestamp_format$ ----> %Y-%m-%d %H:%M:%S.%1N
$assumed_timezone$ ----> GMT
    This first macro should convert a timestamp to a standardized epoch time by using either a timezone found in the timestamp itself or if no timezone is found in the timestamp to revert to using the 3rd argument of the "assumed_timezone". You have the ability to leave the 3rd argument blank as well and then the catchall timezone is the user's configured timezone preference.

The second macro `convert_epoch_to_specific_timezone(3)` has the input args
$epoch$ ----> standardized_epoch (this is default fieldname of the output of the previous macro)
$timestamp_format$ ----> %Y-%m-%d %H:%M:%S.%1N
$output_timezone$ ----> EST
    This macro is taking in a epoch value and returns a human readable timestamp set to any timezone requested in the 3rd argument. (thats the idea at least)

Using the 2 macros together should be able to convert any timestamp to another with a desired timezone association.

If you are interested in the macros, shoot me a message and I can get them packaged up for you and share.

In the mean time why dont you try appending "+0000" to your REPORTED_DATE and convert to epoch including the timezone specifier
Example:

    | eval
        REPORTED_DATE2=strptime('REPORTED_DATE'."+0000", "%Y-%m-%d %H:%M:%S.%1N%z")

[coldwolf7]

In the mean time why don't you try appending "+0000" to your REPORTED_DATE and convert to epoch including the timezone specifier

This was  perfect and worked great.

I am very interested in macros I have never done them can you help me build the ones out you did 

[sshelly_splunk]

I would suggest pinging the Splunk admins, as the data is coming in with an issue, and will always be an issue until they modify the input or sourcetype.

You can add/remove whatever number of hours you need for a particular _time field, but if it gets corrected in the future, all of your searches will fail. As well, I'm not sure how things would behave if you were to drilldown from a dashboard into raw data.

It really is a simple as adding that TZ key/value to the sourcetype. What that does is makes the display of the data with different timezones seamless to end users. For example, searching for the last 60 minutes data sets configured in GMT AND CST will correctly display to the end user if TZ is configured for the sourcetypes.