topic Re: How to find the earliest date in a multivalue field in Splunk Search

How to find the earliest date in a multivalue field

nfieglein — Wed, 05 Nov 2014 22:25:03 GMT

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.

Re: How to find the earliest date in a multivalue field

musskopf — Wed, 05 Nov 2014 22:32:19 GMT

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

Re: How to find the earliest date in a multivalue field

nfieglein — Wed, 05 Nov 2014 22:46:43 GMT

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

Re: How to find the earliest date in a multivalue field

musskopf — Wed, 05 Nov 2014 23:20:25 GMT

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

Re: How to find the earliest date in a multivalue field

nfieglein — Thu, 06 Nov 2014 15:05:25 GMT

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

Re: How to find the earliest date in a multivalue field

nfieglein — Fri, 07 Nov 2014 21:30:53 GMT

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