How do you return the latest value only if it's not equal to "closed"?

[stike100]

I'm having a tough time figuring this one out for some reason. The datasource I am using contains multiple records based on the ID with a second field called status that could be equal to (Closed, Open, In Progress). What I am wanting is to only return the latest record, but only on IDs that have not been closed already. Simplified the query to focus on just this issue.

index=XXX sourcetype=DDDD 
| fields ID, status, openDate, lastUpdateDate 
| dedup ID
| where status!="Closed"
| table ID, status, openDate, lastUpdateDate

When I run it, I get all the Open and In progress latest records, but I am also getting the last open or in progress record for records that have been closed already, and I want to ignore those all together.

Hopefully that makes sense!

Thanks for any help!

[Vijeta]

Try this

 index=XXX sourcetype=DDDD 
 | fields ID, status, openDate, lastUpdateDate 
 | stats latest(status) as status ,earliest(openDate) as openDate, latest(lastUpdateDate) as UpdateDate by ID
 | where status!="Closed"
 | table ID, status, openDate, lastUpdateDate

[somesoni2]

Give this a try

index=XXX sourcetype=DDDD 
| fields ID, status, openDate, lastUpdateDate 
| stats list(status) as status values(openDate) as openDate list(lastUpdateDate) as lastUpdateDate by ID
| where isnull(mvfind(status,"Closed"))
| eval status=mvindex(status,0) | eval lastUpdateDate=mvindex(lastUpdateDate ,0)
| table ID, status, openDate, lastUpdateDate