topic How to get the most recent event with search criteria in Splunk Search

How to get the most recent event with search criteria

AshChakor — Mon, 16 Nov 2020 17:04:31 GMT

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”

Re: How to get the most recent event with search criteria

ITWhisperer — Mon, 16 Nov 2020 18:04:09 GMT

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

Re: How to get the most recent event with search criteria

richgalloway — Mon, 16 Nov 2020 18:06:50 GMT

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"

Re: How to get the most recent event with search criteria

AshChakor — Mon, 16 Nov 2020 19:27:01 GMT

@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!