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