Splunk Search

Stats Latest is not returning the Latest value

robinettdonWY
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
1 Solution

dmarling
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

somesoni2
Revered Legend

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

dmarling
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.
0 Karma

robinettdonWY
Path Finder

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

Thanks, for your help!

0 Karma
Get Updates on the Splunk Community!

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...

The Visibility Gap: Hybrid Networks and IT Services

The most forward thinking enterprises among us see their network as much more than infrastructure – it's their ...

Get Operational Insights Quickly with Natural Language on the Splunk Platform

In today’s fast-paced digital world, turning data into actionable insights is essential for success. With ...