How to get the most recent event with search criteria

[AshChakor]

I have the following resultset I want to get the most recent events

Resultset A

Custom_ID	Eligibility	Start_date	End_Date	Updated_date	Code
1331931	Not Eligible	1/1/2011 0:00		11/12/2020 13:32	C
1331931	Eligible	1/1/2011 0:00		11/12/2020 13:07	C
1331931	Eligible	1/1/2011 0:00		11/12/2020 12:44	B
1331931	Eligible	1/1/2011 0:00		11/12/2020 12:33	B
1331931	Eligible	1/1/2011 0:00		11/12/2020 11:32	B
1331931	Not Applicable	1/1/2011 0:00		11/12/2020 10:52	B
1318156	Eligible	12/1/2017 0:00	12/31/2017 0:00	2/26/2020 13:36	B
1318156	Eligible	1/1/2018 0:00		2/26/2020 13:36	C
1319106	Eligible	9/9/2018 0:00		2/26/2020 13:36	D
1319106	Eligible	8/1/2016 0:00	9/8/2018 0:00	2/26/2020 13:36	B
1314052	Eligible	11/17/2016 0:00		2/26/2020 13:36	E
1314052	Eligible	1/1/2011 0:00	11/16/2016 0:00	2/26/2020 13:36	A

I am looking for the most recent eligible events

Expected output

Custom_ID	Eligibility	Start_date	End_Date	Updated_date	Code
1318156	Eligible	1/1/2018 0:00		2/26/2020 13:36	C
1319106	Eligible	9/9/2018 0:00		2/26/2020 13:36	D
1314052	Eligible	11/17/2016 0:00		2/26/2020 13:36	E

I tried following query

index=my_index |  where isnull(END_DT)| table  Custom_ID, Eligibility, Start_date, End_Date, Updated_date,Code

Custom_ID	Eligibility	Start_date	End_Date	Updated_date	Code
1331931	Not Eligible	1/1/2011 0:00		11/12/2020 13:32	C
1331931	Eligible	1/1/2011 0:00		11/12/2020 13:07	C
1331931	Eligible	1/1/2011 0:00		11/12/2020 12:44	B
1331931	Eligible	1/1/2011 0:00		11/12/2020 12:33	B
1331931	Eligible	1/1/2011 0:00		11/12/2020 11:32	B
1331931	Not Applicable	1/1/2011 0:00		11/12/2020 10:52	B
1318156	Eligible	1/1/2018 0:00		2/26/2020 13:36	C
1319106	Eligible	9/9/2018 0:00		2/26/2020 13:36	D
1314052	Eligible	11/17/2016 0:00		2/26/2020 13:36	E

How do I eliminate events with 1331931 since the most recent activity is “Not Eligible”

[richgalloway · Accepted solution]

If I understand the question correctly, this should help.  The dedup command takes the most recent event for each ID.  Then we throw out those that are not eligible.

index=foo
| dedup Custom_ID
| where Eligibility="Eligible"

[AshChakor]

@richgalloway  Thanks!

It worked! I modified the query and added isnull(End_dt) before dedup. 

index=foo
| where isnull(END_DT)
| dedup Custom_ID
| where Eligibility="Eligible"

Thanks much!

[ITWhisperer]

index=my_index 
| stats last(Eligibility) as Eligibility last(Start_date) as Start_date last(End_date) as End_date last(Updated_date) as Updated_date last(Code) as Code by Custom_ID 
| where Eligibility != "Not Eligible"
| table  Custom_ID, Eligibility, Start_date, End_Date, Updated_date,Code

However, note the inefficiency of not equals in where clauses described here