Splunk Search

How to create correct format inside a multivalue field?

Flenwy
Explorer

Hello to all,

I have a multivalue field with a date and also a null value. In addition I have the problem that the format of the date is twisted from the original data.

Hope someone can help me 🙂

example:

outputdates (mv-field)
2023-07-29 12:06:20
28-07-2023 00:03:05
null

needed result:
outputdates (mv-field)
2023-07-29 12:06:20
2023-07-28 00:03:05

i tried splitting the field with mvexpand and then using strftime to adjust the format, unfortunately this is not working and i don't know exactly why.
Do I need to transform the "outputdates" as a time field to make strtime work again?

Am any help grateful.
Many greetings,
Flenwy



Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

You can do it like this

| makeresults
| fields - _time
| eval outputdates=split("2023-07-29 12:06:20,28-07-2023 00:03:05,", ",")
``` This uses any number of strftime variants to parse the dates - the succeeding one will prevail ```
| eval outputdates=mvmap(outputdates, strftime(max(
                         strptime(outputdates, "%F %T"), 
                         strptime(outputdates, "%d-%m-%Y %T")), 
                                               "%F %T"))

mvmap is used to iterate through the MV values. The first 3 lines set up your example

 

View solution in original post

Flenwy
Explorer

Thank you all for the great help!
All your examples helped me understand some mechanics more in detail.

The solutions from @bowesmana was on the point the think i though about but i did not know how to do it.

Kind regards to all of you!

Miasm1
Explorer

 

Hello Flenwy,

Certainly! It looks like you're dealing with dates in two different formats and also null values in a multivalue field. Here's a step-by-step solution to help you address the problem:

Identify the Format: You need to identify the format of each date string and then apply the necessary transformation. In your case, you have two formats: 'YYYY-MM-DD hh:mm:ss' and 'DD-MM-YYYY hh:mm:ss'.

Handle Null Values: Since you also have null values, you need to check for them before applying any transformations.

Transform the Dates: Depending on the identified format, you can then convert the date to the required format.

Here's a snippet of code that should help you achieve your goal:

from datetime import datetime

outputdates = [
    "2023-07-29 12:06:20",
    "28-07-2023 00:03:05",
    None
]

result = []

for date in outputdates:
    if date is None:
        continue
    try:
        # If it's in 'YYYY-MM-DD' format
        parsed_date = datetime.strptime(date, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        # If it's in 'DD-MM-YYYY' format
        parsed_date = datetime.strptime(date, '%d-%m-%Y %H:%M:%S')

    result.append(parsed_date.strftime('%Y-%m-%d %H:%M:%S'))

print(result) # Output: ['2023-07-29 12:06:20', '2023-07-28 00:03:05']

 

The code snippet above reads through the outputdates list, recognizes the two formats, and standardizes them into the 'YYYY-MM-DD hh:mm:ss' format, ignoring any null values.

Remember to adjust the code according to your specific environment or programming language if you are not using Python.

bowesmana
SplunkTrust
SplunkTrust

You can do it like this

| makeresults
| fields - _time
| eval outputdates=split("2023-07-29 12:06:20,28-07-2023 00:03:05,", ",")
``` This uses any number of strftime variants to parse the dates - the succeeding one will prevail ```
| eval outputdates=mvmap(outputdates, strftime(max(
                         strptime(outputdates, "%F %T"), 
                         strptime(outputdates, "%d-%m-%Y %T")), 
                                               "%F %T"))

mvmap is used to iterate through the MV values. The first 3 lines set up your example

 

richgalloway
SplunkTrust
SplunkTrust

To convert textual dates from one format to another requires both strptime and strftime.

| eval outputdates=if(match(outputdates,"\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d"), outputdates, strftime(strptime(outputdates, "%d-%m-%Y %H:%M:%S"), "%Y-%m-%d %H:%M:%S"))

Because strptime will return null if the input doesn't match the specified format, we use if to test for the format we want to convert.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...