topic Re: How to create correct format inside a multivalue field? in Splunk Search

How to create correct format inside a multivalue field?

Flenwy — Mon, 07 Aug 2023 19:26:00 GMT

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

Re: Splunk search, Correct format inside a multivalue field

richgalloway — Mon, 07 Aug 2023 13:21:36 GMT

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.

Re: How to create correct format inside a multivalue field?

bowesmana — Tue, 08 Aug 2023 05:57:55 GMT

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

Re: How to create correct format inside a multivalue field?

Miasm1 — Tue, 08 Aug 2023 07:27:00 GMT

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.

Re: How to create correct format inside a multivalue field?

Flenwy — Thu, 17 Aug 2023 13:58:00 GMT

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!