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 (4)
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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...