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!

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! 🌈 In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...