Most Recent record with a specific field value

[adsFab]

Hi,

I am try to get the most recent value and search for specific status

item itemdesc _time status
ITEM01 COKE 2021-09-21 22:00:05 FAILED
ITEM01 COKE 2021-09-20 13:00:15 FAILED

ITEM02 COKE 2021-09-21 21:00:12 PASSED
ITEM02 COKE 2021-09-21 20:00:05 PASSED
ITEM02 COKE 2021-09-21 19:00:05 FAILED

ITEM03 COKE 2021-09-20 12:00:05 FAILED
ITEM03 COKE 2021-09-19 11:00:15 PASSED

Need to check most recent status by item, and pull only if status = Failed

O/p
ITEM01 COKE 2021-09-21 22:00:05 FAILED
ITEM03 COKE 2021-09-20 12:00:05 FAILED

In this case ITEM02 is ignored since most recent status is PASSED

[bowesmana · Accepted solution]

@adsFab 

Paste this search to the window, which shows you how to do this

| makeresults
| eval _raw="item itemdesc _time status
ITEM01 COKE 2021-09-21T22:00:05 FAILED
ITEM01 COKE 2021-09-20T13:00:15 FAILED
ITEM02 COKE 2021-09-21T21:00:12 PASSED
ITEM02 COKE 2021-09-21T20:00:05 PASSED
ITEM02 COKE 2021-09-21T19:00:05 FAILED
ITEM03 COKE 2021-09-20T12:00:05 FAILED
ITEM03 COKE 2021-09-19T11:00:15 PASSED"
| multikv forceheader=1
| eval _time=strptime(time, "%FT%T")
| table _time item itemdesc status
| stats latest(status) as status latest(_time) as _time by item itemdesc
| where status="FAILED"

The first part up to 'table...' is setting up your data. Just then use stats latest() to get the last status and time for each item and then just filter on the FAILED status.

Hope this helps.

[adsFab]

Thank you so much.  @bowesmana Your solution worked

[ashvinpandey]

@adsFab Try using the below command after your index and sourcetype:

| search status="FAILED"
| stats latest(status) as status latest(_time) as time by item
| convert ctime(time)

Also, If this reply helps you, an upvote would be appreciated.