How to find the earliest date in a multivalue field

[nfieglein]

I have a multivalue field which contains date strings. I would like to find the earliest one of the field and set a new variable to that value. Foreach seems to choke on multivalue fields. Any ideas would be grand.

[nfieglein · Accepted solution]

I am going to give both musskopf and myself a partial answer on this. His definitely find the right value in a single date multivalue and you could probably do the same thing with all of the date fields in an event. Mine is a bit more involved, but I only have one pass to get the earliest/latest date. You choose the one you like.

My code:

convert mktime(*Date) as *DateEpoch timeformat="%a %b %d %H:%M:%S %Z %Y"
 | eval CreateDateEpoch=now()+(60*60*24*365)
 | foreach *DateEpoch
 [eval CreateDateEpoch= if (typeof('<<FIELD>>')=="Invalid", CreateDateEpoch, min(CreateDateEpoch, '<<FIELD>>'))]
 | eval CreateDate = strftime(CreateDateEpoch, "%m/%d/%Y %H:%M:%S")

Musskopf's code:

| gentimes start=0 end=1 | eval mv="Fri Oct 31 00:00:00 CDT 2014,Mon Oct 27 00:00:00 CDT 2014,Sat Nov 01 00:00:00 CDT 2014,Thu Oct 30 00:00:00 CDT 2014,Tue Oct 28 00:00:00 CDT 2014,Wed Oct 29 00:00:00 CDT 2014" | makemv delim="," mv
 | eval datemv=strptime(mv,"%a %b %d %H:%M:%S CDT %Y")
 | eval most_recent_seconds=max(datemv)
 | eval most_recent_human=strftime(most_recent_seconds,"%a %b %d %H:%M:%S CDT %Y")

[musskopf]

Could you pls provide an example how your MV field looks like?

[nfieglein]

LogEntry.Content.CommentSegmentGrp.CommentSegment{}.CommentDate

Fri Oct 31 00:00:00 CDT 2014

Mon Oct 27 00:00:00 CDT 2014
Sat Nov 01 00:00:00 CDT 2014
Thu Oct 30 00:00:00 CDT 2014
Tue Oct 28 00:00:00 CDT 2014
Wed Oct 29 00:00:00 CDT 2014

[musskopf]

Hi nfieglein,

Here an approach might work...

the first 3 commands are just to generate a similar MV field you have

| gentimes start=0 end=1 | eval mv="Fri Oct 31 00:00:00 CDT 2014,Mon Oct 27 00:00:00 CDT 2014,Sat Nov 01 00:00:00 CDT 2014,Thu Oct 30 00:00:00 CDT 2014,Tue Oct 28 00:00:00 CDT 2014,Wed Oct 29 00:00:00 CDT 2014" | makemv delim="," mv
| eval datemv=strptime(mv,"%a %b %d %H:%M:%S CDT %Y")
| eval most_recent_seconds=max(datemv)
| eval most_recent_human=strftime(most_recent_seconds,"%a %b %d %H:%M:%S CDT %Y")

The idea was to first convert your date to seconds, and them select the most recent. After that you might wish to convert back to date format.

If you're using Splunk 6.2 there is a new command named mvsort which might help as well. Or have a look here, this guy created his own mvdedup command with the sort feature: http://answers.splunk.com/answers/11394/is-it-possible-to-sort-or-reorder-a-multivalue-field.html

[nfieglein]

Since I wanted to span multiple date fields, I came up with this code that seems to be working.

convert mktime(*Date) as *DateEpoch timeformat="%a %b %d %H:%M:%S %Z %Y"
| eval CreateDateEpoch=now()+(60*60*24*365)
| foreach *DateEpoch
[eval CreateDateEpoch= if (typeof('<<FIELD>>')=="Invalid", CreateDateEpoch, min(CreateDateEpoch, '<<FIELD>>'))]
| eval CreateDate = strftime(CreateDateEpoch, "%m/%d/%Y %H:%M:%S")