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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...