Splunk Search
Highlighted

Stats Latest is not returning the Latest value

Path Finder

The following search returns the listed DateTime values for the field S3KeyLastModified.

index="aws-billing" source="s3://report/aws-cost-allocation-2019-03.csv" RecordType = "LinkedLineItem"
| stats values(S3KeyLastModified) AS LastModified

LastModified
2019-03-30T11:30:33.000Z
2019-03-30T20:47:45.000Z
2019-03-31T06:39:54.000Z
2019-03-31T16:40:31.000Z
2019-04-01T01:27:58.000Z
2019-04-02T16:58:12.000Z
2019-04-06T16:59:59.000Z

I need to find the latest value in that list... when I used the following:
index="aws-billing" source="s3://report/aws-cost-allocation-2019-03.csv"
| stats latest(S3KeyLastModified) AS LastModified

The result is:
LastModified
2019-04-02T16:58:12.000Z

I've event tried converting to UNIX time first and still get the same result.
index="aws-billing" source="s3://report/aws-cost-allocation-2019-03.csv"
| eval S3KeyLastModified=strptime(S3KeyLastModified, "%Y-%m-%dT%H:%M:%S.%N")
| stats latest(S3KeyLastModified) AS LastModified

LastModified
1554245892.000000

I'm stumped... any thoughts?
Thanks!

0 Karma
Highlighted

Re: Stats Latest is not returning the Latest value

Builder

Latest on a stats is using the _time (which is the event time) to calculate what happened latest. What you want is to convert LastModified to epoch time prior to your stats then use max on the epoch time:

index="aws-billing" source="s3://report/aws-cost-allocation-2019-03.csv" 
| eval LastModified=strptime(LastModified, "%Y-%m-%dT%H:%M:%S.%3NZ")
| stats max(LastModified) as LastModified
| fieldformat LastModified=strftime(LastModified, "%Y-%m-%dT%H:%M:%S.%3NZ")

Just ripping out the example you provided as a run anywhere example will prove it out:

| makeresults count=1
| eval LastModified="2019-03-30T11:30:33.000Z
2019-03-30T20:47:45.000Z
2019-03-31T06:39:54.000Z
2019-03-31T16:40:31.000Z
2019-04-01T01:27:58.000Z
2019-04-02T16:58:12.000Z
2019-04-06T16:59:59.000Z"
| makemv LastModified delim="
" 
| fields - _time
| mvexpand LastModified
| eval LastModified=strptime(LastModified, "%Y-%m-%dT%H:%M:%S.%3NZ")
| stats max(LastModified) as LastModified
| fieldformat LastModified=strftime(LastModified, "%Y-%m-%dT%H:%M:%S.%3NZ")

That returns 2019-04-06T16:59:59.000Z

If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

0 Karma
Highlighted

Re: Stats Latest is not returning the Latest value

Path Finder

Thanks! It just occurred to me that "max" might work and looking back here you confirmed.

Thanks, for your help!

0 Karma
Highlighted

Re: Stats Latest is not returning the Latest value

SplunkTrust
SplunkTrust

The latest function in stats fetches the most recent event that was indexed, which satisfied your query. So if event with LastModified=2019-04-02T16:58:12.000Z was indexed after event with LastModified=2019-04-06T16:59:59.000Z (they might be coming from different file/host), the latest command will return. The best method would be to convert to epoch and use max as @dmarling suggested below.

0 Karma