How to filter a result with two timestamp ?

[mah]

Hi,

I have a search which I want to optimise by replace the join command : 

index="AAA" sourcetype=BBB

| stats count(OK) as OK as TOTAL by Date ID
| bin Date span=1d
| stats sum(OK)  by Date ID
| sort -Date

it returns results like this : 

Date	ID	OK
2020-09-30	XXX	123
2020-09-30	YYY	26
2020-09-29	ZZZ	763
2020-09-29	XXX	453

I want to retrieve only the last Date of each day but the only way to do that is by catching the last ID which is based on another timestamp. So I have a second request which retrieve the last ID : 

index="AAA" sourcetype=BBB

| stats max(Timestamp) as Timestamp by ID
| sort  Timestamp desc
| head 1

The result is : 

ID

XXX

I use a join command but I would like to know ik there is another way to create the search without the join.

Do you have a better solution ?

Thanks 

[ITWhisperer]

index="AAA" sourcetype=BBB
| bin Date span=1d
| stats sum(OK) by Date ID
| eventstats latest(Date) as latestDate by ID
| where Date=latestDate
| fields - latestDate
| sort -Date

[mah]

Hi, it does not work because :

- in the first query the timestamp is a field call "Date"

- in the second query the timestamp to get the last ID is "Timestamp" : the query must return just one ID 

Your search still returns events with not always the last ID : 

Date	ID	OK
2020-09-01	XXX (latest)	344
2020-09-30	AAA	867
2020-09-30	XXX (latest)	3473
2020-09-30	BBB	5387
2020-09-29	BBB	772
2020-09-29	XXX (latest)	82

But what I want is a table like that :

Date	ID	OK
2020-09-01	XXX (latest)	344
2020-09-30	XXX (latest)	3473
2020-09-29	XXX (latest)	82

[ITWhisperer · Accepted solution]

index="AAA" sourcetype=BBB
| bin Date span=1d
| stats sum(OK) by Date ID
| eventstats latest(ID) as latestID
| where ID=latestID
| fields - latestID
| sort -Date

[mah]

Great ! It is exactly my need !

Thank you !