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”

Labels (1)
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, an upvote 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, an upvote would be appreciated.

View solution in original post

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!