Splunk Search

How to get the most recent event with search criteria

AshChakor
Path Finder

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”

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.

AshChakor
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...