Splunk Search

eval last event of 3 date fields

splunkLPN
Path Finder

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

0 Karma
1 Solution

harsmarvania57
SplunkTrust
SplunkTrust

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.

View solution in original post

harsmarvania57
SplunkTrust
SplunkTrust

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
Path Finder

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.

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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
Path Finder

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 ?)

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...