eval last event of 3 date fields

[splunkLPN]

Hello,
3 date fields (A B C) :
in the source file |20180830|NULL|20180223
How can I compare this 3 dates and extract a new field : LastEvent ?
the LastEvent value in this case is 20180830

Il suppose | fillnull value="0" to begin
but for the rest ? 🙂
thank's for your help

[harsmarvania57 · Accepted solution]

Hi @splunkLPN,

You need to use split and mvindex to achieve that.

I have created run anywhere search which is extracting correct result.

| makeresults
| eval date="|20180830|NULL|20180223"
| eval date_expand=split(date,"|")
| eval correct_date=mvindex(date_expand,1)

Please give it a try and modify query based on your requirement.

[splunkLPN]

Thank you. I extracted a new field that I called "date". | table date give the good result :

    |20140327|NULL|20140327
    |20080901|NULL|20080901
    |20000412|20000412|20000412
    |20110403|NULL|20110403

if I try

|makeresults 
|eval date_expand=split(date,"|") 
|eval correct_date=mvindex(date_expand,1)) | table correct_date

I've got "Error in 'eval' command: The expression is malformed."

in "date_expand,1", the number one give the jungest date or the first position ? I need to extract the jungest date.

[harsmarvania57]

First you need to remove |makeresults from your query and you have given extra ) while creating correct_date field, your query will be like

<yourBaseQuery>
| fields date
|eval date_expand=split(date,"|") 
|eval correct_date=mvindex(date_expand,1) | table correct_date

mvindex(date_expand,1) will provide date from first position. Can you please explain "jungest date" ?

[splunkLPN]

Here are samples and the good answer after :

|20140327|NULL|20140327 : 20140327
|20080901|NULL|20080902 : 20080902
|20000415|20000412|20000412 : 20000415
|20110402|NULL|20110403 : 20110403

3 fields A, B, C , how keep the last event ? (or how compare 3 date field ?)

[harsmarvania57]

Try this one

<yourquery>
| fields date
| eval date_expand=split(date,"|")
| eval first_field=mvindex(date_expand,1), second_field=mvindex(date_expand,2), third_field=mvindex(date_expand,3)
| foreach *_field [ eval <<FIELD>>=if('<<FIELD>>'="NULL",0,strptime(<<FIELD>>, "%Y%m%d")) ]
| eval correct_date=strftime(if(first_field >= second_field, if(first_field >= third_field, first_field, if (second_field >= third_field, second_field, third_field)), second_field), "%Y%m%d")

[splunkLPN]

thank's a lot for the time spent to share your knowledge. If I good understand no need to split the line to create fields already existing, (I 've only put the raw because it's sometimes easier) : DAT_DER_IMAP, DAT_DER_POP, DAT_DER_WEB are the names of the fields.

| foreach DAT_DER_* [ eval <<FIELD>>=if('<<FIELD>>'="NULL",0,strptime(<<FIELD>>, "%Y%m%d")) ]
| eval correct_date=strftime(if(first_field >= second_field, if(first_field >= third_field, first_field, if (second_field >= third_field, second_field, third_field)), second_field), "%Y%m%d")
| table login_forgee correct_date datecrea

no error, but the "correct_date" column is empty

[harsmarvania57]

While calculating correct date you need to modify first_field, second_field and third field with your actual field name (DAT_DER_IMAP, DAT_DER_POP & DAT_DER_WEB).

[splunkLPN]

oh gosh! sorry blush that's works fine 🙂
how can I give you points ?

[harsmarvania57]

Great it worked, if it really helped you then you can accept/upvote my answer.