Splunk Search

How to convert multiple date formats in same field?

mistydennis
Communicator

I have some data coming in with multiple date formats in the same field, and I'm having trouble reporting on these dates. I'd like to keep the dates consistent - how do I create a statement to change just the dates that are in the undesirable format?

What I have:

DateAdded

2021-11-03
2/15/2022
1/13/2023

 

What I would like:

DateAdded
2021-11-03
2022-02-15
2023-01-13
Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

There are plenty of ways to do this using various forms of eval. You can use if/case to match the types then convert based on match type as suggested, or you could do something like

| eval date=coalesce(strptime(DateAdded, "%m/%d/%Y"), strptime(DateAdded, "%F"))

which would parse each date/time until one of them converts to non-null

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

There are plenty of ways to do this using various forms of eval. You can use if/case to match the types then convert based on match type as suggested, or you could do something like

| eval date=coalesce(strptime(DateAdded, "%m/%d/%Y"), strptime(DateAdded, "%F"))

which would parse each date/time until one of them converts to non-null

 

0 Karma

Tom_Lundie
Contributor

Here is a one liner:

| eval DateAdded = if(like(DateAdded, "%/%/%"), strftime(strptime(DateAdded, "%m/%d/%Y"),"%Y-%m-%d"), DateAdded)

Based off your examples, I have assumed that the dates will either be in YYYY-mm-dd format or dd/mm/YYYY format.
If there are more than two formats, you might want to break these out into multiple fields using a rex command.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...