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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...