Splunk Search

How to get the latest date from a lookup

Mary666
Communicator

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:

MarblesLast_Scan_Date
Blue08/01/2020
Blue10/04/2020
Blue11/08/2021


Desired Result:

Marblesdesired_time
Blue11/08/2021


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

Labels (3)
Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

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
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...