How to get the latest date from a lookup

[Mary666]

Hello All, 

Anyone know how I can get the latest date from a lookup file? I am using the script below:

| inputlookup  append=t Blue_Marbles_Report.csv  | rename "Last Scan Date" as "Last_Scan_Date" 
| eval updated=strptime(Last_Scan_Date,"%FT%T%:z")
| eval desired_time=strftime(updated, "%B %d, %Y")
| stats latest(desired_time) as desired_time

| table  Marbles, desired_time

But the latest(desired_time) does not deliver any results. This is what I have on my original file:

Marbles	Last_Scan_Date
Blue	08/01/2020
Blue	10/04/2020
Blue	11/08/2021

Desired Result:

Marbles	desired_time
Blue	11/08/2021

Hope to get some help on this, thanks in advance. 

[PickleRick]

Ok, you're trying to do something latest() is not meant for.

The earliest()/latest() stats functions return chronologically first/last occurence of given field. But the basis for ordering is not the value of this field, but the _time field.

In other means, it's roughly ewuivalent to

<...> | sort _time | stats last(<field>)

As you don't have the _time field in your outputlookup, all values are treated equally and splunk will probably treat first/last of them in any order you have at the moment as earliest()/latest().

If you need to calculate max/min timestamp you can either simply use max/min on a field containing a unix timestamp (which is a numerical field after all) or sort by tha column as @somesoni2 aleady showed and either get stats first()/last() or do head/tail.

[somesoni2 · Accepted solution]

Try this

| inputlookup  append=t Blue_Marbles_Report.csv  | rename "Last Scan Date" as "Last_Scan_Date" 
| eval updated=strptime(Last_Scan_Date,"%FT%T%:z")
| sort 1 -updated
| eval desired_time=strftime(updated, "%B %d, %Y")
| table  Marbles, desired_time