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
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.
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.
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.
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" ?
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 ?)
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")
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
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).
oh gosh! sorry blush that's works fine 🙂
how can I give you points ?
Great it worked, if it really helped you then you can accept/upvote my answer.