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
Ultra Champion

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
Ultra Champion

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
Ultra Champion

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
Ultra Champion

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
Ultra Champion

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
Ultra Champion

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

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...